Use the spreadsheet entitled Assignment 2 Template.xlsx to make calculations below and then answer the questions based on your calculations. The three stocks used in the spreadsheet are Safeway Company (SFY), General Motors (GM), and Microsoft (MSFT). The Russell 3000 index is used to measure the market portfolio.

PDR

Date	MSFT	MSFT Div	MSFT Return	SWY	SWY Div	SFY Return	GM	GM Div	GM Return	Russell 3000	Rus 3000 Return
6/1/2012	$ 28.27			$ 15.09			$ 18.89			803.63
7/2/2012	$ 27.24		-0.0364	$ 12.93	$ 0.16	-0.1328	$ 18.88		-0.0005	810.65	0.0087
8/1/2012	$ 28.68		0.0529	$ 13.01		0.0062	$ 20.45		0.0832	828.86	0.0225
9/4/2012	$ 27.69	$ 0.20	-0.0275	$ 13.52		0.0392	$ 21.79		0.0655	849.28	0.0246
10/1/2012	$ 26.55		-0.0412	$ 13.70	$ 0.16	0.0249	$ 24.43		0.1212	833.61	-0.0185
11/1/2012	$ 24.97		-0.0595	$ 14.37		0.0489	$ 24.79		0.0147	837.83	0.0051
12/3/2012	$ 25.06	$ 0.23	0.0128	$ 15.35		0.0682	$ 27.62		0.1142	846.37	0.0102
1/2/2013	$ 25.75		0.0275	$ 16.33	$ 0.16	0.0741	$ 26.91		-0.0257	891.79	0.0537
2/1/2013	$ 26.29		0.0210	$ 20.24		0.2394	$ 26.01		-0.0334	901.62	0.0110
3/1/2013	$ 27.06	$ 0.23	0.0380	$ 22.51		0.1122	$ 26.65		0.0246	935.52	0.0376
4/1/2013	$ 31.31		0.1571	$ 19.24	$ 0.16	-0.1383	$ 29.54		0.1084	949.83	0.0153
5/1/2013	$ 33.24		0.0616	$ 19.66		0.0218	$ 32.47		0.0992	969.99	0.0212
6/3/2013	$ 32.90	$ 0.23	-0.0033	$ 20.38		0.0366	$ 31.91		-0.0172	955.85	-0.0146
7/1/2013	$ 30.33		-0.0781	$ 22.22	$ 0.18	0.0991	$ 34.36		0.0768	1007.03	0.0535
8/1/2013	$ 32.04		0.0564	$ 22.31		0.0041	$ 32.65		-0.0498	976.83	-0.0300
9/3/2013	$ 31.92	$ 0.23	0.0034	$ 27.75		0.2438	$ 34.46		0.0554	1011.55	0.0355
10/1/2013	$ 33.97		0.0642	$ 30.28	$ 0.18	0.0976	$ 35.40		0.0273	1053.3	0.0413
11/1/2013	$ 36.85		0.0848	$ 30.34		0.0020	$ 37.10		0.0480	1081.59	0.0269
12/2/2013	$ 36.16	$ 0.28	-0.0111	$ 28.43		-0.0630	$ 39.15		0.0553	1108.29	0.0247
1/2/2014	$ 36.57		0.0113	$ 27.27	$ 0.18	-0.0345	$ 34.56		-0.1172	1072.28	-0.0325
2/3/2014	$ 37.30		0.0200	$ 32.69		0.1988	$ 34.68		0.0035	1120.66	0.0451
3/3/2014	$ 39.91	$ 0.28	0.0775	$ 32.41		-0.0086	$ 33.27		-0.0407	1124.9	0.0038
4/1/2014	$ 39.34		-0.0143	$ 33.38	$ 0.18	0.0355	$ 33.32	$ 0.30	0.0105	1125.08	0.0002
5/1/2014	$ 40.15		0.0206	$ 33.66		0.0084	$ 33.42		0.0030	1147.18	0.0196
6/2/2014	$ 40.89	$ 0.28	0.0254	$ 33.89		0.0068	$ 35.38		0.0586	1174.07	0.0234
7/1/2014	$ 42.32		0.0350	$ 34.01	$ 0.23	0.0103	$ 32.96	$ 0.30	-0.0599	1149.59	-0.0209
8/1/2014	$ 44.83		0.0593	$ 34.32		0.0091	$ 33.91		0.0288	1195.42	0.0399
9/2/2014	$ 45.74	$ 0.28	0.0265	$ 34.07		-0.0073	$ 31.40		-0.0740	1168.71	-0.0223
10/1/2014	$ 46.33		0.0129	$ 34.63	$ 0.23	0.0232	$ 30.87	$ 0.30	-0.0073	1199.57	0.0264
11/3/2014	$ 47.47		0.0246	$ 34.61		-0.0006	$ 32.86		0.0645	1226.12	0.0221
12/1/2014	$ 46.12	$ 0.31	-0.0219	$ 35.12		0.0147	$ 34.63		0.0539	1224.11	-0.0016
1/2/2015	$ 40.11		-0.1303	$ 35.10	$ 0.23	0.0060	$ 32.35	$ 0.30	-0.0572	1188.93	-0.0287

