Today is April 30, 2015, and you have just started your new job with a financial planning firm In addition to studying for all your license exams, you have been asked to review a portion of a clients stock portfolio to determine the risk return profiles of 12 stocks in the portfolio Unfortunately, your small firm cannot afford the expensive databases that would provide all this information with a few simple keystrokes, but thats why they hired you Specifically, you have been asked to determine the monthly average returns and standard deviations for the 12 stocks for the past five years In the following chapters, you will be asked to do more extensive analyses on these same stocks The stocks (with their symbols in parentheses) are Archer Daniels Midland (ADM) Boeing (BA) Caterpillar (CAT) Deere Co (DE) General Mills, Inc (GIS) eBay (EBAY) Hershey (HSY) International Business Machines Corporation (IBM) JPMorgan Chase Co ( JPM) Microsoft (MSFT) Procter and Gamble (PG) Walmart (WMT) THIS IS THE SPREADSHEET RESULTS FOR THE STOCKS Date ADM BA CAT DE GIS EBAY HSY IBM JPM MSFT PG WMT 4 1 2010 null null null null null null null null null null null null 5 1 2010 20 58263 51 91504 47 78618 47 88065 27 38756 9 010942 38 48621 98 84789 32 34098 20 87326 47 37018 40 93182 6 1 2010 21 14736 51 0529 47 24351 46 22043 27 31451 8 253367 39 68783 97 94269 29 91419 18 70003 46 50947 39 14169 7 1 2010 22 40867 55 43816 54 85659 55 631 26 29944 8 800505 38 91775 101 8451 32 91295 20 97556 47 42447 41 68219 8 1 2010 25 23433 49 73487 51 58796 52 78603 28 02198 9 776936 38 4789 97 66507 29 75054 19 07387 46 63105 40 82722 9 1 2010 26 27465 54 46536 62 2919 58 21733 28 31646 10 26936 39 67223 106 9252 31 14151 20 00899 46 86551 43 83334 10 1 2010 27 42705 57 82135 62 22856 64 34181 29 1069 12 56734 41 25612 114 466 30 78968 21 7901 49 67884 44 3657 11 1 2010 23 86285 52 19802 67 34776 62 58245 27 5863 12 26431 39 01365 112 7602 30 64096 20 63809 48 09187 44 30019 12 1 2010 24 8837 53 74111 74 5602 69 57794 27 78931 11 71296 39 57766 117 5048 34 75373 22 94332 50 65908 44 16913 1 1 2011 27 02628 57 21623 77 22703 76 47496 27 15686 12 77778 39 19154 129 7068 36 81831 22 79536 49 7141 46 17537 2 1 2011 30 75719 59 29969 82 3251 75 84396 29 22302 14 10354 43 91736 129 6107 38 29599 21 85001 50 01904 42 80713 3 1 2011 29 91992 61 23447 89 05956 81 5144 28 75878 13 06397 45 92949 131 0804 37 81205 20 99513 48 86871 42 86478 4 1 2011 30 7591 66 0799 92 30682 82 33646 30 35605 14 47391 48 76894 137 1172 37 42656 21 43339 51 48668 45 59507 5 1 2011 26 92876 64 63043 84 97298 72 69255 31 53451 13 11869 47 0957 135 7908 35 65858 20 68091 53 59345 45 7941 6 1 2011 25 17682 61 55785 85 50302 69 62711 29 51256 13 58165 48 33925 138 5136 33 76183 21 64047 50 84979 44 36074 7 1 2011 25 36889 58 6769 79 34291 66 63848 29 61563 13 78367 47 99062 146 83 33 35775 22 80573 49 186 44 00178 8 1 2011 23 78228 55 67104 73 3951 68 59915 30 30749 12 99242 49 86977 138 8042 31 16477 22 13986 51 35507 44 40248 9 1 2011 20 83548 50 72545 59 5549 54 80633 30 77118 12 41162 50 6823 141 8091 24 99156 20 84734 50 95185 43 63902 10 1 2011 24 30386 55 15167 76 18573 64 80553 30 80315 13 39646 48 96265 149 7238 28 84152 22 30473 51 60509 47 69183 11 1 2011 25 29483 57 58273 79 38111 67 66585 32 19655 12 4537 49 34764 152 4567 25 92304 21 42527 52 49735 49 52483 12 1 2011 24 1603 61 88128 73 47699 66 04359 32 56726 12 76515 53 18144 149 7151 27 83147 21 90728 54 23724 50 24794 1 1 2012 24 18565 62 58147 88 4968 73 94377 32 09983 13 29966 52 57886 156 8149 31 22148 24 91995 51 25343 51 91565 2 1 2012 26 3567 63 23108 93 03883 71 17999 31 10783 15 04209 52 25176 160 1775 33 08176 26 78495 55 41619 49 98658 3 1 2012 26 89747 63 11105 86 77432 69 43761 32 03351 15 5303 53 12677 170 5443 38 764 27 40299 55 08017 51 78027 4 1 2012 26 19233 65 17319 83 71945 71 08759 31 57879 17 26431 58 04704 169 2611 36 23482 27 19912 52 15447 50 17594 5 1 2012 27 08438 59 07166 71 67973 63 75962 31 32418 16 49411 57 91709 157 6707 28 13046 24 7952 51 47993 56 06144 6 1 2012 25 21289 63 42175 69 46278 69 80153 31 53694 17 68098 62 74672 160 531 30 31979 26 15492 50 62042 59 7857 7 1 2012 22 28335 63 08887 68 89011 66 70963 31 66787 18 64478 62 49411 160 8593 30 54891 25 1973 53 33947 63 82455 8 1 2012 22 84704 60 94632 70 25236 65 22467 32 45775 19 97896 62 56381 159 9318 31 77908 26 35158 56 01125 62 25531 9 1 2012 23 37205 59 76615 70 8369 71 616 32 88689 20 35774 62 07918 171 0008 34 63697 25 61382 57 82023 63 62615 10 1 2012 23 07967 60 48748 69 82423 74 61551 33 07669 20 31145 60 29273 160 3509 35 66376 24 5638 57 72018 64 67796 11 1 2012 22 95929 63 78491 70 60204 73 4016 34 10683 22 23064 64 16336 156 6745 35 40997 22 91129 58 69005 62 09153 12 1 2012 23 71693 65 11009 74 2216 75 47135 33 63256 21 46465 63 61187 158 5855 37 90107 23 17765 57 05954 58 82401 1 1 2013 24 70405 63 82277 81 96326 82 58249 34 89731 23 5564 69 9801 168 123 40 55595 23 81979 63 16975 60 64111 2 1 2013 27 58749 66 44064 76 94835 77 11268 38 79129 23 02609 73 40644 166 2685 42 45753 24 12351 64 54614 61 36068 3 1 2013 29 38453 74 64978 72 44991 75 48841 41 3578 22 81987 77 49961 177 3358 41 19041 25 03193 65 29175 64 87172 4 1 2013 29 56747 79 48441 70 53391 78 86629 42 28878 22 04966 78 94282 168 39 42 53565 28 96039 65 04603 67 8118 5 1 2013 28 07777 86 10157 71 9344 76 93215 39 7566 22 76936 78 89857 172 946 47 67476 30 53528 65 51501 65 29896 6 1 2013 29 7074 89 53352 69 15932 71 75683 40 98106 21 76768 79 42623 159 6319 46 10277 30 43222 65 71128 65 38341 7 1 2013 31 95013 91 85838 69 51143 73 8208 43 91128 21 75505 84 39925 162 9146 48 67033 28 05332 68 53643 68 41164 8 1 2013 30 84628 90 82706 69 68361 74 32733 41 97349 21 03956 81 80154 152 248 44 45086 29 42779 66 97701 64 05801 9 1 2013 32 44278 103 1635 70 40967 72 32787 40 78201 23 48485 82 71201 155 4519 45 47131 29 52869 64 99927 65 31241 10 1 2013 36 01819 114 5774 70 37591 73 16865 42 90962 22 18434 88 73879 150 4403 45 33934 31 4186 69 4363 67 7762 11 1 2013 35 44576 117 8698 71 923 75 31434 43 26069 21 26263 86 63747 150 8348 50 70683 33 832 72 98585 71 53812 12 1 2013 38 39712 120 2732 77 20243 81 65311 42 81462 23 09344 87 38776 158 3038 51 82341 33 44489 70 55068 69 48938 1 1 2014 34 92898 110 3775 79 83792 77 28467 41 19331 22 39057 89 3381 149 113 49 05855 33 82931 66 39964 66 33213 2 1 2014 35 91987 113 6026 83 5228 77 2577 43 24722 24 73485 95 10824 156 2783 50 68169 34 2495 68 68276 66 34988 3 1 2014 38 61553 111 2038 85 58997 81 6362 44 79454 23 24916 94 2564 163 3467 54 15145 36 92024 70 37668 67 88651 4 1 2014 38 91811 114 332 90 78378 84 40717 45 83185 21 81397 86 88924 166 7242 49 93243 36 38882 72 07934 71 25663 5 1 2014 39 99498 119 8527 88 57184 82 44487 47 86137 21 35101 87 88235 156 4477 49 87914 36 8752 71 10584 68 62843 6 1 2014 39 46836 113 3784 94 15145 81 88421 45 77893 21 06902 88 34915 154 7211 51 7192 37 82472 69 16955 67 52419 7 1 2014 41 51738 107 3633 87 28956 77 47712 43 69648 22 23485 79 98335 163 5979 51 76408 39 14904 68 05178 66 18395 8 1 2014 44 61329 112 9952 95 09554 76 54861 46 87056 23 35859 82 95037 164 1356 53 73482 41 20807 73 73171 67 91096 9 1 2014 45 94427 114 1995 86 34191 74 63693 44 29787 23 83418 87 09557 162 9856 54 44888 42 3143 74 29064 69 23663 10 1 2014 42 25793 111 9851 88 41698 78 43468 45 62372 22 09596 87 53365 141 1517 54 6658 42 8528 77 4223 69 05551 11 1 2014 47 36486 120 4573 88 37891 79 42497 46 69895 23 09764 91 52198 139 2371 54 74292 43 63776 80 84065 79 2595 12 1 2014 46 97029 117 2104 80 41074 81 12128 47 21242 23 61953 95 38879 138 6946 56 94502 42 66384 81 43067 77 75649 1 1 2015 42 11971 131 0885 70 25508 78 6366 46 45992 22 3064 93 81014 132 5309 49 48339 37 10699 75 35175 77 37148 2 1 2015 43 2488 136 0301 73 42632 83 63042 47 99006 24 3729 95 2511 139 9913 56 12079 40 27576 76 64359 76 41549 3 1 2015 43 07226 136 1728 70 88429 80 94428 50 49707 24 27609 93 08137 139 7197 55 47972 37 61157 73 7716 74 8859 4 1 2015 44 41714 130 0573 76 95149 84 13013 49 37292 24 5202 84 78878 149 1127 57 93408 44 99327 71 58384 71 48877 2 Convert these prices to monthly returns as the percentage change in the monthly prices Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the return for the first month PLEASE DISPLAY IN WORKSHEET IN EXCEL 3 Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks Convert the monthly statistics to annual statistics for easier interpretation (multiply the mean monthly return by 12, and multiply the monthly standard deviation by 12 ) PLEASE DISPLAY IN WORKSHEET IN EXCEL 4 Add a column in your Excel worksheet with the average return across stocks for each month This is the monthly return to an equally weighted portfolio of these 12 stocks Compute the mean and standard deviation of monthly returns for the equally weighted portfolio Double check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation PLEASE DISPLAY IN WORKSHEET IN EXCEL 5 Using the annual statistics, create an Excel plot with standard deviation (volatility) on the x axis and average return on the y axis as follows a Create three columns on your spreadsheet with the statistics you created in Questions 3 and 4 for each of the individual stocks and the equally weighted portfolio The first column will have the ticker, the second will have annual standard deviation, and the third will have the annual mean return b Highlight the data in the last two columns (standard deviation and mean), choose Insert Chart XY Scatter Plot choose Insert Chart XY Scatter Plot Complete the chart wizard to finish the plot 6 What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio You will work on items 1 6 and submit is as an excel spreadsheet

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 03, 2024

