Question

1 Approved Answer

Posted on Aug 20, 2024

Please show how to get answers by formula for the cells in orange. thank you. first three pictires in blue are data of returns for

Please show how to get answers by formula for the cells in orange. thank you. first three pictires in blue are data of returns for stocks. image text in transcribed

Question 1 Data ow You are finally able to invest in the stock market. You know a bit about a well diversified portfolio and decide to invest in 3 stocks. Their monthly returns are presented below: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Date 1/1/15 2/1/15 3/1/15 4/1/15 5/1/15 6/1/15 7/1/15 8/1/15 9/1/15 10/1/15 11/1/15 12/1/15 1/1/16 2/1/16 3/1/16 4/1/16 5/1/16 6/1/16 7/1/16 8/1/16 AAPL 6.14% 9.64% 3.14% 0.58% 4.10% -3.7256 -3.29% -6.91% 2.32% 8.34% -1.00% 11.02% -7.52% -0.67% 12.72% -13.99% 6.53% 4.27% 9.01% 181% AMZN 14.24% 7.23% -2.12% 13.35% 1.77% 1.13% 23.51% -4.34% -0.19% 22.27% 6.22% 1.67% -13.15% -5.87% 7.44% 11.11% 9.58% -0.99% 6.04% 1.36% GIS -1.59% 250% 5.2296 -2.2396 1.46% -0.77% 4.47% -2.49% 1.11% 3.53% -0,60% -0.17% -1.99% 4.14% 7.65% -3.17% 2.35% 13.60% 0.80% -1.49% TGT -3.03% 4.37% 6,82% -3.95% 0.62% 2.91% 0.27% -4.89% 1.04% -1.88% -6.06% 0,15% -0.26% 8.33% 4.88% -3.38% -13.48% 1.51% 7.89% -6.82% Instructions Your Info Q1 Data Q1 B C D E E F 29 30 86 31 32 33 34 35 36 37 38 39 40 41 42 9/1/16 10/1/16 11/1/16 12/1/16 1/1/17 2/1/17 3/1/17 4/1/17 5/1/17 6/1/17 7/1/17 8/1/17 9/1/17 10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18 9/1/18 10/1/18 11/1/18 12/1/18 6.55% 0.43% -2.66% 4.80% 4.77% 12.89% 4.87% -0.01% 6.34% -5.72% 3.27% 10.27% -6.02% 9.68% 1.66% -1.52% -1.06% 6.38% -5.81% -1.50% 13.08% -0.94% 2.80% 19.62% -0.83% -3.05% -18.40% -11.67% 8.86% -5.67% -4.97% 2.41% 7.13% 2.62% 4.91% 4.34% 7.53% -2.68% 2.04% -0.73% -1.96% 14.97% 6.47% -0.62% 24.06% 4.24% -4.30% 8.21% 4.05% 4.31% 4.57% 13.24% -0.48% -20.22% 5.77% -11.13% -9.80% -2.97% -1.68% 1.36% 1.15% -3.38% -2.25% -2.54% -1.34% -2.36% 0.47% -4.31% -2.82% 0.31% 8.94% 4.83% -1.35% -13.57% -10.86% -2.93% -3.32% 4.66% 4.07% -0.11% -6.72% 2.05% -3.40% -7.97% -2.15% 0.07% 12.38% -6.49% -10.73% -8.86% -6.09% 1.20% -1.25% -5.19% 8.38% -3.78% 8.22% 0.05% 1.46% 8.93% 15.28% 0.25% -7.93% 4.57% 0.40% 4.43% 5.99% 8.45% 0.81% -5.19% -15.15% -6.86% 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | A D E 57 58 59 60 61 62 63 64 65 66 67 B 1/1/19 2/1/19 3/1/19 4/1/19 5/1/19 6/1/19 7/1/19 8/1/19 9/1/19 10/1/19 11/1/19 5.52% 4.03% 9.70% 5.64% -12.76% 13.05% 7.64% -2.02% 7.30% 11.07% 5.23% 14.43% -4.59% 8.59% 8.19% -7.86% 6.68% -1.42% -4.85% -2.27% 2.35% -1.74% 14.12% 6.05% 9.80% -0.54% -3.94% 6.23% 1.12% 1.30% 2.45% -7.73% 3.60% F 10.46% -0.49% 10.49% -3.54% 3.91% 7.66% -0.24% 23.89% -0.12% 0.00% 18.81% co Question 1 You want to build a portfolio of the assets on the previous worksheet. However, you devise different strategies for building this portfolio. Each stratey requires a different allocation of the assets of interest. To begin, fill in the information on each stock below (please use population statistics AAPL AMZN GIS Hint I have named the ranges of each stock by their ticker Hint: Use the INDIRECT function in ali celis for a more advanced approach Expected Return Standard Deviation Risk Free 0.05% Now deterine the Var-cov Matrix of the 3 assets above. Please round your results to decimal places (please use population statistics when required). AAPL AMZN GIS 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Hint: I have called this range varcov AAPL AMZN GIS You have decided upon pursing one of three strategies in building your portfolio, First is an equally weighted strategy, where each stock takes up equal weight in the portfolio. Second is a minimum risk stratgey where minimize overall portfolio risk while only taking long (buy) positions. The third is a maximum return stratgey where you attempt to maximize the overall return on the portfolio. Given these three stratgles, please find the relative weights of each asset and the risk and return of the resulting portfolio (Solver would be a useful tool here) Question 1 Data ow You are finally able to invest in the stock market. You know a bit about a well diversified portfolio and decide to invest in 3 stocks. Their monthly returns are presented below: 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Date 1/1/15 2/1/15 3/1/15 4/1/15 5/1/15 6/1/15 7/1/15 8/1/15 9/1/15 10/1/15 11/1/15 12/1/15 1/1/16 2/1/16 3/1/16 4/1/16 5/1/16 6/1/16 7/1/16 8/1/16 AAPL 6.14% 9.64% 3.14% 0.58% 4.10% -3.7256 -3.29% -6.91% 2.32% 8.34% -1.00% 11.02% -7.52% -0.67% 12.72% -13.99% 6.53% 4.27% 9.01% 181% AMZN 14.24% 7.23% -2.12% 13.35% 1.77% 1.13% 23.51% -4.34% -0.19% 22.27% 6.22% 1.67% -13.15% -5.87% 7.44% 11.11% 9.58% -0.99% 6.04% 1.36% GIS -1.59% 250% 5.2296 -2.2396 1.46% -0.77% 4.47% -2.49% 1.11% 3.53% -0,60% -0.17% -1.99% 4.14% 7.65% -3.17% 2.35% 13.60% 0.80% -1.49% TGT -3.03% 4.37% 6,82% -3.95% 0.62% 2.91% 0.27% -4.89% 1.04% -1.88% -6.06% 0,15% -0.26% 8.33% 4.88% -3.38% -13.48% 1.51% 7.89% -6.82% Instructions Your Info Q1 Data Q1 B C D E E F 29 30 86 31 32 33 34 35 36 37 38 39 40 41 42 9/1/16 10/1/16 11/1/16 12/1/16 1/1/17 2/1/17 3/1/17 4/1/17 5/1/17 6/1/17 7/1/17 8/1/17 9/1/17 10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18 9/1/18 10/1/18 11/1/18 12/1/18 6.55% 0.43% -2.66% 4.80% 4.77% 12.89% 4.87% -0.01% 6.34% -5.72% 3.27% 10.27% -6.02% 9.68% 1.66% -1.52% -1.06% 6.38% -5.81% -1.50% 13.08% -0.94% 2.80% 19.62% -0.83% -3.05% -18.40% -11.67% 8.86% -5.67% -4.97% 2.41% 7.13% 2.62% 4.91% 4.34% 7.53% -2.68% 2.04% -0.73% -1.96% 14.97% 6.47% -0.62% 24.06% 4.24% -4.30% 8.21% 4.05% 4.31% 4.57% 13.24% -0.48% -20.22% 5.77% -11.13% -9.80% -2.97% -1.68% 1.36% 1.15% -3.38% -2.25% -2.54% -1.34% -2.36% 0.47% -4.31% -2.82% 0.31% 8.94% 4.83% -1.35% -13.57% -10.86% -2.93% -3.32% 4.66% 4.07% -0.11% -6.72% 2.05% -3.40% -7.97% -2.15% 0.07% 12.38% -6.49% -10.73% -8.86% -6.09% 1.20% -1.25% -5.19% 8.38% -3.78% 8.22% 0.05% 1.46% 8.93% 15.28% 0.25% -7.93% 4.57% 0.40% 4.43% 5.99% 8.45% 0.81% -5.19% -15.15% -6.86% 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | A D E 57 58 59 60 61 62 63 64 65 66 67 B 1/1/19 2/1/19 3/1/19 4/1/19 5/1/19 6/1/19 7/1/19 8/1/19 9/1/19 10/1/19 11/1/19 5.52% 4.03% 9.70% 5.64% -12.76% 13.05% 7.64% -2.02% 7.30% 11.07% 5.23% 14.43% -4.59% 8.59% 8.19% -7.86% 6.68% -1.42% -4.85% -2.27% 2.35% -1.74% 14.12% 6.05% 9.80% -0.54% -3.94% 6.23% 1.12% 1.30% 2.45% -7.73% 3.60% F 10.46% -0.49% 10.49% -3.54% 3.91% 7.66% -0.24% 23.89% -0.12% 0.00% 18.81% co Question 1 You want to build a portfolio of the assets on the previous worksheet. However, you devise different strategies for building this portfolio. Each stratey requires a different allocation of the assets of interest. To begin, fill in the information on each stock below (please use population statistics AAPL AMZN GIS Hint I have named the ranges of each stock by their ticker Hint: Use the INDIRECT function in ali celis for a more advanced approach Expected Return Standard Deviation Risk Free 0.05% Now deterine the Var-cov Matrix of the 3 assets above. Please round your results to decimal places (please use population statistics when required). AAPL AMZN GIS 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Hint: I have called this range varcov AAPL AMZN GIS You have decided upon pursing one of three strategies in building your portfolio, First is an equally weighted strategy, where each stock takes up equal weight in the portfolio. Second is a minimum risk stratgey where minimize overall portfolio risk while only taking long (buy) positions. The third is a maximum return stratgey where you attempt to maximize the overall return on the portfolio. Given these three stratgles, please find the relative weights of each asset and the risk and return of the resulting portfolio (Solver would be a useful tool here)