Question 2: Explanation of Statistical Calculations

Question 3: Compare the calculated Beta with the reported Beta on Yahoo! Finance.

Question 4: Regression Statistics

Ticker Start ^GSPC 9/30/1994 CAT 9/30/1994 End TODAYS ### Day ### Month Year END START INFORMATION 11/30/1999 9/30/1994 Correct Question Answer 1a-Date 1b-Date 1c-% 1d-Date 1e-% 1f-Text 2a-number 2b-number 2c-% 2d-number 2e-number 3a-Date 3b-Text 3c-Date 3d-Text 3e-Date 3f-Text 3g-Yahoo Number 3g-Calculated-number 4-Rf- % 4a-Number 4b-Number 4c-Text 4d-Slope-Number 4d-Intercept-Number Correct Data? 0.00 RAW Score 0 Score for Q2 Qualitative (Update This Manually - Max score 1 point) 0.00 Scaled Score Feedback To Be Provided to Student: Points What It Should be Mkt Returns Student Answer Max score 1 point) Co. Returns Date What It is Mkt Returns Co. Returns Date RM - E(RM) RC - E(RC) [RM - E(RM)][RC - E(RC)] 0.Case Instructions Analyzing Historical Risk vs. Return for a Company. Choose a company that you are using in the investment challenge and follow the steps below. Step 0: Enter your First & Last name AND the TICKER Symbol for your stock. Then click Save File eg: TICKER for Microsoft Corporation is MSFT - ENSURE THAT THE COMPANY HAS 61 MONTHS OF DATA & BETA AVAILABLE ON Yahoo! FINANCE FIRST NAME LAST NAME TICKER Hanshin Oh CAT 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 click on the S&P 500 link in the upper left-hand corner. b. Click on Historical Data tab from the available tabs. c. Choose a time period with 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. Click on the "Download Data" link under the "Apply" button and copy (DO NOT CUT) the closing prices and dates into the highlighted areas of this spreadsheet under the tab 2.Calc. Returns - THEN CLICK THE SORT AND CALCULATE RETURNS BUTTON Step 2: Repeat the 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: Answer the questions asked on the Questions Tab. (4 questions in All) Page 21 of 37 1.Questions Question 1: Interpretation of Line Graph. (Generated Automatically. See: 3.Line Graph Worksheet) 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. Enter Instructor Password Here a. What is the most recent month in which the company return moves in the opposite direction of the S&P 500 return? (0.5 points) (ie. Firm monthly return is positive & market return negative, or vice-versa.) Choose from the drop down boxes below b. In what month was the largest positive monthly return for the company in the past 5 years? (0.5 points) Choose from the drop down boxes below c. What was the largest positive monthly return? Enter answer in highlighted cell below (0.5 points) 15.7588798 % d. In what month was the largest negative monthly return for the company in the past five years? (0.5 points) e. What was the largest negative monthly return? Enter answer in highlighted cell below (0.5 points) -13.4264173 % f. Based on the line Graph 1 would you say that your company returns and S&P 500 returns. (0.5 points) Question 2: 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 for the answers expected I68 What is the Expected Monthly Return (E(R)) for the company and its value? C66/60, where C66 is the sum of monthly returns, therefore this is the average monthly return for company Value = 0.012750 I69 a. What is the Variance for the company and its value? (0.4 points) I66/60 where I66 is sum of squared deviations from mean for Caterpillar, therefore it represent average squared deviation from mean Value = 0.004142222 I70 b. What is the Standard Deviation for the company and its Value? (0.4 points) sqare root of Apple's variance (I69) therefore represent the average deviations from mean for Apple Inc. Value = F68 c. What is the Expected Return (E(R)) for the market index and its Value? (0.4 points) Page 22 of 37 1.Questions B66/60, where B66 is the sum of monthly returns for Market (S&P 500), therefore this is the average monthly return for Market (S&P 500 Value = F69 d. What is the Variance for the market index and its Value? (0.4 points) F66/60 where F66 is sum of squared deviations from mean for Market (S&P 500)., therefore it represents average squared deviation fro Value = F70 e. What is the Standard Deviation for the market index and its Value? (0.4 points) sqare root of Market's (F69) therefore represent the average deviations from mean for Market (S&P 500). Value = Question 3: 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? (0.5 points) (i.e. both had negative amounts in collumn E and collumn G.) b. Was the covariance for this month positive or negative? (0.5 points) c. What is the most recent month for which the company return and market return were greater than their expected returns? (0.5 points) d. Was the covariance for this month positive or negative? (0.5 points) e. What is the most recent month for which the company return and market return moved in opposite directions than their expected returns? (0.5 points) ________ f. Was the covariance for this month positive or negative? (0.5 points) Look up your company's Beta on Yahoo!Finance by doing the following steps: 1. Enter the ticker symbol under "Quote Lookup". 2. Click on Statistics on the left-hand side Page 23 of 37 1.Questions 3. Beta is listed in the right-hand column, one of the first statistical measures reported. Don't be surprised if your Beta is different than the beta reported on yahoo finance. g. Enter the values of beta from Yahoo Finance as well as the Beta Calculated by you (this is available in Cell G72 of the worksheet "5. Calc Beta" Yahoo! Beta (0.5 points) 1.30 Your Beta Question 4: Regression Statistics Regression Instructions Select Data and then Data Analysis Tab from top options of Excel. (See instructions on Regression tab if Data Analysis option is missing). 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 worksheet. Generate the Regression output and answer the following questions NOTE: There is an Example of MLHR Regression Output on Tab below. IMPORTANT: Input the Risk-Free rate below BEFORE you run regressions. Otherwise the intercept value will be incorrect. Go to the following website and input the latest available 5-year Daily Treasury Yield Curve Rate below https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 5-Year Rf % (0.5 Points) a. What is the value of the Y-Intercept for the Regression Y-Intercept (0.5 points) b. What is the value of the measure of how well the regression model estimates company returns? Fit-Measure (0.5 points) c. How well does this regression model fit the data? (0.5 Points) d. Input the regression coefficients from the regression output, to generate the regression equation. Slope (0.5 points) Y-Intercept Equation y=x+ Note: we are basically trying to plot the CAPM equation: Re = Rf + b(Rm - Rf). So your intercept will ~ the monthly risk free rate. Remember to upload to CANVAS upon completion. Page 24 of 37 2.Calc. Returns Adj Close Date Adj Close Month # Date Mkt Returns Co. Returns Input Worksheet 1 10/1/2017 2557.1499 10/1/2017 136.028625 1.499976% 9.726557% In the highlighted columns B & C copy a series of closing monthly 2 9/1/2017 2519.3601 9/1/2017 123.970558 1.930298% 6.145206% values for the S&P. Do the same for your company in columns D&E. 3 8/1/2017 2471.6499 8/1/2017 116.793365 0.054643% 3.850869% This price series in columns C and E are then converted to a return 2470.3 7/1/2017 112.462578 4 7/1/2017 1.934883% 6.039461% series in columns F and G for the S&P and for your company. 5 6/1/2017 2423.4099 6/1/2017 106.057289 0.481378% 1.925441% 2411.8 5/1/2017 104.053795 6 5/1/2017 1.157625% 3.956328% Note: The data shown IS AS AN EXAMPLE for the S&P and MLHR 2384.2 4/1/2017 100.093758 7 4/1/2017 0.909121% 10.241480% data. You need to update these columns with your own data 8 3/1/2017 2362.72 3/1/2017 90.795006 -0.038920% -4.034760% 9 2/1/2017 2363.6399 2/1/2017 94.612389 3.719816% 1.883789% 10 1/1/2017 2278.8701 1/1/2017 92.863045 1.788436% 3.148590% 11 12/1/2016 2238.8301 12/1/2016 90.028419 1.820076% -2.951027% 12 11/1/2016 2198.8101 11/1/2016 92.765968 3.417452% 15.508677% 13 10/1/2016 2126.1499 10/1/2016 80.310822 -1.942568% -5.981754% 14 9/1/2016 2168.27 9/1/2016 85.420464 -0.123445% 8.322155% 15 8/1/2016 2170.95 8/1/2016 78.857796 -0.121924% -0.024839% 16 7/1/2016 2173.6001 7/1/2016 78.877388 3.560980% 9.167679% 17 6/1/2016 2098.8601 6/1/2016 72.253426 0.091092% 4.551092% 18 5/1/2016 2096.95 5/1/2016 69.108246 1.532460% -5.793868% 2065.3 4/1/2016 73.358543 19 4/1/2016 0.269940% 1.541681% 20 3/1/2016 2059.74 3/1/2016 72.244759 6.599111% 13.057624% 21 2/1/2016 1932.23 2/1/2016 63.900829 -0.412836% 10.134337% 22 1/1/2016 1940.24 1/1/2016 58.020805 -5.073532% -8.416704% 23 12/1/2015 2043.9399 12/1/2015 63.353043 -1.753019% -6.455622% 24 11/1/2015 2080.4099 11/1/2015 67.725121 0.050487% 0.646543% 25 10/1/2015 2079.3601 10/1/2015 67.290062 8.298312% 11.673813% 26 9/1/2015 1920.03 9/1/2015 60.255901 -2.644283% -13.613539% 27 8/1/2015 1972.1801 8/1/2015 69.751556 -6.258082% -2.888672% 28 7/1/2015 2103.8401 7/1/2015 71.826385 1.974203% -7.297824% 29 6/1/2015 2063.1101 6/1/2015 77.480797 -2.101167% -0.586016% 30 5/1/2015 2107.3899 5/1/2015 77.937523 1.049138% -0.981661% 78.71019 31 4/1/2015 2085.51 4/1/2015 0.852082% 8.559291% 32 3/1/2015 2067.8899 3/1/2015 72.504333 -1.739611% -3.462017% 2104.5 2/1/2015 75.104462 33 2/1/2015 5.489251% 4.513888% 34 1/1/2015 1994.99 1/1/2015 71.860748 -3.104081% -12.629753% 35 12/1/2014 2058.8999 12/1/2014 82.248535 -0.418859% -9.015896% 36 11/1/2014 2067.5601 11/1/2014 90.398796 2.453359% -0.043050% 37 10/1/2014 2018.05 10/1/2014 90.437729 2.320146% 2.403329% 38 9/1/2014 1972.29 9/1/2014 88.315224 -1.551384% -9.205103% 39 8/1/2014 2003.37 8/1/2014 97.268929 3.765530% 8.942626% 40 7/1/2014 1930.67 7/1/2014 89.284546 -1.507983% -7.288124% 41 6/1/2014 1960.23 6/1/2014 96.303246 1.905833% 6.299522% 42 5/1/2014 1923.5699 5/1/2014 90.596123 2.103028% -2.436496% 92.85862 43 4/1/2014 1883.95 4/1/2014 0.620079% 6.068230% 87.54612 44 3/1/2014 1872.34 3/1/2014 0.693217% 2.474990% 45 2/1/2014 1859.45 2/1/2014 85.431694 4.311703% 3.933270% 46 1/1/2014 1782.59 1/1/2014 82.198601 -3.558291% 3.413709% 47 12/1/2013 1848.36 12/1/2013 79.485207 2.356279% 7.340441% 48 11/1/2013 1805.8101 11/1/2013 74.049637 2.804947% 2.198301% 49 10/1/2013 1756.54 10/1/2013 72.456818 4.459575% -0.048001% 50 9/1/2013 1681.55 9/1/2013 72.491615 2.974952% 1.041969% 51 8/1/2013 1632.97 8/1/2013 71.744064 -3.129802% 0.247718% 71.56678 52 7/1/2013 1685.73 7/1/2013 4.946208% 0.509145% 53 6/1/2013 1606.28 6/1/2013 71.204247 -1.499930% -3.857818% 54 5/1/2013 1630.74 5/1/2013 74.061401 2.076281% 1.985537% 55 4/1/2013 1597.5699 4/1/2013 72.619514 1.808577% -2.644599% 56 3/1/2013 1569.1899 3/1/2013 74.592178 3.598772% -5.846043% 57 2/1/2013 1514.6801 2/1/2013 79.223625 1.106065% -6.118508% 58 1/1/2013 1498.11 1/1/2013 84.386841 5.042810% 10.430523% 59 12/1/2012 1426.1899 12/1/2012 76.416229 0.706823% 5.126697% 60 11/1/2012 1416.1801 11/1/2012 72.689651 0.284672% 1.113957% 71.88884 61 10/1/2012 1412.16 10/1/2012 Question 1: Interpretation of Line Graph. (Generated Automatically. See: 3.Line Graph Worksheet) Company vs. S&P 500 over time 20.000000% 15.000000% 10.000000% 5.000000% 0.000000% -5.000000% -10.000000% -15.000000% -20.000000% ver time Mkt Returns Co. Returns 4.Calc. Stats. This spreadsheet calculates the Expected Returns, Variances, and Standard Deviations of 2 Return Series. Page down to bottom for results Distance Market Company from Mean Variance(M) Variance(C) (RM - E(RM)) (RM - E(RM))2 (RC - E(RC)) (RC - E(RC))2 Date Returns Returns Oct-17 Sep-17 Aug-17 Jul-17 Jun-17 May-17 Apr-17 Mar-17 Feb-17 Jan-17 Dec-16 Nov-16 Oct-16 Sep-16 Aug-16 Jul-16 Jun-16 May-16 Apr-16 Mar-16 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Apr-15 Mar-15 Feb-15 Jan-15 Dec-14 Nov-14 Oct-14 Sep-14 Aug-14 Jul-14 Jun-14 May-14 Apr-14 Mar-14 Feb-14 Jan-14 Dec-13 Nov-13 Oct-13 Sep-13 Aug-13 Jul-13 Jun-13 May-13 Apr-13 Mar-13 Feb-13 Jan-13 Dec-12 Nov-12 Sums: 0.01500 0.01930 0.00055 0.01935 0.00481 0.01158 0.00909 -0.00039 0.03720 0.01788 0.01820 0.03417 -0.01943 -0.00123 -0.00122 0.03561 0.00091 0.01532 0.00270 0.06599 -0.00413 -0.05074 -0.01753 0.00050 0.08298 -0.02644 -0.06258 0.01974 -0.02101 0.01049 0.00852 -0.01740 0.05489 -0.03104 -0.00419 0.02453 0.02320 -0.01551 0.03766 -0.01508 0.01906 0.02103 0.00620 0.00693 0.04312 -0.03558 0.02356 0.02805 0.04460 0.02975 -0.03130 0.04946 -0.01500 0.02076 0.01809 0.03599 0.01106 0.05043 0.00707 0.00285 0.61840 E(R): Variance: Standard Deviation: 0.09727 0.06145 0.03851 0.06039 0.01925 0.03956 0.10241 -0.04035 0.01884 0.03149 -0.02951 0.15509 -0.05982 0.08322 -0.00025 0.09168 0.04551 -0.05794 0.01542 0.13058 0.10134 -0.08417 -0.06456 0.00647 0.11674 -0.13614 -0.02889 -0.07298 -0.00586 -0.00982 0.08559 -0.03462 0.04514 -0.12630 -0.09016 -0.00043 0.02403 -0.09205 0.08943 -0.07288 0.06300 -0.02436 0.06068 0.02475 0.03933 0.03414 0.07340 0.02198 -0.00048 0.01042 0.00248 0.00509 -0.03858 0.01986 -0.02645 -0.05846 -0.06119 0.10431 0.05127 0.01114 0.76503 0.00469 0.00900 -0.00976 0.00904 -0.00549 0.00127 -0.00122 -0.01070 0.02689 0.00758 0.00789 0.02387 -0.02973 -0.01154 -0.01153 0.02530 -0.00940 0.00502 -0.00761 0.05568 -0.01444 -0.06104 -0.02784 -0.00980 0.07268 -0.03675 -0.07289 0.00944 -0.03132 0.00018 -0.00179 -0.02770 0.04459 -0.04135 -0.01450 0.01423 0.01289 -0.02582 0.02735 -0.02539 0.00875 0.01072 -0.00411 -0.00337 0.03281 -0.04589 0.01326 0.01774 0.03429 0.01944 -0.04160 0.03916 -0.02531 0.01046 0.00778 0.02568 0.00075 0.04012 -0.00324 -0.00746 0.00002 0.00008 0.00010 0.00008 0.00003 0.00000 0.00000 0.00011 0.00072 0.00006 0.00006 0.00057 0.00088 0.00013 0.00013 0.00064 0.00009 0.00003 0.00006 0.00310 0.00021 0.00373 0.00077 0.00010 0.00528 0.00135 0.00531 0.00009 0.00098 0.00000 0.00000 0.00077 0.00199 0.00171 0.00021 0.00020 0.00017 0.00067 0.00075 0.00064 0.00008 0.00011 0.00002 0.00001 0.00108 0.00211 0.00018 0.00031 0.00118 0.00038 0.00173 0.00153 0.00064 0.00011 0.00006 0.00066 0.00000 0.00161 0.00001 0.00006 0.04369 Market 0.01031 0.00073 0.02698 0.08452 0.04870 0.02576 0.04764 0.00650 0.02681 0.08966 -0.05310 0.00609 0.01874 -0.04226 0.14234 -0.07257 0.07047 -0.01300 0.07893 0.03276 -0.07069 0.00267 0.11783 0.08859 -0.09692 -0.07731 -0.00629 0.10399 -0.14889 -0.04164 -0.08573 -0.01861 -0.02257 0.07284 -0.04737 0.03239 -0.13905 -0.10291 -0.01318 0.01128 -0.10480 0.07668 -0.08563 0.05024 -0.03712 0.04793 0.01200 0.02658 0.02139 0.06065 0.00923 -0.01323 -0.00233 -0.01027 -0.00766 -0.05133 0.00710 -0.03920 -0.07121 -0.07394 0.09155 0.03852 -0.00161 0.00714 0.00237 0.00066 0.00227 0.00004 0.00072 0.00804 0.00282 0.00004 0.00035 0.00179 0.02026 0.00527 0.00497 0.00017 0.00623 0.00107 0.00500 0.00001 0.01388 0.00785 0.00939 0.00598 0.00004 0.01081 0.02217 0.00173 0.00735 0.00035 0.00051 0.00531 0.00224 0.00105 0.01933 0.01059 0.00017 0.00013 0.01098 0.00588 0.00733 0.00252 0.00138 0.00230 0.00014 0.00071 0.00046 0.00368 0.00009 0.00018 0.00001 0.00011 0.00006 0.00263 0.00005 0.00154 0.00507 0.00547 0.00838 0.00148 0.00000 0.24853 Company 0.01275 0.00414 0.06436 5.Calc. Beta This spreadsheet calculates the Covariance and Beta of a Company Return Series with a Market Series. Page down to bottom for results Distance Distance Market Company from Mean from Mean Covariance(M,C) (RM - E(RM)) (RC - E(RC)) (RM - E(RM))((RC - E(RC)) Date Returns Returns Oct-17 Sep-17 Aug-17 Jul-17 Jun-17 May-17 Apr-17 Mar-17 Feb-17 Jan-17 Dec-16 Nov-16 Oct-16 Sep-16 Aug-16 Jul-16 Jun-16 May-16 Apr-16 Mar-16 Feb-16 Jan-16 Dec-15 Nov-15 Oct-15 Sep-15 Aug-15 Jul-15 Jun-15 May-15 Apr-15 Mar-15 Feb-15 Jan-15 Dec-14 Nov-14 Oct-14 Sep-14 Aug-14 Jul-14 Jun-14 May-14 Apr-14 Mar-14 Feb-14 Jan-14 Dec-13 Nov-13 Oct-13 Sep-13 Aug-13 Jul-13 Jun-13 May-13 Apr-13 Mar-13 Feb-13 Jan-13 Dec-12 Nov-12 Sums: 0.01500 0.01930 0.00055 0.01935 0.00481 0.01158 0.00909 -0.00039 0.03720 0.01788 0.01820 0.03417 -0.01943 -0.00123 -0.00122 0.03561 0.00091 0.01532 0.00270 0.06599 -0.00413 -0.05074 -0.01753 0.00050 0.08298 -0.02644 -0.06258 0.01974 -0.02101 0.01049 0.00852 -0.01740 0.05489 -0.03104 -0.00419 0.02453 0.02320 -0.01551 0.03766 -0.01508 0.01906 0.02103 0.00620 0.00693 0.04312 -0.03558 0.02356 0.02805 0.04460 0.02975 -0.03130 0.04946 -0.01500 0.02076 0.01809 0.03599 0.01106 0.05043 0.00707 0.00285 0.61840 0.09727 0.06145 0.03851 0.06039 0.01925 0.03956 0.10241 -0.04035 0.01884 0.03149 -0.02951 0.15509 -0.05982 0.08322 -0.00025 0.09168 0.04551 -0.05794 0.01542 0.13058 0.10134 -0.08417 -0.06456 0.00647 0.11674 -0.13614 -0.02889 -0.07298 -0.00586 -0.00982 0.08559 -0.03462 0.04514 -0.12630 -0.09016 -0.00043 0.02403 -0.09205 0.08943 -0.07288 0.06300 -0.02436 0.06068 0.02475 0.03933 0.03414 0.07340 0.02198 -0.00048 0.01042 0.00248 0.00509 -0.03858 0.01986 -0.02645 -0.05846 -0.06119 0.10431 0.05127 0.01114 0.76503 E(R): Variance and Covariance: Standard Deviation: 0.00469 0.00900 -0.00976 0.00904 -0.00549 0.00127 -0.00122 -0.01070 0.02689 0.00758 0.00789 0.02387 -0.02973 -0.01154 -0.01153 0.02530 -0.00940 0.00502 -0.00761 0.05568 -0.01444 -0.06104 -0.02784 -0.00980 0.07268 -0.03675 -0.07289 0.00944 -0.03132 0.00018 -0.00179 -0.02770 0.04459 -0.04135 -0.01450 0.01423 0.01289 -0.02582 0.02735 -0.02539 0.00875 0.01072 -0.00411 -0.00337 0.03281 -0.04589 0.01326 0.01774 0.03429 0.01944 -0.04160 0.03916 -0.02531 0.01046 0.00778 0.02568 0.00075 0.04012 -0.00324 -0.00746 0.08452 0.04870 0.02576 0.04764 0.00650 0.02681 0.08966 -0.05310 0.00609 0.01874 -0.04226 0.14234 -0.07257 0.07047 -0.01300 0.07893 0.03276 -0.07069 0.00267 0.11783 0.08859 -0.09692 -0.07731 -0.00629 0.10399 -0.14889 -0.04164 -0.08573 -0.01861 -0.02257 0.07284 -0.04737 0.03239 -0.13905 -0.10291 -0.01318 0.01128 -0.10480 0.07668 -0.08563 0.05024 -0.03712 0.04793 0.01200 0.02658 0.02139 0.06065 0.00923 -0.01323 -0.00233 -0.01027 -0.00766 -0.05133 0.00710 -0.03920 -0.07121 -0.07394 0.09155 0.03852 -0.00161 Market 0.01031 0.00073 0.02698 Company 0.01275 0.00414 0.06436 Calculating Beta: Beta = Cov(C,M)/Var(M) = 0.00040 0.00044 -0.00025 0.00043 -0.00004 0.00003 -0.00011 0.00057 0.00016 0.00014 -0.00033 0.00340 0.00216 -0.00081 0.00015 0.00200 -0.00031 -0.00035 -0.00002 0.00656 -0.00128 0.00592 0.00215 0.00006 0.00756 0.00547 0.00303 -0.00081 0.00058 0.00000 -0.00013 0.00131 0.00144 0.00575 0.00149 -0.00019 0.00015 0.00271 0.00210 0.00217 0.00044 -0.00040 -0.00020 -0.00004 0.00087 -0.00098 0.00080 0.00016 -0.00045 -0.00005 0.00043 -0.00030 0.00130 0.00007 -0.00030 -0.00183 -0.00006 0.00367 -0.00012 0.00001 0.05673 Market & Company 0.00095 1.30 Note: Beta measures the comovement of the company returns with the market. Mkt Returns Mkt Premium Co. Returns 0.0149997592 1.49998% 0.0972655701 0.0193029785 0.0005464328 0.0193488261 0.0048137751 0.0115762514 0.0090912085 -0.0003891972 0.0371981603 0.0178843582 0.0182007622 0.0341745222 -0.0194256793 -0.0012344508 -0.0012192431 0.0356098011 0.0009109211 0.0153246024 0.0026993985 0.0659911146 -0.0041283604 -0.050735322 -0.0175301852 0.0005048693 0.0829831178 -0.0264428316 -0.0625808182 0.0197420297 -0.0210116724 0.0104913824 0.0085208197 -0.0173961069 0.054892511 -0.0310408058 -0.0041885879 0.0245335888 0.0232014608 -0.0155138372 0.0376552955 -0.0150798306 1.93030% 0.05464% 1.93488% 0.48138% 1.15763% 0.90912% -0.03892% 3.71982% 1.78844% 1.82008% 3.41745% -1.94257% -0.12345% -0.12192% 3.56098% 0.09109% 1.53246% 0.26994% 6.59911% -0.41284% -5.07353% -1.75302% 0.05049% 8.29831% -2.64428% -6.25808% 1.97420% -2.10117% 1.04914% 0.85208% -1.73961% 5.48925% -3.10408% -0.41886% 2.45336% 2.32015% -1.55138% 3.76553% -1.50798% 0.0614520611 0.038508694 0.0603946137 0.0192544059 0.0395632763 0.1024147958 -0.0403476018 0.0188378919 0.0314859022 -0.0295102726 0.1550867702 -0.0598175397 0.0832215498 -0.0002483855 0.0916767878 0.0455109221 -0.057938678 0.0154168138 0.1305762403 0.1013433716 -0.0841670384 -0.0645562228 0.0064654272 0.1167381266 -0.136135386 -0.0288867246 -0.0729782374 -0.0058601554 -0.009816607 0.0855929121 -0.0346201668 0.0451388844 -0.1262975322 -0.0901589552 -0.0004304951 0.0240332856 -0.092051029 0.0894262597 -0.0728812402 Annual 5-year Risk-free rate 0.00000% Monthly 5-year Risk-free rate 0 Regression Instructions Select Data and then Data Analysis Tab from top options Select Regression. Input the Company Returns (collumn C of 6.Regression Input the Market Risk Premium (collumn B of 6.Regress Select OK and Regression Output should be created on a Instructions for adding Regression option on Excel 1. Click the Microsoft Office Button or the "File" Tab , and then click Excel Options. 2. Click Add-ins, and then in the Manage box, select Ex 3. Click Go. 4. In the Add-Ins available box, select the Analysis Too Tip If Analysis ToolPak is not listed in the Add-Ins ava If you are prompted that the Analysis ToolPak is not curr 0.0190583317 0.02103028 0.006200789 0.0069321656 0.04311703 -0.0355829057 0.0235627916 0.0280494716 0.0445957526 0.0297495232 -0.031298019 0.0494620798 -0.0149993016 0.0207628117 0.0180857679 0.0359877235 0.0110606492 0.0504280965 0.0070682305 0.002846717 1.90583% 2.10303% 0.62008% 0.69322% 4.31170% -3.55829% 2.35628% 2.80495% 4.45958% 2.97495% -3.12980% 4.94621% -1.49993% 2.07628% 1.80858% 3.59877% 1.10606% 5.04281% 0.70682% 0.28467% 0.0629952233 -0.0243649647 0.0606823009 0.0247499014 0.0393327011 0.0341370942 0.0734044112 0.0219830106 -0.0004800141 0.0104196913 0.0024771828 0.0050914519 -0.0385781792 0.0198553656 -0.0264459901 -0.0584604277 -0.0611850845 0.1043052255 0.0512669678 0.0111395733 Analysis Tab from top options of Excel. (See instructions on Regression tab if Data Analys s (collumn C of 6.Regression spreadsheet for Y variable) mium (collumn B of 6.Regression spreadsheet for X var.) Output should be created on a new worksheet. egression option on Excel ice Button or the "File" Tab in the Menu in the Manage box, select Excel Add-ins. e box, select the Analysis ToolPak check box, and then click OK. s not listed in the Add-Ins available box, click Browse to locate it. e Analysis ToolPak is not currently installed on your computer, click Yes to install it. SUMMARY OUTPUT Regression Statistics Multiple R 0.5444656028 R Square 0.2964427927 Adjusted R Square 0.284312496 Standard Error 0.0549070408 Observations 60 ANOVA df Regression Residual Total Intercept X Variable 1 1 58 59 SS MS F Significance F 0.0736759168 0.073676 24.43821 0.000006891 0.1748574213 0.003015 0.2485333381 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% -0.0006342867 0.0075879628 -0.083591 0.933669 -0.0158232444 0.014555 1.2986517278 0.2626987227 4.943502 6.89E-006 0.7728031016 1.8245 Lower 95.0% Upper 95.0% -0.015823 0.014555 0.772803 1.8245 Question 1: Interpretation of Line Graph. (Generated Automatically. See: 3.Line Graph Worksheet) SUMMARY OUTPUT Regression Statistics Multiple R 0.5376341931 R Square 0.2890505256 Goodness of Fit Measurement Adjusted R Square 0.276792776 Standard Error 0.0981293361 Observations 60 ANOVA df Regression Residual Total Intercept X Variable 1 Regression Equation: 1 58 59 Coefficients -0.00043 1.12218 SS MS F Significance F 0.2270705128 0.22707051 23.5810435 9.43795E-006 0.5585032631 0.00962937 0.7855737759 Standard Error t Stat P-value Lower 95% Upper 95% 0.0126790337 -0.03390728 0.97306751 -0.02580975 0.024949932 0.2310889711 4.85603166 9.438E-006 0.659600527 1.584750194 Y = -0.00043 + 1.12218X Lower 95.0% Upper 95.0% -0.025809755 0.0249499318 0.6596005267 1.5847501944