Economic Production Lot Size Model
This spreadsheet illustrates the analysis and solution of the problem described in Chater 10 PPT presentation.
Example: Beauty Bar Soap
Beauty Bar Soap is produced on a production line that has an annual capacity of 60,000 cases.
The annual demand is estimated at 26,000 cases, with the demand rate essentially constant
throughout the year.
The cleaning, preparation, and setup of the production line cost approximately $135. The
manufacturing cost per case is $4.50, and the annual holding cost is figured at a 24% rate.
Other relevant data include a five-day lead time to schedule and set up a production run and 25
working days per year.
n Chater 10 PPT presentation.
capacity of 60,000 cases.
te essentially constant
oximately $135. The
gured at a 24% rate.
up a production run and 250
Analysis and Solution
Main formulas:
Holding Cost: HC = (Q*I*C/2 )*(1 - D/P)
Setup Cost: OC = Co * D/Q
Calculated Chart Data Input Data
Q Holding Setup Total I= 24% holding cost rate
250 $ 77 $ 14,040 $ 14,117 C = $ 4.50 unit cost of inventory
500 $ 153 $ 7,020 $ 7,173 D= 26000 demand
750 $ 230 $ 4,680 $ 4,910 Co = $ 135.00 setup cost per production run
1000 $ 306 $ 3,510 $ 3,816 P= 60000 production capacity (items per yea
1250 $ 383 $ 2,808 $ 3,191 W= 250 work days per year
1500 $ 459 $ 2,340 $ 2,799
1750 $ 536 $ 2,006 $ 2,541
2000 $ 612 $ 1,755 $ 2,367 $5,000
2250 $ 689 $ 1,560 $ 2,249
$4,500
2500 $ 765 $ 1,404 $ 2,169
2750 $ 842 $ 1,276 $ 2,118 $4,000
3000 $ 918 $ 1,170 $ 2,088
$3,500
3250 $ 995 $ 1,080 $ 2,075
3500 $ 1,071 $ 1,003 $ 2,074 $3,000
3750 $ 1,148 $ 936 $ 2,084
4000 $ 1,224 $ 878 $ 2,102 $2,500
4250 $ 1,301 $ 826 $ 2,126 $2,000
4500 $ 1,377 $ 780 $ 2,157
4750 $ 1,454 $ 739 $ 2,192 $1,500
5000 $ 1,530 $ 702 $ 2,232
$1,000
5250 $ 1,607 $ 669 $ 2,275
5500 $ 1,683 $ 638 $ 2,321 $500
5750 $ 1,760 $ 610 $ 2,370
$-
6000 $ 1,836 $ 585 $ 2,421
0 1000 2000 3000 4000 5000
6250 $ 1,913 $ 562 $ 2,474
Analysis
From the table , we can see that the total cost is minimal between Q=3,250 and Q=3,500; the average is 3,3
chart suggests that deviations by plus or minus 500 would not make big difference which is less than 10 per
Solution (how much to produce)
We use the precise formula for the optimal Q:
Qo = SQRT(2*D*Co/(I*C*(1-D/P))).
Qo = 3386.8 This is very close to our guessed value 3,375 items per production cycle.
Solution (number of production cycles per year)
We use the formula:
N = D/Qo
N= 7.677 times per year
Solution (total annual variable cost)
We use the formula from the chart area (just by copy-pasting below and substituting the variable Q by the
to the optimal Qo).
3386.8 $ 1,036 $ 1,036 $ 2,073 Total annual variable cost
Solution (production line parameters)
32.566 Duration of each production cycle
14.112 Time to produce Qo items per run (at maximal production capacity)
18.454 Production line idling time
43.3% Production line utilization rate
Solution (amximum inventory)
We use the formula:
Imax = (1-D/P)*Qo
Imax = 1919.2 Maximum inventory
of inventory
t per production run
n capacity (items per year)
Holding
Setup
Total
5000 6000 7000
,500; the average is 3,375. The
hich is less than 10 per cent.
duction cycle.
g the variable Q by the reference
mal production capacity)