PLEASE HELP WITH THE QUESTIONS BELOW:

1) Provide the background of the problem you are attempting to resolve.

2) Create a mathematical equation for the problem on the first tab of your Excel Worksheet

3) Solve the problem using Solver (linear programming) on the second tab of your Excel Worksheet.

4) Provide a management report (discuss results, sensitivity analysis, and provide recommendations) for your findings in a Word Document. Students should use screenshots to insert both the Solver solution and the sensitivity analysis in the management report completed in Word.

5) Both the Solver Workbook and Word document should be submitted in Blackboard using the provided link.

Case Study II - Retirement Planning Services Numerous problems in the area of finance can be addressed by using the various optimization techniques. These problems often involve attempting to maximize the return on an investment while meeting certain cash-flow requirements and risk constraints. Alternatively, we may want to minimize the risk on an investment while maintaining a certain level of return. We'll consider one such problem here and discuss several other financial engineering problems throughout this text. Brian Givens is a financial analyst for Retirement Planning Services, Inc., who specializes in designing retirement income portfolios for retirees using corporate bonds. He has just completed a consultation with a client who expects to have $750,000 in liquid assets to invest when she retires next month. Brian and his client agreed to consider upcoming bond issues from the following six companies: The column labeled "Return" in this table represents the expected annual yield on each bond, the column labeled "Years to Maturity" indicates the length of time over which the bonds will be payable, and the column labeled "Rating" indicates an independent underwriter's assessment of the quality or risk associated with each issue. Brian believes that all of the companies are relatively safe investments. However, to protect his client's income, Brian and his client agreed that no more than 25% of her money should be invested in any one investment and at least half of her money should be invested in long-term bonds that mature in 10 or more years. Also, even though BynaStar, Eagle Vision, and Qptipre offer the highest returns, it was agreed that no more than 35% of the money should be invested in these bonds because they also represent the highest risks (that is, they were rated lower than "very good" ). Brian needs to determine how to allocate his client's investments to maximize her income while meeting their agreed upon investment restrictions. Case Study II - Retirement Planning Services Numerous problems in the area of finance can be addressed by using the various optimization techniques. These problems often involve attempting to maximize the return on an investment while meeting certain cash-flow requirements and risk constraints. Alternatively, we may want to minimize the risk on an investment while maintaining a certain level of return. We'll consider one such problem here and discuss several other financial engineering problems throughout this text. Brian Givens is a financial analyst for Retirement Planning Services, Inc., who specializes in designing retirement income portfolios for retirees using corporate bonds. He has just completed a consultation with a client who expects to have $750,000 in liquid assets to invest when she retires next month. Brian and his client agreed to consider upcoming bond issues from the following six companies: The column labeled "Return" in this table represents the expected annual yield on each bond, the column labeled "Years to Maturity" indicates the length of time over which the bonds will be payable, and the column labeled "Rating" indicates an independent underwriter's assessment of the quality or risk associated with each issue. Brian believes that all of the companies are relatively safe investments. However, to protect his client's income, Brian and his client agreed that no more than 25% of her money should be invested in any one investment and at least half of her money should be invested in long-term bonds that mature in 10 or more years. Also, even though BynaStar, Eagle Vision, and Qptipre offer the highest returns, it was agreed that no more than 35% of the money should be invested in these bonds because they also represent the highest risks (that is, they were rated lower than "very good" ). Brian needs to determine how to allocate his client's investments to maximize her income while meeting their agreed upon investment restrictions