Ch.
19 - Inventory Theory
Contents:
Basic EOQ Model (Solver)
Basic EOQ Model (Analytical)
EOQ Model with Planned Shortages (Solver)
EOQ Model with Planned Shortages (Analytical)
EOQ Model with Quantity Discounts (Analytical)
Template for the Stochastic Continuous-Review Model
Template for the Stochastic Single-Period Model for Perishable Products, No Setup Cost
Template for the Stochastic Single-Period Model for Perishable Products, with Setup Cost
Template for the Stochastic Two-Period Model, No Setup Cost
Template for the Stochastic Infinite-Period Model, No Setup Cost
Click on the tab below to select the worksheet of your choice.
Basic EOQ Model (Solver)
Data Results
a = 6000 (demand/year) Reorder Point = 216
K = $115 (setup cost)
h = $4.20 (unit holding cost) Annual Setup Cost = $1,203.74
L = 9 (lead time in days) Annual Holding Cost = $1,203.74
WD = 250 (working days/year) Total Variable Cost = $2,407.49
Decision
Q= 573.21 (order quantity)
Basic EOQ Model (Analytical)
Data Results
a = 6000 (demand/year) Reorder Point = 216
K = $115 (setup cost)
h = $4.20 (unit holding cost) Annual Setup Cost = $1,203.74
L = 9 (lead time in days) Annual Holding Cost = $1,203.74
WD = 250 (working days/year) Total Variable Cost = $2,407.49
Decision
Q= 573.21 (order quantity)
EOQ Model with Planned Shortages (Solver)
Data Results
a = 6000 (demand/year) Max Inventory Level = 458.94
K = $115 (setup cost)
h = $4.20 (unit holding cost) Annual Setup Cost = $963.77
p = $7.50 (unit shortage cost) Annual Holding Cost = $617.80
Annual Shortage Cost = $345.97
Decision Total Variable Cost = $1,927.53
Q= 715.94 (order quantity)
Q-S= 257.00 (maximum shortage)
EOQ Model with Planned Shortages (Analytical)
Data Results
a = 6000 (demand/year) Max Inventory Level = 458.94
K = $115 (setup cost)
h = $4.20 (unit holding cost) Annual Setup Cost = $963.77
p = $7.50 (unit shortage cost) Annual Holding Cost = $617.80
Annual Shortage Cost = $345.97
Decision Total Variable Cost = $1,927.53
Q= 715.94 (order quantity)
Q-S = 257.00 (maximum shortage)
EOQ Model with Quantity Discounts (Analytical)
Data
a = 6000 (demand/year)
K = $115 (setup cost)
I = 0.21 (inventory holding cost rate)
N = 3 (number of discount categories)
Annual Annual Annual Total
Range of order quantities Purchase Setup Holding Variable
Category Price Lower Limit Upper Limit EOQ Q* Cost Cost Cost Cost
1 $20.00 0 749 573.21 573.21 $120,000.00 $1,203.74 $1,203.74 $122,407.49
2 $19.80 750 1999 576.10 750.00 $118,800.00 $920.00 $1,559.25 $121,279.25
3 $19.60 2000 10000000 579.03 2000.00 $117,600.00 $345.00 $4,116.00 $122,061.00
Results
Optimal Q = 750
Total Variable Cost = $121,279.25
Template for the Stochastic Continuous-Review Model
Data Results
a = 8000 (average demand/unit time) Q= 25674.89
K = 12000 (setup cost) R= 10791.84
h = 0.3 (unit holding cost)
p = 10 (unit shortage cost)
L = 0.75 (service level)
Demand During Lead Time
Distribution = Normal
mean = 0
stand. dev. = 16000
Template for the Stochastic Single-Period Model for Perishable Produc
Unit ordering cost c= 20
Unit holding cost h= 100
Unit shortage cost p= 45
Exponential distribution Uniform distribution
mean = 10000 a= 0
y0 = 1892.42 b= 20000
y0 = 3448.28
r Perishable Products, No Setup Cost
Template for the Stochastic Single-Period Model for Perishable Produc
Unit ordering cost c= 20
Unit holding cost h= -9
Unit shortage cost p= 45
Setup cost K= 2
Exponential distribution Uniform distribution
mean = 10000 a= 0
S= 11856.24 b= 20000
s= 11795.98 S= 13888.89
s= 13841.75
Perishable Products, with Setup Cost
Template for the Stochastic Two-Period Model, No Setup Cost
Unit ordering cost c= 10
Unit holding cost h= 10
Unit shortage cost p= 15
Exponential distribution Uniform distribution
mean = 5 t= 10
y10 = 3.71 y10 = 5.42
y20 = 1.12 y20 = 2
etup Cost
Template for the Stochastic Infinite-Period Model, No Setup Cost
Unit ordering cost c= 35
Unit holding cost h= 1
Unit shortage cost p= 15
Discount factor a= 1
Exponential distribution Uniform distribution
mean = 400 t= 800
y0 = 1044.53 y0 = 741.25
o Setup Cost
