Need to know average return and standard deviation of each of the three sets of data.
AutoSave OFF OFFE HE S Home Insert. Draw Page Layout - Cambria 10 [A BI U V Review View Tell me Wran Text v # & A Merge & Center 110 X fx A B D E F G H 1 Part 2 Assume that you recently graduated and landed a job as a financial planner with Cicero Services, an investment advisory company, Your first client recently inherited some assets and has asked you to evaluate them. The client owns a stock portfolio invested in The Walt Disney Company (DIS) and Automatic Data Processing, Inc. (ADP). You have observed monthly stock prices for S&P 500 (^GSPC), The Walt Disney Company (DIS), and Automatic Data Processing, Inc. (ADP) over the five-year period from December of 2015 to December of 2020. 4 Calculate average monthly return (annualized) and the standard deviation of returns for S&P 500 (^GSPC), The Walt Disney 5 Company (DIS), and Automatic Data Processing, Inc. (ADP). Note: *For 60 monthly returns (five years' worth of monthly returns) you will use 61 monthly prices. "Remember to annualize the monthly rate of return (simply multiply the calculated return by 12) *Assume that the risk-free rate is 3% and the market risk premium is 6%. 6 7 8 Ticker 9 GSPO S&P 500 10 DIS The Walt Disney Company Automatic Data Processing, Inc. 11 ADP 12 13 lats 14 Referate free rate. The Marketrisk premium 6.00% 15 RPM of The Walt Disney 50% 16 Company in Portfolio of Automatic Data Processing, Inc. in 50% Portfolio 17 18 19 DIS ADP 20 21 Observation Month-Yea 22 *GSPC utomatic Data Processing, In S&P 500 Price Returns Frice 73.551994 2043939941 72.582512 -15.8171% 1,940239990 73.980194 23.1077% 1,932229980 78.365372 71.1300% 2,059.739990 23 1 24 The Walt Disney Company Price Metars 35 Dec-2015 90.505562 Jan-2016 90.100734 -$8.73345 Feb-2016 90.117691 3.7572% Mar-2016 93.693344 476131% 4 Apr-2016 97,419945 47.2293% 77.737923 93.608437 16.9494% 77210518 92.287621 -16.9320 80.752831 90.523369 -22.9403% 78.666153 2 25 3 26 27 5 May 2016 28 6 Jun 2016 Jul-2016 9.6081% 2,065.300049 -8.1413% 2,096.949931 55.0544% 2,098.860107 -31.0084% 2,173600098 11.6015% 2,170949951 -21.5118% 2,168270020 29 7 30 31 9 8 Aug-2016 09.765007 -100530% 79426689 Sep-2016 B8.201545 -2032595 78.002846 Oct-2016 32 38.082993 -2.1969% 77451477 8.4823% 2,126.149902 10 11 Nov-2016 33 122 0 2108810050 34 94.193308 832452% Dec-2016 99,039917 61.7434% Jan-2017 105.960678 83.8542% 35 36 12 13 14 15 Mar-2017 108.584564 35.9706% Start Here Feb-2017 105.424416 -6.0731% 85422585 91436501 84.40235 2.238830078 90.380676 -13.8565% 2,278870117 91839462 19.3686% 2,363.639893 91633614 -2.6897% 2 362.719971 + 37 Project 1 Grading Select destination and press ENTER or choose Paste G Poste Formulas Data A A Excel Project1_SU22 General $ % Y J V Cell Conditional Format Formatting as Table Styles L M N Rens 60.8824% The values in D23, F22, and 822 are correct. You can use them to check your formulas -4.9540% 79.1893 3.2393% 18.3895% 1.0931% 42.7318 -1.4631% -1.4813% -23.3108% 41.0094% 21.0409% 21.4612% 44.6378% -0.467041 507 polnes possibie 9 K 0 Insert Delete Format P EAP Y +4 Sort & Filter Q Share 0. P Find & Select Q Comments Sensitivity T d) Analyze Data S # + R 100% T U 41 13 15 K11 13 ****. 14 15 AutoSave OFF OFFE HE S Home Insert. Draw Page Layout Cambria 10 CA BIU Y + X fxx K C NIMAL EXP da 40 23 78 10 31 33 14 V ak 31 12 24 40 44 40 AT 45 W ** 63 M M ce 1.0 29 M 20 T LI 71 M 75 TE TY G 51 28 35 Paste 8 Sarthe Mater (The Semes Fr P 21 hide EDULELIS MA W211 1 2014 CEL 1 M-201A 1 2014 a 2015 1 BERRIA 21 11 NE 14 32 33 11 34 11 MOR 3017 281 14 15 MESELT -3015 May 2017 CELT ST PL 34 ac Mus ww Wo 4105 VIEW IS CIRCH 152017 1985-0 76381A Ma-281 May 201 TRASA NANCY SONDID KUAL 2016 2017 1713401 CELT LANG L KIEDEN . The Malay kamakta Pramod ng ba ION MILES co PETO ALVI 11 KRO HAMM 13 33 34 38 AAXHI JONGE YO ALLKON 1st want ATED XUCHH LALA T 1XXXX V 18:27 vitch WAKI MEDION ANALI ONSIT Im Swirl VICH TRWAL ADIDA M-220 4-2825 33 M2120 54 2813 114 2014 130 2224 22 VECH THE MADRI MOVIE vite day WARISACE FROM THE CECHU GOEDIN 13.00 VICAL VIROME WADLANI 2785- LANAL -IVYOW 34 M-281-9 m 45 Ap-2017 M19 43 43 POD +4 NACHLY LAMSI HALL 45 14 The 201 NICELY 48 44 TAKW savet. howw www.1 www. AT TOTAL www. TWATOT www ACT NOUS TURYS IA20 INTRAL MAVICH -KAMERL ROGE BRIZGAL MIL.14. -KIPE KAPTEE AMAA ANALYS MEIN THIWA UDIN BLATIV ASI-I VICT ww verer Hora. VIDRIC. Formulas Data A A A son *200.000 K ATI A Sa S18-2020 LOLARG -ZAM 12 12 www 24 18.30 MATA - WARNA Mina Shikkee nkchrm z kerakmasing Start Here Project 1 Select destination and press ENTER or choose Paste 1130 www DIAL www 13/03/ Jaises POPL AMER 2004% WITAL 0 (0%ma Nmek Maka Tersanding, ka. (EP) Grading + w LIBER 1000% SAL AUN KUTOT WAKA NIK DENNICHE: SAE TEST MACHAL www CO 242 CINTAL KLAU INTUAL LE PL Www SINTAL Review = = 1 View == mer, TAKIE KA PITA 1 H Tell me 3 Excel Project1 SU22 General S Wraa Text v Merge & Center 1: A % 6 > +00 A Conditional Format Cell Formatting as Table Styles Insert Delete Format M EM E-AD Y Sort & Filter # Find & Select Share KD P Analyze Data --- T 244 Q Comments Sensitivity + 60% T AL AutoSave OFF OFFE HE S Home Insert. Draw Page Layout - Cambria 10 [A BI U V Review View Tell me Wran Text v # & A Merge & Center 110 X fx A B D E F G H 1 Part 2 Assume that you recently graduated and landed a job as a financial planner with Cicero Services, an investment advisory company, Your first client recently inherited some assets and has asked you to evaluate them. The client owns a stock portfolio invested in The Walt Disney Company (DIS) and Automatic Data Processing, Inc. (ADP). You have observed monthly stock prices for S&P 500 (^GSPC), The Walt Disney Company (DIS), and Automatic Data Processing, Inc. (ADP) over the five-year period from December of 2015 to December of 2020. 4 Calculate average monthly return (annualized) and the standard deviation of returns for S&P 500 (^GSPC), The Walt Disney 5 Company (DIS), and Automatic Data Processing, Inc. (ADP). Note: *For 60 monthly returns (five years' worth of monthly returns) you will use 61 monthly prices. "Remember to annualize the monthly rate of return (simply multiply the calculated return by 12) *Assume that the risk-free rate is 3% and the market risk premium is 6%. 6 7 8 Ticker 9 GSPO S&P 500 10 DIS The Walt Disney Company Automatic Data Processing, Inc. 11 ADP 12 13 lats 14 Referate free rate. The Marketrisk premium 6.00% 15 RPM of The Walt Disney 50% 16 Company in Portfolio of Automatic Data Processing, Inc. in 50% Portfolio 17 18 19 DIS ADP 20 21 Observation Month-Yea 22 *GSPC utomatic Data Processing, In S&P 500 Price Returns Frice 73.551994 2043939941 72.582512 -15.8171% 1,940239990 73.980194 23.1077% 1,932229980 78.365372 71.1300% 2,059.739990 23 1 24 The Walt Disney Company Price Metars 35 Dec-2015 90.505562 Jan-2016 90.100734 -$8.73345 Feb-2016 90.117691 3.7572% Mar-2016 93.693344 476131% 4 Apr-2016 97,419945 47.2293% 77.737923 93.608437 16.9494% 77210518 92.287621 -16.9320 80.752831 90.523369 -22.9403% 78.666153 2 25 3 26 27 5 May 2016 28 6 Jun 2016 Jul-2016 9.6081% 2,065.300049 -8.1413% 2,096.949931 55.0544% 2,098.860107 -31.0084% 2,173600098 11.6015% 2,170949951 -21.5118% 2,168270020 29 7 30 31 9 8 Aug-2016 09.765007 -100530% 79426689 Sep-2016 B8.201545 -2032595 78.002846 Oct-2016 32 38.082993 -2.1969% 77451477 8.4823% 2,126.149902 10 11 Nov-2016 33 122 0 2108810050 34 94.193308 832452% Dec-2016 99,039917 61.7434% Jan-2017 105.960678 83.8542% 35 36 12 13 14 15 Mar-2017 108.584564 35.9706% Start Here Feb-2017 105.424416 -6.0731% 85422585 91436501 84.40235 2.238830078 90.380676 -13.8565% 2,278870117 91839462 19.3686% 2,363.639893 91633614 -2.6897% 2 362.719971 + 37 Project 1 Grading Select destination and press ENTER or choose Paste G Poste Formulas Data A A Excel Project1_SU22 General $ % Y J V Cell Conditional Format Formatting as Table Styles L M N Rens 60.8824% The values in D23, F22, and 822 are correct. You can use them to check your formulas -4.9540% 79.1893 3.2393% 18.3895% 1.0931% 42.7318 -1.4631% -1.4813% -23.3108% 41.0094% 21.0409% 21.4612% 44.6378% -0.467041 507 polnes possibie 9 K 0 Insert Delete Format P EAP Y +4 Sort & Filter Q Share 0. P Find & Select Q Comments Sensitivity T d) Analyze Data S # + R 100% T U 41 13 15 K11 13 ****. 14 15 AutoSave OFF OFFE HE S Home Insert. Draw Page Layout Cambria 10 CA BIU Y + X fxx K C NIMAL EXP da 40 23 78 10 31 33 14 V ak 31 12 24 40 44 40 AT 45 W ** 63 M M ce 1.0 29 M 20 T LI 71 M 75 TE TY G 51 28 35 Paste 8 Sarthe Mater (The Semes Fr P 21 hide EDULELIS MA W211 1 2014 CEL 1 M-201A 1 2014 a 2015 1 BERRIA 21 11 NE 14 32 33 11 34 11 MOR 3017 281 14 15 MESELT -3015 May 2017 CELT ST PL 34 ac Mus ww Wo 4105 VIEW IS CIRCH 152017 1985-0 76381A Ma-281 May 201 TRASA NANCY SONDID KUAL 2016 2017 1713401 CELT LANG L KIEDEN . The Malay kamakta Pramod ng ba ION MILES co PETO ALVI 11 KRO HAMM 13 33 34 38 AAXHI JONGE YO ALLKON 1st want ATED XUCHH LALA T 1XXXX V 18:27 vitch WAKI MEDION ANALI ONSIT Im Swirl VICH TRWAL ADIDA M-220 4-2825 33 M2120 54 2813 114 2014 130 2224 22 VECH THE MADRI MOVIE vite day WARISACE FROM THE CECHU GOEDIN 13.00 VICAL VIROME WADLANI 2785- LANAL -IVYOW 34 M-281-9 m 45 Ap-2017 M19 43 43 POD +4 NACHLY LAMSI HALL 45 14 The 201 NICELY 48 44 TAKW savet. howw www.1 www. AT TOTAL www. TWATOT www ACT NOUS TURYS IA20 INTRAL MAVICH -KAMERL ROGE BRIZGAL MIL.14. -KIPE KAPTEE AMAA ANALYS MEIN THIWA UDIN BLATIV ASI-I VICT ww verer Hora. VIDRIC. Formulas Data A A A son *200.000 K ATI A Sa S18-2020 LOLARG -ZAM 12 12 www 24 18.30 MATA - WARNA Mina Shikkee nkchrm z kerakmasing Start Here Project 1 Select destination and press ENTER or choose Paste 1130 www DIAL www 13/03/ Jaises POPL AMER 2004% WITAL 0 (0%ma Nmek Maka Tersanding, ka. (EP) Grading + w LIBER 1000% SAL AUN KUTOT WAKA NIK DENNICHE: SAE TEST MACHAL www CO 242 CINTAL KLAU INTUAL LE PL Www SINTAL Review = = 1 View == mer, TAKIE KA PITA 1 H Tell me 3 Excel Project1 SU22 General S Wraa Text v Merge & Center 1: A % 6 > +00 A Conditional Format Cell Formatting as Table Styles Insert Delete Format M EM E-AD Y Sort & Filter # Find & Select Share KD P Analyze Data --- T 244 Q Comments Sensitivity + 60% T AL
