Excel Table Closing Prices
Date	MDC	IBM	r(MDC)	r(IBM)
3/1/2009	26.3	88.42
4/1/2009	28.86	94.19
5/1/2009	26.12	97.5
6/1/2009	25.61	95.79
7/1/2009	29.97	108.19
8/3/2009	32.09	108.8
9/1/2009	29.76	110.24
10/1/2009	27.94	111.16
11/2/2009	25.63	116.97
12/1/2009	26.79	121.19
1/4/2010	29	113.31
2/1/2010	29.76	118.25
3/1/2010	30.1	119.26
4/1/2010	33.31	119.96
5/3/2010	27.5	117.08
6/1/2010	23.62	115.42
7/1/2010	25.52	120.02
8/2/2010	23.54	115.66
9/1/2010	25.67	126
10/1/2010	22.77	134.89
11/1/2010	22.27	133.47
12/1/2010	25.66	138.47
1/3/2011	27.57	152.85
2/1/2011	23.61	153.34
3/1/2011	22.8	154.47
4/1/2011	26.26	161.58
5/2/2011	24.46	160.73
6/1/2011	22.37	163.23
7/1/2011	20.52	173.03
8/1/2011	17.99	164.28
9/1/2011	15.58	167.11
10/3/2011	20.6	176.44
11/1/2011	16.64	180.38
12/1/2011	16.44	176.43
1/3/2012	18.48	184.79
2/1/2012	23.15	189.49
3/1/2012	24.29	200.97
4/2/2012	26.47	199.46
5/1/2012	27.29	186.58
6/1/2012	31.05	189.17
7/2/2012	30.28	189.56
8/1/2012	33.22	189.27
9/4/2012	36.89	201.51
10/1/2012	36.63	188.96
11/1/2012	33.98	185.44
12/3/2012	36.44	186.88
1/2/2013	38.98	198.12
2/1/2013	38.1	196.76
3/1/2013	36.34	208.97
4/1/2013	37.28	198.43
5/1/2013	36.79	204.76
6/3/2013	32.23	188.11
7/1/2013	31.37	191.98
8/1/2013	27.59	180.31
9/3/2013	29.75	183.19
10/1/2013	28.94	177.28
11/1/2013	29.96	178.7
12/2/2013	31.96	186.55
1/2/2014	30.63	175.72
2/3/2014	31.19	185.17

Calculate the returns for both stock in each month during this five-year period by using rt+1=ln(PtPt+1)= Calculate the average monthly return and standard deviation of monthly returns for each stock. Using only the information (average monthly return \& standard deviation), which stock would you have preferred to own over this five-year period? Calculate the correlation coefficient () for the two sets of returns by using the "CORREL" function in Excel. If the historical returns (from 2.), standard deviations (from 2.), and correlation (from 3.) fairly represent the future, calculate the expected return and standard deviation of a portfolio consisting of 50% invested in each stock. Would you now prefer to choose the portfolio or an individual stock? (I.e., which one would you choose from the following three: a 50-50 portfolio by MCD \& IBM; MCD only; and IBM only?) Using the Excel Solver, find the weights for each stock that would result in a minimum portfolio standard deviation. At the end of your group report file (i.e., the Excel file), report each group member's contribution (in percentage)