A company specializing in retirement planning wants to invest a client's $750,000 liquid assets potentially into bonds issued by six companies. The attached Excel file includes these investment opportunities along with their returns (expected annual yields), years to maturity (the length of time over which bonds are payable), and the ratings (based on the quality/risk of the investments).

To control the investor's risk, the company does not want to invest more than 25% of the fund in one investment. Also it wants to invest at least half of the money in long-term bonds which mature in ten or more years. In addition, it is agreed that no more than 35% of the fund should go into investments with "fair" and "good" ratings.

1. How should the company allocate the investor's money to different bonds to maximize the total return on investment (i.e., wighted average of returns)? To keep the model simple, assume that the company can reinvest into a bond when it matures. This allows us to leave the "years of maturity" out of the objective function.
2. Use the sensitivity analysis report to find out how much more would the company's investment plan yield if the investor increased the money by $100. Provide the answer to this question within a textbox in the submitted Excel file.

You just need to submit an Excel file containing the spreadsheet model and the optimal solution obtained using Excel solver. No need to submit an algebraic model.

Given DATA :

B B D Years to Company Return Rating 1 Maturity 2 Company 1 8.65% 11 1-Excellent 3 Company 2 9.50% 103-Good 4 Company 3 10.00% 64-Fair 5 Company 4 8.75% 101-Excellent 6 Company 5 9.25% 73-Good 7 Company 6 9.00% 132-Very Good 8 B B D Years to Company Return Rating 1 Maturity 2 Company 1 8.65% 11 1-Excellent 3 Company 2 9.50% 103-Good 4 Company 3 10.00% 64-Fair 5 Company 4 8.75% 101-Excellent 6 Company 5 9.25% 73-Good 7 Company 6 9.00% 132-Very Good 8