70.2K

Verified Solution

Link Copied!

Suggested Steps for Successful Completion of Project

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. Dont forget to convert monthly returns to annual returns.

Initial Calculations:

1. Calculate the Original Debt and Original Equity using the data provided in the case.
2. 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)
3. 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)

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.
3. Go to the following website and get the latest appropriate 5-year Daily Treasury Yield Curve Rate
   • US Department of the Treasure Resource Center
   • Note: see footnote [8] in the Full Case for more detail on this.
4. Beta for ELP from (this is the comparable Beta)
   • Note: Use the value of 1.46 as the Beta for ELP. (This is current as of 11/15/2017)
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.
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).
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.
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.
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.
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
11. Complete the Worksheet "Answer Sheet" as needed. (see instructions on the sheet)

I. Given the following data on proposed capital budgeting project. (Inputs Expected from you are highlighted in yellow) Rm Rf Parameters Economic life of project in years. Price of New Equipment Change in NWC Original Debt Original Equity New Debt New Equity Old D/E Ratio New D/E Ratio % Raised via Bonds %% Raised via Equity Old Wd 6 PELP (From Yahoo!) PELP (Unlevered) Re PPSLwc (Re-levered) Fixed Costs Variable Costs (% of Revenue) Salvage value of New Equipment Rd New Wd 0.0%New We 100.0% WACC Marginal Tax Rate Generation First Year Capacity factor Capacity Factor Growth Rate Sale Price 0 kw per hour 0% 0% per kw-hour ELP Total Liabiliries. Old We ELP Total Stockholder Equity ELP DE Ratio Note Cells C20 and C21 include the initial (today's) cash flows Cost of Bonds Cost of Stock Column D through I are the operating cash flows Cells I38-141 contain the terminal cash flows. Cells I38-141 contain the terminal cash flows Spreadsheet for determining Cash Flows 0 Timeline: Year 1 2 3 4 5 6 IL Net Investment Outlay Initial CFs Price of Equipment S Change in NWC III Cash Flows from Operations Revenue Generation Capacity factor 0% Electricity Generation 0 $0.00 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 I. Given the following data on proposed capital budgeting project. (Inputs Expected from you are highlighted in yellow) Rm Rf Parameters Economic life of project in years. Price of New Equipment Change in NWC Original Debt Original Equity New Debt New Equity Old D/E Ratio New D/E Ratio % Raised via Bonds %% Raised via Equity Old Wd 6 PELP (From Yahoo!) PELP (Unlevered) Re PPSLwc (Re-levered) Fixed Costs Variable Costs (% of Revenue) Salvage value of New Equipment Rd New Wd 0.0%New We 100.0% WACC Marginal Tax Rate Generation First Year Capacity factor Capacity Factor Growth Rate Sale Price 0 kw per hour 0% 0% per kw-hour ELP Total Liabiliries. Old We ELP Total Stockholder Equity ELP DE Ratio Note Cells C20 and C21 include the initial (today's) cash flows Cost of Bonds Cost of Stock Column D through I are the operating cash flows Cells I38-141 contain the terminal cash flows. Cells I38-141 contain the terminal cash flows Spreadsheet for determining Cash Flows 0 Timeline: Year 1 2 3 4 5 6 IL Net Investment Outlay Initial CFs Price of Equipment S Change in NWC III Cash Flows from Operations Revenue Generation Capacity factor 0% Electricity Generation 0 $0.00 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

Answer & Explanation Solved by verified expert

See Answer