Homework - Overview & Purpose:
Part 1 (FactSet Excel Plug In, FDS Codes & Side Bar) acquaints you with the use of FactSet's Excel Plug- In/FDS Codes/Side Bar. FactSet allows you to download a variety of fundamental, market, corporate governance and M&A information ("data items") for a large sample of firms. This is a wonderful resource when you screen for comparable companies, analyze industries or search for potential investment opportunities/M&A targets. We will use FactSet's side bar to download 7 variables for 4,441 companies.
Part 2 (Complex LBO model) tests your familiarity with the LBO model for DKS that we discussed in class.
Part 3 (Simple LBO model) asks you to recreate a very simple (not full blown) LBO model for DKS. The goal is to carefully think about links and formulas that drive a particular output that you see in an equity research report. After recreating the model, you can test how sensitive a model's output variable (e.g. IRR) is with respect to the model's input variables.
Part 4 (Regulation) familiarizes you with commonly filed SEC forms - you examine for approx. 20 forms what they are filed for. The goal is not to explore one form in depth; the goal is to make you aware of standard forms in which firms and management frequently make company, transaction and trading information available to the public.
PART 1: FactSet Excel Plug In, FDS Codes & Side Bar
Open the excel file titled "Universe_012019" and copy all tickers provided in the worksheet titled "universe" into column A of the worksheet titled "Q1_downloads". Then, use FactSet's FDS Codes / Sidebar to download the following data items for each company:
1. Column B: Company Name
2. Column C: Current Market Value (of Equity)
3. Column D: Enterprise Value/EBITDA (use data item: FG_EVAL_EBITDA_OPER, start/end date: "end of last calendar year")
4. Column E: EBITDA margin for the latest completed period
5. Column F: Bullet Proof Rating
6. Column G: Number of M&A deals engaged in over past 60 months ("# last 60m")
7. Column H: Total value of M&A deals engaged in over past 60 months ("v last 60m")
Recommended steps:
1. For the first ticker, create the formulas (using e.g. sidebar and cell-referencing the ticker), then go to the FactSet Ribbon and click on "refresh".
2. If the formulas work, copy them down for the remaining tickers.
3. Select all cells that show you "#calc", go to the FactSet Ribbon and click on "refresh >> selection".
4. You will see a "#N/A" error message if FactSet cannot download a variable for a particular ticker. To show a blank cell instead of the error message, change the "FDS" code (or "FDSR"/"FDSC" code) to "FDSB" code (or "FDSRB"/"FDSCB" code). You can do this by simply adding a "B" to the formula. I'd recommend you manually only change the formulas for the first ticker and then copy the formulas down. You will not need to refresh the FactSet screen.
Once the cells are populated with values (or remain blank if data is unavailable), copy the table to a new Excel tab using "alt E+S+V" or "alt E+S+U" (in other words: use paste special to only copy the values or values and number formats; but do not copy the formulas).
1. Show all 7 downloaded data items for the ticker "BA"
Then, sort all tickers based on "# last 60m" (column G) and look at the company names:
2. List the company name and number for "# last 60m" of the 4 companies with the highest number of M&A deals engaged in over the past 60 months. Which industry is represented most frequently?
Now, sort all tickers based on "v last 60m" (column H).
3. List the company name and value for "v last 60m" of the 4 companies with the highest total value of M&A deals engaged in over the past 60 months. Which industries are represented?
PART 2: Complex LBO Model
1. Open the file titled "DKS_final 2020 HW" - this is the LBO model that we discussed in class. If the step up in PPE at the time of the acquisition is $500,000,000 and the non-depreciable amount of existing PPE at the time of the acquisition is $10,000,000...
a. ...what is the amount for total depreciation in FY 2016 using the complex depreciation schedule? Hint: you need to change two numbers in the model and one non-numeric setting
b. ...what is the end of period balance for PPE in FY 2016 using the complex depreciation schedule?
2. Open the model titled "DKS_final 2020 HW" and ignore any changes that you made in part 2.1.
If Term Loan A's term is reduced from 5 years to 2 years...
a. ...which rows in the model's income statement are affected (i.e. change), if any? List the exact name of the income statement row as shown in column B.
3. Open the model titled "DKS_final 2020 HW" and ignore any changes that you made in parts 2.1 and
2.2. What is the financial sponsor's IRR if you eliminate the equity "sweetener" that is provided at exit to holders of preferred stock? In other words: what is the financial sponsor's IRR if holders of preferred stock receive no share of the equity value upon exit?
4. Open the model titled "DKS_final 2020 HW" and ignore any changes that you made in parts 2.1, 2.2 and 2.3. Why does the third sensitivity table (IRR>exit multiple/tax structure) show the same IRR for asset and stock structures? Limit your answer to max. 3 sentences.
PART 3: Simple LBO Model
Instructions: Please re-build in Excel the simple LBO model for DKS that is displayed on page 2 of the UBS equity research report that is uploaded on Canvas under material>classes 6,7 ("UBS_DKS report"). You can see that this model is not a fullblown 3-F/S model. Instead, this model forecasts FCFF based on forecasts for EBIT-Taxes+D&A-Capex-Change in WC1. Further, you can see in the report that there is no fixed amortization schedule for debt - instead, debt is only repaid when FCFF after interest is positive. If FCFF after interest is positive, the full amount is used for debt repayment (and senior debt is fully repaid before subordinated debt is repaid. Assume that when senior debt is repaid, the two senior debt types are repaid proportionately based on their amounts listed in 'sources of funds').
When you calculate interest expense, please base your calculation on average debt balances [(EOP prior year + EOP current year)/2]. Using average debt balances and the interest rates that are provided in figure 1 in the UBS model will give you slightly different interest expense than shown in the model.
That's fine. One goal of this homework exercise is to build a model using average debt balances to calculate interest expense.
When you rebuild the model
- please do not add any additional features and do not make any assumptions that are not implied by the numbers/information provided to you.
- only hardcode numbers that you cannot calculate with a formula (i.e. minimize the number of hardcoded numbers). Please do hardcode D&A, Capex and Change in WC (as nominal numbers) because the model doesn't indicate the numbers' driver.
- project annual revenue and EBIT based on hardcoded historical numbers for revenue and EBIT (2012) and hardcoded annual revenue growth rates and EBIT margins for 2013-2021.
- show hardcoded numbers as blue numbers with yellow background. Show formulas as black numbers with white background
Questions:
1. Provide a picture of your completed model. In Excel, select all cells in your model and press 'Ctrl+C'. Then, put your cursor in the textbox of your answer file and press 'Alt+E+S+P'. If needed, resize the inserted picture so that it fits into the textbox in the answer file
2. Change the revenue growth rate for 2014 in your model to (12%), i.e. negative 12%. Assume that any cash shortfall in 2014 is funded through the beginning-of-period cash balance in 2014. Do not add additional assumptions to the model.
What are your new numbers for:
- Interest expense 2015
- After tax EBIT + Equity income 2015
- FCFF 2015
- Total Debt, EOP 2015
- 3-YR-IRR (2014-2017 as in report)
3. Provide a picture of your completed figure 2 based on part 2.2. (i.e. after changing the revenue growth rate for 2014)
4. Using your completed model based on part 2.2.: what would DKS's cash balance be at the BOP 2015? Hint: you also need to look into the sources/uses table.
PART 4: Regulation
Questions:
1. Please provide for each of the SEC forms and schedules shown on the below slide a brief explanation what the form or schedule is filed for (you can limit your explanation to a short phrase/sentence). Note: the pre-formatted answer file contains a list of the forms (so you won't need to re-type them from the slide)
2. For each form and schedule, please specify if it is a periodic filing (per), an ownership filing (ow), a transactions-related proxy filing (tra), a registration filing (reg) or a delisting filing (del). Note: Place an X in the pre-formatted answer file's respective cell. If multiple cells apply for a specific form, mark them all.
3. Which form has an estimated average time burden of 671 hours? Which form has an estimated burden of > 4,000 hours?
4. For each of the exhibits (note: exhibits are different than forms), please specify what information the exhibit contains
Attachment:- Complex LBO Model.rar