A company manufactures two products. Each product can be produced on either of two machines. Machine A takes 3 hours to produce one product 1 and 4 hours to produce one product 2. Machine B takes 2...

A company manufactures two products. Each product can be produced on either of two machines.

• Machine A takes 3 hours to produce one product 1 and 4 hours to produce one product 2. Machine B takes 2 hours to produce one product 1 and 3 hours to produce one product 2. Each month 1000 hours of time are available for production with each machine.
  


• Moreover, the production of each product requires a particular number of hours of skilled labor. Producing one product 1 needs 1.5 hours of labor and producing one product 2 needs 2 hours of labor. Each month 1800 hours of skilled labor are available for production.


• The 1 unit selling price of product 1 is $200 and that of product 2 is $225 for the coming month.


• The total production quantity of the two products should not exceed 800 units for the coming month.

The company seeks to determine a production plan that maximizes monthly total revenue. An LP formulation of this problem is provided in the Excel file. This formulation is not correct.

Question:

Find all the mistakes in the spreadsheet provided (you should also check the setup of the Solver options). Modify the spreadsheet to setup the model correctly. Alternatively, you can setup the correct model and then compare with the model provided to identify the mistakes.

Prodcution Production Plan Times (hours) needed to manufacture each product on each machine Machine AMachine B Product 132 Product 243 Skilled labor (hours) needed to manufacture each product Labor Product 11.5 Product 22 Demands and prices Price Product 1200 Product 2225 Monthly production plan Machine AMachine BQuantity for each productMax total production Product 1-3803800<= 800="" product="" 2="" 996="" 48318381384="" 48318382380="" resources="" used="" actual="" available="" machine="" a="" hours="" 1000="">=1000 Machine B hours1000>=1000 Skilled labor hours96636765960>=1800 Total revenue$10,871,636,195,500 Sensitivity to maximum total production (originally 800) Max Total ProductionQuantity for Prod 1Quantity for Prod 2Total Revenue Answers to the questions: [You should summarize your answers to the question here] a) Correct problem formulation b) Mistakes in the given model