Question $5$: efficient frontier

Referencing the cells in the "diversification" sheet, enter the expected return and standard deviation

for WMT and CVX in B$2$:C$3$ and the correlation between them in B$5.$

Construct two$-$asset portfolios with different weights in WMT and CVX$.$ Calculate the returns and

standard deviations of each possible portfolio in D$9$:E$43.$

In a $$Scatter with Smooth Lines$$ chart, plot the investment opportunity set. Make sure the y values

are the returns and the x values are the standard deviations. Your figure should look somewhat like

Figure $6\mathrm{.}3$ in Section $6\mathrm{.}2$ of the textbook. Here$$s how to create a scatter chart.

Find the minimum variance portfolio using the formula in $($B$18)$ of SpreadSheet $6\mathrm{.}6$ in Section $6\mathrm{.}2$

of the textbook. Enter the portfolio weight for WMT$,$ the expected return and the standard deviation

of the minimum variance portfolio in G$9$:I$9.$

Divers sLIDE$-$

MSFT F WMT CVX JNJ

E$[$Ri$]$

SDi

Correlation Matrix

MSFT F WMT CVX JNJ

MSFT

F Minimum Variance Portfolio $-$ Solver

WMT MSFT F WMT CVX JNJ Total

CVX $1\mathrm{.}000000\mathrm{.}000000\mathrm{.}000000\mathrm{.}000000\mathrm{.}00000$

JNJ

E$[$RP$]$ Var$($P$)$ SDP

Covariance Matrix #VALUE!

MSFT F WMT CVX JNJ

MSFT

F

WMT

CVX

JNJ

XTX $($bonus$)$

Efficient Frontier $-$

WMT CVX

return

S$.$D$.$

Corr$($RWMT$,$RCVX$)$

Portfolio Weights Return S$.$D$.$ Minimum variance portfolio

WWMT WCVX E$[$RP$]$ SDP WWMT$($min$)$ E$[$RP$]$ SDP

$-0\mathrm{.}21\mathrm{.}2$

$-0\mathrm{.}151\mathrm{.}15$

$-0\mathrm{.}11\mathrm{.}1$

$-0\mathrm{.}051\mathrm{.}05$

$01$

$0\mathrm{.}050\mathrm{.}95$

$0\mathrm{.}10\mathrm{.}9$

$0\mathrm{.}150\mathrm{.}85$

$0\mathrm{.}20\mathrm{.}8$

$0\mathrm{.}250\mathrm{.}75$

$0\mathrm{.}30\mathrm{.}7$

$0\mathrm{.}350\mathrm{.}65$

$0\mathrm{.}40\mathrm{.}6$

$0\mathrm{.}450\mathrm{.}55$

$0\mathrm{.}50\mathrm{.}5$

$0\mathrm{.}550\mathrm{.}45$

$0\mathrm{.}60\mathrm{.}4$

$0\mathrm{.}650\mathrm{.}35$

$0\mathrm{.}70\mathrm{.}3$

$0\mathrm{.}750\mathrm{.}25$

$0\mathrm{.}80\mathrm{.}2$

$0\mathrm{.}850\mathrm{.}15$

$0\mathrm{.}90\mathrm{.}1$

$0\mathrm{.}950\mathrm{.}05$

$10$

$1\mathrm{.}05-0\mathrm{.}05$

$1\mathrm{.}1-0\mathrm{.}1$

$1\mathrm{.}15-0\mathrm{.}15$

$1\mathrm{.}2-0\mathrm{.}2$

$1\mathrm{.}25-0\mathrm{.}25$

$1\mathrm{.}3-0\mathrm{.}3$

$1\mathrm{.}35-0\mathrm{.}35$

$1\mathrm{.}4-0\mathrm{.}4$

$1\mathrm{.}45-0\mathrm{.}45$

$1\mathrm{.}5-0\mathrm{.}5$