Please turn in an EXCEL file named with your last name and the name of your firm. Make sure your name and the name of your firm appears at the top of EVERY WORKSHEET and in the name of that worksheet!!! The easiest way to do this is with the header in the Zeroed DCF file you choose for continuing value. You can turn the DCF in as soon as it is finished.The sooner you turn it in the sooner you get it back.. A😊
- Prepare a table of historical values for your target firm similar to the ones we have been using for the DCF examples. Make sure that your historical values table shows the name of the firm that is covered somewhere close to the historical percentages.
IF your depreciation and amortization % is more than 3% greater than your capex % look in the target firm’s 10-K for the note on amortization to see if the dollar amount of amortization is what is causing the increase, as in Costar. If so note the source of the amortization in your assumptions and show the dollar amount of amortization each year and the corresponding percentage of revenue at the bottom of your assumptions page.
IF your TARGET firm has stock based compensation greater than 3% of revenue listed at the top of the cash flow statement on Bloomberg then adjust the labels of the DCF and add the amount of the stock based compensation to depreciation and amortization. Show the amount of stock based compensation in dollar terms and as a percentage of revenue at the bottom of the DCF assumptions page.
IF you make the adjustment for stock based compensation in the DCF assumptions you also have to go to the 10K or maybe to a proxy statement that is referenced in the 10-K and get the estimated value of outstanding stock options held by top managers. Subtract this amount as a stronger claim at the bottom of the DCF, as with Costar.
2) Explain each assumption you will use to build your DCF for the target. A text box works well for this. I have posted samples of a fabulous way to present your DCF and the assumptions in an Excel page all on one page[1]. You can also look at the DCF section of the posted sample projects. For now use the WACC on Bloomberg as your discount rate.
The Revenue Growth forecast is especially important since it determines whether you can use a multiple (of Sales, EBITDA or EBIT) (as for WMT), or a constant growth model (as for Costco) or a two-stage growth model (as for Costar).
Explain your thinking carefully here! If the firm is not growing at something close to your assumed long-term normal growth rate you have to use a two-stage growth model to compute Continuing Value, as for Costar.
EXPLAIN YOUR LOGIC for each of the DCF annual assumptions and your choice for the Continuing Value calculation and each of the assumptions in the Continuing Value calculation.
Remember the rules to operationalize a DCF
3) Build your DCF with your initial set of assumptions. Show the current and past two year price history of the stock at the bottom of the DCF, either with an inserted graph or with a text box.
5) Send both the Inputs worksheet for your target and the DCF page to me and to Michael electronically by midnight in a single Excel file.
6) Name your file. Include your last name and the name of the firm in both the title of the file and the top of EVERY Worksheet. DO NOT CALL IT DCF OR 425 PLEASE… Note: you don’t have to worry about sensitivity analysis yet. You will have a chance to put that into the final version after the DCF First Pass is checked.. AJ
[1] Note: I used to ask for the historical information for your target firm and two competitors but we have not ended up using that information lately. You will see it in the samples though, just ignore that.
