finance project

profileELEVEN NN
example.xlsm
Home>Business & Finance homework help>finance project

Data, Q2

Date CPB COST COKE CLX CL CPB COST COKE CLX CL
12/31/10 39.67 126.61 94.97 94.88 60.87
1/31/11 40.69 130.12 101.89 97.26 64.19 2.57% 2.78% 7.28% 2.51% 5.44%
2/28/11 40.65 138.92 110.35 98.82 62.84 -0.09% 6.76% 8.31% 1.61% -2.09%
3/31/11 39.04 131.17 110.29 94.98 60.98 -3.95% -5.58% -0.05% -3.89% -2.97% Table of Covariance
4/30/11 42.50 131.11 111.09 97.04 60.87 8.85% -0.05% 0.73% 2.16% -0.17% CPB COST COKE CLX CL
5/31/11 41.89 124.19 147.77 93.76 59.61 -1.43% -5.28% 33.02% -3.38% -2.07% CPB 0.45% 0.16% 0.32% 0.13% 0.10%
6/30/11 43.35 133.60 158.47 100.89 61.99 3.48% 7.58% 7.24% 7.61% 3.99% COST 0.16% 0.28% 0.12% 0.13% 0.12%
7/31/11 42.47 128.77 152.67 100.90 57.57 -2.03% -3.61% -3.66% 0.01% -7.14% COKE 0.32% 0.12% 1.29% 0.13% 0.16%
8/31/11 44.85 133.30 189.46 104.86 58.16 5.61% 3.51% 24.10% 3.92% 1.03% CLX 0.13% 0.13% 0.13% 0.29% 0.15%
9/30/11 44.95 145.79 206.93 110.68 60.81 0.22% 9.37% 9.22% 5.55% 4.55% CL 0.10% 0.12% 0.16% 0.15% 0.22%
10/31/11 46.51 148.83 189.98 113.53 60.54 3.48% 2.09% -8.19% 2.58% -0.45%
11/30/11 46.79 149.29 179.03 115.84 61.41 0.59% 0.31% -5.76% 2.04% 1.43% ,
12/31/11 50.23 139.69 172.54 117.87 62.24 7.35% -6.43% -3.62% 1.75% 1.37%
1/31/12 55.32 138.69 171.64 116.17 60.87 10.13% -0.72% -0.52% -1.44% -2.20%
2/29/12 57.14 146.06 156.94 115.84 65.52 3.30% 5.32% -8.56% -0.28% 7.63%
3/31/12 55.28 137.30 156.56 115.08 65.77 -3.26% -6.00% -0.24% -0.66% 0.38% Table of expected returns and risk
4/30/12 54.52 138.30 121.51 118.84 65.65 -1.37% 0.73% -22.39% 3.27% -0.18% E(return) Standard Deviation
5/31/12 59.89 145.99 145.09 127.95 68.26 9.84% 5.56% 19.41% 7.66% 3.96% CPB 0.23% 6.71%
6/30/12 56.05 155.45 140.12 121.18 69.40 -6.40% 6.48% -3.43% -5.29% 1.68% COST 1.53% 5.27%
7/31/12 54.91 150.69 148.13 121.87 69.68 -2.04% -3.07% 5.72% 0.57% 0.40% COKE 2.81% 11.37%
8/31/12 49.47 142.16 146.02 116.42 69.50 -9.91% -5.66% -1.42% -4.47% -0.27% CLX 1.05% 5.39%
9/30/12 49.14 137.84 139.26 111.62 66.89 -0.66% -3.04% -4.63% -4.12% -3.75% CL 0.41% 4.66%
10/31/12 52.12 139.92 159.72 108.20 61.48 6.07% 1.51% 14.69% -3.07% -8.09%
11/30/12 55.40 149.70 165.02 112.37 61.68 6.29% 6.99% 3.32% 3.86% 0.32%
12/31/12 57.01 153.30 166.69 112.35 60.87 2.91% 2.40% 1.01% -0.02% -1.31%
1/31/13 54.69 165.67 170.13 128.94 69.18 -4.07% 8.07% 2.06% 14.76% 13.66%
2/28/13 52.75 157.21 203.70 127.08 69.38 -3.56% -5.10% 19.73% -1.45% 0.29%
3/31/13 53.02 166.43 209.47 126.00 68.29 0.52% 5.86% 2.83% -0.85% -1.57%
4/30/13 53.46 169.16 225.44 128.69 72.78 0.82% 1.64% 7.63% 2.14% 6.57%
5/31/13 48.36 156.45 226.56 126.33 70.65 -9.54% -7.51% 0.50% -1.83% -2.92%
6/30/13 48.99 155.06 237.67 126.57 68.81 1.30% -0.89% 4.90% 0.19% -2.60%
7/31/13 43.14 153.33 211.66 132.19 68.66 -11.94% -1.12% -10.94% 4.44% -0.22%
8/31/13 43.71 161.23 213.80 125.87 69.82 1.34% 5.15% 1.01% -4.78% 1.69%
9/30/13 44.23 158.08 223.52 120.74 67.52 1.17% -1.95% 4.55% -4.08% -3.29%
10/31/13 46.39 181.00 214.00 133.79 69.82 4.88% 14.50% -4.26% 10.81% 3.40%
11/30/13 45.27 183.20 213.55 142.87 72.71 -2.41% 1.22% -0.21% 6.78% 4.14%
12/31/13 43.80 191.81 200.91 136.10 71.54 -3.24% 4.70% -5.92% -4.74% -1.60%
1/31/14 40.80 187.90 185.35 124.71 66.81 -6.83% -2.04% -7.74% -8.37% -6.62%
2/28/14 41.05 185.97 171.51 128.60 69.43 0.60% -1.03% -7.47% 3.12% 3.93%
3/31/14 38.65 194.58 167.26 113.23 63.18 -5.84% 4.63% -2.48% -11.95% -9.00%
4/30/14 32.15 195.65 126.74 117.72 61.47 -16.84% 0.55% -24.22% 3.97% -2.71%
5/31/14 38.74 206.85 134.42 131.77 63.14 20.51% 5.73% 6.06% 11.93% 2.73%
6/30/14 39.08 216.48 144.36 131.69 65.29 0.89% 4.66% 7.39% -0.06% 3.39%
7/31/14 38.01 230.75 168.98 142.27 65.12 -2.74% 6.59% 17.05% 8.03% -0.26%
8/31/14 35.29 233.06 181.66 147.59 65.65 -7.15% 1.00% 7.50% 3.75% 0.81%
9/30/14 36.05 226.86 172.04 145.67 58.39 2.13% -2.66% -5.29% -1.30% -11.05%
10/31/14 38.12 229.49 212.11 163.57 62.70 5.77% 1.16% 23.29% 12.29% 7.38%
11/30/14 32.08 202.61 177.03 152.23 58.75 -15.84% -11.71% -16.53% -6.93% -6.30%
12/31/14 34.46 213.48 215.38 146.54 63.85 7.40% 5.36% 21.66% -3.74% 8.67%
1/31/15 35.40 217.56 247.67 157.05 65.46 2.73% 1.91% 14.99% 7.17% 2.53%
2/28/15 37.47 241.49 287.61 159.46 68.11 5.86% 11.00% 16.13% 1.54% 4.05%
3/31/15 38.02 244.87 324.78 158.74 72.34 1.47% 1.40% 12.92% -0.45% 6.20%
4/30/15 36.00 238.93 302.01 148.81 69.62 -5.31% -2.42% -7.01% -6.25% -3.76%
5/31/15 39.73 264.26 299.25 153.11 71.67 10.36% 10.60% -0.91% 2.89% 2.94%

