Inventory Simulation .xls

Click on the link above to download the Excel sheet.

This workbook is intended for teaching or research. You are welcome to use it in any manner,

and change it as you see fit. It comes without any guarantee whatsoever, and is distributed

free of charge. Changes are frequent, so check back frequently for a new version.

Most inventory control systems use a formula for lead-time demand to set safety stock levels.

Research has uncovered situations where that method leads to large and expensive errors.*

In particular, if replenishment orders might not arrive in the same order in which they are placed, then

the above method will leave you with too much inventory if your objective is a high level of protection,

and too little inventory if you are aiming to run out of stock frequently. That is, the variance of the

inventory level is smaller than the variance of lead-time demand.

*See Robinson, L.R, J.R. Bradley and L.J. Thomas, "Consequences of Order Crossover under

Order-up-to Inventory Policies." M&SOM Manufacturing and Service Operations Management,

Volume 3, No. 3 (2001), pp.175-188.

This workbook contains a macro, written in Visual Basic, that allows you to simulate the inventory control

system known variously as the Min-Max system, the (s,S) system, or the reorder-level, order-up-to system.

Two versions are available in the simulation:

>

Periodic review: orders may be placed only at specific points of time, such as daily or weekly.

>

Continuous review: orders are placed instantly, as soon as inventory reaches the reorder level.

In both cases, the state of the system is tracked at all times, so that accurate costs may be calculated.

The word "order" refers to an action taken to replenish the supply of an item that is stocked in inventory

and sold to customers. The word "demand" refers to a customer wanting to buy one unit of the item.

A "backorder" is an unsatisfied demand for which the customer will take delivery at a later time.

The simulation assumes that all customers are willing to wait if their demand is backordered.

The simulation allows orders to cross. It assumes that the lead time of one order is independent of

that for any other order, and therefore crossing occurs whenever the lead time for an order is longer

than the interval between orders plus the lead time for the next order.

Please note that the independence assumption is not true in some real circumstances. For example,

if both orders are shipped by rail, and if one freight car cannot pass another, the orders cannot cross.

However, in that case lead times are also not independent, but rather are positively correlated, so

models that assume independence (i.e. most inventory models) are also incorrect.

Contents: These are the sheets in this workbook.

Introduction

(this sheet), with the following sections:

1. Measuring Inventory and Shortages at Time of Delivery

(a) Shortfall below Reorder Level at Delivery: Shortfall@Deliv

(b) Distribution of Shortfall@Delivery if Orders do not Cross

(i) Continuous Review

(ii) Periodic Review

2. Inventory and Shortages at Any Time

(a) Shortfall below Order-up-to Level

(b) Average Inventory and Shortages

3. Cost of Inventory, Backorders and Ordering:

4. Optimization

Simulate

where you set up the model and run the simulation.

Graphs

where simulation results are displayed in detail for any run stored on the Data sheet.

Trace

where the first part of the most recent simulation run is shown in a table and a graph.

Data

where the results of all simulation runs are stored, until you erase them.

Other sheets in this book, if any, may contain data and graphs from previous simulation experiments.

1. Measuring Inventory and Shortages at Time of Delivery:

s

reorder level, or Min

Inv

On-hand inventory

S

order-up-to level, or Max

BO

Number of units backordered to customers

Q

S - s

NetInv

= Inv - BO (can be positive or negative)

L

A value of lead time

InvPosition

NetInv + Outstanding Orders

D

A value of one-period demand

DL

Demand that occurs during lead time

mL, VarL

Average & Variance of L

mDL, VarDL

Average & Variance of DL

mD, VarD

Average & Variance of D

(a) Shortfall below Reorder Level at Delivery: Shortfall@Deliv

Protection against shortages focuses attention on inventory at the time a replenishment order

arrives. Safety stock governs the likelihood that backorders will exist at that instant.

In the simulation, @Deliv refers to events that happen just before replenishments occur.

