Problem 1: Estimating Beta for Disney

The monthly return data for Disney stock and S&P500 for the period of Sept 2008 ~ Sept 2013 are provided in Homework1_Problem1_Disney.xls. Estimate the beta of Disney stock for this period using regression analysis. [See Handout 1 and Handout 2]

Ri= ?+ ?Rm

Where Riis the monthly return of Disney stock, and Rmis the monthly return of S&P 500 index.

Based on your regression results, answer the following questions:

1)What is the estimate of ??

2)What is the estimate of ?and its 95% Confidence Interval?0.252761

3)The annual risk free rate during the period was 0.48%, what was the monthly risk free rate? Show your calculation.

0.48%/12=0.04%

4)What is Jensen's Alpha? Did Disney stock perform better or worse than expected?

Problem 2: Impact of Financial Leverage on Disney Beta

Based on the beta you have estimated in Problem 1 for Disney, answer the following questions:

1)Calculate the unlevered beta for the company, assuming the marginal tax rate for the company was 38% and the average debt/equity ratio over the period of the regression was 25%.

2)Based on the unlevered beta, calculate levered betas for different level of financial leverage, and fill out the following table, assuming a constant marginal tax rate of 38%.

Debt to Capital Debt/Equity Ratio Levered Beta

0.00%

10.00%

20.00%

30.00%

40.00%

50.00%

60.00%

70.00%

80.00%

90.00%

Problem 3: Estimate Bottom-up Beta for Disney

Based on the data provided in "Homework1_Problem3_Disney Bottom Up Beta.xls" for Disney, calculate bottom-up beta for Disney:

a)Estimate unlevered beta and unlevered beta corrected for cash for each business segment;

b)Estimated unlevered beta for the firm;

c)Estimate beta for the firm.

Show your calculation formula, and fill out and print the table in "Homework1_Problem3_Disney Bottom Up Beta.xls".

to tutor?

I need to know which number in EXCEL means. all the equation used in these question.

