Question

Complete in Excel: A manufacturer produces four parts: XJ201, XM897, TR29, and BR788. The net profit per unit are $9, $12, $15, and $11 respectively. The products are each processed in the wiring and drilling departments then go to assembly. The final step in the plant is a final inspection. The time in minutes spent at each of the four departments is listed in the following table:

                        Wiring             Drilling            Assembly        Inspection

XJ201             30                    180                  120                  30

XM897           90                    60                    240                  60

TR29               90                    120                  60                    30

BR788             60                    180                  120                  30

The labor capacity in hours for each department is listed below:

                        Wiring             Drilling            Assembly        Inspection

Capacity         1500                3000                2600                1200

The company has firm contracts for 150 units of XJ201, 100 units of XM897, 300 units of TR29, and 400 units of BR788. The company cannot sell more than 180 units of XJ201. The operations manager needs to determine how many to produce of each to maximize profits.

Part B

The marketing department has set two additional production requirements:

Production of TR29 can be no more than one-third of the total production of all four products.

The production of BR788 has to be exactly twice as great as the production of XM897.

Requirements:

Give a typed formulation with decision variables clearly defined and all constraints clearly defined. (2 pts)

Solve the formulation in solver. (2 pts)

Answer 1

Let us assume that we produce X1, X2, X3 and X4 units of  XJ201, XM897, TR29, and BR788 respectively.

Hence decision variables are X1, X2, X3 and X4

Profit per unit for XJ201, XM897, TR29, and BR788 are $9, $12, $15, and $11 respectively.

OPTIMAL EQUATION

Z = 9X1 + 12X2 + 15X3 + 11X4

CONSTRAINTS

30X1 + 90X2 + 90X3 + 60X4 <=1500*60 (wiring time constraint)

180X1 + 60X2 + 120X3 + 180X4 <=3000*60 (drilling time constraint)

120X1 + 240X2 + 60X3 + 120X4 <=2600*60 (assembly time constraint)

30X1 + 60X2 + 30X3 + 30X4 <=1200*60 (inspection time constraint)

X1 >= 150

X1<= 180

X2 >= 100

X3 >= 300

X4 >= 400

Additional constraints

X3 <= (1/3)* (X1 +X2 +X3 +X4) (production constraint on TR29)

X4 = 2*X2 (production of BR788 twice that of XM897)

1) Formulation of table

2) Formulas used

3) Solver inputs

4) Final solution

Hence the optimum quantities of X1, X2, X3 and X4 should be 180 , 222, 422 and 444 respectively and the optimal value of the profit would be $15498