I need help completing Step 1, I've started it and answered Q1 and Q2... but I need some direction and my work checked. Also, how to compute the tangent portfolio and line.

Lab Exercise: Markowitz 3 Asset Portfolio Efficient Frontier Students are encouraged to work in teams of 4 individuals or less. A diffierent team member should gather the data for steps 3-5. This exercise is designed to illustrate the creation of Efficient Frontiers using various weights for 2 or 3 asset portfolios. After completing the exercise students should have a better understanding of how Efficient Frontiers are created in practice and theory. Students should be able to explain the role correlation plays in creating a minimum risk portfolio. Download the "Markowitz 3 Asset Portfolio EF" file from ANGEL. SAVE and OPEN file in Excel NOT ANGEL. Step 1 Print the graph for combinations of 3 stocks: Oracle, Cisco, and Citigroup by clicking on tab E below & printing. Answer the following questions related to the printout. You should also click on tabs B and C below to see how the inputs for the Efficient Frontier graphs were calculated. The objective of step 1 is to understand how the spreadsheet works. Lable and answer the following questions on the printout of the overlapping 3 EF sets (Tab E graph below). (You may first wish to examine Tab D, 2 stock combinations shown separately. 3 stocks are graphed together in tab E.) 1. Label the three points on the graph that represent 100% investment in each stock. (Label with the ticker symbol of the stock for example ORCL, CSCO, C) 2. Circle and label the minimum risk portfolio and write the % invested in each stock next to the point. (Hint: Move cursor over a point on graph and compare labels to TAB C data.) 3.a. Which stocks provide the greatest diversification benefits? Why? b. Put a square around your optimal portfolio and identify the E(R) and standard deviation for this portfolio. answer. (Note: There is more than one correct answer.) 4. Based on this historical information which stocks would you choose for a portfolio? Why? 5. Assume you require a monthly return of 1.5% on $10,000, and can only choose one of the portfolios out of the portfolios graphed. What portfolio most closely meets this and how much money ($10,000 must be invested) would you put in each stock? Cirlce this portfolio and write MRP for 1.5% next to it. Step 2: Gather the most recent 61 monthly stock prices for three common stocks from http://finance.yahoo.com/. a. Go to yahoo.com and enter a ticker symbol for a stock in the Investment Challenge game in the Get Quotes area of Yahoo! Finance. b. Click on Chart to view a graph of historical stock prices. If a company does not have 5 years of data choose another one. c. At the bottom of the chart, click on Historical Prices. d. Make the start date five years from today and change to monthly data then click Get Historical Data to get 61 months of data. (You need 61 observations to calculate 60 returns.) e. Scroll down to the bottom of the page click on Download Spreadsheet Format and save the data on a floppy disk or P: drive. f. Copy (DO NOT Cut & Paste) the adjusted close prices from the 3 stocks into the highlighted areas of B.Input Series tab below. g. Repeat steps 2.a-f for two more stocks. Note: You may need to request to show all files when pulling up the yahoo data, since it did not save as an Excel file. Notice how the expected returns, standard deviations, covariances and correlations are calculated by looking at the formulas. h. Make a print out of the tab E graph and label your optimal investment in two of the three stocks. Step 3: Replace the stock you did not choose in Step 2 with another stock under consideration in the Investment Challenge Game. a. Go to Yahoo.com and gather monthly adjusted close prices for your new stock and replace one stock in the B.Input Series tab below. b. Make a print out of the tab E graph and label your optimal investment in two of the three stocks. Step 4: Staple the graphs from the previous steps in order to this instruction sheet and turn in one set per team next class period. 1) Do you think this exercise would be useful for a first time investor who is trying to decide on two stocks to invest? Why or Why Not? 2) Do you think this exercise would be a useful strategy for an investor trying to pick an optimal portfolio of 30 stocks? Why or Why Not? Date ORCL 1-Oct-02 8.2 3-Sep-02 7.86 1-Aug-02 9.59 1-Jul-02 10.01 3-Jun-02 9.47 1-May-02 7.92 1-Apr-02 10.04 1-Mar-02 12.8 1-Feb-02 16.62 2-Jan-02 17.26 3-Dec-01 13.81 1-Nov-01 14.03 1-Oct-01 13.56 4-Sep-01 12.58 1-Aug-01 12.21 2-Jul-01 18.08 1-Jun-01 19 1-May-01 15.3 2-Apr-01 16.16 1-Mar-01 14.98 1-Feb-01 19 2-Jan-01 29.12 1-Dec-00 29.06 1-Nov-00 26.5 2-Oct-00 33 1-Sep-00 39.38 1-Aug-00 45.47 3-Jul-00 37.6 1-Jun-00 42.03 1-May-00 35.94 3-Apr-00 39.97 1-Mar-00 39.03 1-Feb-00 37.12 3-Jan-00 24.98 1-Dec-99 28.01 1-Nov-99 16.95 1-Oct-99 11.89 1-Sep-99 11.38 2-Aug-99 9.12 1-Jul-99 9.52 1-Jun-99 9.28 3-May-99 6.2 1-Apr-99 6.76 1-Mar-99 6.59 RA RA-E(RA) 0.0433 0.0166 -0.1804 -0.2071 -0.0420 -0.0686 0.0570 0.0303 0.1957 0.1690 -0.2112 -0.2378 -0.2156 -0.2423 -0.2298 -0.2565 -0.0371 -0.0638 0.2498 0.2231 -0.0157 -0.0424 0.0347 0.0080 0.0779 0.0512 0.0303 0.0036 -0.3247 -0.3513 -0.0484 -0.0751 0.2418 0.2152 -0.0532 -0.0799 0.0788 0.0521 -0.2116 -0.2383 -0.3475 -0.3742 0.0021 -0.0246 0.0966 0.0699 -0.1970 -0.2236 -0.1620 -0.1887 -0.1339 -0.1606 0.2093 0.1826 -0.1054 -0.1321 0.1694 0.1428 -0.1008 -0.1275 0.0241 -0.0026 0.0515 0.0248 0.4860 0.4593 -0.1082 -0.1349 0.6525 0.6258 0.4256 0.3989 0.0448 0.0181 0.2478 0.2211 -0.0420 -0.0687 0.0259 -0.0008 0.4968 0.4701 -0.0828 -0.1095 0.0258 -0.0009 -0.2922 -0.3188 varA 0.0003 0.0429 0.0047 0.0009 0.0286 0.0566 0.0587 0.0658 0.0041 0.0498 0.0018 0.0001 0.0026 0.0000 0.1234 0.0056 0.0463 0.0064 0.0027 0.0568 0.1400 0.0006 0.0049 0.0500 0.0356 0.0258 0.0334 0.0174 0.0204 0.0163 0.0000 0.0006 0.2110 0.0182 0.3917 0.1591 0.0003 0.0489 0.0047 0.0000 0.2210 0.0120 0.0000 0.1017 CSCO 9.46 10.48 13.82 13.19 13.95 15.78 14.65 16.93 14.27 19.8 18.11 20.44 16.92 12.18 16.33 19.22 18.2 19.26 16.98 15.81 23.69 37.44 38.25 47.88 53.88 55.25 68.62 65.44 63.56 56.94 69.33 77.31 66.1 54.75 53.56 44.6 37 34.28 33.9 31.07 32.22 27.25 28.51 27.39 RB -0.0973 -0.2417 0.0478 -0.0545 -0.1160 0.0771 -0.1347 0.1864 -0.2793 0.0933 -0.1140 0.2080 0.3892 -0.2541 -0.1504 0.0560 -0.0550 0.1343 0.0740 -0.3326 -0.3673 -0.0212 -0.2011 -0.1114 -0.0248 -0.1948 0.0486 0.0296 0.1163 -0.1787 -0.1032 0.1696 0.2073 0.0222 0.2009 0.2054 0.0793 0.0112 0.0911 -0.0357 0.1824 -0.0442 0.0409 0.1202 RB-E(RB) -0.1105 -0.2548 0.0346 -0.0676 -0.1291 0.0640 -0.1478 0.1733 -0.2924 0.0802 -0.1271 0.1949 0.3760 -0.2673 -0.1635 0.0429 -0.0682 0.1211 0.0609 -0.3458 -0.3804 -0.0343 -0.2143 -0.1245 -0.0379 -0.2080 0.0354 0.0164 0.1031 -0.1919 -0.1164 0.1564 0.1942 0.0091 0.1878 0.1923 0.0662 -0.0019 0.0779 -0.0488 0.1692 -0.0573 0.0277 0.1071 varB 0.0122 0.0649 0.0012 0.0046 0.0167 0.0041 0.0218 0.0300 0.0855 0.0064 0.0162 0.0380 0.1414 0.0714 0.0267 0.0018 0.0046 0.0147 0.0037 0.1196 0.1447 0.0012 0.0459 0.0155 0.0014 0.0433 0.0013 0.0003 0.0106 0.0368 0.0135 0.0245 0.0377 0.0001 0.0353 0.0370 0.0044 0.0000 0.0061 0.0024 0.0286 0.0033 0.0008 0.0115 C 27.98 29.65 32.75 31.11 35.94 40.05 40 45.74 41.8 43.78 46.47 44.1 41.76 37.15 42.02 45.92 48.32 46.87 44.82 41.02 44.85 50.91 46.45 45.31 47.74 49.05 52.97 47.84 40.89 42.2 40.04 40.53 35.03 38.48 37.59 36.37 36.62 29.62 29.91 30 31.88 29.64 33.5 28.5 RC -0.0563 -0.0947 0.0527 -0.1344 -0.1026 0.0012 -0.1255 0.0943 -0.0452 -0.0579 0.0537 0.0560 0.1241 -0.1159 -0.0849 -0.0497 0.0309 0.0457 0.0926 -0.0854 -0.1190 0.0960 0.0252 -0.0509 -0.0267 -0.0740 0.1072 0.1700 -0.0310 0.0539 -0.0121 0.1570 -0.0897 0.0237 0.0335 -0.0068 0.2363 -0.0097 -0.0030 -0.0590 0.0756 -0.1152 0.1754 0.0874 RC-E(RC) -0.0668 -0.1051 0.0422 -0.1449 -0.1131 -0.0092 -0.1360 0.0838 -0.0557 -0.0684 0.0433 0.0455 0.1136 -0.1264 -0.0954 -0.0602 0.0205 0.0353 0.0822 -0.0959 -0.1295 0.0855 0.0147 -0.0614 -0.0372 -0.0845 0.0967 0.1595 -0.0415 0.0435 -0.0226 0.1465 -0.1001 0.0132 0.0231 -0.0173 0.2258 -0.0202 -0.0135 -0.0695 0.0651 -0.1257 0.1650 0.0769 varC cov(A,B) 0.0045 -0.0018 0.0111 0.0528 0.0018 -0.0024 0.0210 -0.0021 0.0128 -0.0218 0.0001 -0.0152 0.0185 0.0358 0.0070 -0.0444 0.0031 0.0186 0.0047 0.0179 0.0019 0.0054 0.0021 0.0016 0.0129 0.0193 0.0160 -0.0010 0.0091 0.0574 0.0036 -0.0032 0.0004 -0.0147 0.0012 -0.0097 0.0067 0.0032 0.0092 0.0824 0.0168 0.1423 0.0073 0.0008 0.0002 -0.0150 0.0038 0.0278 0.0014 0.0072 0.0071 0.0334 0.0094 0.0065 0.0254 -0.0022 0.0017 0.0147 0.0019 0.0245 0.0005 0.0003 0.0215 0.0039 0.0100 0.0892 0.0002 -0.0012 0.0005 0.1175 0.0003 0.0767 0.0510 0.0012 0.0004 -0.0004 0.0002 -0.0054 0.0048 0.0000 0.0042 0.0796 0.0158 0.0063 0.0272 0.0000 0.0059 -0.0341 cov(A,C) -0.0011 0.0218 -0.0029 -0.0044 -0.0191 0.0022 0.0329 -0.0215 0.0036 -0.0153 -0.0018 0.0004 0.0058 -0.0005 0.0335 0.0045 0.0044 -0.0028 0.0043 0.0228 0.0485 -0.0021 0.0010 0.0137 0.0070 0.0136 0.0177 -0.0211 -0.0059 -0.0055 0.0001 0.0036 -0.0460 -0.0018 0.0144 -0.0069 0.0041 -0.0045 0.0009 0.0001 0.0306 0.0138 -0.0001 -0.0245 cov(B,C) 0.0074 0.0268 0.0015 0.0098 0.0146 -0.0006 0.0201 0.0145 0.0163 -0.0055 -0.0055 0.0089 0.0427 0.0338 0.0156 -0.0026 -0.0014 0.0043 0.0050 0.0332 0.0493 -0.0029 -0.0031 0.0076 0.0014 0.0176 0.0034 0.0026 -0.0043 -0.0083 0.0026 0.0229 -0.0194 0.0001 0.0043 -0.0033 0.0150 0.0000 -0.0011 0.0034 0.0110 0.0072 0.0046 0.0082 1-Feb-99 4-Jan-99 1-Dec-98 2-Nov-98 1-Oct-98 1-Sep-98 3-Aug-98 1-Jul-98 1-Jun-98 1-May-98 1-Apr-98 2-Mar-98 2-Feb-98 2-Jan-98 1-Dec-97 3-Nov-97 1-Oct-97 9.31 9.23 7.19 5.71 4.93 4.85 3.32 4.42 4.09 3.94 4.31 5.26 4.1 3.88 3.72 5.55 5.96 SUMS: 0.0087 0.2837 0.2592 0.1582 0.0165 0.4608 -0.2489 0.0807 0.0381 -0.0858 -0.1806 0.2829 0.0567 0.0430 -0.3297 -0.0688 -0.0180 0.2571 0.2325 0.1315 -0.0102 0.4342 -0.2755 0.0540 0.0114 -0.1125 -0.2073 0.2563 0.0300 0.0163 -0.3564 -0.0955 1.6006 2.7355 variance (A) = E(RA)= standard deviation (A)= Covariance Matrix A B C A 0.0456 0.016388 0.002862 B 0.0164 0.0239 0.006885 C 0.0029 0.0069 0.0106 29.47 29.47 29.47 29.47 29.47 29.47 0.0003 0.0661 0.0541 0.0173 0.0001 0.1885 0.0759 0.0029 0.0001 0.0127 0.0430 0.0657 0.0009 0.0003 0.1270 0.0091 29.47 29.47 29.47 0.0456 0.0267 0.2135 24.45 27.89 23.2 18.84 15.75 15.45 13.65 15.96 15.34 12.6 12.21 11.4 10.98 10.51 9.29 9.58 9.11 -0.1233 0.2022 0.2314 0.1962 0.0194 0.1319 -0.1447 0.0404 0.2175 0.0319 0.0711 0.0383 0.0447 0.1313 -0.0303 0.0516 -0.1365 0.1890 0.2183 0.1830 0.0063 0.1187 -0.1579 0.0273 0.2043 0.0188 0.0579 0.0251 0.0316 0.1182 -0.0434 0.0384 0.7887 variance (B) = E(RB)= standard deviation (B)= Correlation Matrix A B A 1.000 0.497 B 0.497 1.000 C 0.130 0.432 0.0186 0.0357 0.0476 0.0335 0.0000 0.0141 0.0249 0.0007 0.0417 0.0004 0.0034 0.0006 0.0010 0.0140 0.0019 0.0015 1.4313 0.0239 0.0131 0.1545 C 0.130 0.432 1.000 26.21 25.01 22.1 22.35 20.9 16.68 19.73 29.91 26.96 27.24 27.21 26.68 24.74 22.04 23.96 22.62 20.75 0.0480 0.1317 -0.0112 0.0694 0.2530 -0.1546 -0.3404 0.1094 -0.0103 0.0011 0.0199 0.0784 0.1225 -0.0801 0.0592 0.0901 0.0375 0.1212 -0.0217 0.0589 0.2425 -0.1651 -0.3508 0.0989 -0.0208 -0.0094 0.0094 0.0679 0.1120 -0.0906 0.0488 0.0796 0.6292 variance (C) = E(RC)= standard deviation (C)= 0.0014 0.0147 0.0005 0.0035 0.0588 0.0272 0.1231 0.0098 0.0004 0.0001 0.0001 0.0046 0.0125 0.0082 0.0024 0.0063 -0.0007 0.0312 -0.0050 0.0077 -0.0025 -0.0717 0.0967 0.0053 -0.0002 0.0011 -0.0019 0.0174 0.0034 -0.0015 -0.0174 -0.0076 -0.0051 0.0229 -0.0047 0.0108 0.0015 -0.0196 0.0554 0.0027 -0.0042 -0.0002 0.0005 0.0017 0.0035 -0.0107 -0.0021 0.0031 0.9833 0.1717 cov(A,B) cov(A,C) 0.0106 0.0164 0.0029 0.0105 Corr(A,B) Corr(A,C) 0.1032 0.4969 0.1299 0.4131 cov(B,C) 0.0069 Corr(B,C) 0.4320 0.6389 0.0025 0.0486 0.0508 0.0241 -0.0001 0.0515 0.0435 0.0015 0.0023 -0.0021 -0.0120 0.0064 0.0009 0.0019 0.0155 -0.0037 Correlation Matrix A B C A 1.0000 B 0.4969 1.0000 C 0.1299 0.4320 1.0000 Expected Returns A B C 2.67% 1.31% 1.05% Standard Deviations A B C 0.2135 0.1545 0.1032 Set 1 wA wB wC s.d. RPortfolio 1 2 0% 10% 100% 90% 0% 0% 0.154 0.151 1.31% 1.45% 3 4 5 6 7 8 9 10 11 20% 30% 40% 50% 60% 70% 80% 90% 100% 80% 70% 60% 50% 40% 30% 20% 10% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0% 0.149 0.151 0.154 0.160 0.168 0.177 0.188 0.200 0.214 1.59% 1.72% 1.86% 1.99% 2.13% 2.26% 2.40% 2.53% 2.67% Set 2 wA wB wC s.d. RPortfolio 12 13 0% 10% 0% 0% 100% 90% 0.103 0.098 1.05% 1.21% 14 15 16 17 18 19 20 21 22 20% 30% 40% 50% 60% 70% 80% 90% 100% 0% 0% 0% 0% 0% 0% 0% 0% 0% 80% 70% 60% 50% 40% 30% 20% 10% 0% 0.098 0.103 0.112 0.124 0.140 0.157 0.175 0.194 0.214 1.37% 1.53% 1.70% 1.86% 2.02% 2.18% 2.34% 2.51% 2.67% Set 3 wA wB wC s.d. RPortfolio 23 24 0% 0% 0% 10% 100% 90% 0.103 0.101 1.05% 1.08% 25 26 27 28 29 30 31 32 33 0% 0% 0% 0% 0% 0% 0% 0% 0% 20% 30% 40% 50% 60% 70% 80% 90% 100% 80% 70% 60% 50% 40% 30% 20% 10% 0% 0.100 0.101 0.105 0.110 0.117 0.125 0.134 0.144 0.154 1.10% 1.13% 1.15% 1.18% 1.21% 1.23% 1.26% 1.29% 1.31% Efficient Frontier Set 1 (A&B) 5.00% 4.50% Portfolio Expected Return 4.00% 3.50% 3.00% 2.50% 2.00% 1.50% 1.00% 0.50% 0.00% 0.000 0.100 0.200 0.300 0.400 0.500 0.600 0.700 Portfolio Standard Deviation 5.00% 4.50% 4.00% Portfolio Exp. 3.50% 3.00% 2.50% 2.00% 1.50% 1.00% 0.50% 0.00% Efficient Frontier 2 (A&C) Ret. 0.000 0.100 0.200 0.300 0.400 0.500 0.600 0.700 Portfolio Standard Deviation 5.00% 4.50% 4.00% 3.50% 3.00% 2.50% 2.00% 1.50% 1.00% 0.50% 0.00% EF Set 3 (B&C) Portfolio Exp. Ret. 0.000 0.100 0.200 0.300 0.400 0.500 0.600 0.700 Portfolio Standard Deviation Efficient Frontier 3.00% 2.50% Portfolio Expected Return 2.00% 1.50% 1.00% 0.50% 1.37% 1.00% 0.50% 0.00% 0.080 0.100 0.120 0.140 0.160 Portfolio Standard Deviation 0.180 160 on 0.180 0.200 0.220 0.240 Portfolio s.d. RPortfolio 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 0.154 0.151 0.149 0.151 0.154 0.160 0.168 0.177 0.188 0.200 0.214 0.1032 0.098 0.0977 0.1026 0.1118 0.1245 1.31% 1.45% 1.59% 1.72% 1.86% 1.99% 2.13% 2.26% 2.40% 2.53% 2.67% 1.05% 1.21% 1.37% 1.53% 1.70% 1.86% X ORCL CSCO C Y 0.214 0.0267 0.154 0.0131 0.103 0.015 Mini Risk Minimum Variance 0.09774 Portfolio 1.37% Assets ORCL CSCO C / 0.0267 0.2135 0.1249 0.0131 0.1545 0.0851 0.0105 0.1032 0.1016 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 0.1396 0.1565 0.1747 0.1938 0.2135 0.1032 0.1005 0.0999 0.1013 0.1047 0.1099 0.1166 0.1247 0.1338 0.1438 2.02% 2.18% 2.34% 2.51% 2.67% 1.05% 1.08% 1.10% 1.13% 1.15% 1.18% 1.21% 1.23% 1.26% 1.29%