Question

A company is planning its production schedule over the next six months (it is currently the end of month 2). The demand (in units) for its product over that timescale is as shown below:

Month              3        4       5       6        7       8
Demand             5000     6000    6500    7000     8000    9500

The company currently has in stock: 1000 units which were produced in month 2; 2000 units which were produced in month 1; 500 units which were produced in month 0.

The company can only produce up to 6000 units per month and the managing director has stated that stocks must be built up to help meet demand in months 5, 6, 7 and 8. Each unit produced costs £15 and the cost of holding stock is estimated to be £0.75 per unit per month (based upon the stock held at the beginning of each month).

The company has a major problem with deterioration of stock in that the stock inspection which takes place at the end of each month regularly identifies ruined stock (costing the company £25 per unit). It is estimated that, on average, the stock inspection at the end of month t will show that 11% of the units in stock which were produced in month t are ruined; 47% of the units in stock which were produced in month t-1 are ruined; 100% of the units in stock which were produced in month t-2 are ruined. The stock inspection for month 2 is just about to take place.

The company wants a production plan for the next six months that avoids stockouts. Formulate their problem as a linear program.

Because of the stock deterioration problem the managing director is thinking of directing that customers should always be supplied with the oldest stock available. How would this affect your formulation of the problem?

Solution

Variables

Let

P_t be the production (units) in month t (t=3,...,8)

I_it be the number of units in stock at the end of month t which were produced in month i (i=t,t-1,t-2)

S_it be the number of units in stock at the beginning of month t which were produced in month i (i=t-1,t-2)

d_it be the demand in month t met from units produced in month i (i=t,t-1,t-2)

Constraints

production limit

P_t <= 6000

initial stock position

I₂₂ = 1000

I₁₂ = 2000

I₀₂ = 500

relate opening stock in month t to closing stock in previous months

S_t-1,t = 0.89I_t-1,t-1

S_t-2,t = 0.53I_t-2,t-1

inventory continuity equation where we assume we can meet demand in month t from production in month t. Let D_t represent the (known) demand for the product in month t (t=3,4,...,8) then

closing stock = opening stock + production - demand

and we have

I_t,t = 0 + P_t - d_t,t

I_t-1,t = S_t-1,t + 0 - d_t-1,t

I_t-2,t = S_t-2,t + 0 - d_t-2,t

where

d_t,t + d_t-1,t + d_t-2,t = D_t

no stockouts

all inventory (I,S) and d variables >= 0

Objective

Presumably to minimise cost and this is given by

SUM{t=3 to 8}15P_t + SUM{t=3 to 9}0.75(S_t-1,t+S_t-2,t) + SUM{t=3 to 8}25(0.11I_t,t+0.47I_t-1,t+1.0I_t-2,t)

Solve using Solver in Excel or GAMS.

Answer 1

In stock

Month 0 = 500 units

Month 1 = 2000 units

Month 2 = 1000 units

Now given the information that stock inspection will happen at the end of month t, we know that 11% of 1000 units will be ruined, 47% of 2000 units will be ruined and all 500 units from month 0 will be ruined.

Hence at the beginning of month 3 we shall have 890 + 1060 = 1950 units.

Now if we use this information on the spreadsheet it should look like the picture shown below.

Stock leftover is the number of units that remains after production and previous stock has been used to satisfy demand for the month.

Stock ruined is calculated by adding 0% from t-2, 47% from t-1 and 11% from t

Stock remaining is calculated by subtracting stock ruined from summation of month t, t-1, and t-2

Unit required is calculated by subtracting stock remaining for month t-1 from demand for month t

Units produced are the decision variables

The costs (production cost, holding cost and ruined stock cost) are calculated by multiplying their cost against number of units.

Finally on excel we setup a few constraints,

Units produced must be less than or equal to 6000

Units produced must be an integer

Stock leftovers must be greater than or equal to 0 (no stock out)

Once this is setup, use the solver to find the decision variables and minimize the cost. The screeshots are shown below.