Question 5 (30 points). Given the historical returns of the four stocks below, finish the following portfolio management problem. Assume the annual risk-free rate is 3%. (1) Find the monthly mean returns and monthly variance-covariance matrix for the four stocks. (5 points) (2) Find the annual mean returns and annual variance-covariance matrix. (5 points) Using the annual mean returns and annual variance-covariance matrix to do the following questions: (3) Set up cells S18 through V18 (highlighted in yellow) in matrix formulas so that we can use Solver to find the weights for minimum-variance frontier using annual statistics. You don't need to acutually use the Solver, just need to set the cells in correct formulas (10 points) (4) Select the conditions or objectives listed in order to use Solver to solve for the weights. (type Yes or No under each condition or objective) (5 points) (5) On the Capital Allocation Line, what does it mean when the weight of optimal risky portfolio is above one? Is there a cost directly associated with this? If there is, what does this cost represent? (5 points) Answer Part (4) Here Type Yes or No under each condition or objective 21 Annual risk-free rate of retum 3% Stock Retum (1) Mean Returns 23 24 Date 25 1/3/2011 3.880% 2612/1/2011-7555% 27| N MSFT -3.425% 3.646% -3.627% 10.370% 3.225% -1.153% -2.640% 2.438% -3.253% 1.090% 1.435% 1.315% 0.501% -0.384% 1.346% -1.312% -3.228% 7.899% 2.428% -1.712% 2.163% 2.737% Walmart Target WMT TGT JNJ MSFT 1. Do we need this condition? Weights sum up to 0. -9.228% -3.797% -4.953% 1.819% 1.356% -5.446% 9.323% 0.973% -5.226% 11.009% -3.249% -2.850% -0.805% 11.523% 2.749% -0.555% 0.481% 0.479% 4.139% 6.103% -0.983% 0.451% -0.643% -3.652% -4.574% 2.078% -2.957% 3.895% 5.250% -2.322% -6.646% 6.729% -3.265% 1.478% 12.886% 7.865% 1.622% -0.740% -8.600% 4.709% -3.756% 5.151% -3.488% -4.211% 3/1/2011 4.12011 5.47996 0.826% 2. Do we need this condition? Weights sum up to 1. (1) Variance-Covariance Matrix 291 522011 1.092% 3016/1/2011-3.845% 311 7/1/2011-0804% 32 | 8/1/2011 1.62196 33| 9/1/2011-2.452% 34110/3/2011 8.878% 351 11/1/2011 3.766% 36| 12/1/2011 2.074% 371 1/3/2012 2.648% 3812/1/2012-3789% 391 3/1/2012 4.191% 40| 4.2.2012-3.810% 411 5/1/2012 11.764% 421 6/1/2012 5.752% 431 7/2/2012 6.543% 44| 8/1.2012-1.945% 45| 9/4/2012 1.630% 461 10/1/2012 1.646% WIT TGT JNJ MS WMT TGT NJ 3, Do we need this condition? Target return equals to 10%. 4. Do we need this objective? Minimize portfolio volatility (2) Annualized Mean Returns WMT TGT JNJ MSFT 5. Do we need this objective? Maximize portfolio volatility (2) Annualized Variance-Covariance Matrix WMT TGT JNJMSFT Answer Part (5) Here: WMT TGT UNJ MSFT (3) Minimum-Variance Frontier Target Portfolio Expected Return WEIGHTS OF STOCKS Sum ofAnnual Portfolio Annual Portfolio Sharpe MSFTWeightsVolatility Expected Return Ratio WMITGT JNJ 10%