Q3 and Q4

Table of expected returns and risk
E(return) Standard Deviation
CPB 0.23% 6.71%
COST 1.53% 5.27%
COKE 2.81% 11.37%
CLX 1.05% 5.39%
CL 0.41% 4.66%
Table of covariances
CPB COST COKE CLX CL
CPB 0.45% 0.16% 0.32% 0.13% 0.10%
COST 0.16% 0.28% 0.12% 0.13% 0.12%
COKE 0.32% 0.12% 1.29% 0.13% 0.16%
CLX 0.13% 0.13% 0.13% 0.29% 0.15%
CL 0.10% 0.12% 0.16% 0.15% 0.22%
Proporition of wealth invested in
CPB 0.00 Wa
COST 0.51 Wb
COKE 0.13 Wc
CLX 0.29 Wd
CL 0.08 We
Total 1.00
This bit is used to find the risk of the portfolio
CPB COST COKE CLX CL
Proportion 0.00% 50.89% 12.51% 28.55% 8.05%
CPB 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000%
COST 50.89% 0.0000% 0.0720% 0.0076% 0.0185% 0.0048%
COKE 12.51% 0.0000% 0.0076% 0.0203% 0.0045% 0.0016%
CLX 28.55% 0.0000% 0.0185% 0.0045% 0.0237% 0.0035%
CL 8.05% 0.0000% 0.0048% 0.0016% 0.0035% 0.0014%
Expected return on the portfolio of all 5 assets
1.46%
Risk associated with the portfolio of all 5 assets
4.46%

