Finance excel

profileJessie 1
1Gengzongxuan.xlsx
Home>Business & Finance homework help>Finance excel

Data

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

Q2

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%

Q3

Table of Covariance
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%

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 Covariance
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.1 Wa
COST 0.2 Wb
COKE 0.3 Wc
CLX 0.2 Wd
CL 0.2 We
Total 1
CPB COST COKE CLX CL
Proportion 10% 20% 30% 20% 20%
CPB 10.00% 0.00450% 0.00317% 0.00969% 0.00269% 0.00196%
COST 20.00% 0.00317% 0.01112% 0.00717% 0.00510% 0.00469%
COKE 30.00% 0.00969% 0.00717% 0.11645% 0.00752% 0.00978%
CLX 20.00% 0.00269% 0.00510% 0.00752% 0.01162% 0.00615%
CL 20.00% 0.00196% 0.00469% 0.00978% 0.00615% 0.00869%
VARIANCE 0.26821%
Expected return on the portfolio of all 5 assets
1.46%
Risk associated with the portfolio of all 5 assets
5.18%
21.8240%

Q5

Table of expected returns and risk
E(return) Standard Deviation Variance Return Risk CPB COST COKE CLX CL Total weight
CPB 0.23% 6.71% 0.45% 1.3491% 3.9301% 9.1251% 69.1055% 5.1323% 8.8749% 7.7622% 100.0000%
COST 1.53% 5.27% 0.28% 1.5945% 4.0001% 6.1731% 68.5472% 16.4903% 4.8269% 3.9625% 100.0000%
COKE 2.81% 11.37% 1.29% 1.7338% 4.2000% 5.7763% 66.7536% 23.8082% 2.2237% 1.4382% 100.0000%
CLX 1.05% 5.39% 0.29% 1.8279% 4.4001% 3.7191% 64.5378% 28.7689% 1.6409% 1.3333% 100.0000%
CL 0.41% 4.66% 0.22% Riskfree rate 0.33% 1.9164% 4.6001% 2.1798% 63.5294% 32.8829% 1.4078% 0.0000% 100.0000%
1.9789% 4.8000% 1.1005% 61.3762% 36.5238% 0.9995% 0.0000% 100.0000%
Table of Covariance 2.0312% 4.9999% 0.5780% 59.7111% 39.7109% 0.0000% 0.0000% 100.0000%
CPB COST COKE CLX CL 2.0759% 5.2000% 0.0000% 57.3733% 42.6267% 0.0000% 0.0000% 100.0000%
CPB 0.45% 0.16% 0.32% 0.13% 0.10% 2.1106% 5.4000% 0.0000% 54.6491% 45.3509% 0.0000% 0.0000% 100.0000%
COST 0.16% 0.28% 0.12% 0.13% 0.12% 2.1434% 5.6000% 0.0000% 52.0674% 47.9326% 0.0000% 0.0000% 100.0000%
COKE 0.32% 0.12% 1.29% 0.13% 0.16% 2.1750% 5.8002% 0.0000% 49.5929% 50.4071% 0.0000% 0.0000% 100.0000%
CLX 0.13% 0.13% 0.13% 0.29% 0.15% 2.2053% 6.0000% 0.0000% 47.2124% 52.7876% 0.0000% 0.0000% 100.0000%
CL 0.10% 0.12% 0.16% 0.15% 0.22% 2.2347% 6.2001% 0.0000% 44.8997% 55.1003% 0.0000% 0.0000% 100.0000%
2.2634% 6.4001% 0.0000% 42.6481% 57.3519% 0.0000% 0.0000% 100.0000%
Proporition of wealth invested in 2.2915% 6.6003% 0.0000% 40.4439% 59.5561% 0.0000% 0.0000% 100.0000%
CPB 1 Wa 2.3189% 6.8000% 0.0000% 38.2888% 61.7112% 0.0000% 0.0000% 100.0000%
COST 0 Wb 2.3460% 7.0000% 0.0000% 36.1681% 63.8319% 0.0000% 0.0000% 100.0000%
COKE 0 Wc 2.3857% 7.3000% 0.0000% 33.0466% 66.9534% 0.0000% 0.0000% 100.0000%
CLX 0 Wd 2.4247% 7.6003% 0.0000% 29.9831% 70.0169% 0.0000% 0.0000% 100.0000%
CL 0 We 2.4630% 7.9000% 0.0000% 26.9764% 73.0236% 0.0000% 0.0000% 100.0000%
Total 1 2.5008% 8.2000% 0.0000% 24.0100% 75.9900% 0.0000% 0.0000% 100.0000%
CPB COST COKE CLX CL 2.5402% 8.5000% 0.0000% 21.0807% 78.9193% 0.0000% 0.0000% 100.0000%
Proportion 100% 0% 0% 0% 0% 2.5772% 8.8000% 0.0000% 18.1836% 81.8164% 0.0000% 0.0000% 100.0000%
CPB 100.00% 0.4504% 0.0000% 0.0000% 0.0000% 0.0000% 2.6140% 9.1000% 0.0000% 15.3147% 84.6853% 0.0000% 0.0000% 100.0000%
COST 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000% 2.6504% 9.4000% 0.0000% 12.4704% 87.5296% 0.0000% 0.0000% 100.0000%
COKE 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000% 2.6865% 9.7000% 0.0000% 9.6480% 90.3520% 0.0000% 0.0000% 100.0000%
CLX 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000% 2.7224% 10.0000% 0.0000% 6.8451% 93.1549% 0.0000% 0.0000% 100.0000%
CL 0.00% 0.0000% 0.0000% 0.0000% 0.0000% 0.0000% 2.7580% 10.3000% 0.0000% 4.0597% 95.9403% 0.0000% 0.0000% 100.0000%
2.7935% 10.6000% 0.0000% 1.2899% 98.7101% 0.0000% 0.0000% 100.0000%
2.8100% 10.7402% 0.0000% 0.0000% 100.0000% 0.0000% 0.0000% 100.0000%
Expected return on the portfolio of all 5 assets
0.23%
Risk associated with the portfolio of all 5 assets
6.71%
Market price of risk (Slope of CML)
-1.60% -0.0160113876

