Transcript Inventory control model

Statistical Inventory
control models
Using Excel
Learning objective

After this class the students should be
able to:
• calculate the appropriate order quantity in
the face of uncertain demand using Excel
and Cumulative Probability for Newsboy
Model simplified.
Time management

The expected time to deliver this module
is 50 minutes. 30 minutes are reserved
for team practices and exercises and 20
minutes for lecture.
Introduction

We will study situations in which inventory cannot be
carried from period to period similar to Newsboys
Model.
•
perishable products are fruits and vegetables in
supermarkets.
•
products that rapidly become obsolete, such as fashion
items, and
•
those that are bought for specific time periods, such as a
promotional sale for a holiday.
The Strawberry Ordering Model
Cora, buyer for the Fresh Foods supermarket, is
considering the computer specifications for the ordering
of strawberries.
Baskets of strawberries are delivered daily:


•
•

If she orders too few, there will be many stockouts, sales
will be lost, and profit will be low.
If she orders too many, there will be a surplus of
strawberries in the evening that will have to be unloaded
to canneries at a large discount.
What quantity should Cora order?.
Data




Each basket of strawberries sells for
$6.00,
the cost is $4.00, and
the salvage value of any surplus sold to
a cannery is $3.00.
So, each unit sold brings a profit of
$2.00, and each unit salvaged leads to a
loss of $1.00.
Data




basket of strawberries price: $6.00,
basket of strawberries cost: $4.00, and
the salvage value: $3.00.
Each unit sold brings a profit of $2.00,
and each unit salvaged leads to a loss of
$1.00.
Decision tree
Cora knows from
past computer
records that most
daily sales are
between 11 and 20
baskets, so she has
10 alternatives for
the order quantity:
11, 12, . . . , 20. This
decision tree
visually represents
her choices and
possible outcomes.
Dealing with uncertainty


Cora do not have enough information to
derive a sophisticated probability
distribution, then…
She assumes a uniform distribution and
sets the probability of each of the ten
values equal to 0.1.
The model
A
2
3
4
5
6
7
Go to worksheet
B
C
D
Model Inputs
Cost
$4.00
$4.00
Price
$6.00
$6.00
Scrap Value $3.00
$3.00
Profit
$2.00 =D4-D3
Scrap Loss
-$1.00 =D5-D3
General Profit function
( p  c )  Q  k  ( Q  D )
P
( p  c )  Q  h  ( D  Q )
P  profit
p  price per unit
c  cost per unit
k  cost of overage
h  cost of shortage
if Q  D ( overage)
if Q  D ( shortage)
Individual Profits
Demand - D
11
12
13
14
15
16
17
18
19
20
21
22
A
B
Individual
Profits
11
12
13
14
15
16
17
18
19
20
C
D
E
F
11
22
22
22
22
22
22
22
22
22
22
12
21
24
24
24
24
24
24
24
24
24
13
20
23
26
26
26
26
26
26
26
26
14
19
22
25
28
28
28
28
28
28
28
G
H
Order quantity - Q
15
16
18
17
21
20
24
23
27
26
30
29
30
32
30
32
30
32
30
32
30
32
I
J
K
L
17
16
19
22
25
28
31
34
34
34
34
18
15
18
21
24
27
30
33
36
36
36
19
14
17
20
23
26
29
32
35
38
38
20
13
16
19
22
25
28
31
34
37
40
P  f ( D, Q )
C13 =IF($B13<=C$12,$C$6*$B13,$C$6*C$12)+IF($B13< C$12,(C$12
- $B13)*$C$7,0)
Probability
A
B
C
Demand - D
26
Probability
27
11
28
11 10%
29
12 10%
30
13 10%
31
14 10%
32
15 10%
33
16 10%
34
17 10%
35
18 10%
36
19 10%
37
20 10%
D
E
12
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
13
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
F
G
H
I
Order quantity - Q
14
15
16
17
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
10% 10% 10% 10%
J
K
L
18
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
19
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
20
10%
10%
10%
10%
10%
10%
10%
10%
10%
10%
Expected Profits
Demand - D
41
42
43
44
45
46
47
48
49
50
51
52
53
A
B
C
Profit
expected
11
11
2.2
12
2.2
13
2.2
14
2.2
15
2.2
16
2.2
17
2.2
18
2.2
19
2.2
20
2.2
Total 22.0
D
E
12
2.1
2.4
2.4
2.4
2.4
2.4
2.4
2.4
2.4
2.4
23.7
13
2.0
2.3
2.6
2.6
2.6
2.6
2.6
2.6
2.6
2.6
25.1
F
G
H
I
Order quantity - Q
14
15
16
17
1.9
1.8
1.7
1.6
2.2
2.1
2.0
1.9
2.5
2.4
2.3
2.2
2.8
2.7
2.6
2.5
2.8
3.0
2.9
2.8
2.8
3.0
3.2
3.1
2.8
3.0
3.2
3.4
2.8
3.0
3.2
3.4
2.8
3.0
3.2
3.4
2.8
3.0
3.2
3.4
26.2
27.0
27.5
27.7
Q*  17  Expected Profit  27.7
J
K
L
18
1.5
1.8
2.1
2.4
2.7
3.0
3.3
3.6
3.6
3.6
27.6
19
1.4
1.7
2.0
2.3
2.6
2.9
3.2
3.5
3.8
3.8
27.2
20
1.3
1.6
1.9
2.2
2.5
2.8
3.1
3.4
3.7
4.0
26.5
Optimum
Expected Profits
30.0
25.0
20.0
15.0
10.0
5.0
0.0
11 12 13 14 15
16 17 18 19 20
Order quantity
A mathematical shortcut

The solution method used to help Beth just
described enumerates all alternatives and
selects the best one.
•
•
This "brute force" approach is not practical when there
are too many alternatives.
Fortunately, there is a mathematical procedure for
finding the optimal order quantity.
Notation
P=Unit sales price
 C=Unit cost
 S=Unit salvage value
 CF=Critical factor
The critical factor is calculated as
 CF= (P- C)/(P- S)

Procedure to find Q*

Plot the cumulative
probability distribution
of demand.

Mark point A on the yaxis at the value of
CF.

Move horizontally to
point B on the curve.
Drop vertically to point
C on the x-axis.

The point immediately
to the right is Q*.
The strawberry problem
P= $6.00
C = $4.00
S = $3.00
CF= (6 - 4)/(6 - 3)=2/3
Q* = 17 baskets
Exercise




Robin Lowe, a buyer at the Newstorm
Department Store, must decide how
many high-fashion hats to order.
The unit sales price P = $125;
The cost C = $60, and
there is no salvage value because Robin
does not want any of the high-fashion
item sold by some discount house.
Exercise
Probability
equally
distributed
Demand
Between
Between
Between
100
110
126
and
and
and
<100
109
125
150
>150
0.0%
0.5%
5.0%
0.6%
0.0%
How many hats should she order?
Use the method used in this class to solve this problem
(20 minutes)
Reflections
Each team is invited to analyze the
following insights, based on the
statistical model (10) minutes):
1.
2.
“Cycle stock increase as replenishment
frequency decrease”
“Safety stock provide a buffer against
stockout”
Reference

Operations Management Using Excel
.Weida; Richardson and Vazsony,
Duxbury, 2001, Chapter 6, p.136-143