I'm confused with this Final Excel Project and need help answering this.

FIN 301 Penn State World Campus Final Excel project Scenario: Tom Jones, the CFO for the firm PSUWC Energy, LLC, woke up with a start at 4:00 am on July 2, 2012, due to his phone ringing. It was his senior financial analyst, vacationing in Europe, calling with bad news. Tom was supposed to present his project evaluation, at the end of the week, for the Board's proposal that they invest in new equipment that generates electricity, using a new nuclear technology. His staff of financial analysts had been working hard over the last few weeks collecting data and had prepared a model creating a financial forecast about the proposed project's viability. Disaster had struck on the night of July 1'st wherein malware all but wiped out the work of the analysts. Tom needed to prepare a financial analysis of the project to present the Board with his recommendations. All the staff had already left for the July 4th vacation and Tom was on his own. Tom quickly reached his office and managed to salvage what was left of the excel spreadsheet prepared for the presentation. The excel file is attached to this document. What follows is some basic information that Tom knew and was able to retrieve about the project. PSUWC's existing plant has excess capacity, in a fully depreciated building, to install and run the new equipment. Due to relatively rapid advances in the technology, the project was expected to be discontinued in six years. The proposed project was capable of providing 35,000 kW 1 per hour power. Typically, PSUWC ran its plants 24 hours a day, 7 days a week at an average of 58% Capacity factor2, which is what the project would start with. However, his engineers had assured him that the implementation of the new technology would enable them to increase their capacity factor by 15% a year till they reached a 100% capacity factor. A total investment of $34 Million USD for new equipment was required. The equipment had fixed maintenance contracts of $4,000,000 per year with a salvage value of $7,000,000 and variable costs were 50% of revenues. The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $6,000,000 and $1,000,000 of this increase would be offset with accounts payable. PSUWC would be able to sell all the electricity it generated at the rate of $0.15 per kilo-watt hour in the market they served. 1 kW stands for kilo-watt or 1000 watts. A watt is a measure of power. Electricity is priced in kWh (kilo-watt hours). 1 kilo-watt hour represents 1000 Watts of power expended for one hour (1 h) of time. The average price people in the U.S. pay for electricity is about 12 cents per kilowatt-hour. (Context: A typical U.S. household uses about 908 kWh a month of electricity.) 2 The net capacity factor of a power plant is the ratio of its actual output over a period of time, to its potential output if it were possible for it to operate at full nameplate capacity continuously over the same period of time. To calculate the capacity factor, take the total amount of energy the plant produced during a period of time and divide by the amount of energy the plant would have produced at full capacity. Capacity factors vary greatly depending on the type of fuel that is used and the design of the plant. The corporate tax rate was 35 percent and PSUWC currently has 1,000,000 shares of stock outstanding at a current price of $16. The company also has 30,000 bonds outstanding, with a current price of $960. The bonds pay interest semi-annually at the coupon rate is 5.6%. The bonds have a par value of $1,000 and will mature in twenty years. Even though the company has stock outstanding it is not publicly traded. Therefore, there is no publicly available financial information. However, management believes that given the industry they are in the most reasonable comparable publicly traded company is Companhia Paranaense de Energia - COPEL (NYSE Ticker Symbol ELP) 3. In addition, management believes the S&P 500 is a reasonable proxy for the market portfolio. Therefore, the cost of equity is calculated using the beta from ELP and the market risk premium based on the S&P 500 annual expected rate of return 4. Tom knew that because of the size of the proposed project, he had to take into account the change in capital structure the new project would cause his firm. To this end, he had a choice between raising the new capital needed either using 75/25% split between issuing bonds/equity or a 25/75% split between issuing bonds/equity5. The bonds would have to be retired at the end of the project's life6. Tom knew that the cost of debt would depend on the new D/E ratio that the firm would have based on his decision to raise capital. Tom looked at the worksheet titled "Rd with DtoE", realizing that the cost of debt increased with an increasing D/E ratio 7. Additionally, the state government had promised to raise the debt for PSUWC via the issuance of bonds, with the caveat that upon termination of the project PSUWC would have to pay a Nuclear Waste Disposal Fee, equivalent to the amount of money raised via the issuance of debt. Tom needed to calculate the rate at which he would have to discount the project to calculate the Net present Value of the proposed project based on his decision of raising capital and the current capital market environment. This discount rate, the WACC, would obviously influence the NPV and could affect the decision of whether or not to accept the project. Thankfully, he had all the 3 Companhia Paranaense de Energia - COPEL engages in the generation, transmission, distribution, and sale of electricity to industrial, residential, commercial, rural, and other customers primarily in the State of Paran, Brazil. As of December 31, 2015, the company operated 18 hydroelectric plants, 12 wind plants, and 1 thermoelectric plant with a total installed capacity of 5,032.2 megawatts; and owned and operated 2,344 kilometers of transmission lines and 193,527.1 kilometers of distribution lines. It holds concessions to distribute electricity in 394 municipalities in the State of Paran and in the municipality of Porto Unio in the State of Santa Catarina. The company also provides telecommunication services to 4,964 corporate clients, including supermarkets, universities, banks, Internet service providers, and television networks, as well as to 43,023 retail clients; and broadband Internet access to public elementary and middle schools. In addition, it supplies piped gas to 31,790 customers, including thermoelectric plants, cogeneration plants, gas stations, other businesses, and residences through a gas distribution network covering 780 kilometers in the State of Paran. Companhia Paranaense de Energia - COPEL was founded in 1954 and is headquartered in Curitiba, Brazil. Source: http://finance.yahoo.com/q/pr?s=ELP+Profile 4 We calculated a monthly expected return for the market in the return exercise. You can simply multiply that rate by 12 for an expected annual rate on the market. 5 This meant that if he used a 75/25% split between issuing bonds/equity, he would raise 75% of the needed capital of $34,000,000 by issuing bonds and the rest through issuing equity. Costs of issuing new bonds and equity are ignored for the purposes of this project. 6 This meant that the capital raised via issuing debt would have to be returned to the investors at the end of the projects life. 7 For example, a D/E ratio between 1.0 to 1.5 meant that the cost of debt would be 5.3% in the current climate. information needed to calculate this and hence the NPV. Tom needed to clearly show all his calculations and sources for all parameter estimates used in the calculation of the WACC. Gathering all the available information, Tom got a large cup of extra strong coffee and sat down to work on the development of his Capital Budgeting project model. His correct recommendation to the board was critical to the future growth of the firm! 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 E9E10. 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=UTF8#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=UTF8#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. 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 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 Risk-free Rate Explanation of Risk free rate based on current T-bond or T-bill yield 1 WACC Incorrect Calculation with Correct Cacluation with minor errors carried through Explanation of all from above assumptions parameters with explanation 2 Beta Expected Market Return 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 Calculation Explanations assumptions with no explanation 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. 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 5,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 initia Column D through I are the operati Cells I38-I41 contain the terminal ca WACC Spreadsheet for determining Cash Flows Timeline: II. Net Investment Outlay = Initial CFs Year Price Increase in NWC 0 1 $34,000,000.00 $ 5,000,000.00 III. Cash Flows from Operations Revenue Generation Capacity factor Electricity Generation Revenues Costs Variable Costs Fixed Costs Depreciation Loan Interest Earnings Before Taxes Taxes Net Income Depreciation Net operating CFs 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 58% 177828000 $26,674,200.00 WACC Debt Fraction For New Capital NPV 0% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 70.00% 80.00% 90.00% 100.00% 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% A Typical NPV Profile $60,000.00 $50,000.00 $40,000.00 $30,000.00 NPV ---------> $20,000.00 $10,000.00 $- 0% 1% 2% 3% 4% 5% 6% 7% 8% 9% $(10,000.00) $(20,000.00) Discount Rate ------> Rm Rf Beta Re Rd New Wd New We WACC via Equity 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 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% ile 4% 5% 6% 7% 8% 9% te ------> WACC= Stock Value = (We)Re + (Wd)Rd(1 - T) 1,000,000*16 15000000 Bond Value = Total Value = We = Wd = Re = Rf + Be(Rm - Rf) Rm - Rf = Be * (Rm - Rf) = Rf + Be(Rm - Rf) = Rm = Rf = Beta = Re = 30,000*960 28800000 43800000 0.3424657534 0.6575342466 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%