DATE	XOM	BP	WMT	Portfolio	BP
4/1/13	88.38	39.99	77.26
3/1/13	89.49	40.31	74.38
2/1/13	88.93	38.46	69.91
1/2/13	88.78	41.85	69.09
12/3/12	85.41	39.14	67.39
11/1/12	86.98	39.26	70.74
10/1/12	89.41	39.81	73.69
9/4/12	89.68	39.32	72.49
8/1/12	85.61	39.04	71.31
7/2/12	84.62	36.62	72.71
6/1/12	83.38	37.21	68.11
5/1/12	76.61	33.46	64.3
4/2/12	83.55	39.37	57.16
3/1/12	83.93	40.81	59.39
2/1/12	83.7	42.77	56.95
1/3/12	80.59	41.2	59.14
12/1/11	81.57	38.36	57.6
11/1/11	77.42	39.09	56.42
10/3/11	74.71	39.26	54.33
9/1/11	69.49	32.05	49.72
8/1/11	70.82	35	50.95
7/1/11	75.84	39.99	50.13
6/1/11	77.35	38.98	50.54
5/2/11	79.33	40.7	52.52
4/1/11	83.15	40.23	51.94
3/1/11	79.51	38.49	49.18
2/1/11	80.83	42.26	48.77
1/3/11	75.85	41.02	52.61
12/1/10	68.74	38.17	50.6
11/1/10	65.4	34.56	50.47
10/1/10	62.12	35.25	50.54
9/1/10	57.73	35.57	49.94
8/2/10	55.22	30.1	46.78
7/1/10	55.36	33.24	47.49
6/1/10	52.94	24.95	44.59
5/3/10	56.08	37.11	46.9
4/1/10	62.44	44.32	49.47
3/1/10	61.71	48.5	51.28
2/1/10	59.89	45.22	49.59
1/4/10	58.98	46.98	49
12/1/09	62.42	48.53	49.02
11/2/09	68.72	47.87	49.78
10/1/09	65.23	46.73	45.33
9/1/09	62.44	43.94	44.79
8/3/09	62.93	42.47	46.42
7/1/09	63.67	40.62	45.27
6/1/09	63.24	38.7	43.96
5/1/09	62.73	40.18	45.14
4/1/09	59.95	33.86	45.49
3/2/09	61.23	31.98	47.03
2/2/09	61.06	30.59	44.2
1/2/09	68.43	33.19	42.29
12/1/08	71.42	36.53	50.32
11/3/08	71.71	38.06	49.94
10/1/08	65.93	38.15	49.88
9/2/08	69.08	38.51	53.53
8/1/08	71.17	44.24	52.79
7/1/08	71.18	46.51	52.18
6/2/08	78	52.67	50.03
5/1/08	78.56	54.89	51.4
4/1/08	82	54.49	51.39
3/31/08	74.52	45.4	46.7

MUST BE DONE IN EXCEL

Diversification. Log in to Connect or ask your instructor for access to the materials for Chapter 11. You will find a spreadsheet containing 5 years of monthly rates of return on ExxonMobil (XOM), BP, and Walmart (WMT) a. What was the average return and standard deviation of returns for each firm? b. What was the correlation of returns between each pair of firms? Try using Excel's CORREL function, which calculates the correlation between two series of numbers. Which pair of firms exhibits the highest correlation of returns? Is this surprising? c. Now imagine that you held an equally weighted portfolio of ExxonMobil and Walmart (that is, a portfolio with equal dollar investments in each stock). Compute the portfolio's rate of return for each month, and calculate the standard deviation of the portfolio's monthly rate of return. Is the portfolio standard deviation more or less than the average of the standard deviations of the two component stocks? d. Repeat part (c), but this time calculate the results for a portfolio of BP and ExxonMobil. e. Comparing your answers to (c) and (d), which pair of firms provides greater benefits from diversification? Relate your answer to the correlation coefficients you found in part (a)