RETURNS

Date	MSFT	SFY	GM	Russell 3000
7/2/2012	-0.0364	-0.1328	-0.0005	0.0087
8/1/2012	0.0529	0.0062	0.0832	0.0225
9/4/2012	-0.0275	0.0392	0.0655	0.0246
10/1/2012	-0.0412	0.0249	0.1212	-0.0185
11/1/2012	-0.0595	0.0489	0.0147	0.0051
12/3/2012	0.0128	0.0682	0.1142	0.0102
1/2/2013	0.0275	0.0741	-0.0257	0.0537
2/1/2013	0.0210	0.2394	-0.0334	0.0110
3/1/2013	0.0380	0.1122	0.0246	0.0376
4/1/2013	0.1571	-0.1383	0.1084	0.0153
5/1/2013	0.0616	0.0218	0.0992	0.0212
6/3/2013	-0.0033	0.0366	-0.0172	-0.0146
7/1/2013	-0.0781	0.0991	0.0768	0.0535
8/1/2013	0.0564	0.0041	-0.0498	-0.0300
9/3/2013	0.0034	0.2438	0.0554	0.0355
10/1/2013	0.0642	0.0976	0.0273	0.0413
11/1/2013	0.0848	0.0020	0.0480	0.0269
12/2/2013	-0.0111	-0.0630	0.0553	0.0247
1/2/2014	0.0113	-0.0345	-0.1172	-0.0325
2/3/2014	0.0200	0.1988	0.0035	0.0451
3/3/2014	0.0775	-0.0086	-0.0407	0.0038
4/1/2014	-0.0143	0.0355	0.0105	0.0002
5/1/2014	0.0206	0.0084	0.0030	0.0196
6/2/2014	0.0254	0.0068	0.0586	0.0234
7/1/2014	0.0350	0.0103	-0.0599	-0.0209
8/1/2014	0.0593	0.0091	0.0288	0.0399
9/2/2014	0.0265	-0.0073	-0.0740	-0.0223
10/1/2014	0.0129	0.0232	-0.0073	0.0264
11/3/2014	0.0246	-0.0006	0.0645	0.0221
12/1/2014	-0.0219	0.0147	0.0539	-0.0016
1/2/2015	-0.1303	0.0060	-0.0572	-0.0287
Mean Monthly Return
Standard Deviation: Monthly Return
Monthly Return Annualized

COVARIANCES

Covariance Matrix	MSFT	SFY	GM	Russell 3000
MSFT	0.002791
SFY	-0.00067	0.006899
GM	0.000334	-0.00003	0.00346
Russell 3000	0.000258	0.00074	0.000673	0.000593
Beta

PORTFOLIO ANALYSIS

