Question

Hart Manufacturing makes three products. Each product requires manufacturing operations in three departments: A, B, and C. The labor-hour requirements, I department, are as follows: Product 1 Product Z Product 3 Department 1.95 3.45 2.45 2.90 1.90 .40 0.70 0.70 0.70 C During the next production period, the labor-hours available are 540 in department A, 440 in department B, and 140 in department C. The profit contributions per unit are $34 for product 1, $37 rproduct 2, and $39 for product 3. a. Formulate a linear programming model for maximizing total profit contribution. For those boxes in which you must enter subtractive or negative numbers use a minus sign. (Example: -300) Let Pi units of product i produced 34 P1 37 P2+ 39 P3 Мax s.t. 3.45 P2+ 1.95 P 540 2.45P3 1.9 P2 3.4 P3s 2.9P1+ 440 0.7P2 0.7 P3 0.7P 140 P1, P2, P3 0 b. Solve the linear program formulated in part (a). How much of each product should be produced, and what is the projected total profit contribution? 78.1 X X P2 = 112.38 o X Рз - P1 - 78.1 P2 112.38 P3 Profit $ 6813.34 X c. After evaluating the solution obtained in part (b), one of the production supervisors noted that production setup costs had not been taken into account. She noted that setup costs are $490 for product 1, $640 for If the solution developed in part (b) is to be used, what is the total profit contribution after taking into account the setup costs? product 2, and $690 for product 3 5683.33 X Profit t(b). Formulate a mixed-integer linear d. ent realized that the optimal oduct mix, taking into account, might be different from the that takes into Ount Management also stated that we should not consider making more than 220 units of product 1, 195unite of oroduct 2, or 185 units of product 3, For those hoves in which you must enter subtractie negative numbers use a minus sign. (Example: -300) Here introduce a 0-1 variable yi that is one if any quantity product i is produced and zero otherwise. Max 37P2 39 P3+ 34 P1 -490 y+ y3 -640 -690 s.t. 3.45 P2+ 1.95 P1 540V 2.45P3 19 P2+ 3.4 P3 2.9P1+ 440 0.7 P3 0.7P2 0.7 P 140 у 1P + -220 1P2 -195 y2 1P3 - 185 y3 | s P1, P2, P3 2 0; y, y2, y3 0, 1 e. Solve the mixed-integer linear program formulated in part (d). How much part (c) each product should be produced, and what is the projected total profit contribution? Compare this profit contribution to that obtained in o X P1 107.14 X P2 X P3 69.54 Profit 5986.18 X The profit is increased X by $ 302.85

Answer 1

As I can see the formulation part is correct and the only problem is on the solution

So I am providing a detailed solution in Excel solver here

? x Sign in Data Analysis Excel Book1 FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW 3 Connections From Access Clear Refresh Properties Edit Links Solver From Web Reapply From Other Consolidate What-If Relationships Group Ungroup Subtotal Filter Flash Existina Sort Text to Remove Data From Text Advanced Columns Fill Duplicates Validation All Analysis Sources Connections Get Extornal Data Connections Sort & Filter Data Tools Cutline Analysis - SUMPRODUCT(E3:G3,SE$10: $G$10) H3 A G M Q P. P2 p3 Values 2 34 37 6793 3 Objective 39 537 < S.t .95 3,45 2.45 540 4 2.9 1.9 3.4 440 440 < 6 0.7 0.7 0.7 133 < 240 7 8 P2 P3 9 P1 10 Solution 79 111 11 12 13 14 15 16 17 18 19 20 21 Sheet Sheet2 Sheet3 100 % READY

? x Sign in Data Analysis Excel Book FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW 3 Connections From Access Clear Refresh Properties Edit Links Solver From Web Reapply Filter Advanced Cplumns Fill Duplicates Validation From Other Consolidate What-If Relationships Group Ungroup Subtotal Flash Existing Sort Text to Remove Data From Text All Analysis Sources Connections Sort & Filtor Get Extornal Data Connections Data Tools Cutline Analysis J13 J D G 1 P1 Values P7 P: SUMPRODUCT(E3:G3,SE$10:$G$10) 34 3 Objective 37 39 S.t 1,95 3.45 2.45 SUMPRODUCT(E4:G4,$E$10:$G$10) 1.9 3.4 SUMPRODUCT(E5:G5,$E$10:$G$10) SUMPRODUCT(E6:G6,SE$10:$G$10) 540 4 2.9 440 0.7 0.7 0.7 240 7 8 P1 9 P2 P3 79 111 0 10 Solution 11 12 13 14 15 16 17 18 19 20 21 Sheet Sheet2 Sheet3 100 % READY

