information given in first picture. use excel for answer. Just need the formula to calculate the answer.

Verizon Goldman Hershey SIX STOCKS AND THE S&P 500 Exxon Prudential Mobile Sachs XOM PRU GS HSY 344.92 22.91 57.23 12.62 Johnson and Johnson JNJ 173.08 VZ S&P500 Market value 99.27 -Market value in 56 MSY 47 07 46.62 46.15 46 44 43.66 4475 46.61 48.16 48.43 45.69 44 84 40.83 41.46 41.38 38.44 35.85 35 39 3249 33.58 34.11 33.85 3577 Date 3-Aug-06 1-Sep-00 2-Oct-06 1-Nov-06 1-Dec 06 3-Jan-07 1-Feb-07 1-Mar 07 2-Apr-07 1-May-07 1-Jun-07 2-Jul-07 1-Aug-07 4-Sep-07 1-Oct-07 1-Nov-07 3-Dec-07 2-Jan-00 1-Feb-08 3-Mar-08 1-Apr-08 1-May-08 2-Jun-08 1-Jul-08 1-Aug-08 2-Sep-08 1-Oct-08 3-Nov-08 1-Dec-08 2-Jan-09 2-Feb-09 2 Mar 09 1-Apr-09 1-May-09 1-Jun-09 1-Jul-09 3-Aug-09 1-Sep-09 1-Oct-09 2-Nov-09 1-Dec-09 4-Jan-10 1-Feb-10 1-Mar-10 1-Apr-10 3-May-10 1-Jun-10 1.Jul 10 2-Aug-10 1-Sep-10 1-Oct-10 1-Nov-10 1-Dec-10 3-Jan-11 1-Feb-11 1 Mar 11 1-Apr-11 2-May-11 1-Jun-11 1-Jul-11 1-Aug-11 VZ 25.95 27.39 27 59 2704 28.82 30.13 29.26 29.66 30.10 34 42 32 56 3403 33.44 35 35 37.13 34.83 3521 31,61 29.57 29.67 31.84 31.83 29 29 28.51 29.42 26.88 25 25 27.79 28.85 25.79 24.64 26.08 26.57 25.62 26. 91 28.53 27.61 26.90 26.74 28.43 29.94 26.97 26.52 28.44 26.91 25.62 26.09 27.55 27.99 30.89 31.24 30.79 3442 34.71 35.98 37.56 29 36.45 36.75 35.29 35 61 XOM 60.78 50.27 64.15 69.29 69.13 66.84 64.94 68.35 71.91 75.67 76.32 77 45 78.31 84.55 84.03 81.76 85.91 78.59 80.13 77.90 85.71 82.11 81.53 74.40 74.39 72.21 68.92 74.95 74.65 71.52 63 82 64.00 62.66 65.57 66.10 66.55 65.78 65.27 68.18 71.83 65.24 61.65 52.60 64.50 65.26 58.62 5533 57 86 57.72 50.34 54.93 68.35 71.85 79.28 84 49 83.11 86.91 82.92 80.85 79.27 Price data PRU GS 66.79 142.31 69.37 161.96 69.99 182.04 7502 186.85 79.05 191.21 82.06 203.84 83.72 193.84 83.10 198.53 87.46 210.37 93.93 222.12 89.52 208.58 81.60 181.57 82.66 169.68 89 B4 208.95 89.05 239.38 87.73 218 3 86.70 207 64 78.35 193.02 68.00 164.08 72.92 159.97 70.55 185.45 89.61 170.95 55.67 169.49 54.27 178.89 68.69 159,20 67.10 124,28 27.96 90.09 20.94 76.93 29.20 82.19 24.84 78.62 15,83 89.10 18.35 103.82 27.86 125.83 38.51 141.93 35.91 144.75 42.71 16032 48.80 162.79 48.15 181.37 43.64 167 42 48.80 167.28 48.71 166.47 48.93 146.63 154.50 59.22 168.61 62.22 143.48 56.49 142.91 52.53 130.04 149.41 49.53 135.99 53.04 143.50 51.47 160.02 50.68 155.41 58.71 167.37 61.51 162.85 65.83 163.36 61.58 158.19 63.42 150.62 63.78 140 73 63.59 133.09 58.68 134.97 47.15 117.25 29.92 33:57 3321 36 38 34.26 33.43 32.26 3462 31.55 JNJ S&P500 5596 130382 56.20 1335.85 58 33 1.377.96 5736 1.400.65 57.46 1.418.30 58.13 1.4382 55.09 1.406.82 $2.75 142086 56.21 145230 55.75 1.530 52 5429 1.503.35 53.30 1.455.27 54.81 1.473.90 58.28 1.528.75 5781 1.549.35 60.46 1.481.14 59.53 146836 56.35 1.378 55 55.67 1.330.63 58.28 1.322.70 60.28 1385 59 60.38 1.400.58 58.21 1.280.00 61.95 1.267.38 64.14 1.262.85 63.00 1.166.38 55.80 968.75 53.79 805 24 5494 903.25 52.97 4620 735.00 48 70 797 87 48.48 872.81 51.52 919.14 53.05 9193 56.87 5691 1.02069 57 33 1.057.08 55 60 1038.19 59.54 1.095.63 61.13 1.115.10 59.66 1.073.87 60.25 1.104.49 6235 1.109.43 61.491.186 56.28 1.089.41 56.99 1.030.71 56.06 1.101.60 55.55 1.049 33 60.36 1.14120 62.09 1.183.26 60.47 1.180.55 60.77 1.257 64 58.72 1.285.12 60 91 132722 58.74 1.325.83 65.15 1.353761 67291.545.20 66.52 1.320.54 64.79 1.282.28 63.16 1.140.65 32 55 33.85 33.26 34.00 37.73 37 33 360 3596 33.93 34 34 3495 51.30 38.45 56.08 4140 45.47 45.57 46.68 45.77 45.56 46.66 48.52 4620 46.55 46.09 5200 5402 57.36 55.73 56.85 56.44 5549 7416 QUESTION 2 (20 points) The exam spreadsheet includes five years of price data for 6 U.S. stocks. Fill in the template below. Notes: Return: The returns are approximated as the growth rate of the share prices. Excel function: ELN('Stock price data'!B9/'Stock price data"!B8) Sigma: The sigma measures the variability of the return, which is derived by taking the square root of the variance. Excel function: =SQRT(Variance) Beta: The beta coefficient measures the volatility of an individual stock compared to the systematic risk of the entire market. In statistical terms, beta represents the slope of the line through a regression of data points. E.g. Excel function: = =SLOPE(B17:376,$H$17:$H$76) H S&P500 A B D EIF G 1 QUESTION 2: RETURNS AND BETAS Johnson Exxon- Goldman- and 2 Verizon Mobile Prudential Sachs Hershey Johnson 3 VZ XOM PRU GS HSY JNJ 4 Monthly returns 5 Mean 6 Variance 7 Sigma 8 Beta 9 10 Annual returns 11 Mean 12 Variance 13 Sigma 14 15 Return data, month-by-month 16 1-Sep-06 17 2-Oct-06 181-Nov-06 19 1-Dec-06 20 3-Jan-07 Verizon Goldman Hershey SIX STOCKS AND THE S&P 500 Exxon Prudential Mobile Sachs XOM PRU GS HSY 344.92 22.91 57.23 12.62 Johnson and Johnson JNJ 173.08 VZ S&P500 Market value 99.27 -Market value in 56 MSY 47 07 46.62 46.15 46 44 43.66 4475 46.61 48.16 48.43 45.69 44 84 40.83 41.46 41.38 38.44 35.85 35 39 3249 33.58 34.11 33.85 3577 Date 3-Aug-06 1-Sep-00 2-Oct-06 1-Nov-06 1-Dec 06 3-Jan-07 1-Feb-07 1-Mar 07 2-Apr-07 1-May-07 1-Jun-07 2-Jul-07 1-Aug-07 4-Sep-07 1-Oct-07 1-Nov-07 3-Dec-07 2-Jan-00 1-Feb-08 3-Mar-08 1-Apr-08 1-May-08 2-Jun-08 1-Jul-08 1-Aug-08 2-Sep-08 1-Oct-08 3-Nov-08 1-Dec-08 2-Jan-09 2-Feb-09 2 Mar 09 1-Apr-09 1-May-09 1-Jun-09 1-Jul-09 3-Aug-09 1-Sep-09 1-Oct-09 2-Nov-09 1-Dec-09 4-Jan-10 1-Feb-10 1-Mar-10 1-Apr-10 3-May-10 1-Jun-10 1.Jul 10 2-Aug-10 1-Sep-10 1-Oct-10 1-Nov-10 1-Dec-10 3-Jan-11 1-Feb-11 1 Mar 11 1-Apr-11 2-May-11 1-Jun-11 1-Jul-11 1-Aug-11 VZ 25.95 27.39 27 59 2704 28.82 30.13 29.26 29.66 30.10 34 42 32 56 3403 33.44 35 35 37.13 34.83 3521 31,61 29.57 29.67 31.84 31.83 29 29 28.51 29.42 26.88 25 25 27.79 28.85 25.79 24.64 26.08 26.57 25.62 26. 91 28.53 27.61 26.90 26.74 28.43 29.94 26.97 26.52 28.44 26.91 25.62 26.09 27.55 27.99 30.89 31.24 30.79 3442 34.71 35.98 37.56 29 36.45 36.75 35.29 35 61 XOM 60.78 50.27 64.15 69.29 69.13 66.84 64.94 68.35 71.91 75.67 76.32 77 45 78.31 84.55 84.03 81.76 85.91 78.59 80.13 77.90 85.71 82.11 81.53 74.40 74.39 72.21 68.92 74.95 74.65 71.52 63 82 64.00 62.66 65.57 66.10 66.55 65.78 65.27 68.18 71.83 65.24 61.65 52.60 64.50 65.26 58.62 5533 57 86 57.72 50.34 54.93 68.35 71.85 79.28 84 49 83.11 86.91 82.92 80.85 79.27 Price data PRU GS 66.79 142.31 69.37 161.96 69.99 182.04 7502 186.85 79.05 191.21 82.06 203.84 83.72 193.84 83.10 198.53 87.46 210.37 93.93 222.12 89.52 208.58 81.60 181.57 82.66 169.68 89 B4 208.95 89.05 239.38 87.73 218 3 86.70 207 64 78.35 193.02 68.00 164.08 72.92 159.97 70.55 185.45 89.61 170.95 55.67 169.49 54.27 178.89 68.69 159,20 67.10 124,28 27.96 90.09 20.94 76.93 29.20 82.19 24.84 78.62 15,83 89.10 18.35 103.82 27.86 125.83 38.51 141.93 35.91 144.75 42.71 16032 48.80 162.79 48.15 181.37 43.64 167 42 48.80 167.28 48.71 166.47 48.93 146.63 154.50 59.22 168.61 62.22 143.48 56.49 142.91 52.53 130.04 149.41 49.53 135.99 53.04 143.50 51.47 160.02 50.68 155.41 58.71 167.37 61.51 162.85 65.83 163.36 61.58 158.19 63.42 150.62 63.78 140 73 63.59 133.09 58.68 134.97 47.15 117.25 29.92 33:57 3321 36 38 34.26 33.43 32.26 3462 31.55 JNJ S&P500 5596 130382 56.20 1335.85 58 33 1.377.96 5736 1.400.65 57.46 1.418.30 58.13 1.4382 55.09 1.406.82 $2.75 142086 56.21 145230 55.75 1.530 52 5429 1.503.35 53.30 1.455.27 54.81 1.473.90 58.28 1.528.75 5781 1.549.35 60.46 1.481.14 59.53 146836 56.35 1.378 55 55.67 1.330.63 58.28 1.322.70 60.28 1385 59 60.38 1.400.58 58.21 1.280.00 61.95 1.267.38 64.14 1.262.85 63.00 1.166.38 55.80 968.75 53.79 805 24 5494 903.25 52.97 4620 735.00 48 70 797 87 48.48 872.81 51.52 919.14 53.05 9193 56.87 5691 1.02069 57 33 1.057.08 55 60 1038.19 59.54 1.095.63 61.13 1.115.10 59.66 1.073.87 60.25 1.104.49 6235 1.109.43 61.491.186 56.28 1.089.41 56.99 1.030.71 56.06 1.101.60 55.55 1.049 33 60.36 1.14120 62.09 1.183.26 60.47 1.180.55 60.77 1.257 64 58.72 1.285.12 60 91 132722 58.74 1.325.83 65.15 1.353761 67291.545.20 66.52 1.320.54 64.79 1.282.28 63.16 1.140.65 32 55 33.85 33.26 34.00 37.73 37 33 360 3596 33.93 34 34 3495 51.30 38.45 56.08 4140 45.47 45.57 46.68 45.77 45.56 46.66 48.52 4620 46.55 46.09 5200 5402 57.36 55.73 56.85 56.44 5549 7416 QUESTION 2 (20 points) The exam spreadsheet includes five years of price data for 6 U.S. stocks. Fill in the template below. Notes: Return: The returns are approximated as the growth rate of the share prices. Excel function: ELN('Stock price data'!B9/'Stock price data"!B8) Sigma: The sigma measures the variability of the return, which is derived by taking the square root of the variance. Excel function: =SQRT(Variance) Beta: The beta coefficient measures the volatility of an individual stock compared to the systematic risk of the entire market. In statistical terms, beta represents the slope of the line through a regression of data points. E.g. Excel function: = =SLOPE(B17:376,$H$17:$H$76) H S&P500 A B D EIF G 1 QUESTION 2: RETURNS AND BETAS Johnson Exxon- Goldman- and 2 Verizon Mobile Prudential Sachs Hershey Johnson 3 VZ XOM PRU GS HSY JNJ 4 Monthly returns 5 Mean 6 Variance 7 Sigma 8 Beta 9 10 Annual returns 11 Mean 12 Variance 13 Sigma 14 15 Return data, month-by-month 16 1-Sep-06 17 2-Oct-06 181-Nov-06 19 1-Dec-06 20 3-Jan-07