Date	MSFT	SFY	GM	Portfolio	Russell 3000
7/2/2012	-0.0364	-0.1328	-0.0005	-0.0566	0.0087
8/1/2012	0.0529	0.0062	0.0832	0.0474	0.0225
9/4/2012	-0.0275	0.0392	0.0655	0.0257	0.0246
10/1/2012	-0.0412	0.0249	0.1212	0.0350	-0.0185
11/1/2012	-0.0595	0.0489	0.0147	0.0014	0.0051
12/3/2012	0.0128	0.0682	0.1142	0.0651	0.0102
1/2/2013	0.0275	0.0741	-0.0257	0.0253	0.0537
2/1/2013	0.0210	0.2394	-0.0334	0.0757	0.0110
3/1/2013	0.0380	0.1122	0.0246	0.0583	0.0376
4/1/2013	0.1571	-0.1383	0.1084	0.0424	0.0153
5/1/2013	0.0616	0.0218	0.0992	0.0609	0.0212
6/3/2013	-0.0033	0.0366	-0.0172	0.0054	-0.0146
7/1/2013	-0.0781	0.0991	0.0768	0.0326	0.0535
8/1/2013	0.0564	0.0041	-0.0498	0.0036	-0.0300
9/3/2013	0.0034	0.2438	0.0554	0.1009	0.0355
10/1/2013	0.0642	0.0976	0.0273	0.0630	0.0413
11/1/2013	0.0848	0.0020	0.0480	0.0449	0.0269
12/2/2013	-0.0111	-0.0630	0.0553	-0.0063	0.0247
1/2/2014	0.0113	-0.0345	-0.1172	-0.0468	-0.0325
2/3/2014	0.0200	0.1988	0.0035	0.0741	0.0451
3/3/2014	0.0775	-0.0086	-0.0407	0.0094	0.0038
4/1/2014	-0.0143	0.0355	0.0105	0.0106	0.0002
5/1/2014	0.0206	0.0084	0.0030	0.0107	0.0196
6/2/2014	0.0254	0.0068	0.0586	0.0303	0.0234
7/1/2014	0.0350	0.0103	-0.0599	-0.0049	-0.0209
8/1/2014	0.0593	0.0091	0.0288	0.0324	0.0399
9/2/2014	0.0265	-0.0073	-0.0740	-0.0183	-0.0223
10/1/2014	0.0129	0.0232	-0.0073	0.0096	0.0264
11/3/2014	0.0246	-0.0006	0.0645	0.0295	0.0221
12/1/2014	-0.0219	0.0147	0.0539	0.0156	-0.0016
1/2/2015	-0.1303	0.0060	-0.0572	-0.0605	-0.0287
Weights	0.3333	0.3333	0.3333	1.000
Beta	0	0	0	0
Portfolio Beta				0
Mean Monthly Portfolio Return
Standard Deviation: Monthly Portfolio Return
Mean Annualized Monthly Portfolio Return
Covariance(Portfolio, Market)
Mean Monthly Market Return
Standard Deviation: Monthly Market Return
Mean Annualized MonthlyMarket Return
Covariance(Market, Market) (e.g., Variance)
Portfolio Beta

1. Go to the tab labelled Returns. Calculate the following for each of the three stocks and the market index (e.g., write formulas to fill in the yellow shaded cells)

a. Mean monthly return

b. Standard deviation of the monthly return

c. Mean annualized monthly return

2. Go to the tab labelled Covariances and calculate the beta measures for each of the three stocks and the market portfolio.

3. Go to the tab labelled Portfolio Analysis. 3. Calculate all of the values indicated by the yellow shaded cells using the first scenario where the portfolio is made up of equal weights of the three stocks.

a. Mean monthly return for the portfolio

b. Standard deviation of the monthly portfolio return

c. Mean annualized monthly return for the portfolio

d. Covariance of the portfolio with the market return

e. Mean monthly return for the market return

f. Standard deviation of the monthly market return

g. Mean annualized monthly return for the market

h. Covariance of the market with the market return (i.e., the market variance)

i. The portfolio beta

4. Change the portfolio weights to the following:

a. Microsoft: 50%

b. Safeway: 25%

c. General Motors: 25%

d. Note the changes and answer questions below.

5. Change the portfolio weights to the following:

a. Microsoft: 10%

b. Safeway: 80%

c. General Motors: 10%

d. Note the changes and answer questions below.

A Word document that answers the following questions:

a. Based on the standard deviation of the monthly returns, which of the three stocks appears to be the most risky: Safeway, General Motors, or Microsoft? How do they compare to the risk in the market as measured by the standard deviation of the market return?

b. What are the beta measures for each of the three stocks? What do these mean when you compare them for the three stocks?

c. Describe what happens when you change the weights of each stock in the portfolio from the first to the second scenario, and then from the second to the third scenario. What happens to the portfolio return, standard deviation, and beta measurements? Explain why those changes are taking place.

d. Which of the three portfolio mixes would you recommend for an aggressive investor? Explain your reason for making that recommendation.

e. Which of the three portfolio mixes would you recommend for a defensive investor? Explain your reason for making that recommendation.