Q5

Return Risk Wa Wb Wc Wd We Total weight Efficient frontier for risky portfolio
Table of expected returns and risk 0.2300% 6.6000% 98.0000% 0.0000% 0.0000% 0.0000% 2.0000% 100.0000%
E(return) Standard Deviation 0.2600% 5.8200% 82.0000% 0.0000% 0.0000% 0.0000% 18.0000% 100.0000%
CPB 0.23% 6.71% 0.3200% 4.6500% 50.0000% 0.0000% 0.0000% 0.0000% 50.0000% 100.0000%
COST 1.53% 5.27% 0.4200% 4.2600% 26.0000% 2.0000% 0.0000% 4.0000% 68.0000% 100.0000%
COKE 2.81% 11.37% 0.6800% 4.0900% 17.0000% 19.0000% 0.0000% 12.0000% 52.0000% 100.0000%
CLX 1.05% 5.39% 0.8800% 4.0800% 10.0000% 30.0000% 2.0000% 18.0000% 40.0000% 100.0000%
CL 0.41% 4.66% 0.9800% 4.1000% 7.0000% 33.0000% 4.0000% 20.0000% 36.0000% 100.0000%
1.0200% 4.1200% 6.0000% 35.0000% 4.0000% 20.0000% 35.0000% 100.0000%
Table of covariances 1.2000% 4.2100% 1.0000% 41.0000% 7.0000% 24.0000% 27.0000% 100.0000%
CPB COST COKE CLX CL 1.2500% 4.2400% 0.0000% 43.0000% 8.0000% 25.0000% 24.0000% 100.0000%
CPB 0.45% 0.16% 0.32% 0.13% 0.10% 1.3200% 4.3000% 0.0000% 45.0000% 10.0000% 26.0000% 19.0000% 100.0000%
COST 0.16% 0.28% 0.12% 0.13% 0.12% 1.4400% 4.4300% 0.0000% 50.0000% 12.0000% 28.0000% 10.0000% 100.0000%
COKE 0.32% 0.12% 1.29% 0.13% 0.16% 1.6800% 4.8000% 0.0000% 59.0000% 20.0000% 21.0000% 0.0000% 100.0000%
CLX 0.13% 0.13% 0.13% 0.29% 0.15% 1.7400% 4.9500% 0.0000% 61.0000% 23.0000% 16.0000% 0.0000% 100.0000%
CL 0.10% 0.12% 0.16% 0.15% 0.22% 1.8000% 5.1200% 0.0000% 63.0000% 25.0000% 12.0000% 0.0000% 100.0000%
0 1.9400% 5.5900% 0.0000% 68.0000% 32.0000% 0.0000% 0.0000% 100.0000%
Proporition of wealth invested in 0 1.9800% 5.7500% 0.0000% 65.0000% 35.0000% 0.0000% 0.0000% 100.0000%
CPB 0.00 Wa 2.0100% 5.8800% 0.0000% 63.0000% 37.0000% 0.0000% 0.0000% 100.0000%
COST 0.51 Wb 2.1600% 6.6700% 0.0000% 51.0000% 49.0000% 0.0000% 0.0000% 100.0000%
COKE 0.13 Wc 2.2100% 6.9700% 0.0000% 47.0000% 53.0000% 0.0000% 0.0000% 100.0000%
CLX 0.29 Wd 2.4600% 8.6800% 0.0000% 27.0000% 73.0000% 0.0000% 0.0000% 100.0000%
CL 0.08 We 2.5400% 9.2800% 0.0000% 21.0000% 79.0000% 0.0000% 0.0000% 100.0000%
Total 1.00 2.5800% 9.5800% 0.0000% 18.0000% 82.0000% 0.0000% 0.0000% 100.0000%
2.6200% 9.8900% 0.0000% 15.0000% 85.0000% 0.0000% 0.0000% 100.0000%
2.6500% 10.1300% 0.0000% 12.0000% 88.0000% 0.0000% 0.0000% 100.0000%
CPB COST COKE CLX CL 2.6900% 10.4400% 0.0000% 9.0000% 91.0000% 0.0000% 0.0000% 100.0000%
Proportion 0.00% 50.89% 12.51% 28.55% 8.05% 2.7200% 10.6800% 0.0000% 7.0000% 93.0000% 0.0000% 0.0000% 100.0000%
CPB 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000% 2.7800% 11.1600% 0.0000% 2.0000% 98.0000% 0.0000% 0.0000% 100.0000%
COST 50.89% 0.0000% 0.0720% 0.0076% 0.0185% 0.0048% 2.8000% 11.3200% 0.0000% 0.5200% 99.4800% 0.0000% 0.0000% 100.0000%
COKE 12.51% 0.0000% 0.0076% 0.0203% 0.0045% 0.0016% 2.8100% 11.4000% 0.0000% 0.0000% 100.0000% 0.0000% 0.0000% 100.0000%
CLX 28.55% 0.0000% 0.0185% 0.0045% 0.0237% 0.0035%
CL 8.05% 0.0000% 0.0048% 0.0016% 0.0035% 0.0014%
Expected return on the portfolio of all 5 assets
1.46%
Risk associated with the portfolio of all 5 assets
4.46%

