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. Dont 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.07 as the Beta for ELP. (This is current as of 04/18/2019)
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). Worksheet BetaCalculations gives an example of this calculation.
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.
EXTREMELY IMPORTANT: DO NOT add any rows or columns to the existing worksheets. You can ADD worksheets for any calculations
that you may wish to conduct. Also, you may encounter that the sheet "freezes" after data entry is made in certain cells. THIS IS NORMAL
This happens because there is code in the background checking current information as you enter your data. Once the background process is completed,
normal operation will resume. Ensure that you are connected to the Internet when you complete this project.
Finally, the cells are formatted to display in a certain format, please pay attention to what is displayed.
There is a change in the grading for this semesters final project as follows:
1) The final project counts for 30 points of your total grade.
2) You can earn a BONUS 10 points towards your final grade.
3) The case is divided into 2 parts each with one D/E calculation with different D and E numbers.
a. Case A calculations (which include all the cells highlighted in Yellow except the Data Table portion i.e. NPV as a function of WACC) will give you 30/30 points for the final project.
You do not need to complete the Data Table and NPV profile portion of the assignment for a full grade.
b. Case B calculations (which include all the cells highlighted in Yellow except the Data Table portion i.e. NPV as a function of WACC) will earn you 5 bonus points.
c. Data Table portion i.e. NPV as a function of WACC and the NPV profile will earn your 5 bonus points.