Need help with this assignment.I've attached the excel workbook that should be used to record answers and the case scenario.

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.

1. Suggested StepsStart 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 thecurrentcost 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 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.

Ticker ^GSPC ELP Start 6/1/2012 6/1/2012 TODAYS 8/1/2017 Day 8/1/2017 Month Year END START End INFORMATION 11 8 2017 8/1/2017 6/1/2012 0.Case Instructions Final Excel Project - Making a Project Recommendation to Management Step 0: Enter your First & Last name. Then click Save File FIRST NAME Nick Enter Instructor Password Here LAST NAME Jones Suggested Steps for Successful Completion of Project: Step 1: Read the Full Case. Then gather the most recent 61 monthly stock prices for the S&P500 and the comparable company from Yahoo! Finance Note: see the Lesson 10 assignment if you need a refresher on how to do this. Note: While it is perfectly fine to get historical data from different sources, for the purpose of this assignment please use Yahoo! Finance only. Note: Sometimes, Yahoo finance returns Todays/Yesterdays date as the first value (after you sort data in descendng order), with the same price as the beginning of the month. If this happens, please delete the data point which does not correspond to the first business day of the month (typically the 1'st but may vary) before you calculate returns, and MAKE SURE that you have 61 months of data. Otherwise, you will get incorrect results. Don't forget to convert monthly returns to annual returns. Initial Calculations: a) Calculate the Original Debt and Original Equity using the data provided in the case. b) Calculate the NEW Debt and Equity using the data provided. (Note: Values asked are ONLY the portion of D and E generated due to the NEW investment) c) Calculate the Old and New D/E Ratios the data provided. (Note: the New D/E ratio should account for existing capital structure and new investment) Step 2: Calculate the Expected Return on the Market using data from Step 1. Note: see footnote [4] in the Full Case for more detail on this. Step 3: Go to the following website and get the latest appropriate 5-year Daily Treasury Yield Curve Rate https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield Note: see footnote [8] in the Full Case for more detail on this. Step 4: Beta for ELP from (this is the comparable Beta) Note: Use the value of 1.74 as the Beta for ELP. (This is current as of 07/25/2017) Step 5: Unlever the Beta for ELP and use this as the beta corresponding to the unlevered beta of PSUWC Hint: Use the Latest Available (12/16) Total Liabilities (D) and Total Stockholder Equity (E) information from the Balance Sheet Data Provided Hint: The Hamada Equation should be used to complete Steps 5 and 6. Also, assume that the tax rate for ELP and PSUWC is the same. Step 6: Re-lever the Beta calculated in Step 5 for the corrseponding D and E numbers for PSUWC (or/and in sheet "Rd with DtoE") Note: Use this beta (and the Rm, Rf values gathered before) to calculate the Cost of Equity (Re). Step 7: Find the appropriate cost of debt (Rd) using the data given in sheet "Rd with DtoE" (the use of VLOOKUP will help automate the process) Hint: You should calculate the new D/E ratio before starting this step. Step 8: Calculate the WACC for the firm for the chosen D/E split. Hint: You should calculate the new Wd and We numbers before starting this step. Step 9: Complete the Cash Flow portion of the worksheet using the WACC calculated above and the given information. (including the NPV Calculation & Acceptance Decision) IMPORTANT: All cash inflows need to be POSITIVE and all cash outflows need to be NEGATIVE. Step 10: Create the above results to create the Data Table in the area shown and create the plots requested. (ONLY FOR CASE A) Hint: Go through the how-to videos available on Canvas Step 11: Complete the Worksheet "Answer Sheet" as needed. (see instructions on the sheet) Hint: Explain/Clarify any assumptions or methods used. Note: Use this sheet to convey any comments to the instructor. Page 2 of 39 FIN 301: Penn State World Campus_x000D_ Final Excel project_x000D_ It is recommended that you copy and paste the Full Case into a Word Document for easy viewing._x000D_ Ctrl-A followed by Ctrl-C will Copy the text of the case._x000D_ _x000D_ Scenario: _x000D_ _x000D_ Tom Jones, the CFO for the firm PSUWC Energy, LLC, woke up with a start at 4:00 am on 8/11/2017, 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. _x000D_ _x000D_ Disaster had struck on the night of 8/10/2017 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 their annual 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. What follows is some basic information that Tom knew and was able to retrieve about the project._x000D_ _x000D_ 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 36000 kW [1] per hour power. Typically, PSUWC ran its plants 24 hours a day, 7 days a week at an average of 58 % Capacity factor [2] , 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 16 % a year till they reached a 100% capacity factor. (This meant that the capacity factor for year 2, CF2, would be = CF1*(1+growth_rate), till 100% was reached and then would stay at 100%). A total investment of $ 34,000,000.00 USD for new equipment was required. The equipment had fixed maintenance contracts of $ 4,500,000.00 per year with a salvage value of $ 8,000,000.00 and variable costs were 48 % of revenues._x000D_ The new equipment would be depreciated to zero using straight line depreciation. The new project required an increase in working capital of $ 4,000,000.00 and $ 1,200,000.00 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.145 per kilo-watt hour in the market they served. _x000D_ The corporate tax rate was 39 % and PSUWC currently has 1,000,000 shares of stock outstanding at a current price of $ 16.00. The company also has 30,000 bonds outstanding, with a current price of $ 1,039.00. The bonds pay interest semi-annually at a coupon rate of 5.40 %. The bonds have a par value of $1,000 and will mature in 21 years. _x000D_ 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]._x000D_ _x000D_ 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 20 % /80 % split between issuing bonds/equity or a 80 % /20 % split between issuing bonds/equity . The bonds would have to be retired at the end of the project's life [6]. 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._x000D_ 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 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._x000D_ _x000D_ 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!_x000D_ Minimum requirements for the Project. _x000D_ 1. Calculate the WACC for the company._x000D_ 2. Create a partial income statement incremental cash flows from this project in the Blank Template worksheet._x000D_ 3. Enter formulas to calculate the NPV by finding the PV of the cash flows over the next six years. (You can either use the EXCEL formula PV() or use mathematical formula for PV of a lump sum.)_x000D_ 4. Set up the EXCEL worksheet so that you are able to change the parameters in B4-B12, E3-E8,& G11-G12. These are highlighted in Yellow on the Blank template. The other cells which need input from you are highlighted in Light Blue on both worksheets. Run the two cases: CaseA and CaseB. The D/E split for raising new capital are already filled in in the worksheets. The numbers in the worksheet CaseB, which are identical to the numbers in worksheet CaseA will populate automatically when you fill in information in the worksheet CaseA - These are highlighted in Orange. The numbers in CaseB which DO NOT populate need to be calculated. Note that it is important to use the VLOOKUP function for calculating the cost of debt for automatic updating of the excel sheet. _x000D_ 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. _x000D_ 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 Y-axis. The Debt Fraction should be on the Xaxis. 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. _x000D_ 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. _x000D_ NOTE: There is a plethora of information available on the Internet regarding creating plots in excel (Google - how to plot a graph in excel) and creating plots with secondary axis (Google - how to plot a graph in excel with two y axis). _x000D_ 7. Make a clear recommendation whether the company should accept or reject the project for each case scenario._x000D_ 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_x000D_ 9. Turn in your project in the drop box._x000D_ [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.)_x000D_ [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._x000D_ [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_x000D_ [4] Tom would calculate the Expected Return on the Market using 5 years of past monthly data. Therefore, he would collect 61 months of price data for the S&P and calculate the returns for the past 60 months. He would then multiply this by 12 to estimate the annual expected rate. Tom, remembered that if the expected rate of return for the market was too low, too high, or negative, he would have to use a forward looking rate of an historical average of about 9.5%, as the calculated value for the current 5-year period may not be a representation of the future. Tom would consider a E(Rm) between 8-12% acceptable._x000D_ [5] This meant that, for example, if he needed to raise $34,000,000 & 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. _x000D_ [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._x000D_ [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._x000D_ [8] Tom remembered that when he considered the Risk-free rate (Rf) in his calculations, he should consider the riskfree rate with a maturity similar to that of his project. He decided to use the 5-year rate available from the Treasury website for his calculations. Grading Rubric Grading Rubric for Final Project in FIN 301 WACC Calculation 0 0.5 Calculation of Weights No Calculations or Incorrect Calculation, Explanations Brief Explanation, Reasonable estimate Cost of Debt Total Possible Points 2 Correct Calculation without Correct Calculation using using Target Capital Target Capital Structure Structure with Explanation with Explanation Correct Calculation based on Rd with DtoE data with Explanation 2 No Calculations or Beta based on Explanations information from Yahoo! Finance but No subsequent calculations (1 point) Beta based on information from Yahoo! Finance and some subsequent calculations (2 point) Beta Calculated with Delevering and Re-levering to correct for capital structure. (3 points) 3 Expected Market Return No Calculations or Estimate for Market Explanations Return is not reasonable & not based on historical market performance Estimate for Market Return is reasonable & not based on historical market performance Estimate for Market Return is reasonable & based on historical market performance 2 Risk-free Rate No Calculations or Estimate not based on Explanations current Treasury yields but reasonable no explanation Explanation of Risk free rate based on current 5-year T-bond or T-bill yield 1 Cost of Equity (Re) No Calculations or Estimate not based on Explanations CAPMo explanation Estimate based on CAPM with explanation 1 WACC No Calculations or Incorrect Calculation Explanations with minor errors carried through from above assumptions with no explanation Incorrect Calculation with Correct Cacluation with minor errors carried through Explanation of all from above assumptions parameters with explanation 2 Total Possible Points from WACC calculation portion of project Cash Flow Calc 0 1 13 2 Total Possible Points 4 Initial Cash Flows No Calculations or Partially correct CFs Explanations All initial CFs properly identified with brief explanation Operating CFs No Calculations or Few OCFs properly Explanations identified with no explanation Most OCFs properly identified with brief explanation Terminal CFs No Calculations or Partially correct CFs no All terminal after tax CFs Explanations tax adjustments properly identified with brief explanation 2 NPV Calculation: CaseA D/E Split No Calculations or Partial Calculations Explanations All PV of Cash Flows correctly calculated and Justification for use of NPV technique 2 NPV Calculation: CaseB D/E Split No Calculations or Partial Calculations Explanations All PV of Cash Flows correctly calculated and Justification for use of NPV technique 2 2 All OCFs properly identified with brief explanation of all calculations (ie taxes & depr properly accounted for) Total Possible Points from Cash Flow Development portion of project NPV Calculations 0 0.5 4 12 1 2 Create NPV Profile for No Graph Created No Graph, Partial Varying Debt Calculations, Partial Explanation Graph is mostly correct with Graph is correct with all Partial Explanation axis properly labled with Explanation 2 Final Decision by Final Decision is Case: Accept or Reject Incorrect or Not Stated Final Decision is correct with limited or no explanation of basis of decision and all assumptions 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 2 Presentation Somewhat difficult to find some information and some disorder gramar and spelling errors 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 1 Unorganized difficult to follow with spelling and grammar errors Total Possible Points from NPV portion of project Automation 0 Is the Sheet setup for No Automation Automatic Updating of Calculations? 1 0-50% automated 5.00 2 Between 50% - 75% Automated 3 > 75% Automated Total Possible Points Student scored 70% or greater Total Number of Students Percentage of Students Pass Grade 2 No Calculations or Incorrect Calculation Correct Calculation Explanations (calculated using Bond (calculated using Bond Data), Brief Data) with Explanation Explanation, Reasonable estimate Beta Nick Jones 1 3 33.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% Total > 70% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Total Students 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ### Please Note: Students should get full credit for PV and NPV calculations and decsions if they are consistent with inputs from prior steps. In other words, if the student incorrectly states a WACC of 1% or 30% causing all NPV calculations to be incorrect, they can still get full credit for the NPV calculation and decision if it is Highlighted area in yellow above is reported in AACSB report. Please double check that total students is correct. Page 13 Grading Rubric 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 nd decision if it is consistent with their WACC. Page 14 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: Accept/Reject & which D/E Split? Comments you want to provide explantions for your answers to any questions. Please see the grading 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. bric for details. I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Change in NWC Fixed Costs Variable Costs (% of Revenue) Salvage value of New Equipment Marginal Tax Rate Generation First Year Capacity factor Capacity Factor Growth Rate Sale Price Cost of Bonds Cost of Stock $ $ $ $ $ $ $ (Inputs Expected from you ar 6 34,000,000.00 0% 0.0% 36000 kw per hour 58% 0.16 0.145 per kw-hour 1,039.00 16.00 Spreadsheet for determining Cash Flows Timeline: II. Net Investment Outlay = Initial CFs Year Price of Equipment Change in NWC 0 $ $ (34,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 $0.00 $0.00 $0.00 Salvage Value Tax on Salvage Value Return of NWC Disposal Fee $0.00 $0.00 $0.00 $0.00 $0.00 IV. Terminal Cash Flows V. Final Cash Flow Cash Flows Present Value of CFs NPV of Project Accept This Project (on its own merits)? WACC Debt Fraction For New Capital 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: 0% Year 0 1 2 3 4 NPV Discount Rate: $ $ $ $ $ CF PV(CF) (265,000.00) $ (265,000.00) 52,673.75 $ 52,673.75 63,212.50 $ 63,212.50 73,742.50 $ 73,742.50 122,628.50 $ 122,628.50 $ 47,257.25 0% A Typical NPV Profile $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% ject. (Inputs Expected from you are highlighted in yellow) Parameters Rm 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 $ $ (ORANGE Cells will Populate Au 0.000% 0.000% 0.000 0.000 - Rf - b ELP (From Yahoo!) b ELP (Unlevered) Re 0 Rd New Wd 80% New We 20% WACC 0.00% ELP Total Liabilities 0.00% ELP Total Stockholder Equity $0.00 $0.00 0 ELP D/E Ratio Note Cells C20 and C21 include the initial (today's) cash flows. Column D through I are the operating cash flows. Cells I38-I41 contain the terminal cash flows. 1 $ $ 2 58.00% 182,908,800.00 $ 26,521,776.00 $ 3 0.00% - $ - $ 4 0.00% - $ - $ 0.00% - $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 NPV 2% 4% PV(CF) PV(CF) PV(CF) $ (265,000.00) $ (265,000.00) $ $ 51,640.93 $ 50,647.84 $ $ 60,757.88 $ 58,443.51 $ $ 69,489.20 $ 65,556.81 $ $ 113,289.78 $ 104,823.36 $ $ 30,177.80 $ 14,471.52 $ 2% 4% pical NPV Profile 1% 2% 3% 4% 5% Discount Rate ------> 6% 7% 8% 9% 6% 8% PV(CF) (265,000.00) $ (265,000.00) 49,692.22 $ 48,771.99 56,258.90 $ 54,194.53 61,915.62 $ 58,539.17 97,133.26 $ 90,135.61 (0.00) $ (13,358.70) 6% 8% ANGE Cells will Populate Automatically) b PSUWC (Re-levered) 5 $ $ 6 0.00% - $ - $ 0.00% - $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 I. Given the following data on proposed capital budgeting project. Economic life of project in years. Price of New Equipment Change in NWC Fixed Costs Variable Costs (% of Revenue) Salvage value of New Equipment Marginal Tax Rate Generation First Year Capacity factor Capacity Factor Growth Rate Sale Price Cost of Bonds Cost of Stock $ $ $ $ 6 34,000,000.00 36000 kw per hour 58% 16% 0.145 per kw-hour 1,039.00 16.00 Spreadsheet for determining Cash Flows Timeline: II. Net Investment Outlay = Initial CFs Year Price of Equipment Change in NWC 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 Accept This Project (on its own merits)? (Inputs Expected from you ar 0 $ (34,000,000.00) Recommend Management Accept Which Case(s)? 1 WACC Debt Fraction For New Capital 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: 0% Year 0 1 2 3 4 NPV Discount Rate: $ $ $ $ $ CF PV(CF) (265,000.00) $ (265,000.00) 52,673.75 $ 52,673.75 63,212.50 $ 63,212.50 73,742.50 $ 73,742.50 122,628.50 $ 122,628.50 $ 47,257.25 0% A Typical NPV Profile $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% ject. (Inputs Expected from you are highlighted in yellow) Parameters Rm 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 Rf b ELP (From Yahoo!) b ELP (Unlevered) Re Rd New Wd 20.0% New We 80.0% WACC ELP Total Liabilities ELP Total Stockholder Equity ELP D/E Ratio Note Cells C20 and C21 include the initial (today's) cash flows. Column D through I are the operating cash flows. Cells I38-I41 contain the terminal cash flows. 1 58% 182908800 $26,521,776.00 2 3 4 NPV 2% 4% PV(CF) PV(CF) PV(CF) $ (265,000.00) $ (265,000.00) $ $ 51,640.93 $ 50,647.84 $ $ 60,757.88 $ 58,443.51 $ $ 69,489.20 $ 65,556.81 $ $ 113,289.78 $ 104,823.36 $ $ 30,177.80 $ 14,471.52 $ 2% 4% pical NPV Profile 1% 2% 3% 4% 5% Discount Rate ------> 6% 7% 8% 9% 6% 8% PV(CF) (265,000.00) $ (265,000.00) 49,692.22 $ 48,771.99 56,258.90 $ 54,194.53 61,915.62 $ 58,539.17 97,133.26 $ 90,135.61 (0.00) $ (13,358.70) 6% 8% b PSUWC (Re-levered) 5 6 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% Companhia Paranaense de Energia (ADR)(NYSE:ELP) Balance Sheet In Millions of BRL (except for per share items) Cash & Equivalents Short Term Investments Cash and Short Term Investments Accounts Receivable - Trade, Net Receivables - Other Total Receivables, Net Total Inventory Prepaid Expenses Other Current Assets, Total Total Current Assets Property/Plant/Equipment, Total - Gross Accumulated Depreciation, Total Goodwill, Net Intangibles, Net Long Term Investments Other Long Term Assets, Total As of 2016-12-31 As of 2015-12-31 809.05 1,313.00 302.4 1,317.03 1,284.47 2,797.76 2,217.36 3,032.83 2,947.49 3,953.34 130.64 131.02 39.1 49.28 1.29 2 4,402.99 9,996.85 -3,668.74 - Total Assets Accounts Payable Accrued Expenses Notes Payable/Short Term Debt Current Port. of LT Debt/Capital Leases Other Current liabilities, Total Total Current Liabilities Long Term Debt Capital Lease Obligations 6,933.40 10,567.10 -4,611.77 - 6,459.81 2,936.98 3,082.47 6,145.08 2,450.73 2,640.70 30,434.21 1,255.64 862.2 0 2,601.94 936.26 28,947.66 1,613.13 810.55 0 1,232.56 1,132.88 5,656.04 6,235.16 4,789.12 6,528.43 - - Total Long Term Debt 6,235.16 6,528.43 Total Debt Deferred Income Tax Minority Interest Other Liabilities, Total 8,837.10 178.43 291.28 3,209.14 7,760.99 0.21 338.75 3,045.42 15,570.04 14,701.93 Total Liabilities Redeemable Preferred Stock, Total Preferred Stock - Non Redeemable, Net Common Stock, Total Additional Paid-In Capital Retained Earnings (Accumulated Deficit) Treasury Stock - Common Other Equity, Total Total Equity - 7,910.00 - 6,910.00 - 5,955.70 - 6,158.36 - 998.47 1,177.37 14,864.17 14,245.73 Total Liabilities & Shareholders' Equity Shares Outs - Common Stock Primary Issue Total Common Shares Outstanding 30,434.21 - 28,947.66 - 273.66 273.66 As of 2014-12-31 As of 2013-12-31 587.76 1,611.32 1,068.41 389.22 1,808.54 2,130.85 2,178.82 1,337.63 3,225.38 2,388.19 150.62 139.28 20.13 19.98 13.5 1.98 5,218.18 7,132.93 -3,804.69 - 4,680.28 6,649.29 -3,593.41 - 2,174.16 2,224.21 2,568.96 2,035.36 1,308.46 2,769.51 25,618.14 1,587.20 603.32 0 1,299.12 565.75 23,111.44 1,092.24 698.26 0 1,014.57 542.82 4,055.39 4,755.28 3,347.89 3,517.16 - 4,755.28 3,517.16 6,054.40 15.22 352.09 3,109.47 4,531.73 420.5 277.41 2,897.15 12,287.45 10,460.11 - 6,910.00 - 6,910.00 - 5,201.97 - 4,522.68 - 1,218.72 1,218.66 13,330.69 12,651.34 25,618.14 - 23,111.44 - 273.66 273.66 Estimation of Beta with Changes in Cap We can estimate the change in the cost of equity as a function of the capital structure by using the Hamad where: where L is the levered beta, U is the unlevered beta, T is the tax rate, D is the debt and E is the e The following steps may be used to calculate the effects of changing the capital structure on the cost of e 1) Estimate the current beta for the firm with its current capital structure. This represents the beta of the 2) Estimate the unlevered beta for the firm. This represents the beta for the firm if it had no debt, i.e. no 3) Use the unlevered beta to calculate the levered beta for the firm at the new debt level. For example, consider a firm with a current beta of 1.25, D = 45%, E = 55%, and T = 37%. The unlevere This U can then be used to calculate the levered beta (L) for the firm (say for D = 55%, E = 45%, T = 3 In this case, the comparable firm ELP is used to estimate the beta for PSUWC. However, the beta that is Therefore, we need to unlever the listed beta and calculate the "pure" beta for the firm (and therefore our