Part 1 Open the file HW4.xlsx. You will notice that there are


Question Description:

35

Part 1 Open the file HW4.xlsx. You will notice that there are five worksheets, or tabs, in the file. In this part of the homework, you will not use the Accounting Earnings, FCF, or NPV Profile tabs. The revenue forecasts and depreciation and capital maintenance estimates are in the leftmost tab. These forecasts are identical to those in the lecture notes for the class. (a) Please enter the name of each person submitting the assignment in the appropriate spaces in the Rev. and Dep. Proj. tab. The cells are highlighted in grey. – Just put my name. Steve Tebow (b) Go to the Book Values tab and complete the section on Project Fixed Asset Book Values for years 0 and 1 of the projections. Again the relevant cells are highlighted in grey. Initially there is no depreciation and no capital maintenance, since you are just starting the project, so all you have to do for the first two years is to enter the Book Value of Fixed Assets as 2000 in year 0 and 2000 + 1000 in year 1. Now move to year 2. In year 2, depreciation and capital maintenance start to kick in. Compute the Depreciation on Fixed Assets for year 2 first and then the Capital Maintenance for year 2. Finally compute the year 2 Book Value of Fixed Assets as last year’s book value, plus new investments (including capital maintenance investments), minus depreciation. You should compare your results to the calculations in the class notes to make sure that you have not made an error somewhere. Finally, do the same thing for years 3 to 10, and again check your results against the notes. (c) Compute the Book Value of Working Capital each year as 5% of projected total revenues (Magic Kingdom + Epcot + Resorts). The cells to be completed on line 13 are highlighted in grey. You should again compare your results to the calculations in the class notes to make sure that you have not made an error somewhere. (d) Compute the Total Fixed and Working Capital each year as Book Value of Fixed Assets + Book Value of Working Capital. The cells to be completed on line 16 are highlighted in grey. You should again compare your results to the calculations in the class notes. (e) Complete the Pre-Project Investment section. This deals with the fact that Disney has already spent $500 million researching the location and getting the needed licenses for the park. This $500 million pre-project investment is to be depreciated straight line over 10 years. Part 2 In this part of the assignment, you will compute the accounting earnings on the project and estimate the average project ROIC over the first 10 years. (a) Go to the Accounting Earnings tab and complete the section on Project Revenues Values for the 10 years of the projections. The relevant cells on lines 4 to 7 are highlighted in grey. (b) Complete the section on Direct Operating Expenses for the 10 years of the projections. The relevant cells on lines 10 to 13 are highlighted in grey. The assumptions you need are on page 7 of the notes for the class. You should compare your results to the calculations in the notes to make sure that you have not made an error somewhere. (c) Complete the section on Additional Expenses for the 10 years of the projections. The relevant cells on lines 16 to 17 are highlighted in grey. (d) Complete the section on NOPAT estimates for the 10 years of the projections and compare your results to the calculations in the class notes. The relevant cells on lines 20 to 22 are highlighted in grey. (e) Complete the section on ROIC Projections for the 10 years of the projections and compare your results to the calculations in the class notes. The relevant cells on lines 25 to 27 are highlighted in grey. The ROIC is year t is based on the invested capital in year t-1 so the year 0 ROIC cannot be computed. Compare your results to the calculations in the notes. Part 3 In this part of the assignment, you will compute the projected cash flows on the project over the first 10 years. (a) Go to the FCF tab and complete the section on Total

Answer

35