Sheet1 SUMMARY OUTPUT Regression Statistics Multiple R 0.569312422 R Square 0.324116634 Adjusted R Sq 0.312259031 Standard Error 0.050895589 Observations 59 ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 0.070805127 0.070805127 27.33407728 2.5344E-006 57 0.147650575 0.002590361 58 0.218455702 Coefficients Standard Error t Stat P-value Lower 95% -8.9302E-005 0.007190557 -0.01241929 0.990134461 -0.01448815 1.398683652 0.267526797 5.228200195 2.5344E-006 0.862970252 Page 1 Sheet1 nificance F Upper 95% Lower 95.0% Upper 95.0% 0.014309547 -0.01448815 0.014309547 1.934397052 0.862970252 1.934397052 Page 2 Return on Disney (including dividends) Figure 3.4: Returns on Disney: 2004 2008 25.00% 20.00% 15.00% 10.00% 5.00% 0.00% 5.00% 10.00% 15.00% 20.00% 25.00% Jun13 Feb13 Oct12 Jun12 Feb12 Oct11 Jun11 Feb11 Oct10 Jun10 Feb10 Oct09 Jun09 Feb09 Oct08 Month RISK RETURN Current riskfree rate = Number of periods of data= 2.50% 61 Risk premium for stocks= Riskfree rate during period = 10 5.75% 0.48% RISK RETURN Month Sep-08 Oct-08 Nov-08 Dec-08 Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 Nov-09 Dec-09 Jan-10 Feb-10 Mar-10 Apr-10 May-10 Jun-10 Jul-10 Aug-10 Sep-10 Oct-10 Nov-10 Dec-10 Time period 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Index 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 Price(Stock) $30.69 $25.91 $22.52 $22.69 $20.68 $16.77 $18.16 $21.90 $24.22 $23.33 $25.12 $26.04 $27.46 $27.37 $30.22 $32.25 $29.55 $31.24 $34.91 $36.84 $33.42 $31.50 $33.69 $32.54 $33.10 $36.13 $36.51 $37.51 DPS(Stock) Split Factor 1 1 1 1 0.35 1 1 1 1 1 1 1 1 1 1 1 1 0.35 1 1 1 1 1 1 1 1 1 1 1 1 0.4 11 Index Level 1166.36 968.75 896.24 903.25 825.88 735.09 797.87 872.81 919.14 919.32 987.48 1020.62 1057.08 1036.19 1095.63 1115.10 1073.87 1104.49 1169.43 1186.69 1089.41 1030.71 1101.60 1049.33 1141.20 1183.26 1180.55 1257.64 Dividends on market 2.282 1.744 2.975 2.429 1.329 3.135 1.493 1.342 2.405 1.656 1.270 2.442 1.598 1.273 2.660 1.683 1.177 2.579 1.685 1.197 2.620 1.744 1.285 2.628 1.737 1.343 2.895 1.789 RISK RETURN Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11 Dec-11 Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 Jan-13 Feb-13 Mar-13 Apr-13 May-13 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 $38.87 $43.74 $43.09 $43.10 $41.63 $39.04 $38.62 $34.06 $30.16 $34.88 $35.85 $37.50 $38.90 $41.99 $43.78 $43.11 $45.71 $48.50 $49.14 $49.47 $52.28 $49.12 $49.66 $49.79 $53.88 $54.59 $56.80 $62.84 $63.08 0.6 0.75 12 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1286.12 1327.22 1325.83 1363.61 1345.20 1320.64 1292.28 1218.89 1131.42 1253.30 1246.96 1257.60 1312.41 1365.68 1408.47 1397.91 1310.33 1362.16 1379.32 1406.58 1440.67 1412.16 1416.18 1426.19 1498.11 1514.68 1569.19 1597.57 1630.74 1.316 2.934 1.902 1.444 2.950 2.079 1.541 3.089 1.856 1.663 3.517 2.059 1.531 3.442 2.039 1.707 3.646 2.093 1.734 3.815 2.245 1.953 4.088 2.828 1.916 3.758 2.272 1.816 4.205 RISK RETURN Jun-13 Jul-13 Aug-13 Sep-13 58 59 60 61 1 1 1 1 $63.15 $64.65 $60.83 $64.49 13 1 1 1 1 1606.28 1685.73 1632.97 1681.55 2.577 2.242 4.025 2.633 RISK RETURN REGRESSION STATISTICS AND RISK PARAMETERS REGRESSION STATISTICS AND RISK PARAMETERS 95% CI 95% CI Upper Bound RISK AND PERFORMANCE MEASURES Lower Bound Intercept (Alpha) = Slope (Beta)= Monthly Risk FRf = Rf(1 Beta) = Jensen's Alpha InterceptRf(1Beta)= R squared = 14 RISK RETURN Current stock price= Current Annual DPS $68.89 $0.75 15 RISK RETURN Return(Stock) Disney 15.58% 13.08% 2.31% 8.86% 18.91% 8.29% 20.59% 10.59% 3.67% 7.67% 3.66% 5.45% 0.33% 10.41% 7.88% 8.37% 5.72% 11.75% 5.53% 9.28% 5.75% 6.95% 3.41% 1.72% 9.14% 1.07% 3.83% Return(Mkt) S&P 500 16.94% 7.48% 0.78% 8.57% 10.99% 8.54% 9.39% 5.31% 0.02% 7.41% 3.36% 3.57% 1.98% 5.74% 1.78% 3.70% 2.85% 5.88% 1.48% 8.20% 5.39% 6.88% 4.74% 8.76% 3.69% 0.23% 6.53% 16 RISK RETURN 3.63% 12.53% 1.49% 0.02% 3.41% 6.22% 1.08% 11.81% 11.45% 15.65% 2.78% 6.28% 3.73% 7.94% 4.26% 1.53% 6.03% 6.10% 1.32% 0.67% 5.68% 6.04% 1.10% 1.77% 8.21% 1.32% 4.05% 10.63% 0.38% 2.26% 3.20% 0.10% 2.85% 1.35% 1.83% 2.15% 5.68% 7.18% 10.77% 0.51% 0.85% 4.36% 4.06% 3.13% 0.75% 6.27% 3.96% 1.26% 1.98% 2.42% 1.98% 0.28% 0.71% 5.04% 1.11% 3.60% 1.81% 2.08% 17 RISK RETURN 0.11% 2.38% 5.91% 6.02% 1.50% 4.95% 3.13% 2.97% 18 RISK RETURN 19 Estimate Bottom-up Beta - Disney Step 1: Estimate Unlevered Betas for Each Business Segment Business Segments Comparable Firms Number of Firms Average levered Beta Median D/E Corporate Tax Rate Unlevered Beta Unlevered Cash/Firm Corrected Beta Value for Cash Media Networks Radio and TV Broadcasting Companies 24.00 1.22 20.45% 35.00% 0.75% Parks & Resorts Studio Entertainment Theme Park & Entertainment Firms Movie Companies 9.00 11.00 1.58 1.16 120.76% 27.96% 35.00% 35.00% 2.77% 14.08% Consumer Products Toy and Apparel Retailers; Entertainment Software 77.00 1.06 9.18% 35.00% 12.08% Step 2: Estimate Unlevered Betas for the Firm Business Segments Media Networks Parks & Resorts Studio Entertainment Consumer Products Disney Revenue in 2002 (Millions Enterprise of US $) Value/Sales $10,941 $6,412 $7,364 $2,344 $27,061 3.41 2.37 2.63 1.63 Enterprise % of Firm Value Value Unlevered Beta Step 3: Estimate Levered Beta for the Firm Input Firm-Specific Data Marginal Tax Rate of the firm = Market Value of Equity of the firm = Market Value of Debt of the firm = Debt/Equity ratio for the firm = Beta for the Firm = Source: Applied Corporate Finance, Damodaran, 2nd Edition. 37% $ 55,101 $ 14,668