Question
3) The case is divided into 2 parts each with one D/E calculation with different D and E numbers. a. Successful completion of Case A
3) The case is divided into 2 parts each with one D/E calculation with different D and E
numbers.
a. Successful completion of Case A calculations (which include all the cells
highlighted in Yellow - except the Data Table portion i.e. NPV as a function of
WACC).
b. Successful completion of Case B calculations (which include all the cells
highlighted in Yellow - except the Data Table portion i.e. NPV as a function of
WACC)
c. Successful completion of the Data Table portion i.e. NPV as a function of WACC
and the NPV profil
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: 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.07 as the Beta for ELP. (This is current as of 04/21/2018)
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)
Step 11: Complete the Worksheet "Answer Tab 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.
Finally, the cells are formatted to display in a certain format, please pay attention to what is
displayed.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started