Im having a bit of trouble with excel, please help me with the formulas . im not sure if my answers are correct with the formulas i used. thnx

In the worksheet "Data", first compute average return and standard deviation for each asset. The structure of the worksheet "Data" easily allows you to program the average return and standard deviation. You can then transfer the computed mean and stan- dard deviation onto worksheet "Estimation" . Please make sure that you annualize both the returns and standard deviations. In case of returns, please take into account compounding when annualizing the returns For each of the two analyses listed below, perform the portfolio allocation i.e., generate the portfolio opportunity set. You can use the Sheet "Estimation" for this purpose First, you should compute the variance-covariance matrix and then find the weights for each level of the return provided in table "Weights" such that you have minimum standard deviation. Each of the return level is indicated in annual terms and is already u need to be careful and adjust your computations accordingly. . Once you perform the optimization for each return level, you have to use the scatterplot function to draw the portfolio frontier in (annualized) return/standard deviation space, using the results in the worksheet "Data" under table "Weights". You can also graph the return/standard deviation combinations for the 10 assets, using the average return and standard deviation computed earlier. Please use the sheet "Results" to present your graphs as well as final estimates. Repeat this for each of the two different cases listed helo Now, we are ready to setup the solver. 1. First, select any cell in your sheet and program that cell to be the sum of all weights. The reason we need that cell is because we need to constrain it to be equal to 1. In excel, constraints can only be specified by refering to a cell. 2. Now, select solver. Choose the cell where you computed the portfolio standard deviation and choose min, since we want to minimize variance. In the add constraints part, add the constraint that the expected return of the portfolio (choose the cell where you have that) is equal to 6 (i.e., 6 percent). 3. Add another constraint that the sum of the weights is equal to 1 i.e., Coose the cell where you have sum of weights and you want that to be 1 and run the solver. The solver will say it has found a solution. Copy the portfolio risk that you found for return level 6. Also, copy the corresponding weights. Rapeat step 2 and3 for espectedetu lovel 7 GAe, 7 percent eospected portolio reurn) Similarly, doit for all other specified returns levels. 5. This will give you the lowest possible risk for each return level. You need to graph is and standard deviat Finally, for the exercise with short sale constraints, you need to ensure that weights can't be negative. So, you need to put this constraint in solver and repeat steps 2 to 5 above again. 2.2 Setting up the Solver In order to setup the solver for the optimization, the final step is to assume some weights and compute the portfolio variance for this assumption. We can start with the assumption of equal weights. So, set the weights of each asset equal to T Given these weights, compute the n x n matrix titled Bordered Covariance Matrix for Target Returns. Each term in this matrix is defined as w.wjcov(^i,T). Note that, we comptued cov(i,Ti) in the previous subsection, so all we need to do is apply this formula for each cell given the weights. Once you have computed this bordered covariance n x n matrix (which is 10 x 10 since we have 10 assets) the sum of all the elements of this matrix gives you the portfolio variance. So, sum all elements to compute the portfolio variance. Compute the square root of this cell to get the portfolio standard deviation. Similarly, compute the portfolio expected return using the formula from previous sub- section Elr_ ui E [ri) given your assumed equal weights. This step completes your setup to compute portfolio expected return and risk. 10 Now, we are ready to setup the solver Durbl 5 0.0194 0.0644 0.0678 0.0191 6 0.04750.00890.0404 0.0478 0.045 0.0401 0.05080.0382 0.03640.045 0.03710.01790.0029 0.0065 0.02490.0646 0.0165 0.0533 0.0647 0.0352 8 0.02410.0113 0.0402 0.02970.0121 90.0570.0362 0.0403 -0.0704-0.0466 0.0121 10 11 0.03260.01010.0009 0.0244 0.01180.0084 0.0009 0.02680.0057-0.0256 120.0142 13 0.0344 0.0064 0.01280.0087 0.01210.0345 0.0397 14 0.0117 15-0.04310.04630.04810.03580.0286 0.0035 0.0046-0.06480.0230.0033 16 17 -0.0008 0.09420.0309-0.08020.0095 0.01310.017 18 0.0792 0.0527 0.0524 0.0278 0.0307 0.01540.01150.0025 0.03970.00940.003 0.00940.0188 0.01280.0286 0 0.0415 0.00320.0296 0.0427 0.0052 0.0539 0.05150.0543 0.01440.0008 0.0303 0.0576 0.07460.0267 200.0236 0.00580.006 0.0041 0.00640.01310.0173 0.0185 0.0159 0.0088 0.003 0.0156 0.0913 0.0437 0.0555 0.0431 0.0264 0.0386 0.0309 0.0205 0.04440.0093 0.09870.0257 0.0141 210.00550.0419-0.04030.0439 -0.03530.0337 0.0489 0.0118 22 0.0503 23 0.0243-0.004 24 0.00810.00930.00710.0694-0.0046 0.0139 0.01550.02640.0293 0 0.0245 0 0.02180.0139 0.0103 0.01140.05070.0066 0.0201 0.0092 | 0.0488 260.0132-0.0149-0.02020.0384 -0.01610.0355 0.0049 0.0537 27 0.04790.0217 -0.01970.0897 0.0242 0.0237 0 280.04180.0613 -0.04850.0363 290.0062-0.0323 0.04780.0366 0.05830.0842 -0.04010.0534 -0.0835 0.05810.0386 0.01430.0261-0.0056-0.0181-0.0729 0.0452 0.0772 0.0141 0.0261 0.0827 0.0207 31 0.0098 0.01940.0059 0.0109 0.0029 0.0116 0.01920.0345 0.0085 0.0071 32 0.00790.0403 0.037-0.0968-0.00780.02520.03020.0236 0.00290.0038 33 0.00110.12530.0612 -0.04630.0470.0605 0.0036 0.04620.0417 0.0942 34 0.0058 0.0409 0.03820.029 0.0285 0.0095 0.0117 0.0172 0.0004-0.0106 35 36 37 0.00720.0111-0.007 38 0.04680.0569 390.0005 0.0864 0.1076 0.0792 0.01740.04090 0.00670.0509 0.006 0.0356 0.0099 0.0247 0.0084 0.0014 0.02280.00220.03490.0601 0.0189 0.0312 0.0737 0.04740.02790.0283 0,07??- 0,0312 0,0224- - 006 . O0292 0.0909 0.0956 0.0078 0.0054 0.0627-0.0284 0.0445 0.0138 2 2 0.0163 0.0277 0.03230.0432 0.0381 0.0471 0.0373 0.0068 0.04090.0236 0.0419 0 0.002-0.0121 0.0085 0.0217 0.0084 0.0032 3 0.0051 0.0172 0.0270.0282 0.0029 0.02790.0011 0.0036 0.027 0.0308 -0.0052 0.01590.0346 0.0083 0.0425 0.0194 0.0258 0.01610.0043 0.0012-0.0111 0.0227 0.0171-0.0016 0.0035-0.0520.0117 0.0303 -0.02560.0221 0.01660.0178 0.0033 0.0529 0.0503 0.11130.02340.00590.0168 -0.0196 0.025 0.015 0.00920.0684-0.0574 0.0269-0.0224 1 0.0337 0.0295 0.0379 0.0108 0.0372 0.0241 0.0201-0.00830.0249 0.0511 0.0177-0.0022 0.0459-0.0501 0.0244 0.0023 0.01910.02530.0617 0.0283-0.0053 0.0763 0.0318 -0.0281 0.0107-0.0559 -0.03170.02960.0059 0.0331 -0.0252 0.0321-0.02540.02 0.03960.0013 -0.0359 0.1014 0.0278 -0.0012 0.0241 0.0277 0.039 0.0028 2 0.0142 0.0587 0.0107 0.0394 0.0224 0.0268 0.0173 0.03160.0612 0.0104-0.0052 0.0091-0.029 0.01140.0733 0.0295 0.0091 2 Bordered Covariance Matrix for Target Returns NoDur Manuf Enrgy Chems BusEq Telcm Utils Shops Hlth Weights NoDur Manuf Chems BusEq Telcm Utils Shops Hlth Port Var Port S.D. Port Mean Weights Mean Port S. D NoDur Durbl Manuf En Chems Bus Eq Telcm Hlth 10 12 13 14 15 16 18 Correlation Matrix Chems BusEq Telcm Utils Shops Hlth NoDur Durbl Manuf Enrgy NoDur Durbl Manuf Enrgy Chems BusEq Telcm Utils Shops Hlth Covariance Matrix Chems BusEq Telcm Utils Shops Hlth NoDur Durbl Manuf Enrgy NoDur Durbl Manuf Enrgy Chems BusEq Telcm Utils Shops Weights (Case A) Utils Shops Hith Manuf Enrgy Chems Telcm Port S. D Durbl 10 12 13 14 15 16 18 20 Weights-Case B Telcm Utils Shops Hith Manuf Enrgy Chems BusE Port S. D NoDur Durbl 10 12 13 14 15 16 18 20