Question

Clark Clothing Inc produces T-shirts, Shorts, and Pants. The profit contributions ($/unit) are:

T-shirt: $14.30/unit, Short $9.80/unit, and Pant: $12.20/unit. 250 hours for Cutting are available, 230 hours for Sewing, and 20 hours for Packing are available. There are two pending orders: 25 T-shirts and 50 Pants. The mathematical model to maximize total profit is given below:

Max 14.3*T + 9.8*S + 12.2*P

S.T.:

Cutting 1.5*T + 2.2*S + 3.1*P <= 250

Sewing 0.7*T + 1.9*S + 3.7*P <= 230

Packing 0.1*T + 0.3*S + 0.3*P <= 20

T-shirt Order T >= 25

Pants Order P >= 50

N-N T, S, P >= 0 AND INTEGER

1) What is the Optimal Solution?

T ____ Units

S ____ Units

P ____ Units

2) What is the Total Profit?

Assume Profit Contribution for Shorts is 11.5 (not 9.80) AND

the number of hours for Packing is 25 (not 20).

3) What is the new optimal solution?

T____ Units

S ____ Units

P ____ Units

4) What is the total Profit

Please perform in excel using solver, I know how to set this up but have problems putting it into excel and linking things correctly for solver

Answer 1

The ILP is formulated as,

Max 14.3*T + 9.8*S + 12.2*P

S.T.:

Cutting 1.5*T + 2.2*S + 3.1*P <= 250

Sewing 0.7*T + 1.9*S + 3.7*P <= 230

Packing 0.1*T + 0.3*S + 0.3*P <= 20

T-shirt Order T >= 25

Pants Order P >= 50

T, S, P >= 0 and integer

Now, the LP is solved using the excel solver by following these steps,

Step 1: Write the decision variable with value zero. The screenshot is shown below

Step 2: Write the objective function equation while taking the decision variable value. The screenshot is shown below,

Step 3: Write the constraints equation while taking the decision variable value and write the right-side value of the constraint

The screenshot for constraints is shown below,

Step 4: DATA > Solver > OK.

Step 5:

Set Objective: Select objective value,

To: Select Max

Subject to the Constraints > Add > in Cell Reference select constraint value and in Constraint: select right-hand side value of constraint and select the >= inequality.

For T, S, P >= 0 and integer select the reference cell of decision variable value.

Tick Make Unconstrained Variables Non-Negative

Select a Solving Method: Simplex LP

then click Solve. The screenshot is shown below,

Step 6: Select Reports > Answer then Ok

The result is obtained. The screenshots are shown below,

1)

T = 50 Units

S = 0 Units

P = 50 Units

2) What is the Total Profit?

Total Profit = $1325

3)

After changing the values,

T = 63 Units

S = 0 Units

P = 50 Units

4)

Total Profit = $1510.9