Details of this Paper

Part 1 Open the file HW4.xlsx. You will notice th...

Description

Solution


Question

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 FCF Projections for the 10 years of the projections. The relevant cells on lines 4 to 8 are highlighted in grey. Capital expenditures consist of project fixed investments and maintenance investments (plus, in year 0, the $500 million pre-project investment). There is no investment in working capital in year zero so start computing the change in working capital in year 1. Compare your results to the calculations in the notes. (b) Complete the section on Incremental FCF Projections for the 10 years of the projections and compare your results to the calculations in the class notes. The relevant cells on lines 11 to 15 are highlighted in grey. The assumptions regarding the add-back of the fixed SGA are described on page 7 of notes. Part 4 In this part of the assignment, you will determine the NPV profile for the project. (a) Go to the NPV Profile tab and complete the cells with the incremental cash flows for years 0 through 9. In year 10, you must add to the year 10 incremental cash flow the terminal value, i.e., the present value of the incremental cash flows in years 11 and beyond, using the constant growth formula, as on page 20 of the notes. Of course, this requires some assumption about the discount rate. We will determine the NPV for various discount rates, namely 8% to 30% in increments of 1%. So enter 8% in cell M4, 9% in M5, etc. Then link the terminal value calculation in cell L4 to the assumed discount rate in cell M4, etc. In column N, compute the NPV for each discount rate (8%, 9%, ?, 30%). For example, in cell N4 enter the formula ?=NPV(M4,C4:L4)+B4?. (b) Finally, plot the NPV against the discount rate, and compare your graph to what you have in the notes.

 

Paper#8065 | Written in 18-Jul-2015

Price : $25
SiteLock