Excel worksheet W$5$Q$2.$ Ranlux Power is a producer of personal, residential and industrial air conditioning units. Over the coming month, Ranlux wants assistance in determining how many of each product type they should produce to maximize their profits. Ranlux makes four types of air conditioners, micro, personal, residential, and industrial and they have to go through $5$ industrial processes in order: stamping, welding, chemical injection, assembly, and inspection. Additionally, there are storage limits as all of the produced items must be stored in the warehouse at the same time and there are minimum and maximum sale requirements. The following tables contains all of the relevant data for each air conditioning type including the amount of time required in each department $($in hours$),$ the storage requirement $($in ft$2),$ and the profit per unit along with the maximum time$/$department and storage space in the warehouse:

Air Conditioners Stamping Welding Chem. Injection Assembly Inspection Storage Space

Micro $1\mathrm{.}11\mathrm{.}32\mathrm{.}12\mathrm{.}41\mathrm{.}44\mathrm{.}8$

Personal $2\mathrm{.}53\mathrm{.}22\mathrm{.}82\mathrm{.}62\mathrm{.}19\mathrm{.}6$

Residential $43\mathrm{.}63\mathrm{.}33\mathrm{.}53\mathrm{.}111\mathrm{.}8$

Industrial $4\mathrm{.}44\mathrm{.}14\mathrm{.}84\mathrm{.}94\mathrm{.}113\mathrm{.}4$

Limits $8000800080008000800030000$

Air Conditioners Min Sales Max Sales Profit

Micro $0500900$

Personal $07501300$

Residential $10010001800$

Industrial $10015002200$

To help determine how many air conditioners to produce this month, Ranlux developed the following, correct optimization model.

Decision Variables:

hw$3$a$.$png

hw$3$b$.$png

Given this problem, complete the following tasks and questions:

Implement the linear optimization problem $($do not require your variables to be integers!$)$ in Excel and determine the optimal production plan. What is the monthly profit and monthly production for each air conditioner type? Answer the question below and upload your solved Excel workbook $($including the sensitivity analysis from the next steps$).$

Run the sensitivity report. Which constraints are binding?

Only using the sensitivity report $($do not change the value and re$-$run the model$),$ would the optimal production plan change if the profit for each Micro air conditioner change to $950?$ If your answer is yes, explain your reasoning. If your answer is no$,$ what is the new monthly profit and explain your calculation.

Only using the sensitivity report $($do not change the value and re$-$run the model$),$ would the optimal production plan change if the profit for each Residential air conditioner change to $1700?$ If your answer is yes, explain your reasoning. If your answer is no$,$ what is the new monthly profit and explain your calculation.

Only using the sensitivity report $($do not change the value and re$-$run the model$),$ can you determine the new objective function value without re$-$running the model if the number of hours available in the Stamping department change to $8100?$ If your answer is no$,$ explain your reasoning. If yes, what is the new monthly profit and explain your calculation.

Only using the sensitivity report $($do not change the value and re$-$run the model$),$ can you determine the new objective function value without re$-$running the model if the number of hours available in the Welding department change to $7000?$ If your answer is no$,$ explain your reasoning. If yes, what is the new monthly profit and explain your calculation.