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:

1. To calculate free cash flows
   1. Income Statement (in Tottenham problem)
      1. EBIT
      2. Depreciation
      3. Taxes (EBIT * Tax Rate)
   2. Balance Sheet
      1. 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).
      2. 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.
2. 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.
   1. Discount Rate - the Discount Rate for the problem is 10.25%
   2. Growth Rate - the Growth Rate in year 12 is 4%
   3. 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.
3. To find the net present value of the FCF
   1. Discount Rate - the discount Rate for the problem is 10.25%
   2. 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. 

     

Transcribed Image Text:

AutoSave On File Home Paste V R14 27 28 29 A 1 Tottenham 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Ready Clipboard Cut Copy Format Painter Revenues Other Attendance Sponsorship Broadcast Merchandise Insert Draw Page Layout Total s B X Operating Costs Payroll Stadium Op Exp Other EBIT EBITDA Depreciation Total Window Snip Interest Taxes Net Income 5 B Calibri BI U v fx Capital Expenditures +4% per year Net Working Capital A in NWC H Font 11 Base Case Stadium Sheet3 V M C Formulas Data Review Current Year 0 2007 A^ A A 17.40 15.70 28.70 5.20 7.10 74.10 Tottenham-Base+Case+Setup +%2800264711xB0C44%29+%281%29+%281%29+%282%29+%284%29.xlsx - Last Modified: 57m ago 5.00 2.20 2.80 2.26 0.19 0.35 3.30 (43.24) (+ 5 D == Forecast Year 1 2008 18.97 17.11 50.92 56.01 61.62 16.38 17.04 17.72 1.80 1.87 1.95 69.10 74.92 81.29 E 5.85 2.29 Year 2 2009 3.56 2.46 0.38 0.72 6.75 2.38 4.37 2.69 0.59 1.09 View Help Acrobat ab Wrap Text Alignment F Year 3 2010 Merge & Center G 7.72 2.47 5.25 2.93 0.82 1.50 Year 4 2011 H 8.76 2.57 6.19 3.19 1.05 1.95 Year 5 2012 67.78 74.56 82.01 18.43 19.16 2.11 19.93 2.19 2.02 88.23 95.83 104.13 3.43 3.57 3.71 3.86 (47.13) (51.37) (55.99) (61.03) (3.89) (4.24) (4.62) Ty 20.67 22.53 24.56 26.77 31.81 29.18 26.33 18.65 20.33 22.16 24.16 28.70 31.28 34.10 37.17 40.51 48.13 52.46 5.67 6.18 6.73 7.34 44.16 8.00 10.92 8.72 9.51 7.74 8.44 9.19 10.02 11.91 12.98 80.77 88.04 95.95 104.59 114.01 124.27 135.46 General 9.88 2.68 7.20 3.48 1.30 2.42 $ 1 Year 6 2013 % 900 900 Number 90.21 20.73 2.28 113.22 J 11.05 2.78 8.27 3.79 1.57 2.91 Year 7 2014 F K 4.01 4.18 4.34 (66.53) (72.52) (79.05) (5.04) (5.50) (5.99) (6.53) Conditional Format as Cell Formatting Table Styles Styles Year 8 2015 L 12.31 13.61 2.90 3.01 10.60 9.41 4.13 4.50 1.85 2.13 3.43 3.97 Year 9 2016 99.23 109.16 120.07 21.55 22.42 23.31 2.37 2.46 2.56 123.15 134.04 145.94 37.79 34.67 31.28 34.10 41.19 37.17 67.94 57.19 62.33 10.36 12.31 11.29 14.15 15.42 147.65 160.93 175.42 16.81 M Year 10 2017 14.99 16.43 3.13 3.26 11.86 13.17 4.91 5.35 2.43 2.74 4.52 5.08 N Insert Delete Format Year 11 2018 T 17.93 3.39 14.54 Cells 5.83 3.05 5.66 O 132.08 145.29 159.82 24.25 25.22 26.22 2.66 2.77 2.88 158.99 173.28 188.92 Year 12 2019 44.90 50.90 48.94 40.51 44.16 45.93 83.95 15.21 74.06 80.72 13.42 18.32 191.21 208.42 216.76 14.63 19.97 20.77 P 19.50 3.52 15.98 6.36 3.37 6.25 Year 13 2020 AutoSum Fill Y Clear 166.21 27.27 3.00 196.48 20.28 3.66 16.62 6.61 3.50 6.51 4.52 5.28 4.70 4.88 5.08 5.49 (86.16) (93.91) (102.36) (111.58) (121.62) (126.49) (7.11) (7.75) (8.46) (9.21) (10.04) (4.87) Fulford, Christine M. FC Q V AY O Sort & Find & Filter Select Editing R S E T Share b Analyze Data Analysis T Comments Sensitivity Sensitivity U 100% AutoSave On File Home Paste V R14 27 28 29 A 1 Tottenham 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Ready Clipboard Cut Copy Format Painter Revenues Other Attendance Sponsorship Broadcast Merchandise Insert Draw Page Layout Total s B X Operating Costs Payroll Stadium Op Exp Other EBIT EBITDA Depreciation Total Window Snip Interest Taxes Net Income 5 B Calibri BI U v fx Capital Expenditures +4% per year Net Working Capital A in NWC H Font 11 Base Case Stadium Sheet3 V M C Formulas Data Review Current Year 0 2007 A^ A A 17.40 15.70 28.70 5.20 7.10 74.10 Tottenham-Base+Case+Setup +%2800264711xB0C44%29+%281%29+%281%29+%282%29+%284%29.xlsx - Last Modified: 57m ago 5.00 2.20 2.80 2.26 0.19 0.35 3.30 (43.24) (+ 5 D == Forecast Year 1 2008 18.97 17.11 50.92 56.01 61.62 16.38 17.04 17.72 1.80 1.87 1.95 69.10 74.92 81.29 E 5.85 2.29 Year 2 2009 3.56 2.46 0.38 0.72 6.75 2.38 4.37 2.69 0.59 1.09 View Help Acrobat ab Wrap Text Alignment F Year 3 2010 Merge & Center G 7.72 2.47 5.25 2.93 0.82 1.50 Year 4 2011 H 8.76 2.57 6.19 3.19 1.05 1.95 Year 5 2012 67.78 74.56 82.01 18.43 19.16 2.11 19.93 2.19 2.02 88.23 95.83 104.13 3.43 3.57 3.71 3.86 (47.13) (51.37) (55.99) (61.03) (3.89) (4.24) (4.62) Ty 20.67 22.53 24.56 26.77 31.81 29.18 26.33 18.65 20.33 22.16 24.16 28.70 31.28 34.10 37.17 40.51 48.13 52.46 5.67 6.18 6.73 7.34 44.16 8.00 10.92 8.72 9.51 7.74 8.44 9.19 10.02 11.91 12.98 80.77 88.04 95.95 104.59 114.01 124.27 135.46 General 9.88 2.68 7.20 3.48 1.30 2.42 $ 1 Year 6 2013 % 900 900 Number 90.21 20.73 2.28 113.22 J 11.05 2.78 8.27 3.79 1.57 2.91 Year 7 2014 F K 4.01 4.18 4.34 (66.53) (72.52) (79.05) (5.04) (5.50) (5.99) (6.53) Conditional Format as Cell Formatting Table Styles Styles Year 8 2015 L 12.31 13.61 2.90 3.01 10.60 9.41 4.13 4.50 1.85 2.13 3.43 3.97 Year 9 2016 99.23 109.16 120.07 21.55 22.42 23.31 2.37 2.46 2.56 123.15 134.04 145.94 37.79 34.67 31.28 34.10 41.19 37.17 67.94 57.19 62.33 10.36 12.31 11.29 14.15 15.42 147.65 160.93 175.42 16.81 M Year 10 2017 14.99 16.43 3.13 3.26 11.86 13.17 4.91 5.35 2.43 2.74 4.52 5.08 N Insert Delete Format Year 11 2018 T 17.93 3.39 14.54 Cells 5.83 3.05 5.66 O 132.08 145.29 159.82 24.25 25.22 26.22 2.66 2.77 2.88 158.99 173.28 188.92 Year 12 2019 44.90 50.90 48.94 40.51 44.16 45.93 83.95 15.21 74.06 80.72 13.42 18.32 191.21 208.42 216.76 14.63 19.97 20.77 P 19.50 3.52 15.98 6.36 3.37 6.25 Year 13 2020 AutoSum Fill Y Clear 166.21 27.27 3.00 196.48 20.28 3.66 16.62 6.61 3.50 6.51 4.52 5.28 4.70 4.88 5.08 5.49 (86.16) (93.91) (102.36) (111.58) (121.62) (126.49) (7.11) (7.75) (8.46) (9.21) (10.04) (4.87) Fulford, Christine M. FC Q V AY O Sort & Find & Filter Select Editing R S E T Share b Analyze Data Analysis T Comments Sensitivity Sensitivity U 100%

Answer rating: 100% (QA)

Here are the steps to conduct the DCF analysis for the Tottenham problem 1 Set up the base case inco... View the full answer