Coca Cola and Cisco's adjusted stock prices, along with the NYSE Index, are provided on the Data page.Monthly prices for the years 2009 through 2013 have been adjusted for stock splits and dividends. a. Use the information provided on the Data page to calculate monthly rates of return for Coca Cola, Cisco, and the NYSE Index. Use the columns already provided on the Data page.

b. Use the regression function in Excel to estimate Coca Cola?s and Cisco's betas by running regressions of their returns against the NYSE Index?s returns. See the Help! page (see the Help! tab at the bottom) for additional tips on how to run an Excel regression. Appendix B in the back of the textbook also provides some useful tips on conducting a regression analysis using Excel.

Date 200812 200901 200902 200903 200904 200905 200906 200907 200908 200909 200910 200911 200912 201001 201002 201003 201004 201005 201006 201007 201008 201009 201010 201011 201012 201101 201102 201103 201104 201105 201106 201107 201108 201109 201110 201111 201112 201201 201202 201203 201204 201205 201206 201207 201208 201209 Coca Cola Adj Close 18.39 17.58 19.12 18.73 21.38 21.05 21.87 21.40 23.75 23.58 25.48 25.39 24.17 23.48 24.70 24.00 23.08 22.70 24.96 25.31 26.70 27.98 29.03 30.22 28.88 29.37 30.70 31.22 30.92 31.37 31.70 32.84 31.71 32.07 31.78 33.08 31.92 33.03 35.24 36.34 35.59 37.49 38.74 35.86 36.62 35.90 Return -0.044 0.088 -0.020 0.141 -0.015 0.039 -0.021 0.110 -0.007 0.081 -0.004 -0.048 -0.029 0.052 -0.028 -0.038 -0.016 0.100 0.014 0.055 0.048 0.038 0.041 -0.044 0.017 0.045 0.017 -0.010 0.015 0.011 0.036 -0.034 0.011 -0.009 0.041 -0.035 0.035 0.067 0.031 -0.021 0.053 0.033 -0.074 0.021 -0.020 Cisco Adj Close 13.99 13.62 15.67 18.05 17.29 17.43 20.57 20.19 22.00 21.32 21.87 22.37 21.00 22.74 24.33 25.17 21.64 19.91 21.56 18.68 20.47 21.36 17.91 18.91 19.77 17.34 16.08 16.43 15.75 14.64 15.03 14.75 14.59 17.51 17.62 17.09 18.63 18.85 20.06 19.19 15.54 16.34 15.25 18.25 18.26 16.52 Return NYSE Adj Close 5195.79 4617.03 4978.98 5513.36 6004.07 5905.15 6424.28 6643.24 6910.88 6739.45 7092.36 7184.96 6883.78 7035.04 7447.80 7474.40 6791.57 6469.65 6998.99 6704.15 7281.07 7513.35 7430.94 7964.02 8139.16 8438.55 8404.98 8671.41 8477.28 8319.10 8079.44 7528.39 6791.65 7563.38 7484.50 7477.03 7838.48 8113.24 8206.93 8119.06 7463.96 7801.84 7863.93 8014.93 8251.00 8221.40 Return 201210 201211 201212 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312 36.86 35.24 36.20 37.64 39.60 41.45 39.15 39.54 39.51 37.64 37.62 39.29 40.19 41.31 40.66 0.027 -0.044 0.027 0.040 0.052 0.047 -0.055 0.010 -0.001 -0.047 -0.001 0.044 0.023 0.028 -0.016 18.35 19.07 19.96 20.24 20.28 20.47 23.60 23.81 25.21 22.97 23.08 22.39 21.09 22.26 22.00 8260.43 8443.51 8894.71 8868.72 9107.04 9276.88 9302.27 9112.69 9558.83 9270.66 9621.24 10009.64 10183.23 10400.32 10283.42 Cisco's Regression Results SUMMARY OUTPUT Copy and paste the summary output into the table below. Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations ANOVA Regression Residual Total Upper 95.0% Intercept X Variable 1 2. What is Cisco's beta? Check figure: Coefficient for X Variable 1 is = 1.105141536. (Note: This is the beta for Cisco.) 3. From a CAPM perspective, which company is riskier on average? Explain why. c. Assume the risk-free rate on long-term Treasury bonds is 4.35%. Assume also that the average annual return on the NYSE Index is 8.23%. Now use the CAPM to calculate the two companies' required returns. Rf or the risk-free rate = 4.35% Rm or the market return = 8.23% Required return = Coca Cola: Required return (show your steps here) = Required return (show your final answer here) = Cisco: Required return (show your steps here) = Required return (show your final answer here) = Check figure: Cisco's required return is = 8.64%. This difference is referred to as the market risk premium. Risk-free rate + Beta (Rm - Rf)