&A

Page &P

Q6

Table of expected returns and risk on the risky assets
E(return) Risk
CPB 0.23% 6.71%
COST 1.53% 5.27%
COKE 2.81% 11.37%
CLX 1.05% 5.39%
CL 0.41% 4.66%
Riskfree rate 0.33% The return on the risk free asset
Table of covariances for the risky assets
CPB COST COKE CLX CL
CPB 0.45% 0.16% 0.32% 0.13% 0.10%
COST 0.16% 0.28% 0.12% 0.13% 0.12%
COKE 0.32% 0.12% 1.29% 0.13% 0.16%
CLX 0.13% 0.13% 0.13% 0.29% 0.15%
CL 0.10% 0.12% 0.16% 0.15% 0.22%
Proporition of wealth invested in purely risky assets
CPB 0.00 Wa
COST 0.66 Wb
COKE 0.29 Wc
CLX 0.04 Wd
CL 0.00 We
Total 1.0000
CPB COST COKE CLX CL
Proportion 0.00% 66.34% 29.31% 4.35% 0.00%
CPB 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000%
COST 66.34% 0.0000% 0.1224% 0.0232% 0.0037% 0.0000%
COKE 29.31% 0.0000% 0.0232% 0.1112% 0.0016% 0.0000%
CLX 4.35% 0.0000% 0.0037% 0.0016% 0.0006% 0.0000%
CL 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000%
Expected return on the portfolio of all 5 risky assets
1.89%
Risk associated with the portfolio of all 5 risky assets
5.40%
Market Price of Risk ( = Slope of the Capital Market Line)
0.2876063123

Results

Efficient frontier for risky portfolio Capital market line market price of risk (Slope of CML) 0.2876063123
risk return risk return risk free rate 0.33%
6.60% 0.23% 0.00% 0.33%
5.82% 0.26% 1.00% 0.62%
4.65% 0.32% 2.00% 0.91%
4.26% 0.42% 3.00% 1.20%
4.09% 0.68% 4.00% 1.48%
4.08% 0.88% 5.00% 1.77%
4.10% 0.98% 6.00% 2.06%
4.12% 1.02% 7.00% 2.35%
4.21% 1.20% 8.00% 2.63%
4.24% 1.25% 9.00% 2.92%
4.30% 1.32% 10.00% 3.21%
4.43% 1.44% 11.00% 3.50%
4.80% 1.68% 12.00% 3.78%
4.95% 1.74% 13.00% 4.07%
5.12% 1.80% 14.00% 4.36%
5.59% 1.94% 15.00% 4.65%
5.75% 1.98% 16.00% 4.94%
5.88% 2.01% 17.00% 5.22%
6.67% 2.16% 18.00% 5.51%
6.97% 2.21% 19.00% 5.80%
8.68% 2.46% 20.00% 6.09%
9.28% 2.54% 21.00% 6.37%
9.58% 2.58% 22.00% 6.66%
9.89% 2.62% 23.00% 6.95%
10.13% 2.65% 24.00% 7.24%
10.44% 2.69% 25.00% 7.52%
10.68% 2.72% 26.00% 7.81%
11.16% 2.78% 27.00% 8.10%
11.32% 2.80% 28.00% 8.39%
11.40% 2.81% 29.00% 8.67%

Graph