Solver Parameters $H$3 Set Objective: To: 0 Min Value Of: Max By Changing Variable Cells: $E$10:$G$10 Subject to the Constraints: $E$10:$G$10= integer $H$4:$H$6<$J$4:$J$6 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method smooth nonlinear. Select the LP Simplex engine Select the GRG Nonlinear engine for Solver Problems that are for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth Solve Close Help X

c) Considering setup costs if product 3 is not produced then any setup for it is not there

so total profit =6793-490-640=$5663

d) Formulation is correct

It is a mixed integer LP and the variables should be an integer

? x Sign in Data Analysis Excel Book FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW 3 Connections From Access Clear Solver From Web Refresh Properties Edit Links Reapply Filter Advanced Cplumns Fill Duplicates Validation Consolidate What-If Relationships Group Ungroup Subtotal From Other Flash Existina Sort Text to Remove Data From Text All Analysis Sources Connections Connections Sort & Filter Get Extornal Data Data Tools Cutline Analysis L12 F B G H M N P Q T U 1 PZ P3 y1 v3 34 37 39 -490 Objective -640 -690 5669 1.95 3.45 2.45 540 <= 540 4 S.t 0 C 438 <= 440 2.9 1.9 3.4 0 C C 6 0.7 0.7 0.7 C 133 <= 140 7 1 0 0-220 C -143 < = 0 82 < 195 8 0 0 0 1 0 -185 9 0 1 0 <= 0 10 11 P1 P2 P3 y1 y2 y3 77 113 0 1 12 Solution 1 13 14 15 16 17 18 19 20 21 Sheet Sheet2 Sheet3 100 % READY

? x Sign in Data Analysis Excel Book VIEW DATA REVIEW FORMULAS PAGE LAYOUT INSERT HOME FILE Clear 3 Connections Solver From Access Reapply Filter Advanced Cplumns Fill Duplicates Validation Refresh Properties Edit Links Consolidate What-If Relationships Group Ungroup Subtotal From Web Flash Data Remove Text to Sort From Other Existing Analysis From Text All Analysis Connections Sources Cutline Data Tools Sort & Filter Connections Get Extornal Data L12 H G 1 v3 y1 v2 P3 P2 P1 -SUMPRODUCT(E3:J3,$E$ 12:SJ$12) -SUMPRODUCT(E4:14,SE$12:$I$12) < -SUMPRODUCT(E5:J5,SE$12:SJ$12) < -SUMPRODUCT(E6:6,SE$12:$J$12) < SUMPRODUCT(E7:7,SE$12:SI$12) < - SUMPRODUCT(E8:J8,SE$12:$I$12) < -SUMPRODUCT(E9:19,SE$12:SI$12) < -690 490 -640 39 37 34 540 Objective 3 0 0 0 2.45 3.45 1.95 440 5.t 4 0 0 3.4 1.9 2.9 140 0 0.7 0.7 0.2 C 6 0 -220 0 0 0 1 C 7 -195 0 0 0 1 C 8 -185 0 0 0 9 10 v3 v P3 P2 P1 11 1 1 113 0 77 Solution 12 13 14 15 16 17 18 19 20 21 Sheet Sheet2 Sheet3 100 % READY

Solver Parameters $K$3 Set Objective: To: 0 Min Value Of: Max By Changing Variable Cells: $E$12:$J$12 Subject to the Constraints: $E$12:$G$12 = integer $H$12:$J$12 binary Add - $H$4:$H$6 $K$4:$K$9 $J$4:$J$6 $M$4:$M$9 <= Change <= Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method smooth nonlinear. Select the LP Simplex engine Select the GRG Nonlinear engine for Solver Problems that are for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth Solve Close Help X

The profit is increased by $6

Please rate me

Thanks