Problem:
Spreadsheet Assignment (WACC and Capital Budgeting)
Use the project data given below to build a spreadsheet to:
1. Calculate the cash flows for all years of this project.
2. Calculate the WACC for this project.
3. Calculate the NPV and IRR for this project.
4. Use an IF statement to generate a decision for this project in a worksheet other than the worksheet that calculates the project's cash flows.
The Town Amusement Park is considering adding a new amusement park ride. The new ride equipment would cost $290,000 to buy and ship, and $40,620 for handling and assembly.
The company estimates the project life of the proposed new ride to be three years. The proposed ride equipment is expected to have a market or scrap value of $140,500 at the end of the three year project. The new ride equipment will be depreciated straight line to zero across five years. The proposed new ride will add $20,000 in new maintenance costs if the new ride equipment is purchased. The company expects added sales from the new ride to be $150,000 per year. However, the company expects the sales of their other existing rides to be reduced by $25,000 if the new ride project is adopted and purchased.
The company wants to maintain a 0.48 debt to equity ratio. The YTM on the company's existing bonds is 4.3 percent. Its tax rate is 40 percent. The company's stock is selling for $67 per share and it just paid a dividend of $5.20 per share. Dividends are expected to grow at an average constant rate of 2.9 percent per year.
Note: The NPV function in Excel is not correctly programmed to calculate NPV as the term is used in finance. The Excel NPV function calculates only the PV of the future cash flows and does not include the initial outlay in its formulation. Adjust your spreadsheet formulation to reflect this programming error. Double check your spreadsheet results with your calculator to be sure your spreadsheet results are correct.
The spreadsheet must include:
at least two worksheets- with at least one worksheet for inputs and summary of output (results)
at least 2 different types of number format
at least 2 different colors for cell highlighting
all calculations must be done within the spreadsheet
all formulated cell calculations must use cell references (the number inputs must be first listed or input in another cell)
use at least one IF statement to automatically generate a project accept/reject decision
use at least 2 built-in formulas or functions (fx),other than =IF (e.g., =sum(D3:F3), =NPV(rate,A3:A9), etc.)
Include at least 1 cell with a defined name (see the name manager under the formula tab)