a) Please write out the formulation clearly including decision variables, objective functions, and constraints.

b) Please input your formulation from a) into solver and solver the problem. Please show the excel screenshot of your answer.

8.6. 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 pur- chasing so much of Stock 3 greatly reduces the risk of the port- folio, 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 can- not exceed the amount specified by the investor. Then compare the expected return 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. a. 20% b. 0% AS c. Generate a parameter analysis report using Analytic Solver to systematically try all the percentages at 5% intervals from 0% to 50%. TABLE 8.2 Data for the Stocks of the Portfolio Selection Example Stock Expected Return Risk (Standard Deviation) Joint Risk per Stock (Covariance) Pair of Stocks 1 2 3 21% 30 8 25% 45 5 1 and 2 1 and 3 2 and 3 0.040 -0.005 -0.010 296 8 FIGURE 8.13 A spreadsheet model for the portfolio selection example of nonlinear programming, where the changing cells Portfolio (C14:14) give the optimal portfolio and the objective cell Variance (C21) shows the resulting risk. Cells Solier Parameters E F G H Range Name Set Objective Cell: Variance C D Al B D9 To: Min Portfolio Selection Problem (Nonlinear Programming) Covar12 By Changing Variable Cells: Covar 13 E9 Portfolio Covar23 E10 Subject to the Constraints: 3 Stock 1 Stock 2 Stock 3 C9:E11 Expected Return >= MinExpected the 4 Expected Return 21% Covariance 30% 8% Total = One Hundred Percent 5 ExpectedReturn C19 6 Risk (Stand. Dev.) 25% 45% Solver Options: 5% MinExpectedReturn E19 Make Variables Nonnegative 7 One HundredPercent H14 Solving Method: GRG Nonlineu Joint Risk (Covar.) Stock 1 Stock 2 Stock 3 Portfolio C14:E14 or Quadratic (Analytic Solver) 9 0,040 -0.005 SD1 C6 10 Stock 2 -0.010 11 Stock 3 SD2 D6 12 SD3 E6 13 Stock 1 Stock 2 StandDev Stock 3 Total C23 14 Portfolio 40.2% 21.7% 38.1% 100% = 100% Stock1 C14 15 Stock2 16 D14 Minimum Stock3 17 E14 18 Expected StockExpected Return C4:E4 Portfolio 19 Expected Return Return StockStandDev 2 C6:E6 20 18% Total F14 21 Variance 22 C21 23 13 Stock 1 18% Risk (Variance) Risk (Stand. Dev.) 15.4% F Total 14 =SUM(Portfolio B c 19 Expected Return =SUMPRODUCT(StockExpected Return Portfolio) 21 Risk (Variance) -(SD1 Stock 1 y2)+(SD2* Stock2y^2)+((SD3* Stock3y^2)+2"Covarl2*Stock l"Stock2+2"Covar 13*Stock I*Stock3+2+Covar23*Stock2*Stock 22 23 Risk (Stand. Dev.) =SQRT(Variance)