However, rather than tracking inventory, which can be positive or negative, the simulation monitors

"Shortfall below s at delivery," defined as

1)

Shortfall@Deliv = s - NetInv@Deliv at the time (just before) replenishment occurs.

This is a non-negative variable since net inventory is at or below the reorder level, s, whenever any

order is outstanding (i.e. not yet received.)

From Shortfall@Deliv we may compute certain performance measures:

2)

NetInv@Deliv = s - Shortfall@Deliv

3)

Inv@Deliv = MAX(0, s - Shortfall@Deliv)

4)

BO@Deliv = MAX(0, Shortfall@Deliv - s) = Inv@Deliv - s + Shortfall@Deliv

5)

0) = P( Shortfall@Deliv > s)">P(BO@Deliv>0) = P( Shortfall@Deliv > s)

The latter may also be expressed as a rate, although the meaning is a little confusing. It is NOT the

rate at which backorders occur, but rather "occurrences per unit time" of the joint event

"replenishment arrives, backorders exist," or "replenishment arrives too late to prevent backorders."

That event is denoted "BO@Deliv>0" and its occurrence rate is

6)

0) = P( Shortfall@Deliv > s)×(Replenishment Orders Per Unit Time)">Rate(BO@Deliv>0) = P( Shortfall@Deliv > s)×(Replenishment Orders Per Unit Time)

(b) Distribution of Shortfall@Delivery if Orders do not Cross

The following gives the classical argument for the distribution of shortfall, assuming that orders do

not cross, and also assuming that lead times are independent, two assumptions which are convenient

but contradictory. These numbers may be compared to the actual values from the simulation to see

how much is lost if the classical rules are used.

With no order crossing, when an order arrives, all prior replenishment orders have already arrived,

and no subsequent ones have. At the time that order was placed, Inventory Position included the prior

orders, so to compute inventory at delivery, we only have to account for the demand that occurs in the

lead time (or lag time) between placing and receiving the order. That is,

7)

NetInv@Deliv = InvPosition@Ordering - DL and

8)

Shortfall@Deliv = s - InvPosition@Ordering + DL if orders do not cross

(substitute 7 into 1).

(i) Continuous Review

Under continuous review, an order is placed the instant that inventory position reaches the reorder

level. That is,

9)

InvPosition@Ordering = s for continuous review, so

10)

Shortfall@Deliv = DL for continuous review if orders do not cross

(substitute 9 into 8).

>>

Shortfall@Deliv equals lead-time demand for continuous review, if orders do not cross.

The probability that backorders occur before an order arrives is

11)

P(BO@Deliv>0) = P( DL > s )

(substitute 10 into 5).

The following formulas assume that Lead Times are independent and identically distributed, and that

the same is true for Demands, and that Lead Times are independent of Demands. They are, in fact, the

well-known formulas for the mean and variance of lead-time demand.

12)

E[Shortfall@Deliv] = mD mL and

13)

Var[Shortfall@Deliv] = mL VarD + mD2 VarL for continuous review if orders do not cross.

(ii) Periodic Review

Under periodic review, inventory position can reach the reorder point at a time t that is before the end

of the period, so inventory position will be at or below the reorder point when the order is placed.

If t is at the end of the period, the order is placed at the instant that the reorder level is reached.

If t is just after the beginning of the period, a one-period demand occurs before ordering.

This leads to the following inequality:

14)

s - D ≤ InvPosition@Ordering ≤ s

Substituting 14 into 8,

15)

DL ≤ Shortfall@Deliv ≤ DL + D = DL+1 for periodic review if orders do not cross.

>>

Shortfall@Deliv is between the demand during lead time and the demand during one period longer

than lead time, if orders do not cross. Also, because the probability above s is a nonincreasing

function of s,

16)

P(DL > s) ≤ P(Shortfall@Deliv>s) ≤ P( DL + D' - 1> s ) , and so

17)

