USE EXCEL TO COMPLETE THE GIVEN TABLE.

Norma bought a lottery ticket with her colleagues at her new job and just won a share of the Lotto Max Millions and is deciding how to invest part of her share $($$$460,000).$ She is looking at six different corporate bonds as well as three start$-$ups to invest in$.$ She has some stipulations and concerns about the risks associated with each investment. You have been asked to determine the best investment decision to maximize her annual return on the investment within the constraints given. With the help of her financial advisor, Norma has collected the information given in the following table.Investment Decision:

Annual Return:

CONSTRAINTS

Corporate Bond Rate of Return Maturity Risk Rating

Prime Pipeline $3\mathrm{.}71\%$ Long Fair

Dreyton Distribution $3\mathrm{.}22\%$ Short Excellent

Coldlake Exploration $2\mathrm{.}78\%$ Long Good

Orange Manufacturing $3\mathrm{.}88\%$ Medium Good

Zenon Drilling $4\mathrm{.}32\%$ Long Excellent

Venus Fulfilment $4\mathrm{.}47\%$ Medium Fair

In this table, Maturity is the length of time over which the bonds will be payable, and Risk Rating indicates independent underwriter$$s assessment of the quality or risk associated with each issue. The following table lists Norma$$s financial advisor$$s expectations for the rate$-$of$-$returns of each start$-$up investment alternative and the investment amount that each start$-$up requires to be invested.

Start$-$Up Investment Expected Rate of Return Amount to Invest

Storage Works $4\mathrm{.}08\%$ $$85,000$

All$-$In $3$D $4\mathrm{.}14\%$ $$96,500$

Jaydeep Global $3\mathrm{.}60\%$ $$75,000$

Norma has determined the following stipulations about how she wants to invest the money based on the financial advisor$$s recommendations:

$$ Invest the entire amount.

$$ Not invest more than $$100,000$ in the bonds that have Fair rating and not invest more than $$120,000$ in the ones with Good rating.

$$ Invest at least $$28,000$ in Coldlake Exploration $($although it has the lowest return of the bonds, the advisor is hoping for a turnaround$).$

$$ Invest at least $$200,000$ in bonds associated with operations and supply chain management functions: fulfillment, distribution, and manufacturing.

$$ Invest at least $30\%$ of the total money invested in the corporate bonds $($excluding the start$-$up investment$)$ in bonds that have long term maturity.

$$ Invest in at most two start$-$ups.

$$ Invest in either All$-$In$-3$D or Jaydeep Global but not both.

$$ If invest in Storage Works then also invest in Jaydeep Global.

a$)$ What is Norma$$s optimal investment plan? Provide her optimal investment plan in a tabulated format, which shows the investment options vs the amount she must invest in$,$ and state how much she is expecting to earn annually from this plan.

b$)$ Now suppose that the rate of return from Storage Works is $4\mathrm{.}98\%,$ not $4\mathrm{.}08\%.$ Rerun the model and present the new optimal plan. State what has changed and why, in no more than three sentences.

Extra Instructions for Question $2.$

$1.$ The data associated to this question is given in the $$Investment$$ sheet of the Excel template provided. Use this sheet to develop your Solver model and answer the questions.

$2.$ Do not move or resize any of the cells given in the template. Otherwise, points will be deducted.

$3.$ A Solver model must exist in the sheet mentioned in #$1$ above. You will NOT receive ANY points even if the answers in your report are correct but there is no solver model in the Excel sheet OR if the answers in your report and the model$/$numbers in the Excel sheets do NOT match.

Do not forget the constraint names.