Q6

Market
Return Risk risk return
1.3491% 3.9301% 0.00% 0.33%
1.5945% 4.0001% 1.00% 0.50%
1.7338% 4.2000% 2.00% 0.66%
1.8279% 4.4001% 3.00% 0.83% Market price of risk (Slope of CML) 0.1643
1.9164% 4.6001% 4.00% 0.99% risk free rate 0.33%
1.9789% 4.8000% 5.00% 1.15%
2.0312% 4.9999% 6.00% 1.32%
2.0759% 5.2000% 7.00% 1.48%
2.1106% 5.4000% 8.00% 1.65%
2.1434% 5.6000% 9.00% 1.81%
2.1750% 5.8002% 10.00% 1.98%
2.2053% 6.0000% 11.00% 2.14%
2.2347% 6.2001% 12.00% 2.30%
2.2634% 6.4001% 13.00% 2.47%
2.2915% 6.6003% 14.00% 2.63%
2.3189% 6.8000% 15.00% 2.80%
2.3460% 7.0000% 16.00% 2.96%
2.3857% 7.3000% 17.00% 3.13%
2.4247% 7.6003% 18.00% 3.29%
2.4630% 7.9000% 19.00% 3.46%
2.5008% 8.2000% 20.00% 3.62%
2.5402% 8.5000% 21.00% 3.78%
2.5772% 8.8000% 22.00% 3.95%
2.6140% 9.1000% 23.00% 4.11%
2.6504% 9.4000% 24.00% 4.28%
2.6865% 9.7000% 25.00% 4.44%
2.7224% 10.0000% 26.00% 4.61%
2.7580% 10.3000% 27.00% 4.77%
2.7935% 10.6000% 28.00% 4.93%
2.8100% 10.7402% 29.00% 5.10%

GRAPH

0.0393009031542144 0.0400009719507754 0.0420004092763677 0.0440008854773404 0.0460009361463829 0.0480000297433731 0.0499993298720953 0.051999671072642 0.0539998241891924 0.0559998996183066 0.0580023802328983 0.0599999622335149 0.0620008538762859 0.0640006608589746 0.0660032344379133 0.0680004118389888 0.0700003303661607 0.0730003746835499 0.0760032079409236 0.07900021566683 0.0820001665043373 0.0850001298625881 0.0880001022205146 0.0910000811344401 0.0940000648868538 0.097000052249355 0.10000004233583 0.103000034497334 0.106000014275721 0.1074024953028 0.0134910382264232 0.0159450561415828 0.0173377136653565 0.0182785063841108 0.0191641240062513 0.0197887773124838 0.0203115899658962 0.0207585435728017 0.0211055370599426 0.0214343921007669 0.0217495813213131 0.0220528108768135 0.0223473918251693 0.0226341980259857 0.0229149573793238 0.0231894668539443 0.0234596006448006 0.0238572030595034 0.0242474274698711 0.0246304116739281 0.0250082625821242 0.0254016688400653 0.0257724952126622 0.0261397240713956 0.0265037884353697 0.0268650515933303 0.0272238210656524 0.0275803592524041 0.0279348900223976 0.0281

Risk

Return

Market

0 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.1 0.11 0.12 0.13 0.14 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.28 0.29 0.00333333333333333 0.00497633333333333 0.00661933333333333 0.00826233333333333 0.00990533333333333 0.0115483333333333 0.0131913333333333 0.0148343333333333 0.0164773333333333 0.0181203333333333 0.0197633333333333 0.0214063333333333 0.0230493333333333 0.0246923333333333 0.0263353333333333 0.0279783333333333 0.0296213333333333 0.0312643333333333 0.0329073333333333 0.0345503333333333 0.0361933333333333 0.0378363333333333 0.0394793333333333 0.0411223333333333 0.0427653333333333 0.0444083333333333 0.0460513333333333 0.0476943333333333 0.0493373333333333 0.0509803333333333

Sheet1