FIN 301 Penn State World Campus Final Excel project (COMPLETE SOLUTION)

The case discusses the dilemma of the CFO of PSUWC Energy LLC, an energy generating firm, who needs to present his recommendation to the company Board regarding a proposed investment in a new technology. Due to malware issues, he has partially lost the spreadsheets which his analysts had developed. He has available to him a partial income statement and some information regarding the project. He needs to re-work the spreadsheet with some information saved and come up with a justification for his recommendation(s). Put yourself in the shoes of the CFO and calculate the NPV for the proposed project.

Suggested Steps

1. Start by calculating the WACC (Weighted Average Cost of Capital) for the firm. To calculate the WACC, you will need the Cost of Debt, Cost of Equity, and the Tax Rate for the firm. The old cost of debt can be calculated from the information given in the case regarding the firm’s bonds. However, it is important to consider the effect of the new capital raised on the capital structure of the firm. So be sure to consider the new capital structure and the cost of debt corresponding to the new capital structure. Information regarding the current cost of debt as a function of the D/E ratio can be found in the worksheet “Rd with DtoE”.
2. The cost of equity can be estimated from the information regarding a comparable firm given in the case. Remember that the CAPM (Capital Asset Pricing Model) should be used to calculate the cost of equity. This may need you to make some assumptions. If you make any assumptions, please be sure to state them clearly in the “Answer Sheet” worksheet in the excel file. You should have realized by now that such calculations are not an exact science and often you may have to make certain assumptions. It is important to justify that these assumptions are reasonable.
3. Complete the Cash Flows section in the “Blank Template” worksheet in the excel file.
4. Once you have the calculated the final Cash flows, use these along with the WACC as the discount rate to determine the NPV of the project for the two different scenarios presented.  
5. 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 Y-axis. 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. (see case for full instructions)
6. Make a clear recommendation whether the company should accept or reject the project for each case scenario.
7. Turn in your excel sheet [named in the following Format: Lname,Fname,Sec#,FinalProject.xls(x) ] to the dropbox.