first picture is the given information.

how to solve and put in excel

Goldman Hershey Verizon SIX STOCKS AND THE S&P 500 Exxon Mobile Prudential Sachs XOM PRU GS HSY 344.92 22.91 57.23 1262 Johnson and Johnson JNJ 173.08 VZ S&P500 Market value 99.27 -Market value in 56 VZ 25.95 27.39 2759 2704 28.82 30.13 29.26 29.56 30.19 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 Date 3-Aug-06 1-Sep-06 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.00 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 2525 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 7221 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 55.33 5786 57.72 50.34 54.93 68.35 71.85 79.28 84 49 B3.11 36.91 82.92 80.85 79.27 74.16 HSY 4707 46.62 46.15 46.44 43.66 44.75 46.61 48.16 48.43 46.69 44 84 40.83 41.46 41.38 38.44 35.85 35 39 3249 33.56 34.11 33.85 3577 29.92 33.57 3321 36 38 34.26 33.43 32.26 34 62 31.55 Price data PRU GS 66.79 142.31 69.37 161.96 69.99 182.04 75.02 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.84 208.95 89.05 239.38 87.73 21883 86.70 207 64 78.35 193.02 68.00 164.08 72.92 159.97 70.55 185.45 69.61 170.95 55.67 169.49 54.27 178.89 68.60 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 5608 149.41 49.53 135.99 53.04 143.59 51.47 160.02 50.68 155.41 58.71 167.37 61.51 1625 65.83 163.36 61.58 158.19 63.42 150.62 63.78 140 73 63.59 133.00 58.68 134.97 47.15 117.25 JNJ S&P500 5596 1.30382 56.20 1.335.85 58.33 1.377.96 5736 1.400.65 57.40 1.418.30 58.13 1.435.24 55.09 1.406,82 52.75 1.420.86 5621 148237 55.75 1.53062 5429 1.503.35 53.30 1.455.27 54.81 1.473.90 58.28 1.528.75 57 81 1.549.35 60.46 1.481.14 59.53 14683 56 35 1.378.55 55.67 1.390.65 58.28 1.322.70 60.28 1.385 59 60.38 1.400.58 58.21 1.280.00 61.95 1.267.38 64.14 1.262.83 63.09 1.166.38 55.86 968.75 53.79 895 24 54 94 903.25 5297 4620 735.09 48.70 79787 48.48 87281 51 52 919.14 53.05 919 56.87 56 91 1.02069 57 33 1.057.08 55 60 1,036.19 59.54 1.095.63 61.131.115.10 58.66 1.073 87 80.25 1.104.49 6235 1.10943 61.49 1.185.59 56.26 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.25 60.47 1.180.55 60.77 1.257.64 58.72 1.285.12 60 91 1.32722 sa 74 1.32583 65.15 1.33761 6729 1.345.20 66.52 1.320.54 64.79 1.282.28 63.16 1.140.65 3255 27.79 28.85 25.79 24.64 26.08 26.57 25.62 25.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.36 27.99 30.89 31.24 30.79 3442 34.71 35.98 37.56 37.29 36.45 36.75 35.29 33 85 33.26 34.00 37.73 37.33 36.90 3596 33.93 34 34 34.96 51.30 38.45 4140 45.47 45.57 46.68 45.77 45.56 46.66 48.52 4620 46.55 46.09 52.00 5402 57.36 55.73 56.85 56.44 55 49 3561 S&P500 QUESTION 2: RETURNS AND BETAS Exxon Johnson Goldman Verizon Prudential Mobile Sachs and Johnson VZ XOM PRU GS HSY JNU Monthly returns Mean Variance Sigma Beta Annual returns Mean Variance Sigma Return data, month-by- month 1-Sep-06 2.01.08 1-Nov-06 1-Dec-00 3-Jan-07 1-Feb-07 1-Mar- 2. Apr 07 1. May 07 1-Jun-07 2-07 1-Aug-07 4-Sep-07 1-07 1-Nov.07 3-Dec 07 2-an- 1-Feb-08 3.Mar 08 1-Apr- 1-May- 2-Jun- 14.00 1-Aug-08 2-Sep-08 1-Oct- 3-Nov.co 1-Dec-08 2-Jan-09 2-Feb-09 2-Mar-00 1-Apr-00 1-May-09 1-00 11-09 3-Aug-09 1-Sep-09 1-01-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-10 2-Aug-10 1-Sep-10 1-10 1-Nov-10 1-Dec-10 3-Jan-18 1-Feb-11 1- Mar 11 1-Apr. 11 2-May-11 1-Jun-11 111 1-Aug-11 QUESTION 3: THE ENVELOPE Variance-covariance matrix XOM PRU GS HSY JNJ Mean returns VZ VZ XOM PRU GS HSY JNJ Growth rate Discount rate Cost Year 1 cash flow NPV, DISCOUNT AND GROWTH RATES 10% 15% 500 100 Year Cash flow 0 -500.00 2 110.00 3 121.00 4 133.10 5 146.41 100.00 NPV IRR 2% 4% 6% 8% 10% 12% Discount rate Growth $0.00 0% 0% 3% 6% 9% 12% 15% 18% 21% 24% Black-Scholes Option Pricing Model input current stock price riskless rate strike price No of yrs stddev of returns S= r= K= T= 35.75 0.025 36 0.25 O= 0.3 output In (S/K)= (r+o^2/2)T o*T^1/2 d1= d2= N(D1)= N(D2) discounted strike price C=SN(D1)-dspN(D2) Goldman Hershey Verizon SIX STOCKS AND THE S&P 500 Exxon Mobile Prudential Sachs XOM PRU GS HSY 344.92 22.91 57.23 1262 Johnson and Johnson JNJ 173.08 VZ S&P500 Market value 99.27 -Market value in 56 VZ 25.95 27.39 2759 2704 28.82 30.13 29.26 29.56 30.19 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 Date 3-Aug-06 1-Sep-06 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.00 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 2525 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 7221 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 55.33 5786 57.72 50.34 54.93 68.35 71.85 79.28 84 49 B3.11 36.91 82.92 80.85 79.27 74.16 HSY 4707 46.62 46.15 46.44 43.66 44.75 46.61 48.16 48.43 46.69 44 84 40.83 41.46 41.38 38.44 35.85 35 39 3249 33.56 34.11 33.85 3577 29.92 33.57 3321 36 38 34.26 33.43 32.26 34 62 31.55 Price data PRU GS 66.79 142.31 69.37 161.96 69.99 182.04 75.02 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.84 208.95 89.05 239.38 87.73 21883 86.70 207 64 78.35 193.02 68.00 164.08 72.92 159.97 70.55 185.45 69.61 170.95 55.67 169.49 54.27 178.89 68.60 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 5608 149.41 49.53 135.99 53.04 143.59 51.47 160.02 50.68 155.41 58.71 167.37 61.51 1625 65.83 163.36 61.58 158.19 63.42 150.62 63.78 140 73 63.59 133.00 58.68 134.97 47.15 117.25 JNJ S&P500 5596 1.30382 56.20 1.335.85 58.33 1.377.96 5736 1.400.65 57.40 1.418.30 58.13 1.435.24 55.09 1.406,82 52.75 1.420.86 5621 148237 55.75 1.53062 5429 1.503.35 53.30 1.455.27 54.81 1.473.90 58.28 1.528.75 57 81 1.549.35 60.46 1.481.14 59.53 14683 56 35 1.378.55 55.67 1.390.65 58.28 1.322.70 60.28 1.385 59 60.38 1.400.58 58.21 1.280.00 61.95 1.267.38 64.14 1.262.83 63.09 1.166.38 55.86 968.75 53.79 895 24 54 94 903.25 5297 4620 735.09 48.70 79787 48.48 87281 51 52 919.14 53.05 919 56.87 56 91 1.02069 57 33 1.057.08 55 60 1,036.19 59.54 1.095.63 61.131.115.10 58.66 1.073 87 80.25 1.104.49 6235 1.10943 61.49 1.185.59 56.26 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.25 60.47 1.180.55 60.77 1.257.64 58.72 1.285.12 60 91 1.32722 sa 74 1.32583 65.15 1.33761 6729 1.345.20 66.52 1.320.54 64.79 1.282.28 63.16 1.140.65 3255 27.79 28.85 25.79 24.64 26.08 26.57 25.62 25.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.36 27.99 30.89 31.24 30.79 3442 34.71 35.98 37.56 37.29 36.45 36.75 35.29 33 85 33.26 34.00 37.73 37.33 36.90 3596 33.93 34 34 34.96 51.30 38.45 4140 45.47 45.57 46.68 45.77 45.56 46.66 48.52 4620 46.55 46.09 52.00 5402 57.36 55.73 56.85 56.44 55 49 3561 S&P500 QUESTION 2: RETURNS AND BETAS Exxon Johnson Goldman Verizon Prudential Mobile Sachs and Johnson VZ XOM PRU GS HSY JNU Monthly returns Mean Variance Sigma Beta Annual returns Mean Variance Sigma Return data, month-by- month 1-Sep-06 2.01.08 1-Nov-06 1-Dec-00 3-Jan-07 1-Feb-07 1-Mar- 2. Apr 07 1. May 07 1-Jun-07 2-07 1-Aug-07 4-Sep-07 1-07 1-Nov.07 3-Dec 07 2-an- 1-Feb-08 3.Mar 08 1-Apr- 1-May- 2-Jun- 14.00 1-Aug-08 2-Sep-08 1-Oct- 3-Nov.co 1-Dec-08 2-Jan-09 2-Feb-09 2-Mar-00 1-Apr-00 1-May-09 1-00 11-09 3-Aug-09 1-Sep-09 1-01-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-10 2-Aug-10 1-Sep-10 1-10 1-Nov-10 1-Dec-10 3-Jan-18 1-Feb-11 1- Mar 11 1-Apr. 11 2-May-11 1-Jun-11 111 1-Aug-11 QUESTION 3: THE ENVELOPE Variance-covariance matrix XOM PRU GS HSY JNJ Mean returns VZ VZ XOM PRU GS HSY JNJ Growth rate Discount rate Cost Year 1 cash flow NPV, DISCOUNT AND GROWTH RATES 10% 15% 500 100 Year Cash flow 0 -500.00 2 110.00 3 121.00 4 133.10 5 146.41 100.00 NPV IRR 2% 4% 6% 8% 10% 12% Discount rate Growth $0.00 0% 0% 3% 6% 9% 12% 15% 18% 21% 24% Black-Scholes Option Pricing Model input current stock price riskless rate strike price No of yrs stddev of returns S= r= K= T= 35.75 0.025 36 0.25 O= 0.3 output In (S/K)= (r+o^2/2)T o*T^1/2 d1= d2= N(D1)= N(D2) discounted strike price C=SN(D1)-dspN(D2)