statistical inventory control models using excel
TRANSCRIPT
![Page 1: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/1.jpg)
Statistical Inventory control models
Using Excel
![Page 2: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/2.jpg)
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.
![Page 3: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/3.jpg)
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.
![Page 4: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/4.jpg)
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.
![Page 5: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/5.jpg)
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?.
![Page 6: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/6.jpg)
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.
![Page 7: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/7.jpg)
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.
![Page 8: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/8.jpg)
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.
![Page 9: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/9.jpg)
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.
![Page 10: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/10.jpg)
The model
2
3
4
5
6
7
A B C D
$4.00 $4.00$6.00 $6.00$3.00 $3.00$2.00 =D4-D3
-$1.00 =D5-D3
Model InputsCostPriceScrap ValueProfitScrap Loss
Go to worksheet
![Page 11: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/11.jpg)
General Profit function
shortageof cost hoverage of costkunit per costc
unit per pricepprofitP
shortageDQifQDhQcpoverageDQifDQkQcp
P
)()()()()()(
![Page 12: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/12.jpg)
Individual Profits
111213141516171819202122
A B C D E F G H I J K L
11 12 13 14 15 16 17 18 19 2011 22 21 20 19 18 17 16 15 14 1312 22 24 23 22 21 20 19 18 17 1613 22 24 26 25 24 23 22 21 20 1914 22 24 26 28 27 26 25 24 23 2215 22 24 26 28 30 29 28 27 26 2516 22 24 26 28 30 32 31 30 29 2817 22 24 26 28 30 32 34 33 32 3118 22 24 26 28 30 32 34 36 35 3419 22 24 26 28 30 32 34 36 38 3720 22 24 26 28 30 32 34 36 38 40
Individual Profits
Dem
and
- D
Order quantity - Q
)QDfP ,(C13 =IF($B13<=C$12,$C$6*$B13,$C$6*C$12)+IF($B13< C$12,(C$12- $B13)*$C$7,0)
![Page 13: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/13.jpg)
Probability
262728293031323334353637
A B C D E F G H I J K L
11 12 13 14 15 16 17 18 19 2011 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%12 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%13 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%14 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%15 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%16 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%17 10% 10% 10% 10% 10% 10% 10% 10% 10% 10%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%
ProbabilityOrder quantity - Q
Dem
and
- D
![Page 14: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/14.jpg)
Expected Profits
41424344454647484950515253
A B C D E F G H I J K L
11 12 13 14 15 16 17 18 19 2011 2.2 2.1 2.0 1.9 1.8 1.7 1.6 1.5 1.4 1.312 2.2 2.4 2.3 2.2 2.1 2.0 1.9 1.8 1.7 1.613 2.2 2.4 2.6 2.5 2.4 2.3 2.2 2.1 2.0 1.914 2.2 2.4 2.6 2.8 2.7 2.6 2.5 2.4 2.3 2.215 2.2 2.4 2.6 2.8 3.0 2.9 2.8 2.7 2.6 2.516 2.2 2.4 2.6 2.8 3.0 3.2 3.1 3.0 2.9 2.817 2.2 2.4 2.6 2.8 3.0 3.2 3.4 3.3 3.2 3.118 2.2 2.4 2.6 2.8 3.0 3.2 3.4 3.6 3.5 3.419 2.2 2.4 2.6 2.8 3.0 3.2 3.4 3.6 3.8 3.720 2.2 2.4 2.6 2.8 3.0 3.2 3.4 3.6 3.8 4.0
Total 22.0 23.7 25.1 26.2 27.0 27.5 27.7 27.6 27.2 26.5
Profit expected
Dem
and
- D
Order quantity - Q
Optimum727ProfitExpected17Q .*
![Page 15: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/15.jpg)
0.0
5.0
10.015.0
20.0
25.0
30.0
11 12 13 14 15 16 17 18 19 20
Order quantity
Exp
ecte
d P
rofi
ts
![Page 16: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/16.jpg)
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.
![Page 17: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/17.jpg)
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)
![Page 18: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/18.jpg)
Procedure to find Q*
Plot the cumulative probability distribution of demand.
Mark point A on the y-axis 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*.
![Page 19: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/19.jpg)
The strawberry problem
P= $6.00
C = $4.00
S = $3.00
CF= (6 ‑ 4)/(6 ‑ 3)=2/3
Q* = 17 baskets
![Page 20: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/20.jpg)
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.
![Page 21: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/21.jpg)
Exercise
Probability equally
distributed
<100 0.0%Between 100 and 109 0.5%Between 110 and 125 5.0%Between 126 and 150 0.6%
>150 0.0%
Demand
How many hats should she order?
Use the method used in this class to solve this problem (20 minutes)
![Page 22: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/22.jpg)
Reflections
Each team is invited to analyze the following insights, based on the statistical model (10) minutes):
1. “Cycle stock increase as replenishment frequency decrease”
2. “Safety stock provide a buffer against stockout”
![Page 23: Statistical Inventory control models Using Excel](https://reader038.vdocuments.us/reader038/viewer/2022102808/56649da85503460f94a955ea/html5/thumbnails/23.jpg)
Reference
Operations Management Using Excel .Weida; Richardson and Vazsony, Duxbury, 2001, Chapter 6, p.136-143