The Tottenham problem is a Discounted Cash Flow analysis, set up the FCF formula; and discount a
Question:
The Tottenham problem is a Discounted Cash Flow analysis, set up the FCF formula; and discount a series of cash flows.
What data do we need to conduct DCF analysis? For the base case set up, we need:
- To calculate free cash flows
- Income Statement (in Tottenham problem)
- EBIT
- Depreciation
- Taxes (EBIT * Tax Rate)
- Balance Sheet
- Change in NWC - For the Tottenham problem, we are going to calculate NWC for year 0 and year 1 and calculate change in NWC then assume that NWC changes in relationship to the changes in revenue. You can assume NWC in Year 0 is (43.12).
- CapEx - The problem says that in year 0, the team expects 3.3 M in capital maintenance and that this cost will grow at 4%. This will serve as the basis for the CapEx.
- Income Statement (in Tottenham problem)
- To calculate a terminal value (terminal value is the value of the cash flows from the end of the period of change (i.e., periods 1 through 12) to infinity.
- Discount Rate - the Discount Rate for the problem is 10.25%
- Growth Rate - the Growth Rate in year 12 is 4%
- Steady State Cash Flow - years 1 through 12 are the growth period for Tottenham and year 13 is steady state. Accordingly, the FCF from year 13 should be used to calculate the terminal value.
- To find the net present value of the FCF
- Discount Rate - the discount Rate for the problem is 10.25%
- FCF and Terminal Value - the best way to set this up is to calculate FCF for the years 1 through 13. Use year 13 to calculate the terminal value. Link the terminal value calculation to the cell below the year 12 FCF. Create a new row that is each year's FCF plus the terminal value row (you will only have a terminal value for year 12). This will produce a single row of cash flows that can be included in an excel NPV function or an excel cell formula.
Once you have set up the base case (that operates to match the attached example), you need to copy the base case to a new sheet/tab and modify the spreadsheet to reflect the impact of building a new stadium, signing a new player, and doing both.
Stadium
The new stadium project has a number of impacts on cash flow including
Income Statement Impacts (these will flow through to your FCF EBIT starting number)
- Incremental Attendance Revenues for all years
- Incremental Sponsorship Revenues for all years
- Incremental Operating Costs for all years
- Increased Depreciation over the 10-year life of the stadium
Impacts nor reflected in EBIT and that will have to be reflected in FCF calculation
- Capex of stadium build costs in year 1 and 2
At the Stadium tab of the attached spreadsheet, it shows one way to set up the spreadsheet to accomplish this. If your base case works correctly and the formulas are adjusted to capture the incremental impacts, these incremental impacts should flow through to FCF, enterprise value and stick valuation.
Player and Both
The new player has similar direct and indirect impacts as the stadium, and you will find these impacts in the problem narrative. For both these scenarios, you will also cop the base case spreadsheet and make adjustments. Make sure that you understand the limitation on new player revenue associated with playing at the old stadium.
Applied Statistics From Bivariate Through Multivariate Techniques
ISBN: 9781412991346
2nd Edition
Authors: Rebecca M. Warner