1. Follow Steps 1 through 6 as follows and create a separate sheet (tab) in excel (in the same file) when instructed. Also, Create a separate sheet (tab) and call it “Answer” for the following questions 2 to 7. Based on the nature of the question, derive the answers by connecting the cells from a relevant sheet (tab) you created in Question 1 and put comments to clearly explain it. All answers for Questions 2 to 7 should be in the “Answer” sheet.

Important: All the data and other information you will need for this case can be downloaded from any sources that may be convenient for you. https://finance.yahoo.com/ is a good source for all such data and information.

Steps Are:

1. Download the 10-yr TBond Yield (rate) for the last 252 days from December 1, 2020 on a spreadsheet and label it as TBond-rate

2. Calculate the Average, Standard Deviation, Standard Error, and 95% confidence interval of the 10-yr TBond data you have downloaded and show the results in the spreadsheet TBond-rate as well as in the Answer sheet by linking the results

3. Download Walt Disney stock closing price ( Ticker Symbol: DIS) for the last 252 days from December 1, 2020 on a sperate sheet and label it as Disney-Equity-Price

4. Calculate the Average, Standard Deviation, Standard Error, and 95% confidence interval of Walt Disney closing price data you have downloaded in step 3 and show the results in the spread sheet Disney-Equity-Price as well as in the Answer sheet by linking the results

5. Read the most recent Equity Beta of Walt Disney at https://finance.yahoo.com/ and record it in the Answer sheet along with date of your recording.

6. Read the number of shares of Walt Disney outstanding from https://finance.yahoo.com/ and record it in the Answer sheet. The number of shares is not readily available at yahoo. You may have to do some simple calculation to obtain it. You will be using this number for future calculation.

PART 2

2. (5 Points) Compute the weights ( with respect to the total asset value) for Disney’s equity and debt based on the market value of equity taking the average equity value you calculated in step 4 above , number of shares outstanding for Disney from step 6 above, and Disney’s market value of debt of $17,314 Million.

PART 3

3. (5 Points) Calculate Disney’s cost of equity capital using the CAPM, the average risk-free rate which is 10 yr T-bond rate you calculated in step 2 above, the value of beta recorded in step 5 above, and a market return of 5%.

PART 4

4. ( 5 points) Calculate the 95% confidence interval of Disney’s equity cost of capital using the calculation you made in step 2 above.

5. (5 Points) Assuming that Disney has a tax rate of 20% and a cost of debt of 4.018%, calculate its after-tax debt cost of capital.

6. (10 Points) Calculate Disney’s Unlevered and Levered WACC.

7. (10 Points) Calculate Disney’s net debt by subtracting its cash value of $4,333 Million from its debt. Recalculate the weights for the WACC using the market value of equity, net debt (=Total Debt -Cash), and cash-adjusted asset value. Recalculate Disney’s Levered WACC using the weights based on the net debt. How much does it change in terms of percentage?

Answer rating: 100% (QA)

Part 2 Average of TBills is 1043 stdev is 0380 standard error is 0024 95 confidence in... View the full answer