ABC Manufacturing makes three products. Each product requiresmanufacturing operations in three departments: L, M and N. The labour-hourrequirements, by department, are as follows:Department Product 1 Product 2 Product 3L 1.50 3.00 2.00M 2.00 1.00 2.50N 0.25 0.25 0.25During the next production period, the labour-hours available are 450 in departmentL, 350 in department M, and 50 in department N. The profit contributionsper unit are $25 for product 1, $28 for product 2 and $30 for product 3.
(a) Formulate an integer linear program that ABC Manufacturing canuse to maximize the total profit. For this purpose, identify the elementsof this integer linear program below in words or in algebraic form.
(b) Set up the model you formulated in part (2a) above in Excel. Then,use solver to find the optimal solution. Do not forget to choose 0% integertolerance in solver. Attach both your model set up in excel and the answerreport to your answer script. Interpret your results.
(c) After evaluating the solution obtained in part (2b) above, one ofthe production supervisors noted that production setup costs had not beentaken into account. She noted that setup costs are $400 for product 1, $550for product 2 and $600 for product 3. If the solution you obtained in part(2b) is to be used, determine the total profit contribution after accounting forthe setup costs.
(d) Management realized that the optimal product mix, taking setupcosts into account (as this cost will be incurred only if we produce the respectiveproduct), might be different from the one recommended in part (2b).They feel that a new problem needs to be formulated and solved for thispurpose. Management also stated that we should not consider making morethan 175 units of product 1, 150 units of product 2, or 140 units of product3. They want you to formulate this integer programming problem that takesthe setup costs into account. You remember that you need to use linkingconstraints to model this condition. Please identify the elements of this newinteger linear program below in words or in algebraic form.
(e) Set up the model you formulated in part (2d) above in Excel. Then,use solver to find the optimal solution. Attach both your model set up in exceland the answer report to your answer script. Interpret your results.