efficient frontier 6.6000000000000003E-2 5.8200000000000002E-2 4.65E-2 4.2599999999999999E-2 4.0899999999999999E-2 4.0800000000000003E-2 4.1000000000000002E-2 4.1200000 000000001E-2 4.2099999999999999E-2 4.24E-2 4.2999999999999997E-2 4.4299999999999999E-2 4.8000000000000001E-2 4.9500000000000002E-2 5.1200000000000002E-2 5.5899999999999998E-2 5.7500000000000002E-2 5.8799999999999998E-2 6.6699999999999995E-2 6.9699999999999998E-2 8.6800000000000002E-2 9.2799999999999994E-2 9.5799999999999996E-2 9.8900000000000002E-2 0.1013 0.10440000000000001 0.10680000000000001 0.1116 0.1132 0.114 2.3E-3 2.5999999999999999E-3 3.2000000000000002E-3 4.1999999999999997E-3 6.7999999999999996E-3 8.8000000000000005E-3 9.7999999999999997E-3 1.0200000000000001E-2 1.2E-2 1.2500000000000001E-2 1.32E-2 1.44E-2 1.6799999999999999E-2 1.7399999999999999E-2 1.7999999999999999E-2 1.94000 00000000001E-2 1.9800000000000002E-2 2.01E-2 2.1600000000000001E-2 2.2100000000000002E-2 2.46E-2 2.5399999999999999E-2 2.58E-2 2.6200000000000001E-2 2.6499999999999999E-2 2.69E-2 2.7199999999999998E-2 2.7799999999999998E-2 2.8000000000000001E-2 2.81E-2 assets 0.10740249530279999 4.5588889409706797E-2 6.7954784154793599E-2 3.9392073272486797E-2 5.5893536849876196E-3 6.9560816202119801E-3

risk

return

efficient frontier 6.6000000000000003E-2 5.8200000000000002E-2 4.65E-2 4.2599999999999999E-2 4.0899999999999999E-2 4.0800000000000003E-2 4.1000000000000002E-2 4.1200000000000001E-2 4.2099999999999999E-2 4.24E-2 4.2999999999999997E-2 4.4299999999999999E-2 4.8000000000000001E-2 4.9500000000000002E-2 5.1200000000000002E-2 5.5899999999999998E-2 5.7500000000000002E-2 5.8799999999999998E-2 6.6699999999999995E-2 6.9699999999999998E-2 8.6800000000000002E-2 9.2799999999999994E-2 9.5799999999999996E-2 9.8900000000000002E-2 0.1013 0.10440000000000001 0.10680000000000001 0.1116 0.1132 0.114 2.3E-3 2.5999999999999999E-3 3.2000000000000002E-3 4.1999999999999997E-3 6.7999999999999996E-3 8.8000000000000005E-3 9.7999999999999997E-3 1.0200000000000001E-2 1.2E-2 1.2500000000000001E-2 1.32E-2 1.44E-2 1.6799999999999999E-2 1.7399999999999999E-2 1.7999999999999999E-2 1.9400000000000001E-2 1.9800000000000002E-2 2.01E-2 2.1600000000000001E-2 2.2100000000000002E-2 2.46E-2 2.5399999999999999E-2 2.58E-2 2.6200000000000001E-2 2.6499999999999999E-2 2.69E-2 2.7199999999999998E-2 2.7799999999999998E-2 2.8000000000000001E-2 2.81E-2 cap market line 0 0.01 0.02 0.03 0.04 0.05 0.06 7.0000000000000007E-2 0.08 0.09 0.1 0.11 0.12 0.13 0.14000000000000001 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 0.23 0.24 0.25 0.26 0.27 0.28000000000000003 0.28999999999999998 3.3333333333333335E-3 6.2093964565819795E-3 9.0854595798306251E-3 1.1961522703079272E-2 1.4837585826327918E-2 1.7713648949576565E-2 2.0589712072825211E-2 2.346577519607386E-2 2.6341838319322502E-2 2.9217901442571147E-2 3.2093964565819796E-2 3.4970027689068442E-2 3.7846090812317087E-2 4.0722153935565733E-2 4.3598217058814386E-2 4.6474280182063024E-2 4.935034330531167E-2 5.2226406428560322E-2 5.5102469551808961E-2 5.7978532675057613E-2 6.0854595798306259E-2 6.3730658921554897E-2 6.6606722044803543E-2 6.9482785168052189E-2 7.2358848291300834E-2 7.523491141454948E-2 7.8110974537798125E-2 8.0987037661046771E-2 8.386310078429543E-2 8.6739163907544062E-2 assets 0.10740249530279999 4.5588889409706797E-2 6.7954784154793599E-2 3.9392073272486797E-2 5.5893536849876196E-3 6.9560816202119801E-3

risk

return