Reconsider the portfolio selection example, including its spreadsheet model in Figure 8.13, given In Section 8.2. Note in Table 8.2 that Stock 2 has the highest expected return and stock 3 has by far the lowest. Nevertheless, the changing cells Portfolio (C14:E14) provide an optimal solution that calls for purchasing far more of Stock 3 than of Stock 2. Although purchasing so much of Stock 3 greatly reduces the risk of the portfolio, an aggressive investor may be unwilling to own so much of a stock with such a low expected return For the sake of such an investor, add a constraint to the model that specifies that the percentage of Stock 3 in the portfolio cannot exceed the amount specified by the investor. Then compare the expected retum and risk (standard deviation of the return) of the optimal portfolio with that in Figure 8.13 when the upper bound on the percentage of Stock 3 allowed in the portfolio is set at the following values 2. 20% 0.0% c. Generate a parameter analysis report using RSPE to systematically try all the percentages at intervals from 0% to 50% POURE 8.13 A spreadsheet model for the portfolio selection example of nonlinear programming, where the changing es Portfolio (C14:14) give the optimal portfolio and the objective cell Variance (C21) shows the resulting risk D 10 H Ran Name Call 1 Portfolio Selection Problem (Nonlinear Programming) Coworld DY 22 Covart3 E9 Stock Suck 2 Stock Cova23 EIO 4 Espected Retur 215 205 Covare CIENI Expecteer C19 6 RU (Sted. Dev.) 25% 459 5 MEXe Merm F19 URSE CONEJS Stick OscHundee Perce HI4 Stacks 0.040 Portfolio C14E14 0005 10 SDI C6 SD2 D6 SD E6 SD Stock I Stock 2STI 23 14 Portillo 202252200 1000 Stock C14 D14 Minimum Seks E14 Expected Sepers C4E4 1 Return Sexe 06.06 19 pece 2 185 Tocal F14 4010 Twi RELDR1152 de SUMPRODUCTS Pentelo) DORTY a selection example of nonlinear programming, where the changing celle Portfolio (C14 814) ghe the optimal portfolio and the objective cell me engency w move in we same recoil However, the negative Joul Tisk Tor die ouer Two P ninology of probability theory, the joint risk for each of two stocks is the covariance of their retu ice.) Joint Risk per Stock (Covariance) Risk (Standard Deviation) Stock Expected Return Pair of Stocks 21% 30 8 25% 45 5 1 and 2 1 and 3 2 and 3 0.040 -0.005 -0.010 g samples of the returns of the stocks from a number of previous years and then calculating the avera the expected return of a stock also may be made if it appears that the current prospects for the stock an verall variance from individual variances (the square of the standard deviations) and covariances, the tas Risk - (02557)2 + (0455>) + (0.05S; ) +2(0.04 $,$, +2(-0.00578,93 +2 (-0.011$,$ amming model for this example is Din ace uce 12 innse 122 Reconsider the portfolio selection example, including its spreadsheet model in Figure 8.13, given In Section 8.2. Note in Table 8.2 that Stock 2 has the highest expected return and stock 3 has by far the lowest. Nevertheless, the changing cells Portfolio (C14:E14) provide an optimal solution that calls for purchasing far more of Stock 3 than of Stock 2. Although purchasing so much of Stock 3 greatly reduces the risk of the portfolio, an aggressive investor may be unwilling to own so much of a stock with such a low expected return For the sake of such an investor, add a constraint to the model that specifies that the percentage of Stock 3 in the portfolio cannot exceed the amount specified by the investor. Then compare the expected retum and risk (standard deviation of the return) of the optimal portfolio with that in Figure 8.13 when the upper bound on the percentage of Stock 3 allowed in the portfolio is set at the following values 2. 20% 0.0% c. Generate a parameter analysis report using RSPE to systematically try all the percentages at intervals from 0% to 50% POURE 8.13 A spreadsheet model for the portfolio selection example of nonlinear programming, where the changing es Portfolio (C14:14) give the optimal portfolio and the objective cell Variance (C21) shows the resulting risk D 10 H Ran Name Call 1 Portfolio Selection Problem (Nonlinear Programming) Coworld DY 22 Covart3 E9 Stock Suck 2 Stock Cova23 EIO 4 Espected Retur 215 205 Covare CIENI Expecteer C19 6 RU (Sted. Dev.) 25% 459 5 MEXe Merm F19 URSE CONEJS Stick OscHundee Perce HI4 Stacks 0.040 Portfolio C14E14 0005 10 SDI C6 SD2 D6 SD E6 SD Stock I Stock 2STI 23 14 Portillo 202252200 1000 Stock C14 D14 Minimum Seks E14 Expected Sepers C4E4 1 Return Sexe 06.06 19 pece 2 185 Tocal F14 4010 Twi RELDR1152 de SUMPRODUCTS Pentelo) DORTY a selection example of nonlinear programming, where the changing celle Portfolio (C14 814) ghe the optimal portfolio and the objective cell me engency w move in we same recoil However, the negative Joul Tisk Tor die ouer Two P ninology of probability theory, the joint risk for each of two stocks is the covariance of their retu ice.) Joint Risk per Stock (Covariance) Risk (Standard Deviation) Stock Expected Return Pair of Stocks 21% 30 8 25% 45 5 1 and 2 1 and 3 2 and 3 0.040 -0.005 -0.010 g samples of the returns of the stocks from a number of previous years and then calculating the avera the expected return of a stock also may be made if it appears that the current prospects for the stock an verall variance from individual variances (the square of the standard deviations) and covariances, the tas Risk - (02557)2 + (0455>) + (0.05S; ) +2(0.04 $,$, +2(-0.00578,93 +2 (-0.011$,$ amming model for this example is Din ace uce 12 innse 122