Question $1(35$ marks$)$

A financial planner tries to determine how to invest $1$ million dollars for one of his clients in the next $5$ years. The cash flows for five investments under consideration are summarized in Table $1.$

Table $1.$ Summary of cash in$-$flows and out$-$flows $($at the beginning for year$)$

Investment Year $1$ Year $2$ Year $3$ Year $4$ Year $5$

A $-10\mathrm{.}050\mathrm{.}051\mathrm{.}05$

B $-11\mathrm{.}2$

C $-10\mathrm{.}051\mathrm{.}05$

D $-11\mathrm{.}12$

E $-11\mathrm{.}1$

For example, if the financial planner invests $$1$ in investment A at the beginning of year $1,$ she will receive an $(5\%)$ interest of $$0\mathrm{.}05$ at the beginning of each of year $3$ and year $4.$ At the beginning of year $5,$ the principal and $(5\%)$ interest will be returned as $$1\mathrm{.}05.$ In view of escalating healthcare costs in the coming years, the client has just purchased health insurance and would need to pay his annual health insurance premium starting from year $2.$ The payment for year $2$ has been predetermined at $$2,500.$ Thereafter, the premium increase rate per year is $8\%$ from the previous year based on projected medical inflation. Assume the annual insurance premium is to be paid at the beginning of a year. To help the client maximize the amount of money available at the end of year $5,$ the financial planner has formulated the problem by a linear program. The initial layout of the spreadsheet model is given in Table $2.$ On completion and model solved, the Sensitivity Report from Excel Solver is shown in Table $3.$

$($a$)$ What is the optimal investment plan for the next five years? $(3$ marks$)$

$($b$)$ What is the expected maximum amount of money available at the end of year $5?(2$ marks$)$

$($c$)$ Is there any alternative optimal plan? Explain. $(2$ marks$)$

$($d$)$ Apart from the last year $($year $5),$ is there any year with excess cash left after $($re$-)$investment and payment for the annual health insurance premium? If there is$,$ how much is left? Why is money left uninvested which would imply opportunity loss? Explain.$(3$ marks$)$

$($e$)$ What is the impact of the change in budget $($currently $1$ million dollars$)$ on the maximum amount of money available at the end of year $5?$ State the range of the budget in which this impact can be predicted by the given information without resolving the model. Explain. $(5$ marks$)$

$($f$)$ What is the impact of a $1\%$ increase in the current premium increase rate per year $(8\%)$ on the maximum amount of money available at the end of year $5?$ Up to what limit would this impact be predictable from the sensitivity report, assuming all other factors remain unchanged? Alternatively, explain why it could not be determined. $(14$ marks$)$

$($g$)$ What if there is a minimum requirement of $$50,000$ for each investment, if selected? How to determine an optimal investment plan with this additional requirement? You may make changes to the linear program $($base model$)$ in mathematical notations. The resulting model should be a linear program or integer linear program. $($Do NOT solve.$)(6$ marks$)$