PLEASE SOLVE IN EXCEL SPREADSHEEG. Managing a Portfolio. A local bank wants to build a bond portfolio from a set of five bonds with $$1$ million available for investment. The expected annual return, the worstcase annual return on each bond, and the "duration" of each bond are given in the following table. $($The duration of a bond is a measure of the bond's sensitivity to changes in interest rates.$)$

$\backslash$table$[[,$Expected Return,Worst Case Return,Duration$],[$Bond $1,12\mathrm{.}5\%,8\mathrm{.}0\%,8],[$Bond $2,11\mathrm{.}5\%,7\mathrm{.}5\%,7],[$Bond $3,10\mathrm{.}5\%,6\mathrm{.}8\%,6],[$Bond $4,9\mathrm{.}5\%,7\mathrm{.}0\%,5],[$Bond $5,8\mathrm{.}5\%,7\mathrm{.}4\%,3]]$

Chapter $9$ ExY

The bank wants to maximize the expected return from its bond investments, subject to three conditions:

The average worst$-$case return for the portfolio must be at least $7\mathrm{.}2$ percent.

The average duration of the portfolio must be at most $6.$

Because of diversification requirements, at most $40$ percent of the total amount invested can be invested in a single bond.

a$.$ What is the maximum return on the $$1$ million investment? How should the investment be distributed among the bonds to achieve this return? $($Assume that bonds can be purchased in fractional amounts.$)$

b$.$ What is the qualitative pattern in the optimal solution?

c$.$ What is the marginal rate of return on the investment amount? That is$,$ what would be the percentage return on an additional dollar invested? $($Give the percentage to four significant figures.$)$