Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Question is attached below via excel......................................... Date 200812 200901 200902 200903 200904 200905 200906 200907 200908 200909 200910 200911 200912 201001 201002 201003 201004 201005

Question is attached below via excel.........................................

image text in transcribed 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 Cisco Adj Close Return Adj Close Return 18.39 13.99 17.58 -0.044 13.62 19.12 0.088 15.67 18.73 -0.020 18.05 21.38 0.141 17.29 21.05 -0.015 17.43 21.87 0.039 20.57 21.40 -0.021 20.19 23.75 0.110 22.00 23.58 -0.007 21.32 25.48 0.081 21.87 25.39 -0.004 22.37 24.17 -0.048 21.00 23.48 -0.029 22.74 24.70 0.052 24.33 24.00 -0.028 25.17 23.08 -0.038 21.64 22.70 -0.016 19.91 24.96 0.100 21.56 25.31 0.014 18.68 26.70 0.055 20.47 27.98 0.048 21.36 29.03 0.038 17.91 30.22 0.041 18.91 28.88 -0.044 19.77 29.37 0.017 17.34 30.70 0.045 16.08 31.22 0.017 16.43 30.92 -0.010 15.75 31.37 0.015 14.64 31.70 0.011 15.03 32.84 0.036 14.75 31.71 -0.034 14.59 32.07 0.011 17.51 31.78 -0.009 17.62 33.08 0.041 17.09 31.92 -0.035 18.63 33.03 0.035 18.85 35.24 0.067 20.06 36.34 0.031 19.19 35.59 -0.021 15.54 37.49 0.053 16.34 38.74 0.033 15.25 35.86 -0.074 18.25 36.62 0.021 18.26 35.90 -0.020 16.52 NYSE Adj Close Return 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 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) Access Microsoft's Excel's regression tool by clicking Data Analysis under the Data tab. Then, using the arrow key to scroll down the dialog box, select Regression and click on OK. (Data > Data Analysis > Regression > OK). If you do not have the Data Analysis package, you will have to add the Analysis ToolPak by clicking the Microsoft Office button (which is at the left top corner), click the Excel Options tab on the right bottom of the box that appears, and then click Add-Ins. From the Manage box at the bottom, select Excel Add-Ins and click on Go. From the next dialog box, select the AnalysisToolPak check box and click OK. The Regression dialog box that appears (see image to the right) requires entering the Y and X variable ranges, and has additional options pertaining to what output is to be produced and where it should be displayed. For Coca Cola stock, the \"Input Y Range\" prompt requires that cells C4:C63 be entered as the dependent variable of the regression as shown in the image. Similarly, for the NYSE Index, the \"Input X Range\" prompt requires comparable cells as the independent variable. For the purposes of this assignment, none of the additional options are chosen, and the regression output relies upon the default selection of being displayed on an additional worksheet. Click OK to perform the indicated regression. der the Data tab. Then, and click on OK. (Data > e Analysis ToolPak by click the Excel Options tab From the Manage box at og box, select the ires entering the Y and X t is to be produced and e\" prompt requires that as shown in the image. omparable cells as the are chosen, and the d on an additional Note that the cell range identified below does not include a label or heading (the Labels box is left unchecked), just the data cells. In this case, Excel will provide the label "X Variable 1" for the independent variable

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Forecasting Methods And Applications

Authors: Spyros G. Makridakis, Steven C. Wheelwright, Rob J Hyndman

3rd Edition

0471532339, 9780471532330

More Books

Students also viewed these Finance questions

Question

Are there diff erent kinds of memory?

Answered: 1 week ago