Problem $3$ Optimization and Sensitivity Analysis $(25\%)$

Using the information provided, create a linear program using solver in the $$Problem $3-$ Part $1$ sheet in the provided Excel workbook. Then create a sensitivity analysis using that optimization model to answer the questions on the problem $3$ part $3$ answers sheet

Part $1$ Construct an optimization model that maximizes annual return using the information and the table provided on the next page

After showing off all the business analytics and finance skills you learned to your family and friends over Thanksgiving break, one of your most beloved family friends, Havi, approaches you and asks for your help to organize his sizeable investment portfolio.

Havi then shows you all the market research he has done, identifying all the potential investments he is interested in$,$ as well as their current price, their $5$ year return, and how much of a risk he believes each of them to be$,$ see the table provided on the next page.

Havi explains that he has $1$ million dollars available to invest and is looking to maximize the portfolios annual return based on annualizing the $5$ year returns that he has provided.

He also explains how he wants his portfolio to be structured:

He wants to invest all of the money he has available into this new investment portfolio

The amount invested in low risk investments must be at least twice as much as high risk investments

The amount invested in low risk investments must be greater than the combined amount invested in high and medium risk investments

Investments in consumer staples stocks must be greater than investments in consumer discretionary stocks

Investments in Cryotocurrency can not be greater than the amount invested in T$-$Bills

At least $15\%,$ but no more than $35\%$ of the total money invested into stocks must be in the technology sector

At least $15\%$ of the total investment portfolio must be invested in ETFs$22$

At least $5\%$ of the total investment portfolio must be invested in T$-$Bills

At least $50\%$ of the money invested into the Materials, Utilities, Energy, and Industrials sectors$,$ must be invested into the Energy sector

Havi would also like to purchase at least $1000$ shares of Corning stock

If you do not remember how to calculate annualized return from your finance class, the formula is listed below, with return being the expected multi$-$year return, and the periods being how many years that multi$-$year return was calculated over.

$((1+$Return$)^((1/$Periods$)))-1$

Part $2$ Using the optimization model from part $1,$ run a sensetivity analysis using solver, rename the sensetivity report to $$Problem $3$ Part $2$

Part $3$ Using the senesetivity report from part $2,$ answer the questions on the $$Problem $3$ Part $3$ sheet$\backslash$table$[[$Name$,$Type,Sector,Price,$\backslash$table$[[$Year$],[$Return$]],$Risk$],[$Eli Lilly,Stock,Healthcare,$$584\mathrm{.}64,426\mathrm{.}78\%,$Med$],[$Novo$,$Stock,Healthcare,$$96\mathrm{.}40,350\mathrm{.}19\%,$Med$],[$Medtronic$,$Stock,Healthcare,$$72\mathrm{.}70,-22\mathrm{.}76\%,$Med$],[$Corning$,$Stock,Materials,$$27\mathrm{.}17,-12\mathrm{.}87\%,$Low$],[$Prologis$,$Stock,Real Estate,$$102\mathrm{.}43,58\mathrm{.}98\%,$Low$],[$Walmart$,$Stock,Consumer Staples,$$158\mathrm{.}76,62\mathrm{.}79\%,$Low$],[$Costco$,$Stock,Consumer Staples,$$552\mathrm{.}93,140\mathrm{.}56\%,$Low$],[$Starbucks$,$Stock,Consumer Discretion,$$93\mathrm{.}95,59\mathrm{.}43\%,$Med$],[$GameStop$,$Stock,Consumer Discretion,$$13\mathrm{.}33,259\mathrm{.}24\%,$High$],[$Chipotle$,$Stock,Consumer Discretion,$$1,831\mathrm{.}01,319\mathrm{.}91\%,$Med$],[$AEP$,$Stock,Utilities,$$73\mathrm{.}35,0\mathrm{.}05\%,$Low$],[$Exon$,$Stock,Energy,$$110\mathrm{.}29,34\mathrm{.}50\%,$High$],[$Marathon$,$Stock,Energy,$$28\mathrm{.}00,36\mathrm{.}39\%,$High$],[$Valero$,$Stock,Energy,$$130\mathrm{.}04,40\mathrm{.}52\%,$High$],[$Northrop$,$Stock,Industrials,$$485\mathrm{.}69,57\mathrm{.}42\%,$Low$],[$Lockheed$,$Stock,Industrials,$$444\mathrm{.}54,34\mathrm{.}05\%,$Low$],[$Charter$,$Stock,Communications,$$421\mathrm{.}46,30\mathrm{.}58\%,$Med$],[$T$-$Mobile,Stock,Communications,$$137\mathrm{.}21,96\mathrm{.}44\%,$Med$],[$Berkshire$,$Stock,Finance,$$333\mathrm{.}96,58\mathrm{.}95\%,$Med$],[$Visa$,$Stock,Finance,$$232\mathrm{.}25,64\mathrm{.}83\%,$M