Today is April 30, 2015, and you have just started your new job with a financial planning firm. In addition to studying for all your

Today is April 30, 2015, and you have just started your new job with a financial planning firm. In addition to studying for all your license exams, you have been asked to review a portion of a clients stock portfolio to determine the risk/return profiles of 12 stocks in the portfolio. Unfortunately, your small firm cannot afford the expensive databases that would provide all this information with a few simple keystrokes, but thats why they hired you. Specifically, you have been asked to determine the monthly average returns and standard deviations for the 12 stocks for the past five years. In the following chapters, you will be asked to do more extensive analyses on these same stocks.

The stocks (with their symbols in parentheses) are:

Archer Daniels Midland (ADM)

Boeing (BA)

Caterpillar (CAT)

Deere & Co. (DE)

General Mills, Inc. (GIS)

eBay (EBAY)

Hershey (HSY)

International Business Machines Corporation (IBM)

JPMorgan Chase & Co. (??JPM)

Microsoft (MSFT)

Procter and Gamble (PG)

Walmart (WMT)

THIS IS THE SPREADSHEET RESULTS FOR THE STOCKS

Date	ADM	BA	CAT	DE	GIS	EBAY	HSY	IBM	JPM	MSFT	PG	WMT
4/1/2010	null	null	null	null	null	null	null	null	null	null	null	null
5/1/2010	20.58263	51.91504	47.78618	47.88065	27.38756	9.010942	38.48621	98.84789	32.34098	20.87326	47.37018	40.93182
6/1/2010	21.14736	51.0529	47.24351	46.22043	27.31451	8.253367	39.68783	97.94269	29.91419	18.70003	46.50947	39.14169
7/1/2010	22.40867	55.43816	54.85659	55.631	26.29944	8.800505	38.91775	101.8451	32.91295	20.97556	47.42447	41.68219
8/1/2010	25.23433	49.73487	51.58796	52.78603	28.02198	9.776936	38.4789	97.66507	29.75054	19.07387	46.63105	40.82722
9/1/2010	26.27465	54.46536	62.2919	58.21733	28.31646	10.26936	39.67223	106.9252	31.14151	20.00899	46.86551	43.83334
10/1/2010	27.42705	57.82135	62.22856	64.34181	29.1069	12.56734	41.25612	114.466	30.78968	21.7901	49.67884	44.3657
11/1/2010	23.86285	52.19802	67.34776	62.58245	27.5863	12.26431	39.01365	112.7602	30.64096	20.63809	48.09187	44.30019
12/1/2010	24.8837	53.74111	74.5602	69.57794	27.78931	11.71296	39.57766	117.5048	34.75373	22.94332	50.65908	44.16913
1/1/2011	27.02628	57.21623	77.22703	76.47496	27.15686	12.77778	39.19154	129.7068	36.81831	22.79536	49.7141	46.17537
2/1/2011	30.75719	59.29969	82.3251	75.84396	29.22302	14.10354	43.91736	129.6107	38.29599	21.85001	50.01904	42.80713
3/1/2011	29.91992	61.23447	89.05956	81.5144	28.75878	13.06397	45.92949	131.0804	37.81205	20.99513	48.86871	42.86478
4/1/2011	30.7591	66.0799	92.30682	82.33646	30.35605	14.47391	48.76894	137.1172	37.42656	21.43339	51.48668	45.59507
5/1/2011	26.92876	64.63043	84.97298	72.69255	31.53451	13.11869	47.0957	135.7908	35.65858	20.68091	53.59345	45.7941
6/1/2011	25.17682	61.55785	85.50302	69.62711	29.51256	13.58165	48.33925	138.5136	33.76183	21.64047	50.84979	44.36074
7/1/2011	25.36889	58.6769	79.34291	66.63848	29.61563	13.78367	47.99062	146.83	33.35775	22.80573	49.186	44.00178
8/1/2011	23.78228	55.67104	73.3951	68.59915	30.30749	12.99242	49.86977	138.8042	31.16477	22.13986	51.35507	44.40248
9/1/2011	20.83548	50.72545	59.5549	54.80633	30.77118	12.41162	50.6823	141.8091	24.99156	20.84734	50.95185	43.63902
10/1/2011	24.30386	55.15167	76.18573	64.80553	30.80315	13.39646	48.96265	149.7238	28.84152	22.30473	51.60509	47.69183
11/1/2011	25.29483	57.58273	79.38111	67.66585	32.19655	12.4537	49.34764	152.4567	25.92304	21.42527	52.49735	49.52483
12/1/2011	24.1603	61.88128	73.47699	66.04359	32.56726	12.76515	53.18144	149.7151	27.83147	21.90728	54.23724	50.24794
1/1/2012	24.18565	62.58147	88.4968	73.94377	32.09983	13.29966	52.57886	156.8149	31.22148	24.91995	51.25343	51.91565
2/1/2012	26.3567	63.23108	93.03883	71.17999	31.10783	15.04209	52.25176	160.1775	33.08176	26.78495	55.41619	49.98658
3/1/2012	26.89747	63.11105	86.77432	69.43761	32.03351	15.5303	53.12677	170.5443	38.764	27.40299	55.08017	51.78027
4/1/2012	26.19233	65.17319	83.71945	71.08759	31.57879	17.26431	58.04704	169.2611	36.23482	27.19912	52.15447	50.17594
5/1/2012	27.08438	59.07166	71.67973	63.75962	31.32418	16.49411	57.91709	157.6707	28.13046	24.7952	51.47993	56.06144
6/1/2012	25.21289	63.42175	69.46278	69.80153	31.53694	17.68098	62.74672	160.531	30.31979	26.15492	50.62042	59.7857
7/1/2012	22.28335	63.08887	68.89011	66.70963	31.66787	18.64478	62.49411	160.8593	30.54891	25.1973	53.33947	63.82455
8/1/2012	22.84704	60.94632	70.25236	65.22467	32.45775	19.97896	62.56381	159.9318	31.77908	26.35158	56.01125	62.25531
9/1/2012	23.37205	59.76615	70.8369	71.616	32.88689	20.35774	62.07918	171.0008	34.63697	25.61382	57.82023	63.62615
10/1/2012	23.07967	60.48748	69.82423	74.61551	33.07669	20.31145	60.29273	160.3509	35.66376	24.5638	57.72018	64.67796
11/1/2012	22.95929	63.78491	70.60204	73.4016	34.10683	22.23064	64.16336	156.6745	35.40997	22.91129	58.69005	62.09153
12/1/2012	23.71693	65.11009	74.2216	75.47135	33.63256	21.46465	63.61187	158.5855	37.90107	23.17765	57.05954	58.82401
1/1/2013	24.70405	63.82277	81.96326	82.58249	34.89731	23.5564	69.9801	168.123	40.55595	23.81979	63.16975	60.64111
2/1/2013	27.58749	66.44064	76.94835	77.11268	38.79129	23.02609	73.40644	166.2685	42.45753	24.12351	64.54614	61.36068
3/1/2013	29.38453	74.64978	72.44991	75.48841	41.3578	22.81987	77.49961	177.3358	41.19041	25.03193	65.29175	64.87172
4/1/2013	29.56747	79.48441	70.53391	78.86629	42.28878	22.04966	78.94282	168.39	42.53565	28.96039	65.04603	67.8118
5/1/2013	28.07777	86.10157	71.9344	76.93215	39.7566	22.76936	78.89857	172.946	47.67476	30.53528	65.51501	65.29896
6/1/2013	29.7074	89.53352	69.15932	71.75683	40.98106	21.76768	79.42623	159.6319	46.10277	30.43222	65.71128	65.38341
7/1/2013	31.95013	91.85838	69.51143	73.8208	43.91128	21.75505	84.39925	162.9146	48.67033	28.05332	68.53643	68.41164
8/1/2013	30.84628	90.82706	69.68361	74.32733	41.97349	21.03956	81.80154	152.248	44.45086	29.42779	66.97701	64.05801
9/1/2013	32.44278	103.1635	70.40967	72.32787	40.78201	23.48485	82.71201	155.4519	45.47131	29.52869	64.99927	65.31241
10/1/2013	36.01819	114.5774	70.37591	73.16865	42.90962	22.18434	88.73879	150.4403	45.33934	31.4186	69.4363	67.7762
11/1/2013	35.44576	117.8698	71.923	75.31434	43.26069	21.26263	86.63747	150.8348	50.70683	33.832	72.98585	71.53812
12/1/2013	38.39712	120.2732	77.20243	81.65311	42.81462	23.09344	87.38776	158.3038	51.82341	33.44489	70.55068	69.48938
1/1/2014	34.92898	110.3775	79.83792	77.28467	41.19331	22.39057	89.3381	149.113	49.05855	33.82931	66.39964	66.33213
2/1/2014	35.91987	113.6026	83.5228	77.2577	43.24722	24.73485	95.10824	156.2783	50.68169	34.2495	68.68276	66.34988
3/1/2014	38.61553	111.2038	85.58997	81.6362	44.79454	23.24916	94.2564	163.3467	54.15145	36.92024	70.37668	67.88651
4/1/2014	38.91811	114.332	90.78378	84.40717	45.83185	21.81397	86.88924	166.7242	49.93243	36.38882	72.07934	71.25663
5/1/2014	39.99498	119.8527	88.57184	82.44487	47.86137	21.35101	87.88235	156.4477	49.87914	36.8752	71.10584	68.62843
6/1/2014	39.46836	113.3784	94.15145	81.88421	45.77893	21.06902	88.34915	154.7211	51.7192	37.82472	69.16955	67.52419
7/1/2014	41.51738	107.3633	87.28956	77.47712	43.69648	22.23485	79.98335	163.5979	51.76408	39.14904	68.05178	66.18395
8/1/2014	44.61329	112.9952	95.09554	76.54861	46.87056	23.35859	82.95037	164.1356	53.73482	41.20807	73.73171	67.91096
9/1/2014	45.94427	114.1995	86.34191	74.63693	44.29787	23.83418	87.09557	162.9856	54.44888	42.3143	74.29064	69.23663
10/1/2014	42.25793	111.9851	88.41698	78.43468	45.62372	22.09596	87.53365	141.1517	54.6658	42.8528	77.4223	69.05551
11/1/2014	47.36486	120.4573	88.37891	79.42497	46.69895	23.09764	91.52198	139.2371	54.74292	43.63776	80.84065	79.2595
12/1/2014	46.97029	117.2104	80.41074	81.12128	47.21242	23.61953	95.38879	138.6946	56.94502	42.66384	81.43067	77.75649
1/1/2015	42.11971	131.0885	70.25508	78.6366	46.45992	22.3064	93.81014	132.5309	49.48339	37.10699	75.35175	77.37148
2/1/2015	43.2488	136.0301	73.42632	83.63042	47.99006	24.3729	95.2511	139.9913	56.12079	40.27576	76.64359	76.41549
3/1/2015	43.07226	136.1728	70.88429	80.94428	50.49707	24.27609	93.08137	139.7197	55.47972	37.61157	73.7716	74.8859
4/1/2015	44.41714	130.0573	76.95149	84.13013	49.37292	24.5202	84.78878	149.1127	57.93408	44.99327	71.58384	71.48877

2. Convert these prices to monthly returns as the percentage change in the monthly prices. Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the return for the first month. PLEASE DISPLAY IN WORKSHEET IN EXCEL.

3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (multiply the mean monthly return by 12, and multiply the monthly standard deviation by ?12 ). PLEASE DISPLAY IN WORKSHEET IN EXCEL.

4. Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an equally weighted portfolio of these 12 stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all of the individual stocks. Convert these monthly statistics to annual statistics (as described in Step 3) for interpretation. PLEASE DISPLAY IN WORKSHEET IN EXCEL.

5. Using the annual statistics, create an Excel plot with standard deviation (volatility) on the x-axis and average return on the y-axis as follows:

a. Create three columns on your spreadsheet with the statistics you created in Questions 3 and 4 for each of the individual stocks and the equally weighted portfolio. The first column will have the ticker, the second will have annual standard deviation, and the third will have the annual mean return.

b.Highlight the data in the last two columns (standard deviation and mean), choose Insert>Chart>XY Scatter Plot.choose Insert>Chart>XY Scatter Plot. Complete the chart wizard to finish the plot.

6. What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio?

You will work on items 1-6 and submit is as an excel spreadsheet