P(DL > s) ≤ P(BO@Deliv>0) ≤ P( DL + D' - 1> s )

(substitute 16 into 5).

The expected value of 15 yields

18)

mD mL ≤ Shortfall@Deliv ≤ mD (1+mL)

The arguments leading to equation 15 also yield a lower limit for the variance:

19)

Var[Shortfall@Deliv] ≤ mL VarD + mD2 VarL

The upper limit in equation 15 also yields a variance estimate, but it is not necessarily an upper limit:

20)

Var[Shortfall@Deliv] ≈ (1 + mL) VarD + mD2 VarL

2. Inventory and Shortages at Any Time

The simulation also measures the inventory level after every event. Inventory is constant between

events (by definition, since an event is defined as a change of state), so the distribution is

tabulated by accumulating the time that each state persists.

(a) Shortfall below Order-up-to Level

"Shortfall below S" is defined at every time in the simulation as

20)

Shortfall = S - NetInv.

Notice that Shortfall uses a different reference point than Shortfall@Delivery, namely S rather

than s. This is necessary to avoid negative values.

From Shortfall, we may compute more performance measures:

21)

NetInv = S - Shortfall.

22)

Inv = MAX(0, S - Shortfall)

23)

BO = MAX(0, Shortfall - S) = Inv - S + Shortfall

24)

0) = P( Shortfall > S)">P(BO>0) = P( Shortfall > S)

Since a demand is backordered if it arrives when inventory is zero, the average number of demands

backordered per unit time is

25)

Rate(BO) = P( Shortfall ≥ S)×(Demand Rate)

We can also calculate the average time that a backorder endures which, according to Little's Law,

is proportional to the average number of backorders waiting.

Average duration of a Backorder = (Average # Backordered)¸(Rate of Backorders Occuring)

26)

Av(Wait per BO) = Av(BO)¸{Av(DemandRate) × P{Shortfall≥S)}

If you want to include in this average the fact that many customers have zero backorder time, then

27)

Av(Wait per Demand) = Av(BO)¸Av(DemandRate) (includes zero-length backorders.)

(b) Average Inventory and Shortages

Average inventory is greater when computed over time than when computed just before a delivery.

Inventory just before delivery can never be above the reorder point, whereas it can at other times.

The average inventory over time will include the "sawtooth pattern" commonly seen in textbooks,

caused by cycle stock represented by the order quantity. Therefore the exact theoretical expression

for average inventory and backorders is elusive, and I will not try to include it here. However the

simulation results yield averages from the distribution of Shortfall, using equations 22 through 25.

3. Cost of Inventory, Backorders and Ordering:

The simplest model has linear inventory and backorder costs. However, what constitutes backorder

cost? There may be a cost per unit time for backorders, and a fixed cost whenever a backorder

occurs. There also might be a fixed cost per unit time that accrues as long as there are any

backorders. If the gap between s and S is changed, the number of orders placed will change, which

changes the cost of ordering. A model that covers all of these costs is

28)

Average Cost per Period = C1 × Av(Inv) + C2 × Av(BO) + C3 × mD × P(BO ≥ 0)

+ C4 × P(BO>0) + C5 × Av(OrderRate)

4. Optimization

The value of Q (the gap between s and S) is held constant during a simulation. (In fact, it operates as

if the order-up-to level were S=0 with reorder level s = -Q.) However, the output may be used

to represent any (s,S) system that has S-s=Q. You can find the optimal value of s among all

systems that have the same Q as the one in your simulation, and then set S=s+Q.

On the Graphs sheet, an Excel Table calculates costs for a range of values of s. A graph shows the

results. You may input the first value of s and the interval between points. To home in on the

optimum, adjust the first value until the graph is U-shaped, and then lower the interval to 1.

However, the result is only optimal for the value of Q that you simulated. To find an overall

optimum, you must repeat the simulation for a series of values of Q, and use the table to find the

best reorder level for each Q. Record those values and select the one with lowest cost.