Southern New Hampshire University Efficient Portfolio Problem Paper

Content Type

User Generated

User

HFRE_ERZBIRQ_YRTNY_2073

Subject

Business Finance

School

Southern New Hampshire University

Description

There are three attachments. Use each attachment to complete the following:

Efficient Portfolio Problem Set:

Complete Problems 1 and 2 in the provided Excel template. Use Excel or save as an Excel file (.XLS or .XLSX) for all calculations. All formulas should flow through the spreadsheet and entries should not be hard-keyed. Use APA formatting to cite any resources used.

Stocks and Returns Problem Set:

Complete Problems 1, 2, and 3 in the provided Excel template. Use Excel or save as an Excel file (.XLS or .XLSX) for all calculations. All formulas should flow through the spreadsheet and entries should not be hard-keyed. Use APA formatting to cite any resources used.

CAPM Problem Set:

Complete Questions 1 and 2 in the provided Excel template. Use Excel or save as an Excel file (.XLS or .XLSX) for all calculations. All formulas should flow through the spreadsheet and entries should not be hard-keyed. Use APA formatting to cite any resources used.

Unformatted Attachment Preview

Monthly Returns for Boeing (BA), General Electric (GE), Walmart (WMT) BA 2-Feb-09 2-Mar-09 1-Apr-09 1-May-09 1-Jun-09 1-Jul-09 3-Aug-09 1-Sep-09 1-Oct-09 2-Nov-09 1-Dec-09 4-Jan-10 1-Feb-10 1-Mar-10 1-Apr-10 3-May-10 1-Jun-10 1-Jul-10 Mean returns GE WMT -28.69% 12.35% 11.83% 12.29% -5.36% 0.94% 15.59% 8.64% -12.48% 10.08% 3.23% 11.29% 4.83% 13.93% -0.25% -11.51% -2.25% 3.00% -32.58% 17.26% 22.44% 6.33% -13.18% 13.41% 3.68% 17.23% -14.11% 11.66% -5.09% 6.10% 0.50% 12.53% 3.53% -14.26% -11.95% 3.61% 4.42% 6.20% -3.33% -0.78% -2.64% 2.93% 2.51% -3.56% 1.19% 9.35% -1.54% -0.04% 1.20% 3.35% -3.59% -5.33% -5.05% 2.79% 2.64% 1.51% 0.45% Instructions: Calculate Mean and Sigma in YELLOW 1) Complete Excess treturn matrix 2) Complete the Transpose of Excess Return Matrix 3) Variance-covariance matrix using the excess return method BA 2-Feb-09 2-Mar-09 1-Apr-09 1-May-09 1-Jun-09 1-Jul-09 3-Aug-09 1-Sep-09 1-Oct-09 2-Nov-09 1-Dec-09 4-Jan-10 1-Feb-10 1-Mar-10 1-Apr-10 3-May-10 1-Jun-10 1-Jul-10 -28.69% 12.35% 11.83% 12.29% -5.36% 0.94% 15.59% 8.64% -12.48% 10.08% 3.23% 11.29% 4.83% 13.93% -0.25% -11.51% -2.25% 3.00% GE -32.58% 17.26% 22.44% 6.33% -13.18% 13.41% 3.68% 17.23% -14.11% 11.66% -5.09% 6.10% 0.50% 12.53% 3.53% -14.26% -11.95% 3.61% WMT 4.42% 6.20% -3.33% -0.78% -2.64% 2.93% 2.51% -3.56% 1.19% 9.35% -1.54% -0.04% 1.20% 3.35% -3.59% -5.33% -5.05% 2.79% mean sigma Excess return matrix BA GE #DIV/0! #DIV/0! WMT 2-Feb-09 2-Mar-09 1-Apr-09 1-May-09 1-Jun-09 1-Jul-09 3-Aug-09 1-Sep-09 1-Oct-09 2-Nov-09 1-Dec-09 4-Jan-10 1-Feb-10 1-Mar-10 1-Apr-10 3-May-10 1-Jun-10 1-Jul-10 average #DIV/0! this line included as check only! transpose of excess return matrix 2-Feb-09 2-Mar-09 1-Apr-09 1-May-09 1-Jun-09 BA GE WMT Variance-covariance matrix using the excess return method Means BA GE WMT 0.00% BA 0.00% GE 0.00% WMT #N/A #N/A #N/A 1-Jul-09 3-Aug-09 1-Sep-09 1-Oct-09 2-Nov-09 1-Dec-09 4-Jan-10 1-Feb-10 1-Mar-10 1-Apr-10 3-May-10 1-Jun-10 1-Jul-10 PRICE DATA FOR 7 STOCKS AND THE S&P500 Yahoo Microsoft Ford Kellogg Date YHOO MSFT F K 1/4/2010 15.84 18.36 5.41 37.07 2/1/2010 15.66 20.89 5.71 39.91 3/1/2010 14.32 20.67 7.53 40.71 4/1/2010 14.61 21.79 7.15 40.68 5/3/2010 17.81 22.73 6.79 42.53 6/1/2010 15.9 24.51 6.59 44.53 7/1/2010 14.97 26.11 8.37 45.75 8/2/2010 16.78 27.06 9.41 46.29 9/1/2010 15.01 25.02 10.2 47.35 10/1/2010 15.31 25.57 11.05 45.7 11/1/2010 16.53 26.12 11.83 46.82 12/1/2010 16.53 27.24 12.26 48.15 1/3/2011 15.34 23.12 11.04 47.16 2/1/2011 13.84 20.62 9.49 44.4 3/1/2011 13.88 23.12 12.02 44.18 4/1/2011 13.11 21.14 10.63 44.2 5/2/2011 14.17 22.06 11.52 44.94 6/1/2011 16.49 24.02 13.3 44.72 7/1/2011 15.82 22.89 15 44.16 8/1/2011 16.63 25.29 15.8 45.82 9/1/2011 16.12 25.13 15.01 45.12 10/3/2011 16.4 24.23 14.17 48.42 11/1/2011 16.68 23.15 14.03 48.8 12/1/2011 17.7 23.63 14.56 51.77 1/3/2012 16.55 22.95 14.04 51.89 2/1/2012 15.04 23.86 12.98 50.37 3/1/2012 13.1 25.14 11.49 50.79 4/2/2012 13.61 24.56 10.47 49.85 5/1/2012 13.17 22.98 9.1 48.82 6/1/2012 15.64 24.59 10.99 49.75 7/2/2012 15.71 23.8 9.98 45.52 8/1/2012 16.13 24.15 10.13 46.83 9/4/2012 15.47 27.47 11.74 45.85 10/1/2012 14.83 29.72 11.7 48.87 11/1/2012 15.22 30.21 11.79 50.07 12/3/2012 15.54 29.99 10.71 47.21 1/2/2013 15.24 27.51 10.02 45.93 Goldman Sachs Exxon Citicorp GS XOM C 135.94 61.03 37.07 138.63 61.52 29.6 153.55 61.95 31.59 155.91 61.23 49.83 173.71 60.75 48.24 160.35 63.46 40.76 160.21 66.85 40.96 159.44 60.73 32.99 140.44 57.38 33.09 147.98 58.26 33.89 161.49 60.04 40.36 137.42 60.75 43.55 136.87 54.56 39.47 124.55 51.5 37.47 143.1 53.86 40.86 130.25 53.73 36.98 137.52 56.16 38.97 153.26 60.43 41.56 148.84 63.62 41.86 160.3 66.88 47.14 155.98 73.79 48.04 156.46 78.64 46.64 151.51 77.35 44.05 144.26 80.89 45.75 134.79 77.18 41.02 127.47 75.25 41.51 129.27 73.78 38.23 111.65 68.9 30.96 90.83 67.6 25.55 105.24 72.68 31.5 92.45 75.32 27.41 87.21 79.36 26.24 107.5 78.41 30.64 111.37 81.43 33.25 120.3 81.65 36.47 111.38 81.28 32.97 93.01 74.54 26.46 Instructions: 1) calculate: Mean, Variance, Sigma and Beta for all 7 stocks 2) calculate Logarithm based on Price Data PRICE DATA FOR 7 STOCKS AND THE S&P500 Monthly returns Yahoo YHOO Mean Variance Sigma Beta Date 2/1/2010 3/1/2010 4/1/2010 5/3/2010 6/1/2010 7/1/2010 8/2/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/3/2011 2/1/2011 3/1/2011 4/1/2011 5/2/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/3/2011 11/1/2011 12/1/2011 1/3/2012 2/1/2012 3/1/2012 4/2/2012 5/1/2012 6/1/2012 Yahoo YHOO Microsoft Ford MSFT F Microsoft Ford MSFT F Kellogg K Goldman Sachs GS Exxon XOM Citicorp C Kellogg K Goldman Sachs GS Exxon XOM Citicorp C 7/2/2012 8/1/2012 9/4/2012 10/1/2012 11/1/2012 12/3/2012 1/2/2013
Purchase answer to see full attachment

