3 of 6 a 3 a + ++ D Page view | A Read aloud | Draw Highlight Erase Question 2 Give the spreadsheet formula in cell 7 ID Interpretowa. i) Determine the constraints of Aler's portfolio in terms of the Y's. Fil in the window below with all necessary so ver parameters The Beline-based Investment firm of filhouse Capital specializes in recommending technology stock portfolios for wealthy clients and prohibits alicients from shortseling and lending. The table below describes the seven companies that are under consideration for an investment portfolio Company Name Expected Annual Standard Deviation Index Location Rate of Return of Annual Return [Sterohen ACT 2 Huawei (Shenzhen! 70% 40% 1 Foron Sheroher) 20% 4 Ant Group Hangzhou 50% 50% 5 Alibaba Group langzhou Ray Foreign 105 10% Amazon.com (Fores 20% 50% Trece Solved Parameters Se jete OM O value on . D Changing Variable Calle + Subject to the Constant Add a) Sun, one of the Hilhouse 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 Serwhen companies must be in the portfolio if Tencer stock is included in the portfolin, then Ant Group stock must akn be induced reactly one of the two Hangrhou companies must be induced No more than one investment can be made in foreign companies Let X be the binary variable for theith company on the table where X-1 if theith company is included in the portfolio x X-otherwise for 1.7. Determine the objective function and the constraints of Susan's portfolio in terms of the X's. Change Delete Reset AB b) Alex, another dient of Hilhouse Capital, is up to $3 million available for investment. He would like to minimize the variance of the portfolio's return venty (U. SY +0.07V +0.167,7 +0.00 +0.04 +0.25 +0.081.75 +0.6473 +0.0172 +0.081 Y3 +0.251). where is the percentage of the portfolio devoted to the -th company on the table for t = 1....7. Alex wants the expected antal rate of retum for the total portfolio to be at least 308. No individual stock can constitute more than ox of the portfolio. The screen capture below shows the spreadsheet result of Alex's optimal portfolio Load/Save Make Uncentrained variables Non-Negative Select a Seling GRG Nonlinear Method Option : CRG Nonlinex Simples LP Solving Method tvolution Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear Select the LP Simples engine finearthem, and let the Tetinary engine for problems that are non-inseth C D H Help Salve Close YA VE Ya Y 77 D.10400 D.2186 0.025 0.0% 0.5044 DOL Once union L2 0 00122 UNS HS 0 05 14 1 0.3 1 01 1 2 BZ 1 03 3 1 1041 v) Suggest one way to improve the search for the optimal solution. vil 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 minimised variance of the portfolio's retum if the expected annual rate of return for the total portiollo changes to be at least 35%, keeping all other parameters foed. 1 031 0. 0. 03 0. 0216 0.0035 Dass . 1 13 14 15 3 of 6 a 3 a + ++ D Page view | A Read aloud | Draw Highlight Erase Question 2 Give the spreadsheet formula in cell 7 ID Interpretowa. i) Determine the constraints of Aler's portfolio in terms of the Y's. Fil in the window below with all necessary so ver parameters The Beline-based Investment firm of filhouse Capital specializes in recommending technology stock portfolios for wealthy clients and prohibits alicients from shortseling and lending. The table below describes the seven companies that are under consideration for an investment portfolio Company Name Expected Annual Standard Deviation Index Location Rate of Return of Annual Return [Sterohen ACT 2 Huawei (Shenzhen! 70% 40% 1 Foron Sheroher) 20% 4 Ant Group Hangzhou 50% 50% 5 Alibaba Group langzhou Ray Foreign 105 10% Amazon.com (Fores 20% 50% Trece Solved Parameters Se jete OM O value on . D Changing Variable Calle + Subject to the Constant Add a) Sun, one of the Hilhouse 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 Serwhen companies must be in the portfolio if Tencer stock is included in the portfolin, then Ant Group stock must akn be induced reactly one of the two Hangrhou companies must be induced No more than one investment can be made in foreign companies Let X be the binary variable for theith company on the table where X-1 if theith company is included in the portfolio x X-otherwise for 1.7. Determine the objective function and the constraints of Susan's portfolio in terms of the X's. Change Delete Reset AB b) Alex, another dient of Hilhouse Capital, is up to $3 million available for investment. He would like to minimize the variance of the portfolio's return venty (U. SY +0.07V +0.167,7 +0.00 +0.04 +0.25 +0.081.75 +0.6473 +0.0172 +0.081 Y3 +0.251). where is the percentage of the portfolio devoted to the -th company on the table for t = 1....7. Alex wants the expected antal rate of retum for the total portfolio to be at least 308. No individual stock can constitute more than ox of the portfolio. The screen capture below shows the spreadsheet result of Alex's optimal portfolio Load/Save Make Uncentrained variables Non-Negative Select a Seling GRG Nonlinear Method Option : CRG Nonlinex Simples LP Solving Method tvolution Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear Select the LP Simples engine finearthem, and let the Tetinary engine for problems that are non-inseth C D H Help Salve Close YA VE Ya Y 77 D.10400 D.2186 0.025 0.0% 0.5044 DOL Once union L2 0 00122 UNS HS 0 05 14 1 0.3 1 01 1 2 BZ 1 03 3 1 1041 v) Suggest one way to improve the search for the optimal solution. vil 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 minimised variance of the portfolio's retum if the expected annual rate of return for the total portiollo changes to be at least 35%, keeping all other parameters foed. 1 031 0. 0. 03 0. 0216 0.0035 Dass . 1 13 14 15