please

provide formulas

Excel worksheet W5Q2. 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 ft2 ), and the profit per unit along with the maximum time/department and storage space in the warehouse: To help determine how many air conditioners to produce this month, Ranlux developed the following, correct optimization model. Decision Variables: x1 : Number of Micro air conditioners to produce this month x2 : Number of Personal air conditioners to produce this month x3 : Number of Residential air conditioners to produce this month x4 : Number of Industrial air conditioners to produce this month Objective Function: Maximize 900x1+1300x2+1800x3+2200x4 Constraints: 1.1x1+2.5x2+4.0x3+4.4x480001.3x1+3.2x2+3.6x3+4.1x48000 Stamping Department Limit Uiven this problem, complete the following tasks and questions: 1. 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). 2. Run the sensitivity report. Which constraints are binding? 3. 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. 4. 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. 5. 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. 6. 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 monthlv Given this problem, complete the following tasks and questions: 1. 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). 2. Run the sensitivity report. Which constraints are binding? 3. 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. 4. 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. 5. 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. 6. 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. Constraints