Please see the attachment for Question details.

A portfolio manager in charge of a bank portfolio wants to invest a sum of $12 million in a number of bonds. The bonds available for purchase, as well as their respective quality ratings, maturities, and yields, are shown in the following table: Name Type Rating Bank Rating Maturity(Yrs.) Yield to Maturity AfterTax Yield A Municipal AA 2 10 4.4% 4.4% B Agency AA 2 15 5.5% 2.8% C Government AAA 1 5 5.1% 2.6% D Government AAA 1 4 4.3% 2.1% E Municipal BB 5 3 4.7% 4.7% The bank places the following policy limitations on the portfolio manager's actions: - Government and agency bonds must total at least $4 million. - No more than $8 million can be invested in any one bond. - The average quality of the portfolio can not exceed 1.4 on the bank's quality scale (on this scale, a lower number means a higher-quality bond). - The average number of years to maturity of the portfolio can not exceed 5 years. For a portfolio, average portfolio-quality rating and average portfolio maturity are dened as a weighted average, where the weight assigned to each bond type is proportional to the dollar investment in that bond. For the questions below, think of the after-tax yield of the portfolio as essentially the yearly return on the investment. This will serve as a good rst approximation. Assume you can invest any dollar amount in any of the bonds. (a) Assume that the objective of the portfolio manager is to maximize after-tax yearly proceeds (e.g., a 3.0% after-tax yield on a $4 million investment translates into a $0.12 million in yearly proceeds). Formulate an optimization model in Excel and solve it. Describe the optimal solution, which bonds are bought, in what quantities, etc. (b) Include a copy of your model in the spreadsheet answer le