QUMT 6303 Case Study – Measuring Stock Market Risk One measure of the risk or vo
ID: 2749152 • Letter: Q
Question
QUMT 6303 Case Study – Measuring Stock Market Risk
One measure of the risk or volatility of an individual stock is the standard deviation of the total return (capital appreciation plus dividends) over several periods of time. Although the standard deviation is easy to compute, it does not take into account the extent to which the price of a given stock varies as a function of a standard market index, such as the S&P 500.
As a result, many financial analysts prefer to use another measure of risk referred to as beta. Betas for individual stocks are determined by simple linear regression. The dependent variable is the total return for the stock and the independent variable is the total return for the stock market. For this case problem we will use the S&P 500 index as the measure of the total return for the stock market, and an estimated regression equation will be developed using monthly data. The beta for the stock is the slope of the estimated regression equation (b1). The data contained in the file named Beta provides the total return (capital appreciation plus dividends) over 36 months for eight widely traded common stocks and the S&P 500.
The value of beta for the stock market will always be 1; thus, stocks that tend to rise and fall with the stock market will also have a beta close to 1. Betas greater than 1 indicate that the stock is more volatile than the market, and betas less than 1 indicate that the stock is less volatile than the market. For instance, if a stock has a beta of 1.4, it is 40% more volatile than the market, and if a stock has a beta of .4, it is 60% less volatile than the market.
Managerial Report
You have been assigned to analyze the risk characteristics of these stocks. Prepare a report that includes but is not limited to the following items.
A (30 points). Compute descriptive statistics for each stock and the S&P 500. Comment on your results. Which stocks are the most volatile?
B (30 points). Compute the value of beta for each stock. Which of these stocks would you expect to perform best in an up market? Which would you expect to hold their value best in a down market?
C (40 points). Comment on how much of the return for the individual stocks is explained by the market.
Month Microsoft Exxon Mobil Caterpillar Johnson & Johnson McDonald's Sandisk Qualcomm Procter & Gamble S&P 500 Jan-03 -0.08201 -0.02261 -0.0304 -0.00186 -0.11443 -0.24867 0.0349 0.000465 -0.02742 Feb-03 0.00211 0.00293 0.06867 -0.01781 -0.04424 0.09363 -0.08178 -0.043356 -0.017 Mar-03 0.02152 0.02734 0.04681 0.10334 0.06245 0.00839 0.04251 0.087833 0.008358 Apr-03 0.05576 0.00715 0.07622 -0.02609 0.18257 0.43876 -0.11444 0.013588 0.081044 May-03 -0.03717 0.04119 -0.00856 -0.03141 0.09532 0.50165 0.05395 0.021925 0.050899 Jun-03 0.04185 -0.01346 0.06731 -0.04876 0.17779 0.1164 0.07124 -0.028752 0.011322 Jul-03 0.03003 -0.00919 0.21847 0.00174 0.04306 0.39734 0.04285 -0.009587 0.016224 Aug-03 0.00417 0.06661 0.06462 -0.03804 -0.02564 0.06633 0.10459 -0.006601 0.017873 Sep-03 0.04827 -0.02918 -0.04163 -0.00121 0.04996 0.05409 0.00823 0.063352 -0.01194 Oct-03 -0.05396 -0.00055 0.06987 0.01636 0.06202 0.26491 0.13967 0.063833 0.054962 Nov-03 -0.01645 -0.00355 0.0378 -0.0157 0.0412 0.00273 -0.06043 -0.020857 0.007129 Dec-03 0.06457 0.1326 0.09165 0.04787 -0.03121 -0.24276 0.21055 0.037822 0.050765 Jan-04 0.01023 -0.00512 -0.05444 0.03407 0.03665 -0.11275 0.08678 0.01657 0.017276 Feb-04 -0.04051 0.03996 -0.03046 0.01367 0.09946 -0.06372 0.07763 0.014147 0.012209 Mar-04 -0.06031 -0.01375 0.04383 -0.05917 0.00954 0.11566 0.05072 0.02312 -0.01636 Apr-04 0.04813 0.02308 -0.01227 0.06526 -0.0469 -0.18371 -0.05778 0.013063 -0.01679 May-04 0.00383 0.0228 -0.03062 0.03637 -0.03048 0.06479 0.07541 0.019574 0.012083 Jun-04 0.08883 0.02682 0.05428 -0.00018 -0.01515 -0.12008 0.08812 0.009831 0.017989 Jul-04 -0.00245 0.04256 -0.06974 -0.00772 0.05769 0.12125 -0.05166 -0.037472 -0.03429 Aug-04 -0.03896 0.00151 -0.01075 0.05636 -0.01745 -0.03988 0.10157 0.07325 0.002287 Sep-04 0.01282 0.04837 0.1066 -0.03046 0.03738 0.24711 0.02602 -0.033053 0.009364 Oct-04 0.01157 0.01842 0.00622 0.03639 0.03996 -0.28331 0.06557 -0.049704 0.014014 Nov-04 0.06864 0.04673 0.1367 0.03811 0.07341 0.08194 0.00048 0.044939 0.038595 Dec-04 -0.00336 0.0002 0.0651 0.05139 0.04294 0.10585 0.02042 0.029918 0.032458 Jan-05 -0.01647 0.00663 -0.08204 0.02018 0.01029 -0.01081 -0.1217 -0.029049 -0.02529 Feb-05 -0.03957 0.23217 0.06678 0.01832 0.0213 0.08826 -0.03008 -0.00263 0.018903 Mar-05 -0.03935 -0.0586 -0.03798 0.02378 -0.05865 0.03423 0.01609 -0.001695 -0.01912 Apr-05 0.04675 -0.04312 -0.03259 0.02189 -0.05877 -0.14748 -0.0475 0.026981 -0.02011 May-05 0.02292 -0.00947 0.06882 -0.01749 0.05561 0.09578 0.07079 0.018467 0.029952 Jun-05 -0.03721 0.0226 0.01275 -0.0313 -0.1031 -0.08625 -0.1143 -0.043518 -0.00014 Jul-05 0.031 0.02227 0.1365 -0.016 0.12324 0.4252 0.196 0.059905 0.035968 Aug-05 0.07224 0.02451 0.02931 -0.00375 0.04107 0.14814 0.00811 -0.002696 -0.01122 Sep-05 -0.06026 0.06077 0.05875 -0.00174 0.03205 0.24234 0.12692 0.071738 0.006949 Oct-05 -0.00117 -0.11646 -0.1006 -0.01043 -0.05643 0.22056 -0.11151 -0.053649 -0.01774 Nov-05 0.08016 0.03883 0.09869 -0.00862 0.0924 -0.13281 0.14361 0.021432 0.035186 Dec-05 -0.05527 -0.03205 -0.00017 -0.02672 -0.00384 0.23032 -0.05058 0.012065 -0.00095Explanation / Answer
Standard Deviation can be caluculated using the STDEV function of excel.
Beta = Covariance (Stock, Market) / Variance(Market)
A) Volatility can be measured using the standard deviation measure which is a measure of deviation from the mean. Sandisk is the most volatile stock with a standard deviation of 19.54% followed by Qualcomm with a standard deviation of 8.62%. Johnson and Johnson is the least volaitle stock with a standard deviation of 3.49% followed by Proctor and Gamble with a standard deviation of 3.71%.
Systematic Risk or Market Risk is measured by the Beta Measure. The stock with the highest systematic risk is also Sandisk with a Beta of 2.6 and the stock with the lowest systematic risk is Johnson and Johnson with a Beta of 0.01.
B) Value of Beta of each stock is calculated and is there in the table above. In an upmarket, the best performance is expected out of Sandisk because it has the highest beta (2.6). Beta is a measure of the sensitivity of the stock to the market. Thus, Sandisk is the most sensitive stock with respect to the market and is expected to perform best in an upmarket.
In a down market, it is expected that Johnson and Johnson would hold its value best as it has the lowest beta of 0.01 and thus, has extremely low sensitivity with respect to the market.
C) The return for the individual stocks explained by the market can be calculated using the R2 measure. It is calculated using the RSQ function of excel. The values have been calculated in the table above.Using the values of R sqaured, it can be concluded that 33.78% of the return on Mcdonalds stock is explained by the market.Similarly 32.88% of the return on the Caterpillar stock is explained by the market and 18.66% of the return on the Qualcomm stock is explained by the market . Similarly from the table, return for the individual stocks explained by the market can be concluded/interpreted. The return on the Johnson and Johnson stock is the least explained by the market (almost negligible) as the R squared value is 0.0044%.
It can also be seen using the correlation of stocks with respect to the market that the Mcdonalds stock is the highest correlated with the market with a correlation coefficient of 0.58 followed by Caterpillar with a correlation coefficient of 0.57. Also, correlation of Johnson and Johnson with the market is the least with a correlation coefficient of 0.01.
Month Microsoft Exxon Mobil Caterpillar Johnson & Johnson McDonald's Sandisk Qualcomm Procter & Gamble S&P 500 3-Jan -0.08201 -0.02261 -0.0304 -0.00186 -0.11443 -0.24867 0.0349 0.000465 -0.02742 3-Feb 0.00211 0.00293 0.06867 -0.01781 -0.04424 0.09363 -0.08178 -0.04336 -0.017 3-Mar 0.02152 0.02734 0.04681 0.10334 0.06245 0.00839 0.04251 0.087833 0.008358 3-Apr 0.05576 0.00715 0.07622 -0.02609 0.18257 0.43876 -0.11444 0.013588 0.081044 3-May -0.03717 0.04119 -0.00856 -0.03141 0.09532 0.50165 0.05395 0.021925 0.050899 3-Jun 0.04185 -0.01346 0.06731 -0.04876 0.17779 0.1164 0.07124 -0.02875 0.011322 3-Jul 0.03003 -0.00919 0.21847 0.00174 0.04306 0.39734 0.04285 -0.00959 0.016224 3-Aug 0.00417 0.06661 0.06462 -0.03804 -0.02564 0.06633 0.10459 -0.0066 0.017873 3-Sep 0.04827 -0.02918 -0.04163 -0.00121 0.04996 0.05409 0.00823 0.063352 -0.01194 3-Oct -0.05396 -0.00055 0.06987 0.01636 0.06202 0.26491 0.13967 0.063833 0.054962 3-Nov -0.01645 -0.00355 0.0378 -0.0157 0.0412 0.00273 -0.06043 -0.02086 0.007129 3-Dec 0.06457 0.1326 0.09165 0.04787 -0.03121 -0.24276 0.21055 0.037822 0.050765 4-Jan 0.01023 -0.00512 -0.05444 0.03407 0.03665 -0.11275 0.08678 0.01657 0.017276 4-Feb -0.04051 0.03996 -0.03046 0.01367 0.09946 -0.06372 0.07763 0.014147 0.012209 4-Mar -0.06031 -0.01375 0.04383 -0.05917 0.00954 0.11566 0.05072 0.02312 -0.01636 4-Apr 0.04813 0.02308 -0.01227 0.06526 -0.0469 -0.18371 -0.05778 0.013063 -0.01679 4-May 0.00383 0.0228 -0.03062 0.03637 -0.03048 0.06479 0.07541 0.019574 0.012083 4-Jun 0.08883 0.02682 0.05428 -0.00018 -0.01515 -0.12008 0.08812 0.009831 0.017989 4-Jul -0.00245 0.04256 -0.06974 -0.00772 0.05769 0.12125 -0.05166 -0.03747 -0.03429 4-Aug -0.03896 0.00151 -0.01075 0.05636 -0.01745 -0.03988 0.10157 0.07325 0.002287 4-Sep 0.01282 0.04837 0.1066 -0.03046 0.03738 0.24711 0.02602 -0.03305 0.009364 4-Oct 0.01157 0.01842 0.00622 0.03639 0.03996 -0.28331 0.06557 -0.0497 0.014014 4-Nov 0.06864 0.04673 0.1367 0.03811 0.07341 0.08194 0.00048 0.044939 0.038595 4-Dec -0.00336 0.0002 0.0651 0.05139 0.04294 0.10585 0.02042 0.029918 0.032458 5-Jan -0.01647 0.00663 -0.08204 0.02018 0.01029 -0.01081 -0.1217 -0.02905 -0.02529 5-Feb -0.03957 0.23217 0.06678 0.01832 0.0213 0.08826 -0.03008 -0.00263 0.018903 5-Mar -0.03935 -0.0586 -0.03798 0.02378 -0.05865 0.03423 0.01609 -0.0017 -0.01912 5-Apr 0.04675 -0.04312 -0.03259 0.02189 -0.05877 -0.14748 -0.0475 0.026981 -0.02011 5-May 0.02292 -0.00947 0.06882 -0.01749 0.05561 0.09578 0.07079 0.018467 0.029952 5-Jun -0.03721 0.0226 0.01275 -0.0313 -0.1031 -0.08625 -0.1143 -0.04352 -0.00014 5-Jul 0.031 0.02227 0.1365 -0.016 0.12324 0.4252 0.196 0.059905 0.035968 5-Aug 0.07224 0.02451 0.02931 -0.00375 0.04107 0.14814 0.00811 -0.0027 -0.01122 5-Sep -0.06026 0.06077 0.05875 -0.00174 0.03205 0.24234 0.12692 0.071738 0.006949 5-Oct -0.00117 -0.11646 -0.1006 -0.01043 -0.05643 0.22056 -0.11151 -0.05365 -0.01774 5-Nov 0.08016 0.03883 0.09869 -0.00862 0.0924 -0.13281 0.14361 0.021432 0.035186 5-Dec -0.05527 -0.03205 -0.00017 -0.02672 -0.00384 0.23032 -0.05058 0.012065 -0.00095 Standard Devitation 4.54% 5.53% 6.86% 3.49% 6.81% 19.54% 8.62% 3.71% 2.63% Beta 0.46 0.73 1.49 0.01 1.50 2.60 1.41 0.51 1.00 RSQ 7.08% 12.09% 32.88% 0.0044% 33.78% 12.32% 18.66% 12.95% 100.00% Correlation 0.27 0.35 0.57 0.01 0.58 0.35 0.43 0.36 1.00