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.