Tags: APA financial modeling Efficient Portfolio Problem Stocks and Returns Problem CAPM Problem

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi bro, here are the solved problem sets. Just let me know.

PRICES FOR 13 STOCKS AND THE SP50

Date
4/May/09
1/Jun/09
1/Jul/09
3/Aug/09
1/Sep/09
1/Oct/09
2/Nov/09
1/Dec/09
4/Jan/10
1/Feb/10
1/Mar/10
1/Apr/10
3/May/10
1/Jun/10
1/Jul/10
2/Aug/10
1/Sep/10
1/Oct/10
1/Nov/10
1/Dec/10
3/Jan/11
1/Feb/11
1/Mar/11
1/Apr/11
2/May/11
1/Jun/11
1/Jul/11
1/Aug/11
1/Sep/11
3/Oct/11
1/Nov/11
1/Dec/11
3/Jan/12
1/Feb/12
1/Mar/12
2/Apr/12
1/May/12
1/Jun/12
2/Jul/12
1/Aug/12
4/Sep/12
1/Oct/12
1/Nov/12
3/Dec/12

Walmart
WMT
44.05
42.90
44.17
45.30
43.71
44.24
48.57
47.83
47.82
48.39
50.04
48.28
45.77
43.51
46.34
45.65
48.73
49.32
49.25
49.37
51.33
47.59
47.99
50.69
51.25
49.32
48.92
49.72
48.51
53.02
55.06
56.21
57.72
55.57
57.95
55.78
62.75
66.47
70.96
69.59
70.74
71.91
69.03
65.76

