Please, I need help with this assignment by the end of the day.

Problem # RF = $C$67 Market Risk Premium = $G$67 Min Weight = $F$86 Max Weight = $I$86 Weight on Portf A = $C$94 Portfolio Investment = $F$98 1 2 3 4 5 6 0.040% 0.040% 0.050% 0.050% 0.060% 0.060% 0.490% 0.500% 0.520% 0.530% 0.550% 0.560% -20.0% -19.0% -18.0% -17.0% -16.0% -15.0% 25.0% 26.0% 27.0% 28.0% 29.0% 30.0% 80.0% 82.5% 85.0% 87.5% 90.0% 92.5% 1,500,000 1,660,000 1,820,000 1,980,000 2,140,000 2,300,000 7 8 9 10 11 12 13 14 15 0.070% 0.070% 0.080% 0.080% 0.090% 0.100% 0.100% 0.110% 0.110% 0.580% 0.600% 0.610% 0.620% 0.640% 0.660% 0.670% 0.690% 0.700% -14.0% -13.0% -12.0% -11.0% -10.0% -9.0% -8.0% -7.0% -6.0% 31.0% 32.0% 33.0% 34.0% 35.0% 36.0% 37.0% 36.0% 35.0% 95.0% 97.5% 100.0% 102.5% 105.0% 107.5% 110.0% 112.5% 115.0% 2,460,000 2,620,000 2,780,000 2,940,000 3,100,000 3,260,000 3,420,000 3,580,000 3,740,000 16 17 18 19 20 21 22 23 24 0.120% 0.130% 0.140% 0.150% 0.160% 0.170% 0.180% 0.190% 0.200% 0.710% 0.720% 0.730% 0.740% 0.750% 0.760% 0.770% 0.780% 0.790% -8.0% -10.0% -12.0% -13.0% -14.0% -15.0% -16.0% -17.0% -18.0% 34.0% 33.0% 32.0% 31.0% 30.0% 29.0% 28.0% 27.0% 26.0% 117.5% 120.0% 122.5% 125.0% 127.5% 130.0% 132.5% 135.0% 137.5% 3,900,000 4,060,000 4,220,000 4,380,000 4,540,000 4,700,000 4,860,000 5,020,000 5,180,000 25 26 27 28 29 30 0.210% 0.220% 0.230% 0.240% 0.250% 0.260% 0.800% 0.810% 0.820% 0.830% 0.840% 0.850% -19.0% -20.0% -21.0% -22.0% -23.0% -24.0% 27.0% 28.0% 29.0% 30.0% 31.0% 32.0% 140.0% 142.5% 145.0% 147.5% 155.0% 152.5% 5,340,000 5,500,000 5,660,000 5,820,000 5,980,000 6,140,000 Portfolio Assignment 1. Calculate all of the portfolio statistics: a. Browse through the sheet. There is a vector named Ret. These are the mean returns over the period. We will use these as the proxy expected return for each stock. b. Several lines below this are the correlation matrix and the covariance matrix. The relevant portion of the covariance matrix is named VCOV. c. Below VCOV are three vectors named Wgt, Wgt_A, and Wgt_B. These are the weights for the assets in three separate portfolios. These are in lines 84, row 87, and row 88. You must compute the mean (expected return) and standard deviation for each of these three portfolios in the designated spots using matrix functions. (Use CAPM for returns) d. First, calculate the return and risk of the equal weighted portfolio. 2. Below the equal weighted Portfolio you will see Portfolio A and Portfolio B. Compute the return and risk of each of these portfolios. Of course you will get same answer as the equal weighted portfolio since they all have the same weights right now. Next, set up and run Solver twice; once for portfolio A, with the objective of maximizing return, and once again for portfolio B, minimizing the Standard deviation. Your changing cells are the vector of weights (Wgt_A and Wgt_B respectively) and you need a constraint that the weights add to 100%. Add a constraints to limit the weights as indicated in row 86 3. Calculate the covariance and correlation between Portfolio A and B. 4. Calculate the Return and standard deviation of a Combined Portfolio made up of Wa of Portfolio A and (1-Wa%) of portfolio B. 5. Create a Data Table of Risk and Return on that complete portfolio altering the weight in portfolio A. Go from -100% to 250% by 10% increments. 7. Make a table (NOT a Data Table) of other points you should have on your chart. 8. Make a Chart that plots all of the results. a. Begin with making a chart of the efficient frontier. b. Next add all of the individual assets. c. Next add the two efficiently optimized portfolios (A and B) d. Next, add the Market Index. e. Finally, add the optimal portfolio (from step 4). 9. Use the Combined Portfolio on the efficient frontier for you client. Assume your client has just given you money to invest in this portfolio. The amount should be in cell TotalInvestment. a. b. c. d. e. Compute the weight on each stock that will end up in the final portfolio. Look up the stock prices and enter them into the table Compute the Dollar Value to be invested in each stock. Compute the number of shares of each stock that you need to buy to form this portfolio. As you change Wa, you should be able to see this portfolio slide along the efficient frontier. 10. 11. Plot the CAL that goes through the Client Portfolio. Compute the Wa that maximized the Sharpe Ratio. fficient frontier. qattachments_4a1cdf45243be504d4ca90c4c3cc76f149b1bde6.xlsx Equal Weighted Portf Ret Portf StdDev 10% Portf Ret 01/29/2017 Portf StdDev Portf _A Portf _B 10% 10% Min Weight = 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10% 10% 10% 10% 10% 10% Max Weight = 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% 10.0000% Covariance Correlation Combined Portfolio(C) Weight on Portf A Ret Std Dev Calc Num Shares Weight in Final Portf. $ Value in Final Portf. Price per share Number of Shares (Round down nearest hundred) Data Table Portfolio Investment = GM ABT 35.45 45.23 SJM 117.53 DOW 51.49 AA LMT 9.39 219.24 NEM 19.00 PFE 34.19 JNJ 101.87