Complete excel worksheet. Use video if necessary. https://www.youtube.com/watch?v=Q7uniN25vno

Analyzing Historical Risk vs. Return for a Company. Choose a company that you are using in the investment challenge and complete the following steps. Follow the 6 steps below. Submit your answers on these sheets to the Drop Box Step 1: Gather the most recent 61 monthly stock prices for the S&P500 and your company using http://finance.yahoo.com. a. Go to the above website and enter "^GSPC" in the Enter Symbol box in upper left-hand home page of Yahoo! Finance. b. Click on Historical Prices on the left-hand side. c. Choose a start date 5 years prior to today's date, change to monthly, and click Get Historical Data to get 61 months of data. (You need 61 observations to calculate 60 returns.) d. Scroll down to the bottom of the page click on Download Spreadsheet Format and copy (DO NOT CUT) the closing prices and dates into the highlighted areas of this spreadsheet under the tab 2.Calc. Returns below Step 2: Repeat this process of step 1 for a company of your choosing. NOTE: The price series is converted into a return series by calculating Return t = (Pt+1-Pt)/Pt + Divt+1/Pt. When the information is downloaded using adjusted closing prices the Prices are automatically adjusted to include dividend information. Therefore it is only necessary to calculate the change in Price divided by the beginning monthly Price to calculate returns. This is automatically done for you in the spreadsheet tab 2.Calc. Returns, by copying the adjusting monthly closing prices to cell D2:D62. Make sure you have 61 monthly observations with the same starting month for your company and S&P 500 data. Step 3: Interpretation of Line Graph. (Graph is automatically created hit tab Line Graph at bottom of spreadsheet.) Refer to the Line Graph spreadsheet to answer the following questions. (Note place cursor on a point to get values.) The returns for your company and the S&P 500 are shown in reverse chronological order (most recent is first). Helpful Hint: Placing the cursor on the point in the line graph will display the actual numerical input values for that point. a. What is the most recent month in which the company return moves in the opposite dirrection of the S&P 500 return?______ (i.e. the monthly return is going up and the market return is going down, or v.v.) b. In what month was the largest positive monthly return for the company in the past five years?_______ c. What was the largest positive monthly return?________ d. In what month was the largest negative monthly return for the company in the past five years?_______ e. What was the largest negative monthly return?________ f. Based on the line Graph 1 would you say that your company returns and S&P 500 returns ________. 1) always move in opposite directions (i.e. when one is positive the other will be negative). 2) seldom move in the same direction. 3) sometimes move in the same direction. 4) often move in the same direction. 5) always move in the same direction. Step 4: Explanation of Statistical Calculations Using the Calculating Statistic spreadsheet and your text book write the formulas and describe the calculations for the following cells: Cell I68 is shown as an example. I68 a. Expected Monthly Return (E(R)) for the company: = C66/60, where C66 is the sum of monthly returns, therefore this is the average monthly return for company I69 b. Variance for the company: I70 c. Standard Deviation for the company: F68 d. Expected Return (E(R)) for the market index: F69 e. Variance for the market index: F70 f. Standard Deviation for the market index: Step 5: Compare the calculated Beta with the reported Beta on Yahoo! Finance. Covariance is a statistical measurement that caculates how two series move in relationship to each other. The calculations for covariance are shown in collumn H of the 5.Calc Beta spreadsheet. Answer the following questions referring to the Calculate Beta spreadsheet. a. What is the most recent month for which the company return and market return were less than their expected returns?________ (i.e. both had negative amounts in collumn E and collumn G.) b. Was the covariance for this month positive or negative?________ c. What is the most recent month for which the company return and market return were greater than their expected returns?________ d. Was the covariance for this month positive or negative?________ e. What is the most recent month for which the company return and market return moved in opposite directions than their expected returns?________ f. Was the covariance for this month positive or negative?_________ g. Look up your company's Beta on Yahoo!Finance by doing the following steps: 1. Enter the ticker symbol under get quotes. 2. Click on Key Statistics on the left-hand side 3. Beta is on the right-hand side of the screen, one of the first statistical measures reported. Don't be surprised if your Beta is different than the beta reported on yahoo finance. Regression Instructions Select Data and then Data Analysis from top options of Excel. (See instructions on Regression tab if Data Analysis option is not there). Select Regression. Input the Company Returns (collumn C of 6.Regression spreadsheet for Y variable) Input the Market Risk Premium (collumn B of 6.Regression spreadsheet for X var.) Select OK and Regression Output should be created on a new page. Print out the output that was generated and answer the following questions on the output page NOTE: There is an Example of MLHR Regression Output on Tab below. 1. On the output page identify and circle the following a. y-intercept b. measurement of how well regression model estimates company returns c. How well does this regression model fit the data? 2. Write the regression equation from the regression output. After completing the assignment place in dropbox for course. monDate S&P 500 MLHR 1 6/1/2012 1324.18 17.06 2 5/1/2012 1310.33 18.48 3 4/2/2012 1397.91 19.51 4 3/1/2012 1408.47 22.93 5 2/1/2012 1365.68 20.98 6 1/3/2012 1312.41 21.05 7 12/1/2011 1257.6 18.41 8 11/1/2011 1246.96 21.54 9 10/3/2011 1253.3 20.58 10 9/1/2011 1131.42 17.8 11 8/1/2011 1218.89 19.8 12 7/1/2011 1292.28 22.91 13 6/1/2011 1320.64 27.1 14 5/2/2011 1345.2 24.99 15 4/1/2011 1363.61 25.88 16 3/1/2011 1325.83 27.34 17 2/1/2011 1327.22 26.8 18 1/3/2011 1286.12 23.98 19 12/1/2010 1257.64 25.14 20 11/1/2010 1180.55 21.41 21 10/1/2010 1183.26 19.09 22 9/1/2010 1141.2 19.54 23 8/2/2010 1049.33 16.27 24 7/1/2010 1101.6 17.05 25 6/1/2010 1030.71 18.71 26 5/3/2010 1089.41 19.07 27 4/1/2010 1186.69 20.98 28 3/1/2010 1169.43 17.89 29 2/1/2010 1104.49 18.02 30 1/4/2010 1073.87 16.71 31 12/1/2009 1115.1 15.82 32 11/2/2009 1095.63 15.02 33 10/1/2009 1036.19 15.26 34 9/1/2009 1057.08 16.7 35 8/3/2009 1020.62 16.02 36 7/1/2009 987.48 16.38 37 6/1/2009 919.32 15.13 38 5/1/2009 919.14 14.04 39 4/1/2009 872.81 14.64 40 3/2/2009 797.87 10.5 41 2/2/2009 735.09 9.93 42 1/2/2009 825.88 10.73 43 12/1/2008 903.25 12.73 44 11/3/2008 896.24 14.37 45 10/1/2008 968.75 21.35 46 9/2/2008 1166.36 23.74 47 8/1/2008 1282.83 27.3 48 7/1/2008 1267.38 25.28 49 6/2/2008 1280 24.07 50 5/1/2008 1400.38 23.99 51 4/1/2008 1385.59 22.48 52 3/3/2008 1322.7 23.68 53 2/1/2008 1330.63 28.75 54 1/2/2008 1378.55 30.54 55 12/3/2007 1468.36 31.13 56 11/1/2007 1481.14 26.38 57 10/1/2007 1549.38 26.06 58 9/4/2007 1526.75 25.99 59 8/1/2007 1473.99 27.79 60 7/3/2007 1455.27 29.14 61 6/3/2007 1503.35 30.16 Mkt Returns 0.010569856 -0.062650671 -0.007497497 0.031332377 0.04058945 0.043583015 0.008532752 -0.005058645 0.107723038 -0.071762013 -0.056791098 -0.021474437 -0.018257508 -0.013500928 0.028495358 -0.001047302 0.031956583 0.02264559 0.065300072 -0.002290283 0.036855941 0.087551104 -0.047449165 0.068777833 -0.053882377 -0.081975916 0.014759327 0.058796368 0.028513693 -0.036974262 0.017770598 0.057363997 -0.019761986 0.035723384 0.033560173 0.074141757 0.000195835 0.053081427 0.093925076 0.085404508 -0.109931225 -0.085657348 0.007821566 -0.074849032 -0.169424534 -0.090791453 0.012190503 -0.009859375 -0.085962382 0.010674153 0.047546685 -0.005959583 -0.034761162 -0.061163475 -0.008628489 -0.044043424 0.014822335 0.035794001 0.012863592 -0.031981907 Co. Returns -0.07683983 -0.05279344 -0.14914959 0.092945663 -0.00332542 0.143400326 -0.14531105 0.04664723 0.156179775 -0.1010101 -0.13574858 -0.15461255 0.084433774 -0.03438949 -0.05340161 0.020149254 0.117597998 -0.04614161 0.174217655 0.121529597 -0.02302968 0.200983405 -0.0457478 -0.08872261 -0.01887782 -0.09103908 0.172722191 -0.00721421 0.07839617 0.056257901 0.053262317 -0.01572739 -0.08622754 0.042446941 -0.02197802 0.082617317 0.077635328 -0.04098361 0.394285714 0.057401813 -0.07455732 -0.15710919 -0.11412665 -0.32693208 -0.10067397 -0.13040293 0.079905063 0.050270046 0.003334723 0.067170819 -0.05067568 -0.17634783 -0.05861166 -0.01895278 0.180060652 0.012279355 0.002693344 -0.0647715 -0.04632807 -0.03381963 Input Worksheet In the highlighted collumn D copy a series of closing monthly prices for your company. This price series in collumn D is then converted to a return series in collumn F. NOTE: You will copy over MLHR data and the S&P 500 series is already done for you. -0.1 -0.3 -0.4 2/1/2012 4/2/2012 8/1/2007 10/1/2007 12/3/2007 2/1/2008 4/1/2008 6/2/2008 8/1/2008 10/1/2008 12/1/2008 2/2/2009 4/1/2009 6/1/2009 8/3/2009 10/1/2009 12/1/2009 2/1/2010 4/1/2010 6/1/2010 8/2/2010 10/1/2010 12/1/2010 2/1/2011 4/1/2011 6/1/2011 8/1/2011 10/3/2011 12/1/2011 -0.2 6/1/2012 Company vs. S&P 500 over time 0.5 0.4 0.3 0.2 0.1 Mkt Returns Co. Returns 0 This spreadsheet calculates the Expected Returns, Variances, and Standard Deviations of 2 Return Series. Page down to bottom for results Date Jun-12 May-12 Apr-12 Mar-12 Feb-12 Jan-12 Dec-11 Nov-11 Oct-11 Sep-11 Aug-11 Jul-11 Jun-11 May-11 Apr-11 Mar-11 Feb-11 Jan-11 Dec-10 Nov-10 Oct-10 Sep-10 Aug-10 Jul-10 Jun-10 May-10 Apr-10 Mar-10 Feb-10 Jan-10 Dec-09 Nov-09 Oct-09 Sep-09 Aug-09 Jul-09 Jun-09 May-09 Apr-09 Mar-09 Feb-09 Jan-09 Dec-08 Nov-08 Oct-08 Sep-08 Aug-08 Jul-08 Jun-08 May-08 Apr-08 Mar-08 Feb-08 Jan-08 Dec-07 Nov-07 Oct-07 Sep-07 Aug-07 Jul-07 Sums: Market Returns 0.01057 -0.06265 -0.00750 0.03133 0.04059 0.04358 0.00853 -0.00506 0.10772 -0.07176 -0.05679 -0.02147 -0.01826 -0.01350 0.02850 -0.00105 0.03196 0.02265 0.06530 -0.00229 0.03686 0.08755 -0.04745 0.06878 -0.05388 -0.08198 0.01476 0.05880 0.02851 -0.03697 0.01777 0.05736 -0.01976 0.03572 0.03356 0.07414 0.00020 0.05308 0.09393 0.08540 -0.10993 -0.08566 0.00782 -0.07485 -0.16942 -0.09079 0.01219 -0.00986 -0.08596 0.01067 0.04755 -0.00596 -0.03476 -0.06116 -0.00863 -0.04404 0.01482 0.03579 0.01286 -0.03198 -0.03453 E(R): Variance: Standard Deviation: Company Returns -0.07684 -0.05279 -0.14915 0.09295 -0.00333 0.14340 -0.14531 0.04665 0.15618 -0.10101 -0.13575 -0.15461 0.08443 -0.03439 -0.05340 0.02015 0.11760 -0.04614 0.17422 0.12153 -0.02303 0.20098 -0.04575 -0.08872 -0.01888 -0.09104 0.17272 -0.00721 0.07840 0.05626 0.05326 -0.01573 -0.08623 0.04245 -0.02198 0.08262 0.07764 -0.04098 0.39429 0.05740 -0.07456 -0.15711 -0.11413 -0.32693 -0.10067 -0.13040 0.07991 0.05027 0.00333 0.06717 -0.05068 -0.17635 -0.05861 -0.01895 0.18006 0.01228 0.00269 -0.06477 -0.04633 -0.03382 -0.17676 Distance from Mean Variance(M) (RM - E(RM)) (RM - E(RM))2 0.01115 0.00012 -0.06208 0.00385 -0.00692 0.00005 0.03191 0.00102 0.04116 0.00169 0.04416 0.00195 0.00911 0.00008 -0.00448 0.00002 0.10830 0.01173 -0.07119 0.00507 -0.05622 0.00316 -0.02090 0.00044 -0.01768 0.00031 -0.01293 0.00017 0.02907 0.00085 -0.00047 0.00000 0.03253 0.00106 0.02322 0.00054 0.06588 0.00434 -0.00171 0.00000 0.03743 0.00140 0.08813 0.00777 -0.04687 0.00220 0.06935 0.00481 -0.05331 0.00284 -0.08140 0.00663 0.01533 0.00024 0.05937 0.00353 0.02909 0.00085 -0.03640 0.00132 0.01835 0.00034 0.05794 0.00336 -0.01919 0.00037 0.03630 0.00132 0.03414 0.00117 0.07472 0.00558 0.00077 0.00000 0.05366 0.00288 0.09450 0.00893 0.08598 0.00739 -0.10936 0.01196 -0.08508 0.00724 0.00840 0.00007 -0.07427 0.00552 -0.16885 0.02851 -0.09022 0.00814 0.01277 0.00016 -0.00928 0.00009 -0.08539 0.00729 0.01125 0.00013 0.04812 0.00232 -0.00538 0.00003 -0.03419 0.00117 -0.06059 0.00367 -0.00805 0.00006 -0.04347 0.00189 0.01540 0.00024 0.03637 0.00132 0.01344 0.00018 -0.03141 0.00099 0.18032 Market -0.00058 0.00301 0.05482 Variance(C) (RC - E(RC)) (RC - E(RC))2 -0.07389 0.00546 -0.04985 0.00248 -0.14620 0.02138 0.09589 0.00920 -0.00038 0.00000 0.14635 0.02142 -0.14237 0.02027 0.04959 0.00246 0.15913 0.02532 -0.09806 0.00962 -0.13280 0.01764 -0.15167 0.02300 0.08738 0.00764 -0.03144 0.00099 -0.05046 0.00255 0.02310 0.00053 0.12054 0.01453 -0.04320 0.00187 0.17716 0.03139 0.12448 0.01549 -0.02008 0.00040 0.20393 0.04159 -0.04280 0.00183 -0.08578 0.00736 -0.01593 0.00025 -0.08809 0.00776 0.17567 0.03086 -0.00427 0.00002 0.08134 0.00662 0.05920 0.00351 0.05621 0.00316 -0.01278 0.00016 -0.08328 0.00694 0.04539 0.00206 -0.01903 0.00036 0.08556 0.00732 0.08058 0.00649 -0.03804 0.00145 0.39723 0.15779 0.06035 0.00364 -0.07161 0.00513 -0.15416 0.02377 -0.11118 0.01236 -0.32399 0.10497 -0.09773 0.00955 -0.12746 0.01625 0.08285 0.00686 0.05322 0.00283 0.00628 0.00004 0.07012 0.00492 -0.04773 0.00228 -0.17340 0.03007 -0.05567 0.00310 -0.01601 0.00026 0.18301 0.03349 0.01523 0.00023 0.00564 0.00003 -0.06183 0.00382 -0.04338 0.00188 -0.03087 0.00095 0.78557 Company -0.00295 0.01309 0.11442 This spreadsheet calculates the Covariance and Beta of a Company Return Series with a Market Series. Page down to bottom for results Date Jun-12 May-12 Apr-12 Mar-12 Feb-12 Jan-12 Dec-11 Nov-11 Oct-11 Sep-11 Aug-11 Jul-11 Jun-11 May-11 Apr-11 Mar-11 Feb-11 Jan-11 Dec-10 Nov-10 Oct-10 Sep-10 Aug-10 Jul-10 Jun-10 May-10 Apr-10 Mar-10 Feb-10 Jan-10 Dec-09 Nov-09 Oct-09 Sep-09 Aug-09 Jul-09 Jun-09 May-09 Apr-09 Mar-09 Feb-09 Jan-09 Dec-08 Nov-08 Oct-08 Sep-08 Aug-08 Jul-08 Jun-08 May-08 Apr-08 Mar-08 Feb-08 Jan-08 Dec-07 Nov-07 Oct-07 Sep-07 Aug-07 Jul-07 Sums: Market Returns 0.01057 -0.06265 -0.00750 0.03133 0.04059 0.04358 0.00853 -0.00506 0.10772 -0.07176 -0.05679 -0.02147 -0.01826 -0.01350 0.02850 -0.00105 0.03196 0.02265 0.06530 -0.00229 0.03686 0.08755 -0.04745 0.06878 -0.05388 -0.08198 0.01476 0.05880 0.02851 -0.03697 0.01777 0.05736 -0.01976 0.03572 0.03356 0.07414 0.00020 0.05308 0.09393 0.08540 -0.10993 -0.08566 0.00782 -0.07485 -0.16942 -0.09079 0.01219 -0.00986 -0.08596 0.01067 0.04755 -0.00596 -0.03476 -0.06116 -0.00863 -0.04404 0.01482 0.03579 0.01286 -0.03198 -0.03453 Company Returns -0.07684 -0.05279 -0.14915 0.09295 -0.00333 0.14340 -0.14531 0.04665 0.15618 -0.10101 -0.13575 -0.15461 0.08443 -0.03439 -0.05340 0.02015 0.11760 -0.04614 0.17422 0.12153 -0.02303 0.20098 -0.04575 -0.08872 -0.01888 -0.09104 0.17272 -0.00721 0.07840 0.05626 0.05326 -0.01573 -0.08623 0.04245 -0.02198 0.08262 0.07764 -0.04098 0.39429 0.05740 -0.07456 -0.15711 -0.11413 -0.32693 -0.10067 -0.13040 0.07991 0.05027 0.00333 0.06717 -0.05068 -0.17635 -0.05861 -0.01895 0.18006 0.01228 0.00269 -0.06477 -0.04633 -0.03382 -0.17676 E(R): Variance and Covariance: Standard Deviation: Distance from Mean (RM - E(RM)) 0.01115 -0.06208 -0.00692 0.03191 0.04116 0.04416 0.00911 -0.00448 0.10830 -0.07119 -0.05622 -0.02090 -0.01768 -0.01293 0.02907 -0.00047 0.03253 0.02322 0.06588 -0.00171 0.03743 0.08813 -0.04687 0.06935 -0.05331 -0.08140 0.01533 0.05937 0.02909 -0.03640 0.01835 0.05794 -0.01919 0.03630 0.03414 0.07472 0.00077 0.05366 0.09450 0.08598 -0.10936 -0.08508 0.00840 -0.07427 -0.16885 -0.09022 0.01277 -0.00928 -0.08539 0.01125 0.04812 -0.00538 -0.03419 -0.06059 -0.00805 -0.04347 0.01540 0.03637 0.01344 -0.03141 Distance from Mean (RC - E(RC)) -0.07389 -0.04985 -0.14620 0.09589 -0.00038 0.14635 -0.14237 0.04959 0.15913 -0.09806 -0.13280 -0.15167 0.08738 -0.03144 -0.05046 0.02310 0.12054 -0.04320 0.17716 0.12448 -0.02008 0.20393 -0.04280 -0.08578 -0.01593 -0.08809 0.17567 -0.00427 0.08134 0.05920 0.05621 -0.01278 -0.08328 0.04539 -0.01903 0.08556 0.08058 -0.03804 0.39723 0.06035 -0.07161 -0.15416 -0.11118 -0.32399 -0.09773 -0.12746 0.08285 0.05322 0.00628 0.07012 -0.04773 -0.17340 -0.05567 -0.01601 0.18301 0.01523 0.00564 -0.06183 -0.04338 -0.03087 Market -0.00058 0.00301 0.05482 Company -0.00295 0.01309 0.11442 Calculating Beta: Beta = Cov(C,M)/Var(M) = Covariance(M,C) (RM - E(RM))((RC - E(RC)) -0.00082 0.00309 0.00101 0.00306 -0.00002 0.00646 -0.00130 -0.00022 0.01723 0.00698 0.00747 0.00317 -0.00155 0.00041 -0.00147 -0.00001 0.00392 -0.00100 0.01167 -0.00021 -0.00075 0.01797 0.00201 -0.00595 0.00085 0.00717 0.00269 -0.00025 0.00237 -0.00215 0.00103 -0.00074 0.00160 0.00165 -0.00065 0.00639 0.00006 -0.00204 0.03754 0.00519 0.00783 0.01312 -0.00093 0.02406 0.01650 0.01150 0.00106 -0.00049 -0.00054 0.00079 -0.00230 0.00093 0.00190 0.00097 -0.00147 -0.00066 0.00009 -0.00225 -0.00058 0.00097 0.20235 Market & Company 0.00337 1.12 Note: Beta measures the comovement of the company returns with the market. Mkt Returns 0.0105698564 -0.062650671 -0.007497497 0.0313323765 0.0405894499 0.0435830153 0.0085327517 -0.005058645 0.1077230383 -0.071762013 -0.056791098 -0.021474437 -0.018257508 -0.013500928 0.0284953576 -0.001047302 0.0319565826 0.0226455902 0.065300072 -0.002290283 0.0368559411 0.087551104 -0.047449165 0.0687778328 -0.053882377 -0.081975916 0.0147593272 0.0587963676 0.0285136935 -0.036974262 0.0177705977 0.057363997 -0.019761986 0.0357233838 0.0335601734 0.074141757 0.0001958352 0.0530814267 0.0939250755 0.0854045083 -0.109931225 -0.085657348 0.0078215657 -0.074849032 -0.169424534 -0.090791453 0.0121905032 -0.009859375 -0.085962382 0.0106741532 0.0475466848 -0.005959583 -0.034761162 -0.061163475 -0.008628489 -0.044043424 0.014822335 0.0357940013 0.0128635923 -0.031981907 Mkt Premium 0.0068998564 -0.066320671 -0.011167497 0.0276623765 0.0369194499 0.0399130153 0.0048627517 -0.008728645 0.1040530383 -0.075432013 -0.060461098 -0.025144437 -0.021927508 -0.017170928 0.0248253576 -0.004717302 0.0282865826 0.0189755902 0.061630072 -0.005960283 0.0331859411 0.083881104 -0.051119165 0.0651078328 -0.057552377 -0.085645916 0.0110893272 0.0551263676 0.0248436935 -0.040644262 0.0141005977 0.053693997 -0.023431986 0.0320533838 0.0298901734 0.070471757 -0.003474165 0.0494114267 0.0902550755 0.0817345083 -0.113601225 -0.089327348 0.0041515657 -0.078519032 -0.173094534 -0.094461453 0.0085205032 -0.013529375 -0.089632382 0.0070041532 0.0438766848 -0.009629583 -0.038431162 -0.064833475 -0.012298489 -0.047713424 0.011152335 0.0321240013 0.0091935923 -0.035651907 Co. Returns -0.076839827 -0.052793439 -0.149149586 0.0929456625 -0.003325416 0.1434003259 -0.145311049 0.0466472303 0.1561797753 -0.101010101 -0.135748581 -0.154612546 0.0844337735 -0.03438949 -0.053401609 0.0201492537 0.1175979983 -0.046141607 0.1742176553 0.1215295966 -0.023029683 0.200983405 -0.045747801 -0.088722608 -0.018877819 -0.091039085 0.1727221912 -0.007214206 0.07839617 0.0562579014 0.0532623169 -0.015727392 -0.086227545 0.0424469413 -0.021978022 0.0826173166 0.0776353276 -0.040983607 0.3942857143 0.0574018127 -0.074557316 -0.157109191 -0.114126653 -0.326932084 -0.100673968 -0.13040293 0.0799050633 0.0502700457 0.0033347228 0.0671708185 -0.050675676 -0.176347826 -0.058611657 -0.018952779 0.180060652 0.0122793553 0.0026933436 -0.064771501 -0.046328071 -0.033819629 Assumes: 4.5% Annual Risk-free rate & .367% monthly Risk-free rate Regression Instructions Select Data & then Data Analysis from top Excel options (If Data Analysis option is not there see instructions below). Select Regression. Input the Company Returns (collumn C of this spreadsheet for Y variable) Input the Market Risk Premium (collumn B of this spreadsheet for X var.) Select OK and Regression Output should be created on a new page. Print out the output that was generated and answer the following questions on the output page. 1. On the output page identify and circle the following a. y-intercept b. measurement of how well regression model estimates company returns c. How does this regression model fit the data? 2. Write the regression equation from the regression output. Instructions for adding Regression option on Excel 1. Click the Microsoft Office Button , and then click Excel Options. 2. Click Add-ins, and then in the Manage box, select Excel Add-ins. 3. Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK. Tip If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it. If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to ins install it. This page is an example of the regression output generated for the MLHR data. SUMMARY OUTPUT Regression Statistics Multiple R 0.29438966 R Square 0.08666527 Adjusted R Square 0.07091812 Goodness of Fit Measurement Standard Error 0.08009739 Observations 60 ANOVA df Regression Residual Total Intercept X Variable 1 Regression Equation: 1 58 59 Coefficients 0.00580132 0.86293692 SS MS F Significance F 0.0353085446 0.035309 5.503553 0.0224176171 0.372104303 0.006416 0.4074128476 Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% 0.010340535 0.561027 0.576941 -0.014897513 0.02650015 -0.0148975134 0.0265001456 0.3678387622 2.345965 0.022418 0.1266276467 1.5992462 0.1266276467 1.5992461982 Y = 0.0058013 + 0.8629X