d) Use Data Table tool to calculate the return and standard deviation of the portfolio consisting of 60% of AAPL and 40% of WMT.

e) Based on the return and variance-covariance matrix information in parts a) and b),

i) Use Solver to calculate the weights of the minimum variance portfolio (MVP) consisting of WMT and AAPL only. (Note that the solution of this part must include an image of the Solver dialog showing all input values. The image can be shown in a non-Excel file.)

ii) Use Solver to calculate the weights of the efficient portfolio with a return of 2%. (Note that the solution of this part must include an image of the Solver dialog showing all input values. The image can be shown in a non-Excel file.)

2 Date 12/31/2015 Portfolio_E Portfolio_ AAPL AAPL AAPL WMT S&P500 WMT S&P500 Portfolio_D P 0.72 10.28 WMT 149.54 55.85 2,043.94 1.53 5.30 1,940.24 1,932.23 4 1/29/2016 91.23 60.46 38.99 8.25 -5.07 S&P500 0.62 3.11 2/29/2016 91.12 G0.44 -0.12 -0.03 -0.41 Variance-Covariance Matrix AAPL 6 WMT S&P 500 3/31/2016 102.71 62.86 2,059.74 12.72 4.01 6.60 2,0G5.30 2,096,95 2,098.86 2,173.60 2.01 15.72 4/29/2016 88.34 61.37 -13.99 -2.37 0.27 AAPL 105.65 28.05 94.68 G5,44 2.01 5.05 5/31/2016 7.18 6.62 1,53 WMT 15.72 5.05 9.64 6/30/2016 90.64 67,51 -4.27 3.16 0.09 S&P 500 10 7/29/2016 98.80 67.46 9,00 -0.07 3.56 Portfolio Weight in AAPL D E F 11 8/31/2016 101.14 66.50 2,170.95 2.37 -1.43 -0.12 2,168.27 12 9/30/2016 107.76 67.13 6.55 0.95 -0.12 10/31/2016 108.23 G5.18 13 2,126.15 0.44 -2.91 -1.94 Weight in WMT 11/30/2016 12/30/2016 65.56 14 105.89 2,198.81 -2.16 0.59 3.42 Sum 15 110.97 64.80 2,238.83 4.80 -1.16 1.82 Portfolip return 2,278.87 2,3G3.64 62.57 16 1/31/2017 116.27 4.78 -3.44 1.79 Portfolio Variance 17 2/28/2017 131.82 66.50 13.37 6.28 3.72 Portfolio Standard Deviation 18 3/31/2017 138.24 68.07 2,362.72 4.87 2.37 -0.04 19 4/28/2017 138.23 71.00 2,384.20 -0.01 4.30 0.91 2,411.80 20 5/31/2017 147.61 74.72 6.79 5.25 1.16 71.95 21 6/30/2017 139.16 2,423.41 -5.72 -3.72 0.48 5.70 22 7/31/2017 143.71 76.05 2,470.30 3.27 1.93 74,69 23 8/31/2017 159.09 2,471.65 10.70 -1.79 0.05 -6.03 24 9/29/2017 149.50 74,75 2,519.36 0.09 1.93 |10/31/2017 11/30/2017 167.30 12/29/2017 25 163.98 83.53 2,575.26 9.69 11.74 2.22 2.02 1.52 -1.06 93.02 11.36 26 2,647.58 2.81 27 164.75 94.97 2,673.61 2.10 0.98 28 1/31/2018 163.00 102.52 2,823.81 7.95 5.62 29 2/28/2018 174.11 86,56 2,713.83 6.82 -15.56 -3.89 86.07 -0.57 -2.69 30 3/29/2018 164.01 2,640.87 -5.80 31 4/30/2018 161.54 85.58 2,648.05 -1.51 -0.57 0.27 13.51 2.16 32 5/31/2018 183.37 80.36 2,705.27 -6.10 33 6/29/2018 181.64 83.38 2,718.37 -0.94 3.77 0.48 2.80 3.60 34 7/31/2018 186.73 86.87 2,816.29 4.18 3.03 35 8/31/2018 224.15 93.87 2,901.52 20.04 8.05 36 9/28/2018 222.29 91.96 2,913.98 -0.83 -2.03 0.43 10/31/2018 38 11/30/2018 176.46 39 12/31/2018 155.87 37 215.52 98.19 2,711.74 -3.05 6.78 -6.94 95.62 2,7G0.17 -18.12 -2.62 1.79 91.71 2,506.85 -11.67 -4.09 -9.18 Question 1 Question 2- Explore fox P19 N O P Q Mean Return (%) Standard Deviation (%) 1 Stats =STDEV.P(E4:E39) =STDEV.P(F4:F39) AVERAGE(E4:E39) 2 AAPL =AVERAGE(F4:F39) 3 WMT S&P500 AVERAGE(G4:G39) -STDEV.P(G4:G39) 4 S&P 500 6 Variance-Covariance Matrix AAPL WMT COVARIANCE.P(E4: E39, E4: E39) COVARIANCE. P( E4: E39, F4: F39) 7 AAPL 15.72 COVARIANCE.P(F4:F39, F4: F39) COVARIANCE. P( F4: F39, G4: G39) WMT 07 09 COVARIANCE. P(E4:E39,G4:G39) COVARIANCE. P(G4: S&P 500 10 Portfolio 11 E F Weight in AAPL Weight in WMT 12 13 14 Sum 15 Portfolio return 16 Portfolio Variance Portfolio Standard Deviation 17 18 19 20 21 22 23 24 Sheet1 Sheet2 (+ Ready 100% 2 Date 12/31/2015 Portfolio_E Portfolio_ AAPL AAPL AAPL WMT S&P500 WMT S&P500 Portfolio_D P 0.72 10.28 WMT 149.54 55.85 2,043.94 1.53 5.30 1,940.24 1,932.23 4 1/29/2016 91.23 60.46 38.99 8.25 -5.07 S&P500 0.62 3.11 2/29/2016 91.12 G0.44 -0.12 -0.03 -0.41 Variance-Covariance Matrix AAPL 6 WMT S&P 500 3/31/2016 102.71 62.86 2,059.74 12.72 4.01 6.60 2,0G5.30 2,096,95 2,098.86 2,173.60 2.01 15.72 4/29/2016 88.34 61.37 -13.99 -2.37 0.27 AAPL 105.65 28.05 94.68 G5,44 2.01 5.05 5/31/2016 7.18 6.62 1,53 WMT 15.72 5.05 9.64 6/30/2016 90.64 67,51 -4.27 3.16 0.09 S&P 500 10 7/29/2016 98.80 67.46 9,00 -0.07 3.56 Portfolio Weight in AAPL D E F 11 8/31/2016 101.14 66.50 2,170.95 2.37 -1.43 -0.12 2,168.27 12 9/30/2016 107.76 67.13 6.55 0.95 -0.12 10/31/2016 108.23 G5.18 13 2,126.15 0.44 -2.91 -1.94 Weight in WMT 11/30/2016 12/30/2016 65.56 14 105.89 2,198.81 -2.16 0.59 3.42 Sum 15 110.97 64.80 2,238.83 4.80 -1.16 1.82 Portfolip return 2,278.87 2,3G3.64 62.57 16 1/31/2017 116.27 4.78 -3.44 1.79 Portfolio Variance 17 2/28/2017 131.82 66.50 13.37 6.28 3.72 Portfolio Standard Deviation 18 3/31/2017 138.24 68.07 2,362.72 4.87 2.37 -0.04 19 4/28/2017 138.23 71.00 2,384.20 -0.01 4.30 0.91 2,411.80 20 5/31/2017 147.61 74.72 6.79 5.25 1.16 71.95 21 6/30/2017 139.16 2,423.41 -5.72 -3.72 0.48 5.70 22 7/31/2017 143.71 76.05 2,470.30 3.27 1.93 74,69 23 8/31/2017 159.09 2,471.65 10.70 -1.79 0.05 -6.03 24 9/29/2017 149.50 74,75 2,519.36 0.09 1.93 |10/31/2017 11/30/2017 167.30 12/29/2017 25 163.98 83.53 2,575.26 9.69 11.74 2.22 2.02 1.52 -1.06 93.02 11.36 26 2,647.58 2.81 27 164.75 94.97 2,673.61 2.10 0.98 28 1/31/2018 163.00 102.52 2,823.81 7.95 5.62 29 2/28/2018 174.11 86,56 2,713.83 6.82 -15.56 -3.89 86.07 -0.57 -2.69 30 3/29/2018 164.01 2,640.87 -5.80 31 4/30/2018 161.54 85.58 2,648.05 -1.51 -0.57 0.27 13.51 2.16 32 5/31/2018 183.37 80.36 2,705.27 -6.10 33 6/29/2018 181.64 83.38 2,718.37 -0.94 3.77 0.48 2.80 3.60 34 7/31/2018 186.73 86.87 2,816.29 4.18 3.03 35 8/31/2018 224.15 93.87 2,901.52 20.04 8.05 36 9/28/2018 222.29 91.96 2,913.98 -0.83 -2.03 0.43 10/31/2018 38 11/30/2018 176.46 39 12/31/2018 155.87 37 215.52 98.19 2,711.74 -3.05 6.78 -6.94 95.62 2,7G0.17 -18.12 -2.62 1.79 91.71 2,506.85 -11.67 -4.09 -9.18 Question 1 Question 2- Explore fox P19 N O P Q Mean Return (%) Standard Deviation (%) 1 Stats =STDEV.P(E4:E39) =STDEV.P(F4:F39) AVERAGE(E4:E39) 2 AAPL =AVERAGE(F4:F39) 3 WMT S&P500 AVERAGE(G4:G39) -STDEV.P(G4:G39) 4 S&P 500 6 Variance-Covariance Matrix AAPL WMT COVARIANCE.P(E4: E39, E4: E39) COVARIANCE. P( E4: E39, F4: F39) 7 AAPL 15.72 COVARIANCE.P(F4:F39, F4: F39) COVARIANCE. P( F4: F39, G4: G39) WMT 07 09 COVARIANCE. P(E4:E39,G4:G39) COVARIANCE. P(G4: S&P 500 10 Portfolio 11 E F Weight in AAPL Weight in WMT 12 13 14 Sum 15 Portfolio return 16 Portfolio Variance Portfolio Standard Deviation 17 18 19 20 21 22 23 24 Sheet1 Sheet2 (+ Ready 100%