FIN 301 Final Excel project complete solutions correct answers key

FIN 301 Final Excel project complete solutions correct answers key

Penn State World Campus

Final Excel project

Minimum requirements for the Project.

1. Calculate the WACC for the company.

2. Create a partial income statement incremental cash flows from this project in the Blank Template worksheet.

3. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next four years. (You can either use the EXCEL formula PV() or use mathematical formula for PV of a lump sum.)

4. Set up the EXCEL worksheet so that you are able to change the parameters in B4-B12 and E9-E10. These are highlighted in Yellow on the Blank template. Run the two cases: 75/25% split between issuing bonds/equity and a 25/75% split between issuing bonds/equity. You can do this by completing one case and then simply creating a copy of the worksheet and changing the numbers of the split (cells E9 and E10). Alternately, if you are comfortable with creating drop down boxes in excel, you can set the sheets up so that the user can choose this value from the drop down box and obtain the necessary solution. Note that it is important to use the VLOOKUP function for calculating the cost of debt for automatic updating of the excel sheet.

5. Cells B54:64 List the Debt Fraction for New Capital varying from 0% to 100%. Using the Data Table Concept, calculate the WACC and NPV for each value.

6. Using the data generated using Data Tables, plot the NPV & WACC as a function of Debt Fraction. Make sure to plot the NPV on the primary Y-axis and the WACC on the secondary Yaxis. The Debt Fraction should be on the X-axis. Alternately, you can also plot NPV & Debt Fraction as a function of WACC. If you do so, ensure that you plot the NPV on the primary Y-axis and the Debt Fraction on the secondary Y-axis. The WACC should be on the X-axis in this case.

Comment on any peculiarities you may notice in this plot and discuss the possible reasons for these (whether this plot looks different from the NPV Profiles you have studied before, where the NPV is plotted as a function of the discount rate etc). Be specific as to why there may be differences. For your reference, a typical NPV profile is plotted below cells A69-G78 using some random cash flows. Cells A69-G78 contains the data and calculations used to generate the plot shown. NOTE: There is a plethora of information available on the Internet regarding creating plots in excel

(https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=how%20to%20plot%20a%20graph%20in%20excel) and creating plots with secondary axis (https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=how+to+plot+a+graph+in+excel+with+two+y+axis ).

7. Make a clear recommendation whether the company should accept or reject the project for each case scenario.

8. If you have any discussion/explanation for any assumptions you have made or need to provide more discussion, you may do so on the worksheet titled " Answer Sheet "

9. Turn in your project in the drop box.