Yahoo
Microsoft
YHOO
MSFT
15.84
18.36
15.66
20.89
14.32
20.67
14.61
21.79
17.81
22.73
15.90
24.51
14.97
26.11
16.78
27.06
15.01
25.02
15.31
25.57
16.53
26.12
16.53
27.24
15.34
23.12
13.84
20.62
13.88
23.12
13.11
21.14
14.17
22.06
16.49
24.02
15.82
22.89
16.63
25.29
16.12
25.13
16.40
24.23
16.68
23.15
17.70
23.63
16.55
22.95
15.04
23.86
13.10
25.14
13.61
24.56
13.17
22.98
15.64
24.59
15.71
23.80
16.13
24.15
15.47
27.47
14.83
29.72
15.22
30.21
15.54
29.99
15.24
27.51
15.83
28.83
15.84
27.78
14.65
29.24
15.98
28.24
16.84
27.08
18.77
25.47
19.90
25.55

Ford
F
5.41
5.71
7.53
7.15
6.79
6.59
8.37
9.41
10.20
11.05
11.83
12.26
11.04
9.49
12.02
10.63
11.52
13.30
15.00
15.80
15.01
14.17
14.03
14.56
14.04
12.98
11.49
10.47
9.10
10.99
9.98
10.13
11.74
11.70
11.79
10.71
10.02
9.10
8.77
8.91
9.41
10.70
10.98
12.42

Kellogg
K
37.07
39.91
40.71
40.68
42.53
44.53
45.75
46.29
47.35
45.70
46.82
48.15
47.16
44.40
44.18
44.20
44.94
44.72
44.16
45.82
45.12
48.42
48.80
51.77
51.89
50.37
50.79
49.85
48.82
49.75
45.52
46.83
45.85
48.87
50.07
47.21
45.93
46.45
44.91
48.10
49.06
49.69
53.09
53.46

Goldman
Sachs
GS
135.94
138.63
153.55
155.91
173.71
160.35
160.21
159.44
140.44
147.98
161.49
137.42
136.87
124.55
143.10
130.25
137.52
153.26
148.84
160.30
155.98
156.46
151.51
144.26
134.79
127.47
129.27
111.65
90.83
105.24
92.45
87.21
107.50
111.37
120.30
111.38
93.01
93.17
98.07
103.20
110.97
119.48
115.47
125.05

Exxon
XOM
61.03
61.52
61.95
61.23
60.75
63.46
66.85
60.73
57.38
58.26
60.04
60.75
54.56
51.50
53.86
53.73
56.16
60.43
63.62
66.88
73.79
78.64
77.35
80.89
77.18
75.25
73.78
68.90
67.60
72.68
75.32
79.36
78.41
81.43
81.65
81.28
74.54
81.11
82.33
83.29
87.25
86.98
84.62
83.09

2/Jan/13
1/Feb/13
1/Mar/13
1/Apr/13
1/May/13
3/Jun/13
1/Jul/13
1/Aug/13
3/Sep/13
1/Oct/13
1/Nov/13
2/Dec/13
2/Jan/14
3/Feb/14
3/Mar/14
1/Apr/14
1/May/14
2/Jun/14

67.42
68.22
72.59
75.39
73.03
72.69
76.06
71.65
72.61
75.35
79.53
77.70
73.74
73.76
75.96
79.22
76.77
75.07

19.63
21.31
23.53
24.73
26.30
25.13
28.09
27.12
33.17
32.94
36.98
40.44
36.01
38.67
35.90
35.95
34.65
35.13

26.26
26.82
27.60
31.93
33.90
33.55
30.93
32.67
32.55
34.64
37.58
36.87
37.30
38.04
40.70
40.12
40.94
41.70
...