Question

1 Approved Answer

Posted on Sep 07, 2024

1. You are given historical, monthly stock prices for 10 companies in the sheet labeled Question / Data. Use this data to make the following

1. You are given historical, monthly stock prices for 10 companies in the sheet labeled "Question / Data". Use this data to make the following calculations in the sheet labeled "Question / Calculations" (a) Calculate the monthly returns for each company. You need only do this for one cell, and you can copy this calculation to all other cells. (b) Using the monthly returns, calculate the mean return and standard deviation of returns for each company. (c) Based on your preferences for risk, construct a portfolio based on these 10 stocks. You can take long (positive weights) or short positions (negative weights), but the sum of the weights must sum to 1. (Note that the summary statistics for these choices are graphed in the sheet labeled "Question 1 Graph".) (d) Briefly explain your philosophy for the choices that made in part (d). Historical Stock Price Data for 10 Companies (prices are adjusted for splits and dividends) and Jan Miros JP Morgan Chave 797 9.74 90 46 14 13.61 16.24 1594 153 13.69 17.29 177 May Ger Motion 3.38 321 293 412 501 631 439 1.15 3.13 3321 32.73 3431 37.53 101 2165 23.19 282 22.32 9.13 10.03 31 3.4 57 6.09 6. IT 7 7.34 743 729 7.58 16 85 75 7. 6 9.39 Am-6 Sep O. 96 24.11 216 14 37.13 38.06 1873 0 498 42.41 44.66 5.95 Procter and Care 1951 30.41 30.79 12.75 1412 33.9 33.7 DI 3921 H.09 43.52 47.97 50.22 47.54 53.49 5959 61.24 67.18 1144 279 2 971 DO 11.78 2.44 4.23 402 4.17 4.72 5.69 6.35 606 6.21 798 8.94 DOS 21 10.59 10.45 10.4 12.36 13.98 1552 17.00 1497 17.06 15.92 19 19.13 20.51 20.99 17.75 18.5 17.76 19.95 IN 2 222 22.27 1996 24.09 33.74 25.99 2081 96 10.4 10.94 1431 63 17.26 154 1761 16. 2011 10 57 10.38 11 10.33 102 984 9.69 10.14 10.64 2015 32.00 361 48.49 611 M Ape May91 Jun-97 Jal-97 Aug-97 Sep-92 O.97 Now De99 1266 13.08 16.23 BOR 1023 12.67 12.11 11.3 15.11 15.42 15.72 17.39 16.44 16.45 16.17 1761 4457 4813 4155 54.11 50.11 50.05 12.78 14.16 15.45 37.17 22.65 25.65 27.12 3.77 21.81 23.16 23.47 2617 24.14 8.13 10.43 10.9 27 10.49 10.99 9.81 1423 15.74 18.78 16.78 15.25 21.38 192 28.67 2745 31.87 2788 32.21 215 1927 162 17.45 17.57 19.1 1498 12:31 11.79 12.49 13.94 13.67 12.56 14.67 13.01 12.17 12.99 11 21.11 23.71 Feb-98 Mar 98 Apr.98 May. as-98 M. Aug-98 Sup98 37.74 35.57 35.82 35.05 40,67 44. 45.51 4453 12.09 31.12 30.15 2018 31.65 33.26 21.52 9937 67 56 7118 99 76.49 75.99 74.07 75.35 82.93 71.53 489 44.76 43.47 50 15 48 05 46.0L 43.7 40.72 35.05 27.01 30.71 3085 22.45 21.12 27.01 25.17 13.72 21.91 27:43 2639 2164 22.91 27.41 NO Doc. 29.71 33 28.07 37.19 4161 81.14 3337 22.77 2527 21.94 22.1 24.74 25.7 2993 30.88 29.36 11.15 314 2017 1394 3296 3565 15.78 37.99 37.71 3074 11.05 37.12 29 22 31.25 24.45 25.4 2011 32.24 1989 83.75 15.74 1482 14.9 17.11 16.95 17.32 27.71 24.42 27.33 29.46 1.39 43.67 3745 599 19 06 42.15 44.15 31.36 33.72 37.08 41.37 38.18 41.29 66 IP Mar99 Apr-99 May Ju-99 Aug.99 Sep-99 Oc1-99 Now Doc.99 4057 40.26 45.01 42.82 46.2 9 36.39 30.72 37.26 38.95 40.67 41.21 1684 42.13 14.05 13.94 419 5432 47.57 56.99 509 55.11 1957 57.49 50 R2 51 021 27.57 30.24 4214 39.77 20:35 4897 R12 589 ILS 18 12.13 49.73 48.75 53.81 53.62 0.4 18.06 32.1 33.45 4801 43.11 46.68 4334 99.02 56.09 51.32 57.36 5409 61.49 65.83 64.73 72.56 09.06 75.81 86.67 64.13 51.56 50.44 64 59 56.12 44 45.44 Feb-20 Mar-00 Apr-00 May.00 32.1 49.71 52.06 68.02 559 56.77 4287 3927 3232 3165 37.77 41.59 47.78 4337 43.13 4.13 4151 49.67 50.21 50.0 303 30.29 87.08 98.53 10321 9501 8185 50.72 54.04 60.79 51.04 51.65 52.4 5745 41.4 446 53.04 34.79 312 19.92 34.82 34.82 48.77 52.94 55.81 4831 00 34.65 35.52 374 36.91 39.66 46.66 51.63 5106 45.35 08 55.2 45.51 61.65 3149 23.54 2691 2654 3185 35.24 14.16 34.45 39.75 Sep-60 OC-60 Nov.co Dec-00 Jan-01 Feb 01 Mar 01 A-01 May.cl Jun 01 Jul-01 56.56 55.88 52.88 47.63 46.17 +421 44.73 40.25 46.92 47.39 47.99 42.05 34.35 28.61 2161 30.45 55.71 58.04 59.77 59.1 54,04 $4.04 46.25 43.05 40.48 36.2 511 4745 5233 4239 38.62 45.12 47.63 658 67.19 64.13 5663 30.01 44.04 46.47 4153 46.04 46.53 4832 5431 45.98 4422 413 73.44 67.18 65.85 57.94 55.42 59.95 59.49 67.09 27.26 13.79 34.51 36.42 33.01 38.65 37.88 34.01 59.94 61.36 41.38 18.06 3967 48.47 46.57 48.76 46.75 52.25 SY65 59.1 4262 57 E EFIE SE 3279 LERE BR TO 956 1901 PE EM TE PE SO SE 256 Fodes co- LOI ITSE 51.55 985 VOS LITT ILIT IP SELE SE SITE WIS 2144 26 CITE VOTE SEST $S6 ELS 916 19 32 25.51 3503 cally Sot ITE ICSE ELS LSIS DE LETE ERE 22.78 19.08 202 Meyes Mares Fobos Jan- D.2 33. 8819 31 1471 27.32 2274 CE LLOC DE 6 TE FEE SESE LISE 29 LE DES 28 91 SEV ts ME Nov.02 I'S WIZ SEX OF CODE THE SEIC 69 TOPO 29.07 POK NOTE 67.17 370 2561 2585 6 CHES TE to-ley ELSE 352 34 19 669 LVP ST IT 5 CF 8711 81.21 TE SP TE PE CICE 36.45 sos 5965 09 ILOE J. May.cz SESE SC PCI $5.79 87.47 40.50 10.63 36.47 3757 Apo 4502 6 596 57.45 16.14 3667 34 67 COPA 1996 4764 ME LE SE 27 PE LORE LE LE DE LYSE POLE WE ETTE PER 218 ISR NE FL 66 1 SE ELE IS MIC SO SE SESE IESE TETE 188 DES 56.77 38 YT 37.21 3512 33.91 SHE SELE 32.00 24 IVOS St LE LE ESP ZELE 686 F 20.24 68.86 70.23 53.74 SI 10 AN DINO 1o-des SEHE ITO 1. You are given historical, monthly stock prices for 10 companies in the sheet labeled "Question / Data". Use this data to make the following calculations in the sheet labeled "Question / Calculations" (a) Calculate the monthly returns for each company. You need only do this for one cell, and you can copy this calculation to all other cells. (b) Using the monthly returns, calculate the mean return and standard deviation of returns for each company. (c) Based on your preferences for risk, construct a portfolio based on these 10 stocks. You can take long (positive weights) or short positions (negative weights), but the sum of the weights must sum to 1. (Note that the summary statistics for these choices are graphed in the sheet labeled "Question 1 Graph".) (d) Briefly explain your philosophy for the choices that made in part (d). Historical Stock Price Data for 10 Companies (prices are adjusted for splits and dividends) and Jan Miros JP Morgan Chave 797 9.74 90 46 14 13.61 16.24 1594 153 13.69 17.29 177 May Ger Motion 3.38 321 293 412 501 631 439 1.15 3.13 3321 32.73 3431 37.53 101 2165 23.19 282 22.32 9.13 10.03 31 3.4 57 6.09 6. IT 7 7.34 743 729 7.58 16 85 75 7. 6 9.39 Am-6 Sep O. 96 24.11 216 14 37.13 38.06 1873 0 498 42.41 44.66 5.95 Procter and Care 1951 30.41 30.79 12.75 1412 33.9 33.7 DI 3921 H.09 43.52 47.97 50.22 47.54 53.49 5959 61.24 67.18 1144 279 2 971 DO 11.78 2.44 4.23 402 4.17 4.72 5.69 6.35 606 6.21 798 8.94 DOS 21 10.59 10.45 10.4 12.36 13.98 1552 17.00 1497 17.06 15.92 19 19.13 20.51 20.99 17.75 18.5 17.76 19.95 IN 2 222 22.27 1996 24.09 33.74 25.99 2081 96 10.4 10.94 1431 63 17.26 154 1761 16. 2011 10 57 10.38 11 10.33 102 984 9.69 10.14 10.64 2015 32.00 361 48.49 611 M Ape May91 Jun-97 Jal-97 Aug-97 Sep-92 O.97 Now De99 1266 13.08 16.23 BOR 1023 12.67 12.11 11.3 15.11 15.42 15.72 17.39 16.44 16.45 16.17 1761 4457 4813 4155 54.11 50.11 50.05 12.78 14.16 15.45 37.17 22.65 25.65 27.12 3.77 21.81 23.16 23.47 2617 24.14 8.13 10.43 10.9 27 10.49 10.99 9.81 1423 15.74 18.78 16.78 15.25 21.38 192 28.67 2745 31.87 2788 32.21 215 1927 162 17.45 17.57 19.1 1498 12:31 11.79 12.49 13.94 13.67 12.56 14.67 13.01 12.17 12.99 11 21.11 23.71 Feb-98 Mar 98 Apr.98 May. as-98 M. Aug-98 Sup98 37.74 35.57 35.82 35.05 40,67 44. 45.51 4453 12.09 31.12 30.15 2018 31.65 33.26 21.52 9937 67 56 7118 99 76.49 75.99 74.07 75.35 82.93 71.53 489 44.76 43.47 50 15 48 05 46.0L 43.7 40.72 35.05 27.01 30.71 3085 22.45 21.12 27.01 25.17 13.72 21.91 27:43 2639 2164 22.91 27.41 NO Doc. 29.71 33 28.07 37.19 4161 81.14 3337 22.77 2527 21.94 22.1 24.74 25.7 2993 30.88 29.36 11.15 314 2017 1394 3296 3565 15.78 37.99 37.71 3074 11.05 37.12 29 22 31.25 24.45 25.4 2011 32.24 1989 83.75 15.74 1482 14.9 17.11 16.95 17.32 27.71 24.42 27.33 29.46 1.39 43.67 3745 599 19 06 42.15 44.15 31.36 33.72 37.08 41.37 38.18 41.29 66 IP Mar99 Apr-99 May Ju-99 Aug.99 Sep-99 Oc1-99 Now Doc.99 4057 40.26 45.01 42.82 46.2 9 36.39 30.72 37.26 38.95 40.67 41.21 1684 42.13 14.05 13.94 419 5432 47.57 56.99 509 55.11 1957 57.49 50 R2 51 021 27.57 30.24 4214 39.77 20:35 4897 R12 589 ILS 18 12.13 49.73 48.75 53.81 53.62 0.4 18.06 32.1 33.45 4801 43.11 46.68 4334 99.02 56.09 51.32 57.36 5409 61.49 65.83 64.73 72.56 09.06 75.81 86.67 64.13 51.56 50.44 64 59 56.12 44 45.44 Feb-20 Mar-00 Apr-00 May.00 32.1 49.71 52.06 68.02 559 56.77 4287 3927 3232 3165 37.77 41.59 47.78 4337 43.13 4.13 4151 49.67 50.21 50.0 303 30.29 87.08 98.53 10321 9501 8185 50.72 54.04 60.79 51.04 51.65 52.4 5745 41.4 446 53.04 34.79 312 19.92 34.82 34.82 48.77 52.94 55.81 4831 00 34.65 35.52 374 36.91 39.66 46.66 51.63 5106 45.35 08 55.2 45.51 61.65 3149 23.54 2691 2654 3185 35.24 14.16 34.45 39.75 Sep-60 OC-60 Nov.co Dec-00 Jan-01 Feb 01 Mar 01 A-01 May.cl Jun 01 Jul-01 56.56 55.88 52.88 47.63 46.17 +421 44.73 40.25 46.92 47.39 47.99 42.05 34.35 28.61 2161 30.45 55.71 58.04 59.77 59.1 54,04 $4.04 46.25 43.05 40.48 36.2 511 4745 5233 4239 38.62 45.12 47.63 658 67.19 64.13 5663 30.01 44.04 46.47 4153 46.04 46.53 4832 5431 45.98 4422 413 73.44 67.18 65.85 57.94 55.42 59.95 59.49 67.09 27.26 13.79 34.51 36.42 33.01 38.65 37.88 34.01 59.94 61.36 41.38 18.06 3967 48.47 46.57 48.76 46.75 52.25 SY65 59.1 4262 57 E EFIE SE 3279 LERE BR TO 956 1901 PE EM TE PE SO SE 256 Fodes co- LOI ITSE 51.55 985 VOS LITT ILIT IP SELE SE SITE WIS 2144 26 CITE VOTE SEST $S6 ELS 916 19 32 25.51 3503 cally Sot ITE ICSE ELS LSIS DE LETE ERE 22.78 19.08 202 Meyes Mares Fobos Jan- D.2 33. 8819 31 1471 27.32 2274 CE LLOC DE 6 TE FEE SESE LISE 29 LE DES 28 91 SEV ts ME Nov.02 I'S WIZ SEX OF CODE THE SEIC 69 TOPO 29.07 POK NOTE 67.17 370 2561 2585 6 CHES TE to-ley ELSE 352 34 19 669 LVP ST IT 5 CF 8711 81.21 TE SP TE PE CICE 36.45 sos 5965 09 ILOE J. May.cz SESE SC PCI $5.79 87.47 40.50 10.63 36.47 3757 Apo 4502 6 596 57.45 16.14 3667 34 67 COPA 1996 4764 ME LE SE 27 PE LORE LE LE DE LYSE POLE WE ETTE PER 218 ISR NE FL 66 1 SE ELE IS MIC SO SE SESE IESE TETE 188 DES 56.77 38 YT 37.21 3512 33.91 SHE SELE 32.00 24 IVOS St LE LE ESP ZELE 686 F 20.24 68.86 70.23 53.74 SI 10 AN DINO 1o-des SEHE ITO