View the step-by-step solution to:

Use the spreadsheet entitled "Assignment 2 Template.xlsx" to make calculations below and then answer the questions based on your...

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.

 

 

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." 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.


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

 

Word document that answers the following questions:

 

1.     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?

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

3.     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.

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

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

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question