Question

The Mehta firm can normally produce 75 stainless steel sinks in a month. This is done during regular production hours at a cost of $100 per sink. If demand in any one month cannot be satisfied by regular production, the production manager has three other choices:

1. (1) he can produce up to 40 more sinks per month in overtime but at a cost of $150 per sink;

2. (2) he can purchase a limited number of sinks from a friendly competitor for resale (the maximum

   number of outside purchases over the four-month period COMBINED is 200 sinks (NOT 200

   units each month), at a cost of $200 each;

3. (3) Or, he can fill the demand from his on-hand inventory. The ending inventory cost is $20 per

   sink per month.

A constant workforce level is expected. Back orders are NOT permitted (e.g. order taken in period 3 to satisfy demand in later period 2 is not permitted). Inventory on hand at the beginning of month 1 is 40 sinks.

Month	Demand for Stainless Steel Sinks
1	90
2	100
3	250
4	150

a. Formulate algebraically the above problem as a TRANSPORTATION Linear Programming model. Define the decision variables, objective function, and constraints. (16 points)

Hint: Draw the transportation network model that corresponds to the problem, to figure out how to formulate.

b. Formulate this same problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding “Excel Spreadsheet” and the “Answer Report”). Include “managerial statements” that communicate the results of the analyses. (9 points)

Answer 1

a)

Algebraic formulation of LP model is as follows:

Decision variables:

Let Ri be the regular production in month i

Oi be the overtime production in month i

Si be the quantity purchased from outside supplier in month i

Vi be the ending inventory of month i, which is carried over to the next month

Objective function:

Minimize 100R1+100R2+100R3+100R4+150O1+150O2+150O3+150O4+200S1+200S2+200S3+200S4+20V1+20V2+20V3+20V4

Constraints:

R1+O1+S1-V1+V0 = 90

R2+O2+S2-V2+V1 = 100

R3+O3+S3-V3+V2 = 250

R4+O4+S4-V4+V3 = 150

Ri <= 75

Oi <= 40

V0 = 40

S1+S2+S3+S4 <= 200

Ri, Oi, Si, Vi >= 0

-------------------------------------------

b)

Spreadsheet model is as follows:

Excel Formula:

Enter Solver Parameters:

Click Solve to generate the solution:

On the Solver Results window,

Answer Report is following: