You had completed this assignment on 11/13/2016. However, can you provide the breakdown of how you got the WACC and NPV in excel format. I can't figure it out and need that information.

Grading Rubric Grading Rubric for Final Project in FIN 301 0 0.5 1 2 Calculation of Weights No Calculations or Calculation using book Explanations values for weights Correct Calculation using Market Rates with NO Explanation Cost of Debt Incorrect Calculation, No Calculations or Brief Explanation, Explanations Reasonable estimate Correct Calculation based on YTM with Explanation Correct Calculation using Market Rates with Explanation Total Possible Points 2 1 Cost of Equity Beta Expected Market Return Reasonable Estimate of Beta based on Correct Estimate of Beta No Calculations or information provided based on online data with Explanations online (i.e Yahoo Explanation Finance) but No Explanation 1 Estimate for Market No Calculations or Return is not reasonable Explanations based on historical market performance Explanation of Expected Returns based on historical returns with reasonable estimate 1 Explanation of Risk free rate based on current T-bond or T-bill yield 1 Incorrect Calculation with Correct Cacluation with minor errors carried through Explanation of all from above assumptions parameters with explanation 2 Estimate not based on No Calculations or current Treasury yields Explanations but reasonable no explanation Incorrect Calculation with minor errors carried No Calculations or through from above WACC Calculation Explanations assumptions with no explanation Risk-free Rate Total Possible Points from WACC calculation portion of project Cash Flow Calc 0 1 8 2 4 Total Possible Points Initial Cash Flows No Calculations or Partially correct CFs Explanations All initial CFs properly identified with brief explanation Operating CFs Few OCFs properly No Calculations or identified with no Explanations explanation Most OCFs properly identified with brief explanation Terminal CFs All terminal after tax CFs No Calculations or Partially correct CFs no properly identified with Explanations tax adjustments brief explanation 75/25 D/E Split No Calculations or Partial Calculations Explanations All PV of Cash Flows correctly calculated and Justification for use of NPV technique 2.5 25/75 D/E Split No Calculations or Partial Calculations Explanations All PV of Cash Flows correctly calculated and Explantion for underlying assumptions of case 2.5 2 All OCFs properly identified with brief explanation of all calculations (ie taxes & depr properly accounted for) 4 2 Total Possible Points from Cash Flow portion of project 0 1 No Graph, Partial Create NPV Profile for No Calculations or Calculations, Partial Varying Debt/WACC Explanations Explanation Final Decision is Final Decision by Incorrect or Not Case: Accept or Reject Stated Presentation Final Decision is correct with limited or no explanation of basis of decision and all assumptions Somewhat difficult to Unorganized find some information difficult to follow and some disorder with spelling and gramar and spelling grammar errors errors 13 2 3 Graph is correct with all Graph is mostly correct with axis properly labled with Partial Explanation Explanation 3 Final Decision is correct with partial explanation of basis of decision and all assumptions Final Decision is correct with thorough explanation of basis of decision and all assumptions 3 Very Organized, Neat, and Easy for reader to follow with all inputs and assumptions not clearly identified in one area Very Organized, Neat, and Easy for reader to follow with all inputs and assumptions clearly identified in one area 3 Total Possible Points from NPV Profiles and Presentation portion of project Total Possible Points for the Final Project 9 30 Page 1 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 c 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 Yellow on the Blank template. Run the two cases: 75/25% split between issuing bonds/equity and a 25/75% spli bonds/equity. You can do this by completing one case and then simply creating a copy of the worksheet and chan of the split (cells E9 and E10). Alternately, if you are comfortable with creating drop down boxes in excel, you ca so that the user can choose this value from the drop down box and obtain the necessary solution. Note that it is im 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 Conc WACC and NPV for each value. 6. 5) Using the data generated using Data Tables, plot the NPV & WACC as a function of Debt Fraction. Mak NPV on the primary Y-axis and the WACC on the secondary Y-axis. The Debt Fraction should be on the X-axis can also plot NPV & Debt Fraction as a function of WACC. If you do so, ensure that you plot the NPV on the p the Debt Fraction on the secondary Y-axis. The WACC should be on the X-axis in this case. (see case for full instr 6. Make a clear recommendation whether the company should accept or reject the project for each case scenario. 7. If you have any discussion/explanation for any assumptions you have made or need to provide more discussio on the worksheet titled " Answer Sheet " 8. Turn in your project in the drop box. This sheet is provided in case you want to provide explantions for your answers t Topic Weights of Debt and Equity, Wd & We Cost of Debt Calculation Beta for the Firm Expected Market Return Risk-Free Rate WACC WACC Calculation Initial Cash Flows Operating CFs Terminal CFs 75/25 D/E Split 25/75 D/E Split Create NPV Profile for Varying Debt Final Decision by Case: Accept or Reject Comments case you want to provide explantions for your answers to any questions. Please see the g Explanation/Discussion Note: Hitting Alt-Enter in excel enables you to type multiple lines in the same cell in Excel. Like This line - which was created by hitting Alt-Enter after the period in the line above instead of onl You can also copy paste from a Word document and just hit Alt-Enter where you need to go to a new line in formatted properly. We have two senerio for weight of debt and weight of equity. A choice between raising the new capital needed eith split between issuing bonds/equity or a 25/75% split between issuing bonds/equity. I have created a dropdown at E from where the debt and equity ratio get change. As per the instruction given in the file cost of the debt is taken from "Rd with DtoE" sheet of this file via vlookup get cange automatically when ratio of the debt changes As themanagement belives that given the industry they are in the most reasonable comparable publicly traded com Companhia Paranaense de Energia - COPEL so we have taken beta for this company from yahoo finance link as http://finance.yahoo.com/quote/ELP?p=ELP Expected market return is taken as market risk premium based on the S&P 500 annual expected rate of return. I ha the monthly expected return and then multiply the monthly S&P return by 12 to get the annual return. The risk free rate is taken as the US 10-YR treasury bond rate. I have taken US treasury bond rate from given link http://data.cnbc.com/quotes/US10Y As the company have only to securities debt and equity so WACC is calculated on the basis of both of them. We h senerio to calculate on with using 75/25% split between issuing bonds/equity or a 25/75% split between issuing bo WACC = Weight of the debt*Cost of Debt + Weight of Equity*Cost of equity There are two initial cash outflows one is of cost of the machine and second net increase in NWC. For calculating the operating cash flow we have to add back the depreciation to the net earnings. In this we can se operating cash flows are positive. The terminal value of cash flows are claulated at the end of the project means at year 6. We have taken slavage va value as what is invested in the year 0 and Disposal Fee which is equivalent to the amount of money raised via th debt. This meant that if he used a 75/25% split between issuing bonds/equity, he would raise 75% of the needed capital $34,000,000 by issuing bonds and the rest 25% through issuing equity. This meant that if he used a 25/75% split between issuing bonds/equity, he would raise 25% of the needed capital $34,000,000 by issuing bonds and the rest 75% through issuing equity. The NPV profile with varying debt is created by using the Data Table tool in excel. This table shows how the valu changes with change in debt. As the NPV of the project is positive at "5902906.71" at 75/25 and also for 25/75 split the NPV of the project is p "16435971.10" so we can accept both the project.. As the NPV for the project 25/75 split is higher than that of 75/25 split so we first have to accept 25/75 project. ng Rubric for details. I. Given the following data on proposed capital budgeting project. Parameters Economic life of project in years. Price of New Equipment Increase in NWC Fixed Costs Variable Costs Salvage value of New Equipment Marginal Tax Rate Generation First Year Capacity factor Capacity Factor Growth Rate Sale Price $ $ $ $ 6 34,000,000.00 6,000,000.00 4,000,000.00 50% 7,000,000.00 35% 35000 kw per hour 58% 15% $0.15 per kw-hour Original Debt Original Equity New Debt New Equity Old D/E Ratio New D/E Ratio % Raised via Bonds % Raised via Equity Old Wd Old We Note Cells C20 and C21 include the i Column D through I are the ope Cells I38-I41 contain the termin WACC Spreadsheet for determining Cash Flows Timeline: II. Net Investment Outlay = Initial CFs Year Price Increase in NWC 0 $ $ 1 34,000,000.00 6,000,000.00 III. Cash Flows from Operations Revenue Generation Capacity factor Electricity Generation Revenues 58% 177828000 $26,674,200.00 Variable Costs Fixed Costs Depreciation Loan Interest Earnings Before Taxes Taxes Net Income Depreciation Net operating CFs $13,337,100.00 $4,000,000.00 $5,666,666.67 Costs $3,670,433.33 $1,284,651.67 $2,385,781.67 $5,666,666.67 $8,052,448.33 IV. Terminal Cash Flows Salvage Value Tax on Salvage Value Return of NWC Disposal Fee V. Final Cash Flow Cash Flows Present Value of CFs NPV of Project $40,000,000.00 $22,107,308.86 $8,052,448.33 $8,052,448.33 WACC Debt Fraction For New Capital NPV 5.44% 5.58% 5.52% 5.47% 5.41% 5.35% 5.29% 5.23% 5.17% 5.12% 5.06% 5.00% 0% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 70.00% 80.00% 90.00% 100.00% $22,107,308.86 A Typical NPV Profile Discount Rate: Year 0 1 2 3 4 NPV Discount Rate: $ $ $ $ $ 0% 2% CF PV(CF) PV(CF) (265,000.00) $ (265,000.00) $ (265,000.00) 52,673.75 $ 52,673.75 $ 51,640.93 63,212.50 $ 63,212.50 $ 60,757.88 73,742.50 $ 73,742.50 $ 69,489.20 122,628.50 $ 122,628.50 $ 113,289.78 $ 47,257.25 $ 30,177.80 0% 2% $60,000.00 $50,000.00 $40,000.00 $30,000.00 NPV ---------> $20,000.00 $10,000.00 $- 0% $(10,000.00) 1% 2% 3% 4% 5% $(20,000.00) Discount Rate ------> 6% 7% 8% 9% $ 28,800,000.00 Rm $ 16,000,000.00 Rf $ 8,500,000.00 Beta $ 25,500,000.00 Re 1.80 Rd 0.33 New Wd 25% New We 75% WACC 64.29% 35.71% 4% 2.14% 1.85 5.58% 5.00% 25.00% 75.00% 5.44% s C20 and C21 include the initial (today's) cash flows. umn D through I are the operating cash flows. s I38-I41 contain the terminal cash flows. 2 3 4 5 6 73.00% 223818000 $33,572,700.00 88.00% 269808000 $40,471,200.00 100.00% 306600000 $45,990,000.00 100.00% 306600000 $45,990,000.00 100.00% 306600000 $45,990,000.00 $16,786,350.00 $4,000,000.00 $5,666,666.67 $20,235,600.00 $4,000,000.00 $5,666,666.67 $22,995,000.00 $4,000,000.00 $5,666,666.67 $22,995,000.00 $4,000,000.00 $5,666,666.67 $22,995,000.00 $4,000,000.00 $5,666,666.67 $7,119,683.33 $2,491,889.17 $4,627,794.17 $5,666,666.67 $10,294,460.83 $10,568,933.33 $3,699,126.67 $6,869,806.67 $5,666,666.67 $12,536,473.33 $13,328,333.33 $4,664,916.67 $8,663,416.67 $5,666,666.67 $14,330,083.33 $13,328,333.33 $4,664,916.67 $8,663,416.67 $5,666,666.67 $14,330,083.33 $13,328,333.33 $4,664,916.67 $8,663,416.67 $5,666,666.67 $14,330,083.33 $14,330,083.33 $14,330,083.33 $7,000,000.00 -$2,450,000.00 $6,000,000.00 -$8,500,000.00 $16,380,083.33 $16,380,083.33 $10,294,460.83 $10,294,460.83 $12,536,473.33 $12,536,473.33 $14,330,083.33 $14,330,083.33 4% 6% 8% PV(CF) PV(CF) PV(CF) $ (265,000.00) $ (265,000.00) $ (265,000.00) $ 50,647.84 $ 49,692.22 $ 48,771.99 $ 58,443.51 $ 56,258.90 $ 54,194.53 $ 65,556.81 $ 61,915.62 $ 58,539.17 $ 104,823.36 $ 97,133.26 $ 90,135.61 $ 14,471.52 $ (0.00) $ (13,358.70) 4% 6% 8% 5% ------> 6% 7% 8% 9% 75% 25% Cost of Debt as a function of Debt-to-Equity Ratio %Debt %Equity D/E Interest rate 0% 100% 0.00 5.00% 10.0% 90% 0.11 5.00% 20.0% 80% 0.25 5.00% 30.0% 70% 0.43 5.20% 40.0% 60% 0.67 5.20% 50.0% 50% 1.00 5.30% 60.0% 40% 1.50 7.50% 70.0% 30% 2.33 7.800% 80.0% 20% 4.00 7.900% 90.0% 10% 9.00 8.000% 100.0% 0% #DIV/0! 10.000%