14 questions. Financial analysis using excel. See attachments for details.

\fSupplementary Exercise 1 Suppose that the expected return on the market is 12% and the risk-free rate is 6%. According to the CAPM, what is the expected return on a stock with a beta of 0.66? Supplementary Exercise 2 Suppose that the risk-free rate is 3% and the market risk premium is 6%. According to the CAPM, what is the expected rate of return on a stock with a beta of 2? Supplementary Exercise 3 The spreadsheet \"Supplementary 3\" contains the monthly adjusted prices for four different stocks and for the S&P 500 index (a broad market index). Compute the returns of a portfolio that is equally invested in each of the four stocks. a. Make a graph that plots the portfolio's returns on the y-axis and the S&P 500 returns on the x-axis. b. Compute the beta and R-squared of the portfolio by adding a trend-line to the graph. What is the beta of the portfolio? What proportion of the portfolio's total risk is systematic? Supplementary Exercise 4 The spreadsheet \"Supplementary 4\" contains monthly adjusted prices for 5 different stocks and the Russell 3000 index (a broad market index). Compute the monthly returns for the market index and for each stock and then complete the following exercises. For each stock, compute the stock's covariance with the market, Cov(ri, rm). Compute the beta of each stock using the formula Cov(ri, rm) / Var(rm) . Compute the beta of each stock using Excel's slope function. What proportion of each stock's total risk is systematic? Compute the monthly returns of a portfolio that consists of all five stocks, where each stock is equally-weighted. f. Compute the portfolio's covariance with the market, Cov(ri, rm). g. Compute the portfolio's beta using the formula Cov(rp, rm) / Var(rm) . h. Compute the portfolio's beta using Excel's slope function. i. Compute the portfolio's beta by taking the weighted average of the individual stocks' betas. j. What proportion of the portfolio's risk is systematic? Use the RSQ function. a. b. c. d. e. Supplementary 5 You have a portfolio that consists of 35% Microsoft stock, 35% Amazon stock, and 30% GE stock. Microsoft has a beta of 1, Amazon has a beta of 3.0, and GE has a beta of 0.5. Treasury bills (the risk free asset) currently offer a return of 4%, and the expected return on the market is 11.5%. a. Use the CAPM to estimate the expected return of each stock. b. What is the beta of your portfolio? c. Compute the expected return of your portfolio using the CAPM equation and the portfolio beta from part b. d. Compute the expected return of your portfolio by taking the weighted average of the individual stocks' expected returns. Year Market Portfolio 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 11.90% 0.40% 26.90% -8.60% 22.80% 16.50% 12.50% -10.06% 23.90% 11.10% -8.50% 3.90% 14.30% 19.10% -14.70% -26.50% 37.30% 23.80% -7.15% 12.16% ABC XYZ Corp.ABC Corp.XYZ Corp. Corp. 14.40% -22.20% 47.50% 7.70% 42.80% 30.70% 11.40% -32.50% 30.50% 1.80% -6.20% 22.30% 4.30% 6.50% -37.80% -27.60% 97.10% 45.85% -11.25% -4.70% 121.20% -33.90% 3.70% 3.10% 17.20% -16.90% -32.80% -30.40% 114.00% -3.70% -33.00% -33.20% 21.60% 17.80% 7.50% -62.30% 65.40% -28.02% -6.33% 26.67% Portfolio proportions Stock A Stock B Stock C Stock D Date 1/3/2007 2/1/2007 3/1/2007 4/2/2007 5/1/2007 6/1/2007 7/2/2007 8/1/2007 9/4/2007 10/1/2007 11/1/2007 12/3/2007 1/2/2008 2/1/2008 3/3/2008 4/1/2008 5/1/2008 6/2/2008 7/1/2008 8/1/2008 9/2/2008 10/1/2008 11/3/2008 12/1/2008 1/2/2009 Portfolio Beta What proportion of the portfolio's total risk is systematic? 25% 25% 25% 25% Price data Stock A Stock B Stock C Stock D S&P 500 17.03 29.34 24.22 19.35 825.88 16.31 26.87 22.85 18.43 903.25 18 26.59 23.21 17.76 896.24 18.66 28.56 25.21 19.96 968.75 19.24 29.37 26.91 20.69 1166.36 19.56 28.21 28.55 22.15 1282.83 18.98 27.75 27.97 22.04 1267.38 20.13 27.59 28.25 24.14 1280 21.49 28.29 27.6 24.24 1400.38 22.01 35.35 30.6 25.22 1385.59 20.03 32.38 24.54 24.55 1322.7 22.41 34.3 24.51 25.1 1330.63 20.4 31.41 20.04 19.86 1378.55 18.66 26.31 19.9 18.91 1468.36 19.41 27.45 19.92 20.06 1481.14 20.7 27.59 18.63 21.08 1549.38 22.67 27.5 23.06 22.08 1526.75 20.84 26.71 21.88 20.46 1473.99 21.37 24.97 24.57 21.14 1455.27 21.77 26.6 21.73 21.92 1503.35 20.16 26.02 16.48 17.96 1530.62 18.15 21.77 12.2 15.37 1482.37 15.97 19.84 11.17 13.34 1420.86 17.6 19.08 10.24 14.18 1406.82 17.96 20.14 10.61 14.42 1438.24 Return data Date Stock A 1/3/2007 2/1/2007 3/1/2007 4/2/2007 5/1/2007 6/1/2007 7/2/2007 8/1/2007 9/4/2007 10/1/2007 11/1/2007 12/3/2007 1/2/2008 2/1/2008 3/3/2008 4/1/2008 5/1/2008 6/2/2008 7/1/2008 8/1/2008 9/2/2008 10/1/2008 11/3/2008 12/1/2008 1/2/2009 Stock B Stock C Stock D S&P 500 Portfolio returnsPortfoli o returns Date 12/1/2008 1/2/2009 2/2/2009 3/2/2009 4/1/2009 5/1/2009 6/1/2009 7/1/2009 8/3/2009 9/1/2009 10/1/2009 11/2/2009 12/1/2009 1/4/2010 2/1/2010 3/1/2010 4/1/2010 5/3/2010 6/1/2010 7/1/2010 8/2/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/3/2011 2/1/2011 3/1/2011 4/1/2011 5/2/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/3/2011 11/1/2011 12/1/2011 1/3/2012 Cov(ri , rm) Beta, Cov(ri, rm) / Var(rm) Beta, using slope function Portfolio Beta, weighted average of stock betas What proportion of the asset's total risk is systematic? Russell 3000 Index Westar Energy Adj. Price Adj. Price 520.6 17.15 476.26 16.79 424.88 14.13 461.14 14.94 508.91 14.94 534.75 15.21 535.62 16.26 576.56 17.04 595.86 17.78 619.87 17.15 603.26 16.84 636.04 18.1 653.13 19.37 628.99 19.02 648.9 19.08 688.74 20.17 702.94 21.43 646.04 19.9 607.92 19.83 649.39 21.91 617.48 21.99 674.75 22.52 700.37 23.51 702.92 23.15 749.48 23.67 765.14 23.99 791.44 24.46 793.85 25.17 816.66 25.92 805.74 25.9 790 25.95 771.11 24.89 723.15 25.7 665.97 25.8 741.66 26.62 737.78 26.97 742.58 28.44 779.22 28.1 Ford Motor Microsoft Company Dendreon Barrick Corp. Gold Corp. Adj. Price Adj. Price 18.01 2.28 15.84 1.86 15.06 1.99 17.13 2.62 18.9 5.96 19.61 5.73 22.31 6.05 22.07 7.97 23.27 7.57 24.28 7.18 26.17 6.97 27.88 8.86 28.9 9.96 26.71 10.8 27.31 11.69 27.9 12.52 29.09 12.97 24.68 11.68 22.01 10.04 24.69 12.72 22.57 11.25 23.56 12.19 25.65 14.07 24.45 15.88 27.01 16.72 26.84 15.89 25.87 14.99 24.72 14.85 25.23 15.41 24.51 14.86 25.48 13.74 26.85 12.16 26.23 11.08 24.54 9.63 26.26 11.63 25.41 10.56 25.79 10.72 29.34 12.42 Adj. Price 4.58 3.37 3.05 4.2 21.2 22.67 25 24.21 23.37 27.99 25.27 27.34 26.28 27.7 31.23 36.47 54.06 43.4 32.33 32.91 35.84 41.18 36.5 35.58 34.92 35.04 33.59 37.43 43.43 42.39 39.44 36.9 12.28 9 10.94 8.64 7.6 13.58 Adj. Price 35.57 36.27 29.21 31.36 28.15 37.04 32.63 33.94 33.75 36.86 34.95 41.71 38.48 34.02 36.8 37.46 42.55 41.31 44.58 40.35 46.03 45.57 47.34 50.96 52.47 46.88 52.24 51.34 50.45 47.35 44.9 47.16 50.43 46.36 49.19 52.72 45.11 49.11 Russell 3000 Index Westar Energy Microsoft Ford Motor Company Dendreon Corp. Barrick Gold Corp. Return Return Return Return Return Return Portfolio Return ASAP Company WACC D E rD 500,000 300,000 TC 25% rE 11% 6% Elizabeth Company WACC E(rM) 21% rD 8% TC 25% 0.70 1,000,000 D rf E 4% 1,000,000 Abby Company WACC E(rM) 20% rD 10% TC 30% Cov(rAbby,rM) D Var(rM) rF E 0.13 1,500,000 0.11 7% 3,000,000 Ever-Lasting's WACC E(rM) 18% rD 8% TC 30% EverLasting D E 1.00 1,250,000 2,000,000