As a financial advisor, you have been asked by client to create a high-growth but risk-balanced Investment portfolio for their 5100,000 in available funds. Your goal is to maximize total percent return hut manage the risk of loss of principle of the total portfolio to no more than 30 percent. You have built the following table that summarizes your alternative investments, their likely returns over three years, and their risk of losing principle: To further hedge risk, you have established the following limitations: No more than 50 percent of the portfolio is to be invested in the riskier stocks. REITs, and corporate bonds. At least 10 percent is to be held in the highly liquid money market funds account. And finally, the ratio of investment in stock to the total investment in bonds plus annuities must be no more than 2-to-1. Using your Excel/Solver toolset, determine the investment mix that maximizes the likely 3-year return while holding the portfolio's average risk of loss of principle to 30 percent and meeting the portfolio mix constraints described above. Use the following format in Excel: Describe the formulation in the first section in freeform rows. Create a data table section with all data from the formulation in individual cells. Create a model section with cells for decision variables, objective function, and LHS and RHS columns of constraints. Find the optimal solution with Solver and write a statement saying what your investment portfolio, expected 3-year return, and risk of loss of principle will be. If you could bear more risk, where would you shift investments, and from where? As a financial advisor, you have been asked by client to create a high-growth but risk-balanced Investment portfolio for their 5100,000 in available funds. Your goal is to maximize total percent return hut manage the risk of loss of principle of the total portfolio to no more than 30 percent. You have built the following table that summarizes your alternative investments, their likely returns over three years, and their risk of losing principle: To further hedge risk, you have established the following limitations: No more than 50 percent of the portfolio is to be invested in the riskier stocks. REITs, and corporate bonds. At least 10 percent is to be held in the highly liquid money market funds account. And finally, the ratio of investment in stock to the total investment in bonds plus annuities must be no more than 2-to-1. Using your Excel/Solver toolset, determine the investment mix that maximizes the likely 3-year return while holding the portfolio's average risk of loss of principle to 30 percent and meeting the portfolio mix constraints described above. Use the following format in Excel: Describe the formulation in the first section in freeform rows. Create a data table section with all data from the formulation in individual cells. Create a model section with cells for decision variables, objective function, and LHS and RHS columns of constraints. Find the optimal solution with Solver and write a statement saying what your investment portfolio, expected 3-year return, and risk of loss of principle will be. If you could bear more risk, where would you shift investments, and from where
