The Case Rachel Harris is a Charted Financial Analyst (CFA) working at a large w

Question

The Case

Rachel Harris is a Charted Financial Analyst (CFA) working at a large wealth management company. One of her clients just inherited $250,000. This investor believes that the industry leaders will provide safe and stable returns. He also believes benefits of diversification. Based on his investing preference, Rachel identifies the following 5 stocks:

Wal-Mart (WMT) (Data Below)

Citigroup (C) (Data Below)

Johnson and Johnson (JNJ) (Data Below)

IBM (IBM) (Data Below)

Boeing (BA) (Data Below)

As an assistant to Rachel, you are asked to collect the monthly prices of each stock from Yahoo Finance from 11/23/2010 to 11/2/2015 (market booming time period), and then finish the following tasks:

1. Construct the covariance matrix of these five stocks (please explain and show your work and formula)

2. Use Excel Solver function to build the efficient frontier (with and without short sale)

3. Determine the optimal risk portfolio

4. Draw a graph showing the efficient frontier and the CAL line (one graph for the case with short sale and one graph without short sale

BA

IBM

JNJ

C

WMT

Date Open High Low Close Volume Adj Close Monthly returns 11/23/2010 63.43 65.580002 63.099998 63.77 6213200 55.81702 12/1/2010 64.660004 67.389999 62.82 65.260002 4820300 57.121201 2.34% 1/3/2011 66.150002 72.989998 66 69.480003 6661000 60.814911 6.47% 2/1/2011 70 74.290001 69.599998 72.010002 5041500 63.39558 4.24% 3/1/2011 71.93 74.470001 67.339996 73.93 5390900 65.085892 2.67% 4/1/2011 74.290001 80.339996 70.879997 79.779999 5159000 70.236069 7.91% 5/2/2011 80.349998 80.650002 75.129997 78.029999 4390300 69.058197 -1.68% 6/1/2011 77.949997 78.099998 70.290001 73.93 4908400 65.429611 -5.25% 7/1/2011 73.809998 76.199997 69.07 70.470001 5619100 62.367443 -4.68% 8/1/2011 71.610001 71.629997 56.009998 66.860001 8770600 59.573875 -4.48% 9/1/2011 66.959999 67.730003 57.529999 60.509998 6200600 53.915871 -9.50% 10/3/2011 60.099998 68.760002 56.900002 65.790001 6395400 58.62048 8.73% 11/1/2011 64.230003 68.970001 62.119999 68.690002 5866900 61.594707 5.07% 12/1/2011 68.699997 74.739998 68.510002 73.349998 5684400 65.773354 6.78% 1/3/2012 74.699997 76.699997 72.739998 74.18 5428200 66.517616 1.13% 2/1/2012 74.959999 76.739998 74.300003 74.949997 4448400 67.60363 1.63% 3/1/2012 74.959999 75.809998 72.300003 74.370003 4219800 67.080482 -0.77% 4/2/2012 74 77.57 70.589996 76.800003 4555000 69.272301 3.27% 5/1/2012 76.589996 77.830002 68.93 69.610001 4194600 63.155594 -8.83% 6/1/2012 68.669998 74.370003 66.82 74.300003 4421400 67.410728 6.74% 7/2/2012 74.209999 75.959999 70.849998 73.910004 4330600 67.056892 -0.52% 8/1/2012 74.32 74.690002 70.019997 71.400002 3868000 65.168098 -2.82% 9/4/2012 71.5 73.269997 69.029999 69.599998 5638500 63.525204 -2.52% 10/1/2012 69.860001 75 69.199997 70.440002 4565300 64.291893 1.21% 11/1/2012 70.589996 74.860001 69.300003 74.279999 5022200 68.21347 6.10% 12/3/2012 74.410004 76.559998 72.93 75.360001 4749300 69.205269 1.45% 1/2/2013 76.550003 78.019997 72.68 73.870003 9497200 67.83696 -1.98% 2/1/2013 74.489998 77.93 74.230003 76.900002 5632700 71.07312 4.77% 3/1/2013 76.699997 86.839996 76.169998 85.849998 5513700 79.344955 11.64% 4/1/2013 85.599998 93.379997 83.800003 91.410004 5049700 84.483665 6.48% 5/1/2013 91.080002 101.470001 90.720001 99.019997 5265400 91.987694 8.88% 6/3/2013 99.360001 104.150002 96.309998 102.440002 5257200 95.16481 3.45% 7/1/2013 102.93 109.489998 98.989998 105.099998 7149400 97.635895 2.60% 8/1/2013 105.989998 107.949997 101.769997 103.919998 3751700 96.979118 -0.67% 9/3/2013 105.129997 120.379997 104.620003 117.5 4666300 109.6521 13.07% 10/1/2013 117.970001 131.880005 113.339996 130.5 4652400 121.783821 11.06% 11/1/2013 130.899994 142 130.580002 134.25 4496500 125.739922 3.25% 12/2/2013 134.990005 138.880005 130.550003 136.490005 3846400 127.837936 1.67% 1/2/2014 136.009995 144.570007 123.080002 125.260002 5890600 117.319801 -8.23% 2/3/2014 124.239998 130.940002 118.769997 128.919998 6544600 121.428818 3.50% 3/3/2014 127.18 131 121.370003 125.489998 4798100 118.198128 -2.66% 4/1/2014 126.239998 131.5 121.919998 129.020004 3844700 121.52301 2.81% 5/1/2014 128.740005 135.440002 128.270004 135.25 3060700 128.105804 5.42% 6/2/2014 135.5 138.389999 126.199997 127.230003 4022200 120.509438 -5.93% 7/1/2014 127.660004 130.580002 120.410004 120.480003 5336600 114.11599 -5.31% 8/1/2014 120.449997 129.130005 117.870003 126.800003 3918300 120.829506 5.88% 9/2/2014 125.620003 129.869995 124.099998 127.379997 3779200 121.382187 0.46% 10/1/2014 127.370003 128 116.32 124.910004 4873500 119.028496 -1.94% 11/3/2014 125.349998 135.779999 123.370003 134.360001 3921200 128.780594 8.19% 12/1/2014 134.309998 134.399994 120.580002 129.979996 4524900 124.582466 -3.26% 1/2/2015 131.070007 148.25 126.18 145.369995 6018000 139.333389 11.84% 2/2/2015 143.720001 158.830002 143.050003 150.850006 5096900 145.480103 4.41% 3/2/2015 150.850006 156.910004 145.960007 150.080002 3844600 144.737503 -0.51% 4/1/2015 149.970001 155.5 142.75 143.339996 4258700 138.237427 -4.49% 5/1/2015 144.410004 148.300003 140.320007 140.520004 4275200 136.386276 -1.34% 6/1/2015 141.449997 146.529999 138.440002 138.720001 3359000 134.639221 -1.28% 7/1/2015 140.479996 149.179993 139 144.169998 3331800 139.928894 3.93% 8/3/2015 144.440002 146.770004 115.139999 130.679993 4620300 127.644966 -8.78% 9/1/2015 128.160004 139.910004 126.940002 130.949997 4016800 127.908699 0.21% 10/1/2015 131.320007 149.600006 128.559998 148.070007 4363000 144.631104 13.07% 11/2/2015 148.380005 149.470001 147.559998 148.399994 5033400 144.953415 0.22% Average return 1.75% Variance 0.003076

Explanation / Answer

Ans;

Average

WMT IBM BOEEING CITI J&J

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.