Create the spreadsheet below. The spreadsheet should use the returns for assets A and B to form a portfolio return using the weights for each asset shown in cells E1 and E2. The average portfolio return and standard deviation should compute at the bottom of the column of portfolio returns. When you change the weights, the portfolio returns, average, and standard deviation should recalculate.

Create the spreadsheet. Use the Solver function to find the weights that provide the highest return for a standard deviation of 6%, 7.5%, 9%, 10.5%, 12%, and 13.5%. Report the weights and the return for each of these portfolio standard deviations. The Solver function is found in the Data tab. (You may have to enable the function through the File tab, then Options, then Add-ins.) The solver image illustrates the maximizing of the average return for the specific constraints. The constraints are that the weights must be between 0 and 1, inclusive, and must sum to 1. Lastly, set the standard deviation constraint to the desired level.

Transcribed Image Text:

Weight A- Weight B - 0.50 9.1% 11.9% -22.1% 28.7% 10.9% 4.9% 15.8% 3.5% -5.5% 23.45% 15.06% 2.11% 16.00% 32.39% 13.69% 20.11% 4.56% 7.17% 2.06% 7.70% -650% 1.85% 9.81% 22.7% -12.19% 9.38% 29.93% 3.56% -12.66% 15.07% Portfolio 5.51% 8.23% -7.47% 15.38% 9.30% -0.80% 8.82% 6.66% 8.60% 5.63% 12.22% 16.02% 9.78% 9.87% 14.38% 0.50 9.490 6.8% Average Average- 8.1% 14.41% 12.04% -St Dev StDev 6.07% Solver Parameters Set Objective SFS18 By Changing Variable Cells: SES1:SES2 Subject to the Constraints: SESI <= 1 SES10 SES2 <= 1 SES2 >= 0 SES3 1 SFS19 = 0.06 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solng GRGNonlinear Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close