i) Give the spreadsheet formula in cell J7. ii) Interpret row 8. iii) Determine the constraints of Alex's portfolio in terms of the Y{'s. iv) Fill in the window below with all necessary solver parameters. Solver Parameters Set Objective: To: O Max O Min O Value Of: 0 By Changing Variable Cells: Subject to the Constraints: Make Unconstrained Variables Non-Negative Select a Solving Method: GRG Nonlinear Solving Method GRG Nonlinear Simplex LP Evolutionary Add Change Delete Reset All Load/Save Options 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 v) Suggest one way to improve the search for the optimal solution. vi) With reference to the reduced gradient of 0.0157 for Y7 and Lagrange multiplier of 0.0787 for row 7 in the sensitivity report of the spreadsheet result, find the minimized variance of the portfolio's return if the expected annual rate of return for the total portfolio changes to be at least 35%, keeping all other parameters fixed. Index The Beijing-based investment firm of Hillhouse Capital specializes in recommending technology stock portfolios for wealthy clients and prohibits all clients from short selling and lending. The table below describes the seven companies that are under consideration for an investment portfolio. Company Name (Location) Expected Annual Rate of Return Standard Deviation of Annual Return 1 Tencent (Shenzhen) 80% 50% 2 Huawei (Shenzhen) 70% 40% 3 Foxconn (Shenzhen) 30% 20% 4 Ant Group (Hangzhou) 50% 50% 5 Alibaba Group (Hangzhou) 40% 80% 6 eBay (Foreign) 10% 10% 7 Amazon.com (Foreign) 20% 50% a) Susan, one of the Hillhouse Capital's clients, has an objective to maximize expected annual rate of return on investing in exactly four companies with the same amount of capital, subject to the following specifications: At least two Shenzhen companies must be in the portfolio. If Tencent stock is included in the portfolio, then Ant Group stock must also be included. Exactly one of the two Hangzhou companies must be included. No more than one investment can be made in foreign companies. Let X; be the binary variable for the ith company on the table, where x = 1 if the ith company is included in the portfolio X = 0 otherwise for i = 1,...,7. Determine the objective function and the constraints of Susan's portfolio in terms of the Xi's. b) Alex, another client of Hillhouse Capital, has up to $3 million available for investment. He would like to minimize the variance of the portfolio's return given by (0.25 +0.082 +0.162 +0.08Y2Y3 +0.04 +0.08Y4Y5 +0.64 +0.25Y +0.01% +0.08Y6Y7 +0.25), where Y is the percentage of the portfolio devoted to the i-th company on the table for i = 1, ...,7. Alex wants the expected annual rate of return for the total portfolio to be at least 30%. No individual stock can constitute more than 70% of the portfolio. The screen capture below shows the spreadsheet result of Alex's optimal portfolio. A B D E F G 1 2 3 Y1 Y2 4 Decision Variables 0.1043 0.0807 0.2186 Y3 K Y4 Y5 Y6 Y7 Objective Function Variance 0.0725 0.0196 0.5044 0.0120 5 6 LHS RHS 7 0.8 0.7 0.3 0.5 0.4 0.1 0.2 0.3 >= 0.3 8 1 1 1 1 1 1 1 1= 1 9 1 0.1043