1

ch13instructor.xltm

Home >Business & Finance homework help >Management homework help >1

Chapter 13

Excel Templates to accompany Operations Management, Twelfth Edition
created by Lee Tangedahl
Copyright ©2015 McGraw-Hill Education. All rights reserved.
Chapter Thirteen - Inventory Management
				Example 10
	Templates:	ABC Classification System (B)		Example 11a
		Basic Economic Order Quantity (EOQ) Model (B)		Example 11b
		Economic Production Quantity (EPQ) Model (B)		Example 12
		Quantity Discounts (B)		Example 13
		Reorder Point (ROP) with EOQ Ordering (B)		Example 14
		Fixed Order Interval Model (B)		Example 15
		Single Period Model (B)
			Solved Problems:	Solved Problem 1
		(B) - includes Basic template		Solved Problem 2
				Solved Problem 3
		Lecture Suggestions		Solved Problem 4
				Solved Problem 5
	Examples:	Example 1		Solved Problem 6
		Example 2		Solved Problem 7
		Example 3		Solved Problem 8
		Example 4
		Example 5	Problems:	Problems 1-10
		Example 6		Problems 11-20
		Example 7		Problems 21-28
		Example 8		Problems 31-39
		Example 9		Problems 40-44
See the Inventory Management tutorial for a demonstration of the these templates.
See Instructions template for complete instructions.

ABC

ABC Classification System			Basic
<Back
	Dollar Amount		Number of Items
Class	Total	Percent	Total	Percent
A	40,000	52.69%	1	10.00%
B	31,000	40.84%	3	30.00%
C	4,910	6.47%	6	60.00%
Enter lower limit for dollar amount:
A	40,000		B	9,000
		Unit	Dollar
Item	Usage	Cost	Amount	Class
1	25	360	9,000	B
2	10	70	700	C
3	24	500	12,000	B
4	15	100	1,500	C
5	7	70	490	C
6	10	1,000	10,000	B
7	2	210	420	C
8	10	4,000	40,000	A
9	80	10	800	C
10	5	200	1,000	C










Basic Template:		You can simply copy the basic template below and paste into another worksheet.
^Top		If you also copy the graph you will have to fix the cell references in the graph
		(right-click on the graph, click Select Data..., and Edit the Series values for each series).
ABC Classification System
	Dollar Amount		Number of Items
Class	Total	Percent	Total	Percent
A	4,000,000	52.69%	1	10.00%
B	3,100,000	4083.78%	3	30.00%
C	491,000	646.82%	6	60.00%
Enter lower limit for dollar amount:
A	4,000,000		B	900,000
		Unit	Dollar
Item	Usage	Cost	Amount	Class
1	2,500	360	900,000	B
2	1,000	70	70,000	C
3	2,400	500	1,200,000	B
4	1,500	100	150,000	C
5	700	70	49,000	C
6	1,000	1,000	1,000,000	B
7	200	210	42,000	C
8	1,000	4,000	4,000,000	A
9	8,000	10	80,000	C
10	500	200	100,000	C

A Dollar Amount Number of Items 0.52693979712817807 0.1 B Dollar Amount Number of Items 0.40837834277433804 0.3 C Dollar Amount Number of Items 6.4681860097483862E-2 0.6

A Dollar Amount Number of Items 0.52693979712817807 0.1 B Dollar Amount Number of Items 40.837834277433807 0.3 C Dollar Amount Number of Items 6.4681860097483863 0.6

EOQ

Basic Economic Order Quantity (EOQ) Model			Basic	x	Carry	Order	Total	Q
<Back				1	8	ERROR:#N/A	ERROR:#N/A
Annual Demand		D =	9600	30	240	ERROR:#N/A	ERROR:#N/A
Ordering cost per order		S =	75	60	480	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit		H =	16	90	720	8000	8720
Working days per year		D/Y =	288	120	960	6000	6960
Economic Order Quantity		EOQ =	300	150	1200	4800	6000
				180	1440	4000	5440
Actual Order Quantity		Q =	300	210	1680	3428.5714285714	5108.5714285714
Increment		DQ =	10	240	1920	3000	4920
Number of orders per year		D/Q =	32	270	2160	2666.6666666667	4826.6666666667
Length of order cycle (days)		Q/D =	9	300	2400	2400	4800
Average Inventory		Q/2 =	150	330	2640	2181.8181818182	4821.8181818182
Annual carrying cost		(Q/2) * H =	2400	360	2880	2000	4880
Annual ordering cost		(D/Q) * S =	2400	390	3120	1846.1538461539	4966.1538461539
Total Annual Cost		TC =	4800	420	3360	1714.2857142857	5074.2857142857
				450	3600	1600	5200
				480	3840	1500	5340
				510	4080	1411.7647058823	5491.7647058824
				540	4320	1333.3333333333	5653.3333333333
				570	4560	1263.1578947368	5823.1578947368
				600	4800	1200	6000
				300				4800
				300				0
				Start:	0	Stop:	600
				Min Y :	0	Max Y:	9600
				Steps:	20	Step:	30
Basic Template:	You can simply copy the basic template below and paste into another worksheet.
^Top
Basic Economic Order Quantity (EOQ) Model
Annual Demand		D =	9600
Ordering cost per order		S =	75
Annual carrying cost per unit		H =	16
Working days per year		D/Y =	360
Economic Order Quantity		EOQ =	300
Actual Order Quantity		Q =	300
Number of orders per year		D/Q =	32
Length of order cycle (days)		Q/D =	11.25
Average Inventory		Q/2 =	150
Annual carrying cost		(Q/2) * H =	2400
Annual ordering cost		(D/Q) * S =	2400
Total Annual Cost		TC =	4800

Carrying Cost 1 30 60 90 120 150 180 210 240 270 300 330 360 390 420 450 480 510 540 570 600 300 300 8 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 Ordering Cost 1 30 60 90 120 150 180 210 240 270 300 330 360 390 420 450 480 510 540 570 600 300 300 #N/A #N/A #N/A 8000 6000 4800 4000 3428.5714285714284 3000 2666.666666666667 2400 2181.8181818181815 2000 1846.1538461538462 1714.2857142857142 1600 1500 1411.7647058823529 1333.3333333333335 1263.1578947368421 1200 Total Cost 1 30 60 90 120 150 180 210 240 270 300 330 360 390 420 450 480 510 540 570 600 300 300 #N/A #N/A #N/A 8720 6960 6000 5440 5108.5714285714284 4920 4826.666666666667 4800 4821.818181818182 4880 4966.1538461538457 5074.2857142857138 5200 5340 5491.7647058823532 5653.3333333333339 5823.1578947368416 6000 Q

1 30 60 90 120 150 180 210 240 270 300 330 360 390 420 450 480 510 540 570 600 300 300 4800 0

Order Quantity (Q)

EPQ

Economic Production Quantity (EPQ) Model			Basic	x	Carrying	Setup	Total	Q
<Back				1	0.375	ERROR:#N/A	ERROR:#N/A
Annual Demand		D =	48000	240	90	ERROR:#N/A	ERROR:#N/A
Setup cost		S =	45	480	180	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit		H =	1	720	270	3000	3270
Production rate		p =	800	960	360	2250	2610
Usage rate		u =	200	1200	450	1800	2250
Production days per year		D/Y =	240	1440	540	1500	2040
Economic Run Quantity		Qp =	2400	1680	630	1285.7142857143	1915.7142857143
				1920	720	1125	1845
				2160	810	1000	1810
Actual Run Quantity		Q =	2400	2400	900	900	1800
Increment		DQ =	100	2640	990	818.1818181818	1808.1818181818
Number of runs per year		D/Q =	20	2880	1080	750	1830
Cycle time		Q/u =	12	3120	1170	692.3076923077	1862.3076923077
Run time		Q/p =	3	3360	1260	642.8571428571	1902.8571428571
Average Inventory		Iave =	900	3600	1350	600	1950
Maximum Inventory		Imax =	1800	3840	1440	562.5	2002.5
Annual carrying cost		Iave * H =	900	4080	1530	529.4117647059	2059.4117647059
Annual setup cost		(D/Q) * S =	900	4320	1620	500	2120
Total Annual Cost		TC =	1800	4560	1710	473.6842105263	2183.6842105263
				4800	1800	450	2250
				2400				1800
				2400				0
				Start:	0	Stop:	4800
				Min Y :	0	Max Y:	4200
				Steps:	20	Step:	240
Basic Template:	You can simply copy the basic template below and paste into another worksheet.
^Top
Economic Production Quantity (EPQ) Model
Annual Demand		D =	48000
Setup cost		S =	45
Annual carrying cost per unit		H =	1
Production rate		p =	800
Usage rate		u =	200
Production days per year		D/Y =	240
Economic Run Quantity		Qp =	2400
Actual Run Quantity		Q =	2400
Increment		DQ =	100
Number of runs per year		D/Q =	20
Cycle time		Q/u =	12
Run time		Q/p =	3
Average Inventory		Iave =	900
Maximum Inventory		Imax =	1800
Annual carrying cost		Iave * H =	900
Annual setup cost		(D/Q) * S =	900
Total Annual Cost		TC =	1800

Carrying Cost 1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 0.375 90 180 270 360 450 540 630 720 810 900 990 1080 1170 1260 1350 1440 1530 1620 1710 1800 SetupCost 1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 #N/A #N/A #N/A 3000 2250 1800 1500 1285.7142857142858 1125 1000 900 818.18181818181824 750 692.30769230769238 642.85714285714289 600 562.5 529.41176470588243 500 473.68421052631584 450 Total Cost 1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 #N/A #N/A #N/A 3270 2610 2250 2040 1915.7142857142858 1845 1810 1800 1808.1818181818182 1830 1862.3076923076924 1902.8571428571429 1950 2002.5 2059.4117647058824 2120 2183.6842105263158 2250 Q

1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 1800 0

Run Quantity (Q)

Quantity Discounts

Quantity Discounts		Basic										1	2	3	4	5	6	7
<Back	Notes		Price Level:	1	2	3	4	5	6	7	x	1	50	80	100	0	0	0
Minimum quantity for price			Qmin =	1	50	80	100				1	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1	26114	24482	23666	22850	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Price			P =	20.00	18.00	17.00	16.00				5	18288.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		5	18288.4	16656.4	15840.4	15024.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Optimal Q (for each price)			Qopt =	70	70	80	100				10	17319.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		10	17319.2	15687.2	14871.2	14055.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Number of orders per year			D/Qopt =	11.6571428571	11.6571428571	10.2	8.16				15	17002.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		15	17002.8	15370.8	14554.8	13738.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Average Inventory			Qopt/2 =	35	35	40	50				20	16849.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		20	16849.6	15217.6	14401.6	13585.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost			(Qopt/2) * H =	140	140	160	200				25	16761.68	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		25	16761.68	15129.68	14313.68	13497.68	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual ordering cost			(D/Qopt) * S =	139.8857142857	139.8857142857	122.4	97.92				30	16706.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		30	16706.4	15074.4	14258.4	13442.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual purchase cost			P * D =	16320	14688	13872	13056				35	16669.7714285714	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		35	16669.7714285714	15037.7714285714	14221.7714285714	13405.7714285714	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Total Annual Cost			TC =	16599.8857142857	14967.8857142857	14154.4	13353.92				40	16644.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		40	16644.8	15012.8	14196.8	13380.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											45	16627.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		45	16627.6	14995.6	14179.6	13363.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual Demand			D =	816							50	16615.84	14983.84	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		50	16615.84	14983.84	14167.84	13351.84	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Ordering cost per order			S =	12							55	16608.0363636364	14976.0363636364	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		55	16608.0363636364	14976.0363636364	14160.0363636364	13344.0363636364	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit:			H (fixed) =	4							60	16603.2	14971.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		60	16603.2	14971.2	14155.2	13339.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
1	TRUE		H (% of price) =								65	16600.6461538462	14968.6461538462	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		65	16600.6461538462	14968.6461538462	14152.6461538462	13336.6461538462	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Optimal Q (overall)			Qopt =	100							70	16599.8857142857	14967.8857142857	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		70	16599.8857142857	14967.8857142857	14151.8857142857	13335.8857142857	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											75	16600.56	14968.56	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		75	16600.56	14968.56	14152.56	13336.56	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Actual Order Quantity			Q =	100							80	16602.4	14970.4	14154.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		80	16602.4	14970.4	14154.4	13338.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Increment			DQ =	10							85	16605.2	14973.2	14157.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		85	16605.2	14973.2	14157.2	13341.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Price			P =	16							90	16608.8	14976.8	14160.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		90	16608.8	14976.8	14160.8	13344.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Number of orders per year			D/Q =	8.16							95	16613.0736842105	14981.0736842105	14165.0736842105	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		95	16613.0736842105	14981.0736842105	14165.0736842105	13349.0736842105	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Average Inventory			Q/2 =	50							100	16617.92	14985.92	14169.92	13353.92	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		100	16617.92	14985.92	14169.92	13353.92	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost			(Q/2) * H =	200							105	16623.2571428571	14991.2571428571	14175.2571428571	13359.2571428571	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		105	16623.2571428571	14991.2571428571	14175.2571428571	13359.2571428571	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual ordering cost			(D/Q) * S =	97.92							110	16629.0181818182	14997.0181818182	14181.0181818182	13365.0181818182	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		110	16629.0181818182	14997.0181818182	14181.0181818182	13365.0181818182	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual purchase cost			P * D =	13056							115	16635.147826087	15003.147826087	14187.147826087	13371.147826087	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		115	16635.147826087	15003.147826087	14187.147826087	13371.147826087	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Total Annual Cost			TC =	13353.92							120	16641.6	15009.6	14193.6	13377.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		120	16641.6	15009.6	14193.6	13377.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											125	16648.336	15016.336	14200.336	13384.336	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		125	16648.336	15016.336	14200.336	13384.336	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											130	16655.3230769231	15023.3230769231	14207.3230769231	13391.3230769231	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		130	16655.3230769231	15023.3230769231	14207.3230769231	13391.3230769231	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Notes:	Use radio buttons to select holding cost as a fixed amount or as percent of price.										135	16662.5333333333	15030.5333333333	14214.5333333333	13398.5333333333	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		135	16662.5333333333	15030.5333333333	14214.5333333333	13398.5333333333	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
	Enter holding cost as a fixed amount in cell E15 or as a percent of price in cell E16.										140	16669.9428571429	15037.9428571429	14221.9428571429	13405.9428571429	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		140	16669.9428571429	15037.9428571429	14221.9428571429	13405.9428571429	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
^Top											145	16677.5310344828	15045.5310344828	14229.5310344828	13413.5310344828	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		145	16677.5310344828	15045.5310344828	14229.5310344828	13413.5310344828	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											150	16685.28	15053.28	14237.28	13421.28	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		150	16685.28	15053.28	14237.28	13421.28	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											155	16693.1741935484	15061.1741935484	14245.1741935484	13429.1741935484	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		155	16693.1741935484	15061.1741935484	14245.1741935484	13429.1741935484	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											160	16701.2	15069.2	14253.2	13437.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		160	16701.2	15069.2	14253.2	13437.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											165	16709.3454545455	15077.3454545455	14261.3454545455	13445.3454545455	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		165	16709.3454545455	15077.3454545455	14261.3454545455	13445.3454545455	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											170	16717.6	15085.6	14269.6	13453.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		170	16717.6	15085.6	14269.6	13453.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											175	16725.9542857143	15093.9542857143	14277.9542857143	13461.9542857143	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		175	16725.9542857143	15093.9542857143	14277.9542857143	13461.9542857143	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											180	16734.4	15102.4	14286.4	13470.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		180	16734.4	15102.4	14286.4	13470.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											185	16742.9297297297	15110.9297297297	14294.9297297297	13478.9297297297	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		185	16742.9297297297	15110.9297297297	14294.9297297297	13478.9297297297	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											190	16751.5368421053	15119.5368421053	14303.5368421053	13487.5368421053	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		190	16751.5368421053	15119.5368421053	14303.5368421053	13487.5368421053	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											195	16760.2153846154	15128.2153846154	14312.2153846154	13496.2153846154	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		195	16760.2153846154	15128.2153846154	14312.2153846154	13496.2153846154	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											200	16768.96	15136.96	14320.96	13504.96	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		200	16768.96	15136.96	14320.96	13504.96	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											100								0	100	16617.92	14985.92	14169.92	13353.92	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
											100								13353.92	100	16617.92	14888.5892434192	14072.657684878	13256.6958173942
Basic Template:		You can simply copy the basic template below and paste into another worksheet.									Start:	0	Stop:	200
^Top											Min Y :	0	Max Y:	24899.8285714286
											Steps:	40	Step:	5
Quantity Discounts
			Price Level:	1	2	3	4	5	6	7
Minimum quantity for price			Qmin =	1	50	80	100
Price			P =	20.00	18.00	17.00	16.00
Optimal Q (for each price)			Qopt =	70	70	80	100
Number of orders per year			D/Qopt =	11.6571428571	11.6571428571	10.2	8.16
Average Inventory			Qopt/2 =	35	35	40	50
Annual carrying cost			(Qopt/2) * H =	140	140	160	200
Annual ordering cost			(D/Qopt) * S =	139.8857142857	139.8857142857	122.4	97.92
Annual purchase cost			P * D =	16320	14688	13872	13056
Total Annual Cost			TC =	16599.8857142857	14967.8857142857	14154.4	13353.92
Annual Demand			D =	816
Ordering cost per order			S =	12
Annual carrying cost per unit:			H (fixed) =	4	<Must be blank if holding cost is a percent of price
			H (% of price) =

1 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A 18288.400000000001 17319.2 17002.8 16849.599999999999 16761.68 16706.400000000001 16669.771428571428 16644.8 16627.599999999999 16615.84 16608.036363636362 16603.2 16600.646153846155 16599.885714285716 16600.560000000001 16602.400000000001 16605.2 16608.8 16613.073684210525 16617.919999999998 16623.257142857143 16629.018181818181 16635.147826086955 16641.599999999999 16648.335999999999 16655.323076923076 16662.533333333333 16669.942857142858 16677.531034482759 16685.28 16693.174193548388 16701.2 16709.345454545455 16717.599999999999 16725.954285714284 16734.400000000001 16742.929729729731 16751.536842105263 16760.215384615385 16768.96 2 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 14983.84 14976.036363636364 14971.2 14968.646153846154 14967.885714285714 14968.56 14970.4 14973.2 14976.8 14981.073684210527 14985.92 14991.257142857143 14997.018181818181 15003.147826086957 15009.6 15016.335999999999 15023.323076923078 15030.533333333333 15037.942857142858 15045.531034482759 15053.28 15061.174193548388 15069.2 15077.345454545455 15085.6 15093.954285714286 15102.4 15110.929729729729 15119.536842105263 15128.215384615385 15136.96 3 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 14154.4 14157.2 14160.8 14165.073684210527 14169.92 14175.257142857143 14181.018181818181 14187.147826086957 14193.6 14200.335999999999 14207.323076923078 14214.533333333333 14221.942857142858 14229.531034482759 14237.28 14245.174193548388 14253.2 14261.345454545455 14269.6 14277.954285714286 14286.4 14294.929729729729 14303.536842105263 14312.215384615385 14320.96 4 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 13353.92 13359.257142857143 13365.018181818181 13371.147826086957 13377.6 13384.335999999999 13391.323076923078 13398.533333333333 13405.942857142858 13413.531034482759 13421.28 13429.174193548388 13437.2 13445.345454545455 13453.6 13461.954285714286 13470.4 13478.929729729729 13487.536842105263 13496.215384615385 13504.96 5 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 6 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 7 1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 0 13353.92

Order Quantity (Q)

Fixed

Percent of Price

ROP

Reorder Point (ROP) with EOQ Ordering			Basic
<Back						14	ERROR:#NUM!
						14	ERROR:#NUM!
Average demand		d =	2			14	ERROR:#NUM!
Std dev demand		sd =	0			14	ERROR:#NUM!
Average lead time		LT =	7			14	ERROR:#NUM!
Std dev lead time		sLT =	0			14	ERROR:#NUM!
Service level		SL =	0.4			14	ERROR:#NUM!
Increment		DSL =	0.05			14	ERROR:#NUM!
Stock out risk			0.6			14	ERROR:#NUM!
Average demand during lead time		dLT =	14	14	ERROR:#N/A	14	ERROR:#NUM!
Std dev demand during lead time		sdLT =	0	14	14	14	ERROR:#NUM!
		z =	-0.2533471031	14	ERROR:#N/A	14		0
Safety stock		SS =	0			14		1
Reorder point		ROP =	14			14			0
						14			1
Basic Template:	You can simply copy the basic template below and paste into another worksheet.
^Top
Reorder Point (ROP) with EOQ Ordering
Average demand		d =	2
Std dev demand		sd =	0
Average lead time		LT =	7
Std dev lead time		sLT =	0
Service level		SL =	0.4
Stock out risk			0.6
Average demand during lead time		dLT =	14
Std dev demand during lead time		sdLT =	0
		z =	-0.2533471031
Safety stock		SS =	0
Reorder point		ROP =	14

14 14 14 14 14 14 14 14 14 14 14 14 14 14 14 0 0 0 0 0 0 0 0 0 0 0 dLT 14 14 14 14 14 14 14 14 14 14 14 14 14 14 14 0 1 ROP 14 14 14 14 14 14 14 14 14 14 14 14 14 14 14 0 1

Fixed Order Interval

Fixed Order Interval Model		Basic
<Back
Average demand			d =	30
Std dev demand			sd =	3
Lead time			LT =	2
Amount on hand at reorder time			A =	71
Order interval			OI =	7
Service level			SL =	0.99
Increment			DSL =	0.01
Stock out risk				0.01	199	270	ERROR:#N/A	ERROR:#N/A
Average demand during lead time			dLT =	270	199	270	219.9371308664	ERROR:#N/A
Safety stock			SS =	20.9371308664	199	270	ERROR:#N/A	ERROR:#N/A
Amount to order				219.9371308664
Basic Template:	You can simply copy the basic template below and paste into another worksheet.
^Top
Fixed Order Interval Model
Average demand			d =	30
Std dev demand			sd =	3
Lead time			LT =	2
Amount on hand at reorder time			A =	71
Order interval			OI =	7
Service level			SL =	0.99
Stock out risk				0.01
Average demand during lead time			dLT =	270
Safety stock			SS =	20.9371308664
Amount to order				219.9371308664

#N/A #N/A #N/A Amount to order #N/A 219.93713086636757 #N/A Average demand during lead time 270 270 270 dLT less Amount on hand 199 199 199

Single Period

Single Period Model		Basic
<Back
Select demand distribution:			Uniform
	1
Shortage cost (revenue - cost)			Cs =	0.6	0.4
Excess cost (cost - salvage)			Ce =	0.2
Minimum demand				300
Maximum demand				500
Optimal Service Level			SLo =	0.75
Optimal Stocking Level			So =	450

Actual Stocking Level			S =	450
Increment			DS =	10
Actual Service Level			SL =	0.7500
Balance:
			Ce * Service Level	0.15
			Cs * (1 - Service Level)	0.15
Discrete Distribution:		Demand	Freq	Cum












Basic Template:		You can simply copy the basic template below and paste into another worksheet.
^Top
Single Period Model			Demand distribution:	Uniform	Single Period Model		Demand distribution:	Normal
Shortage cost (revenue - cost)			Cs =	0.6	Shortage cost (revenue - cost)		Cs =	0.6
Excess cost (cost - salvage)			Ce =	0.2	Excess cost (cost - salvage)		Ce =	0.2
Minimum demand				300	Average demand			200
Maximum demand				500	Std dev demand			10
Optimal Service Level			SLo =	0.75	Optimal Service Level		SLo =	0.75
Optimal Stocking Level			So =	450	Optimal Stocking Level		So =	206.744897502
Actual Stocking Level			S =	450	Actual Stocking Level		S =	207
Actual Service Level			SL =	0.7500	Actual Service Level		SL =	0.7580
Balance:					Balance:
			Ce * Service Level	0.15			Ce * Service Level	0.1516072696
			Cs * (1 - Service Level)	0.15			Cs * (1 - Service Level)	0.1451781913
Single Period Model			Demand distribution:	Poisson	Single Period Model		Demand distribution:	Discrete
Shortage cost (revenue - cost)			Cs =	3	Shortage cost (revenue - cost)		Cs =	1.6
Excess cost (cost - salvage)			Ce =	2	Excess cost (cost - salvage)		Ce =	0.8
Average demand				4

Optimal Service Level			SLo =	0.6	Optimal Service Level		SLo =	0.6666666667
			Adjust S until SL >= SLo				Adjust S until SL >= SLo
Actual Stocking Level			S =	4	Actual Stocking Level		S =	25
Actual Service Level			SL =	0.6288	Actual Service Level		SL =	0.7300
Balance:					Balance:
			Ce * Service Level	1.2576738704			Ce * Service Level	0.584
			Cs * (1 - Service Level)	1.1134891945			Cs * (1 - Service Level)	0.432
					Discrete Distribution:
						Demand	Freq	Cum
						19	0.01	0.01
						20	0.05	0.06
						21	0.12	0.18
						22	0.18	0.36
						23	0.13	0.49
						24	0.14	0.63
						25	0.1	0.73
						26	0.11	0.84
						27	0.1	0.94
						28	0.04	0.98
						29	0.02	1
								1

0.4 Ce * Service Level 0.15000000000000002 Cs * (1 - Service Level) 0.15

Uniform

Normal

Poisson

Discrete

Lecture

Lecture Suggestions - Chapter 12
<Back
Example 1: ABC Classification System
1.	Select the Example 1 worksheet
2.	You may delete and re-enter the usage and unit cost data for the 12 items if you want. Note that
	although the 12 items for his example are in order according to dollar value, this is not necessary,
	the items can be entered in any order.
3.	Delete the values for A and B.
4.	Initialize A and B by clicking the spinner button for A up two times and the spinner button for B
	up one time, you should have A = 42,000 and B=18,000.
5.	Scroll down so you can observe all 12 items in the table as you change the values of A and B.
	A and B are the lower limits for the A and B classifications and as you click a spinner button the
	value for A or B will change to the next higher (or lower) dollar value in the table. At the same time
	all the items are classified according to the current values for A and B.
6.	Now scroll back up so you can watch the graph and the table at the top of the template and
	adjust the values of A and B to get the desired characteristics for each class, i.e. high value,
	low volume in classification A and low value, high volume in classification C.
Example 18: Single Period Model
1.	Select the Example 18 worksheet
2.	Select Poisson for the demand distribution.
3.	Enter data: Cs = 3, Ce = 2, and average demand = 4 (all given).
4.	Note that optimal service level = .60.
5.	Set actual stocking level = 0 and note that the service level is computed (according to the
	Poisson distribution) to be .0183, i.e. 18% of the time there will be a demand of zero.
6.	Increment the actual stocking level to 1 and note that the probability of demand <= 1 is .0916.
7.	Point out in the graph that the blue bar (expected shortage cost) is high when the actual stocking
	level is low and decreases with increasing actual stocking level (because the probability of a
	shortage goes down). The optimal actual stocking level will occur when the blue and red
	bars are balanced, i.e. you balance the expected shortage cost with the expected excess cost.
7.	Finally, solve for the optimal actual stocking level - increase the actual stocking level until the
	resulting service level just exceeds .60 - and at the same time red and blue bars in the graph
	will come the closest to balancing. This will happen at actual stocking level = 4.

Example 1

ABC Classification System
<Back
	Dollar Amount		Number of Items
Class	Total	Percent	Total	Percent
A	40,000	52.69%	1	10.00%
B	31,000	40.84%	3	30.00%
C	4,910	6.47%	6	60.00%
Enter lower limit for dollar amount:
A	40,000		B	9,000
		Unit	Dollar
Item	Usage	Cost	Amount	Class
1	25	360	9,000	B
2	10	70	700	C
3	24	500	12,000	B
4	15	100	1,500	C
5	7	70	490	C
6	10	1,000	10,000	B
7	2	210	420	C
8	10	4,000	40,000	A
9	80	10	800	C
10	5	200	1,000	C

A Dollar Amount Number of Items 0.52693979712817807 0.1 B Dollar Amount Number of Items 0.40837834277433804 0.3 C Dollar Amount Number of Items 6.4681860097483862E-2 0.6

Example 2

Basic Economic Order Quantity (EOQ) Model			x	Carry	Order	Total	Q
<Back			1	8	ERROR:#N/A	ERROR:#N/A
Annual Demand	D =	9600	30	240	ERROR:#N/A	ERROR:#N/A
Ordering cost per order	S =	75	60	480	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit	H =	16	90	720	8000	8720
Working days per year	D/Y =	288	120	960	6000	6960
Economic Order Quantity	EOQ =	300	150	1200	4800	6000
			180	1440	4000	5440
Actual Order Quantity	Q =	300	210	1680	3428.5714285714	5108.5714285714
Increment	DQ =	10	240	1920	3000	4920
Number of orders per year	D/Q =	32	270	2160	2666.6666666667	4826.6666666667
Length of order cycle (days)	Q/D =	9	300	2400	2400	4800
Average Inventory	Q/2 =	150	330	2640	2181.8181818182	4821.8181818182
Annual carrying cost	(Q/2) * H =	2400	360	2880	2000	4880
Annual ordering cost	(D/Q) * S =	2400	390	3120	1846.1538461539	4966.1538461539
Total Annual Cost	TC =	4800	420	3360	1714.2857142857	5074.2857142857
			450	3600	1600	5200
			480	3840	1500	5340
			510	4080	1411.7647058823	5491.7647058824
			540	4320	1333.3333333333	5653.3333333333
			570	4560	1263.1578947368	5823.1578947368
			600	4800	1200	6000
			300				4800
			300				0
			Start:	0	Stop:	600
			Min Y :	0	Max Y:	9600
			Steps:	20	Step:	30

1 30 60 90 120 150 180 210 240 270 300 330 360 390 420 450 480 510 540 570 600 300 300 4800 0

Order Quantity (Q)

Example 3

Basic Economic Order Quantity (EOQ) Model			x	Carry	Order	Total	Q
<Back			1	18	ERROR:#N/A	ERROR:#N/A
Annual Demand	D =	3600	10	180	ERROR:#N/A	ERROR:#N/A
Ordering cost per order	S =	50	20	360	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit	H =	36	30	540	6000	6540
Working days per year	D/Y =	360	40	720	4500	5220
Economic Order Quantity	EOQ =	100	50	900	3600	4500
			60	1080	3000	4080
Actual Order Quantity	Q =	100	70	1260	2571.4285714286	3831.4285714286
Increment	DQ =	1	80	1440	2250	3690
Number of orders per year	D/Q =	36	90	1620	2000	3620
Length of order cycle (days)	Q/D =	10	100	1800	1800	3600
Average Inventory	Q/2 =	50	110	1980	1636.3636363636	3616.3636363636
Annual carrying cost	(Q/2) * H =	1800	120	2160	1500	3660
Annual ordering cost	(D/Q) * S =	1800	130	2340	1384.6153846154	3724.6153846154
Total Annual Cost	TC =	3600	140	2520	1285.7142857143	3805.7142857143
			150	2700	1200	3900
			160	2880	1125	4005
			170	3060	1058.8235294118	4118.8235294118
			180	3240	1000	4240
			190	3420	947.3684210526	4367.3684210526
			200	3600	900	4500
			100				3600
			100				0
			Start:	0	Stop:	200
			Min Y :	0	Max Y:	7200
			Steps:	20	Step:	10

Carrying Cost 1 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 200 100 100 18 180 360 540 720 900 1080 1260 1440 1620 1800 1980 2160 2340 2520 2700 2880 3060 3240 3420 3600 Ordering Cost 1 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 200 100 100 #N/A #N/A #N/A 6000 4500 3600 3000 2571.4285714285716 2250 2000 1800 1636.3636363636363 1500 1384.6153846153848 1285.7142857142858 1200 1125 1058.8235294117646 1000 947.36842105263145 900 Total Cost 1 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 200 100 100 #N/A #N/A #N/A 6540 5220 4500 4080 3831.4285714285716 3690 3620 3600 3616.363636363636 3660 3724.6153846153848 3805.7142857142858 3900 4005 4118.8235294117649 4240 4367.3684210526317 4500 Q

1 10 20 30 40 50 60 70 80 90 100 110 120 130 140 150 160 170 180 190 200 100 100 3600 0

Order Quantity (Q)

Example 4

Economic Production Quantity (EPQ) Model			x	Carrying	Setup	Total	Q
<Back			1	0.375	ERROR:#N/A	ERROR:#N/A
Annual Demand	D =	48000	240	90	ERROR:#N/A	ERROR:#N/A
Setup cost	S =	45	480	180	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit	H =	1	720	270	3000	3270
Production rate	p =	800	960	360	2250	2610
Usage rate	u =	200	1200	450	1800	2250
Production days per year	D/Y =	240	1440	540	1500	2040
Economic Run Quantity	Qp =	2400	1680	630	1285.7142857143	1915.7142857143
			1920	720	1125	1845
			2160	810	1000	1810
Actual Run Quantity	Q =	2400	2400	900	900	1800
Increment	DQ =	100	2640	990	818.1818181818	1808.1818181818
Number of runs per year	D/Q =	20	2880	1080	750	1830
Cycle time	Q/u =	12	3120	1170	692.3076923077	1862.3076923077
Run time	Q/p =	3	3360	1260	642.8571428571	1902.8571428571
Average Inventory	Iave =	900	3600	1350	600	1950
Maximum Inventory	Imax =	1800	3840	1440	562.5	2002.5
Annual carrying cost	Iave * H =	900	4080	1530	529.4117647059	2059.4117647059
Annual setup cost	(D/Q) * S =	900	4320	1620	500	2120
Total Annual Cost	TC =	1800	4560	1710	473.6842105263	2183.6842105263
			4800	1800	450	2250
			2400				1800
			2400				0
			Start:	0	Stop:	4800
			Min Y :	0	Max Y:	4200
			Steps:	20	Step:	240

Carrying Cost 1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 0.375 90 180 270 360 450 540 630 720 810 900 990 1080 1170 1260 1350 1440 1530 1620 1710 1800 SetupCost 1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 #N/A #N/A #N/A 3000 2250 1800 1500 1285.7142857142858 1125 1000 900 818.18181818181824 750 692.30769230769238 642.85714285714289 600 562.5 529.41176470588243 500 473.68421052631584 450 Total Cost 1 240 480 720 960 1200 1440 1680 1920 2160 2400 26 40 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 #N/A #N/A #N/A 3270 2610 2250 2040 1915.7142857142858 1845 1810 1800 1808.1818181818182 1830 1862.3076923076924 1902.8571428571429 1950 2002.5 2059.4117647058824 2120 2183.6842105263158 2250 Q

1 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 2400 2400 1800 0

Run Quantity (Q)

Example 5

Quantity Discounts											1	2	3	4	5	6	7
<Back	Notes	Price Level:	1	2	3	4	5	6	7	x	1	50	80	100	0	0	0
Minimum quantity for price		Qmin =	1	50	80	100				1	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1	26114	24482	23666	22850	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Price		P =	20.00	18.00	17.00	16.00				5	18288.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		5	18288.4	16656.4	15840.4	15024.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Optimal Q (for each price)		Qopt =	70	70	80	100				10	17319.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		10	17319.2	15687.2	14871.2	14055.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Number of orders per year		D/Qopt =	11.6571428571	11.6571428571	10.2	8.16				15	17002.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		15	17002.8	15370.8	14554.8	13738.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Average Inventory		Qopt/2 =	35	35	40	50				20	16849.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		20	16849.6	15217.6	14401.6	13585.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost		(Qopt/2) * H =	140	140	160	200				25	16761.68	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		25	16761.68	15129.68	14313.68	13497.68	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual ordering cost		(D/Qopt) * S =	139.8857142857	139.8857142857	122.4	97.92				30	16706.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		30	16706.4	15074.4	14258.4	13442.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual purchase cost		P * D =	16320	14688	13872	13056				35	16669.7714285714	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		35	16669.7714285714	15037.7714285714	14221.7714285714	13405.7714285714	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Total Annual Cost		TC =	16599.8857142857	14967.8857142857	14154.4	13353.92				40	16644.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		40	16644.8	15012.8	14196.8	13380.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										45	16627.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		45	16627.6	14995.6	14179.6	13363.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual Demand		D =	816							50	16615.84	14983.84	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		50	16615.84	14983.84	14167.84	13351.84	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Ordering cost per order		S =	12							55	16608.0363636364	14976.0363636364	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		55	16608.0363636364	14976.0363636364	14160.0363636364	13344.0363636364	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit:		H (fixed) =	4							60	16603.2	14971.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		60	16603.2	14971.2	14155.2	13339.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
1	TRUE	H (% of price) =								65	16600.6461538462	14968.6461538462	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		65	16600.6461538462	14968.6461538462	14152.6461538462	13336.6461538462	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Optimal Q (overall)		Qopt =	100							70	16599.8857142857	14967.8857142857	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		70	16599.8857142857	14967.8857142857	14151.8857142857	13335.8857142857	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										75	16600.56	14968.56	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		75	16600.56	14968.56	14152.56	13336.56	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Actual Order Quantity		Q =	100							80	16602.4	14970.4	14154.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		80	16602.4	14970.4	14154.4	13338.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Increment		DQ =	10							85	16605.2	14973.2	14157.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		85	16605.2	14973.2	14157.2	13341.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Price		P =	16							90	16608.8	14976.8	14160.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		90	16608.8	14976.8	14160.8	13344.8	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Number of orders per year		D/Q =	8.16							95	16613.0736842105	14981.0736842105	14165.0736842105	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		95	16613.0736842105	14981.0736842105	14165.0736842105	13349.0736842105	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Average Inventory		Q/2 =	50							100	16617.92	14985.92	14169.92	13353.92	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		100	16617.92	14985.92	14169.92	13353.92	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost		(Q/2) * H =	200							105	16623.2571428571	14991.2571428571	14175.2571428571	13359.2571428571	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		105	16623.2571428571	14991.2571428571	14175.2571428571	13359.2571428571	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual ordering cost		(D/Q) * S =	97.92							110	16629.0181818182	14997.0181818182	14181.0181818182	13365.0181818182	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		110	16629.0181818182	14997.0181818182	14181.0181818182	13365.0181818182	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual purchase cost		P * D =	13056							115	16635.147826087	15003.147826087	14187.147826087	13371.147826087	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		115	16635.147826087	15003.147826087	14187.147826087	13371.147826087	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Total Annual Cost		TC =	13353.92							120	16641.6	15009.6	14193.6	13377.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		120	16641.6	15009.6	14193.6	13377.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										125	16648.336	15016.336	14200.336	13384.336	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		125	16648.336	15016.336	14200.336	13384.336	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										130	16655.3230769231	15023.3230769231	14207.3230769231	13391.3230769231	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		130	16655.3230769231	15023.3230769231	14207.3230769231	13391.3230769231	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Notes:	Use radio buttons to select holding cost as a fixed amount or as percent of price.									135	16662.5333333333	15030.5333333333	14214.5333333333	13398.5333333333	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		135	16662.5333333333	15030.5333333333	14214.5333333333	13398.5333333333	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
	Enter holding cost as a fixed amount in cell E15 or as a percent of price in cell E16.									140	16669.9428571429	15037.9428571429	14221.9428571429	13405.9428571429	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		140	16669.9428571429	15037.9428571429	14221.9428571429	13405.9428571429	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
^Top										145	16677.5310344828	15045.5310344828	14229.5310344828	13413.5310344828	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		145	16677.5310344828	15045.5310344828	14229.5310344828	13413.5310344828	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										150	16685.28	15053.28	14237.28	13421.28	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		150	16685.28	15053.28	14237.28	13421.28	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										155	16693.1741935484	15061.1741935484	14245.1741935484	13429.1741935484	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		155	16693.1741935484	15061.1741935484	14245.1741935484	13429.1741935484	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										160	16701.2	15069.2	14253.2	13437.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		160	16701.2	15069.2	14253.2	13437.2	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										165	16709.3454545455	15077.3454545455	14261.3454545455	13445.3454545455	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		165	16709.3454545455	15077.3454545455	14261.3454545455	13445.3454545455	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										170	16717.6	15085.6	14269.6	13453.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		170	16717.6	15085.6	14269.6	13453.6	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										175	16725.9542857143	15093.9542857143	14277.9542857143	13461.9542857143	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		175	16725.9542857143	15093.9542857143	14277.9542857143	13461.9542857143	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										180	16734.4	15102.4	14286.4	13470.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		180	16734.4	15102.4	14286.4	13470.4	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										185	16742.9297297297	15110.9297297297	14294.9297297297	13478.9297297297	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		185	16742.9297297297	15110.9297297297	14294.9297297297	13478.9297297297	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										190	16751.5368421053	15119.5368421053	14303.5368421053	13487.5368421053	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		190	16751.5368421053	15119.5368421053	14303.5368421053	13487.5368421053	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										195	16760.2153846154	15128.2153846154	14312.2153846154	13496.2153846154	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		195	16760.2153846154	15128.2153846154	14312.2153846154	13496.2153846154	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										200	16768.96	15136.96	14320.96	13504.96	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		200	16768.96	15136.96	14320.96	13504.96	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										100								0	100	16617.92	14985.92	14169.92	13353.92	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										100								13353.92	100	16617.92	14888.5892434192	14072.657684878	13256.6958173942
										Start:	0	Stop:	200
										Min Y :	0	Max Y:	24899.8285714286
										Steps:	40	Step:	5

1 5 10 15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100 105 110 115 120 125 130 135 140 145 150 155 160 165 170 175 180 185 190 195 200 100 100 0 13353.92

Order Quantity (Q)

Fixed

Percent of Price

Example 6

Quantity Discounts											1	2	3	4	5	6	7
<Back	Notes	Price Level:	1	2	3	4	5	6	7	x	1	500	1000	0	0	0	0
Minimum quantity for price		Qmin =	1	500	1000					1	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1	123600.18	123400.17	123200.16	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Price		P =	0.90	0.85	0.80					50	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		50	6009	5808.5	5608	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Optimal Q (for each price)		Qopt =	816	840	1000					100	4818	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		100	4818	4617	4416	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Number of orders per year		D/Qopt =	4.9019607843	4.7619047619	4					150	4427	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		150	4427	4225.5	4024	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Average Inventory		Qopt/2 =	408	420	500					200	4236	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		200	4236	4034	3832	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost		(Qopt/2) * H =	146.88	142.8	160					250	4125	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		250	4125	3922.5	3720	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual ordering cost		(D/Qopt) * S =	147.0588235294	142.8571428571	120					300	4054	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		300	4054	3851	3648	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual purchase cost		P * D =	3600	3400	3200					350	4005.8571428571	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		350	4005.8571428571	3802.3571428571	3598.8571428571	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Total Annual Cost		TC =	3893.9388235294	3685.6571428571	3480					400	3972	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		400	3972	3768	3564	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										450	3947.6666666667	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		450	3947.6666666667	3743.1666666667	3538.6666666667	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual Demand		D =	4000							500	3930	3725	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		500	3930	3725	3520	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Ordering cost per order		S =	30							550	3917.1818181818	3711.6818181818	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		550	3917.1818181818	3711.6818181818	3506.1818181818	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit:		H (fixed) =								600	3908	3702	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		600	3908	3702	3496	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
2	FALSE	H (% of price) =	40.00%							650	3901.6153846154	3695.1153846154	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		650	3901.6153846154	3695.1153846154	3488.6153846154	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Optimal Q (overall)		Qopt =	1000							700	3897.4285714286	3690.4285714286	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		700	3897.4285714286	3690.4285714286	3483.4285714286	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										750	3895	3687.5	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		750	3895	3687.5	3480	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Actual Order Quantity		Q =	1000							800	3894	3686	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		800	3894	3686	3478	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Increment		DQ =	100							850	3894.1764705882	3685.6764705882	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		850	3894.1764705882	3685.6764705882	3477.1764705882	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Price		P =	0.8							900	3895.3333333333	3686.3333333333	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		900	3895.3333333333	3686.3333333333	3477.3333333333	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Number of orders per year		D/Q =	4							950	3897.3157894737	3687.8157894737	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		950	3897.3157894737	3687.8157894737	3478.3157894737	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Average Inventory		Q/2 =	500							1000	3900	3690	3480	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1000	3900	3690	3480	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual carrying cost		(Q/2) * H =	160							1050	3903.2857142857	3692.7857142857	3482.2857142857	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1050	3903.2857142857	3692.7857142857	3482.2857142857	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual ordering cost		(D/Q) * S =	120							1100	3907.0909090909	3696.0909090909	3485.0909090909	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1100	3907.0909090909	3696.0909090909	3485.0909090909	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Annual purchase cost		P * D =	3200							1150	3911.347826087	3699.847826087	3488.347826087	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1150	3911.347826087	3699.847826087	3488.347826087	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Total Annual Cost		TC =	3480							1200	3916	3704	3492	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1200	3916	3704	3492	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1250	3921	3708.5	3496	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1250	3921	3708.5	3496	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1300	3926.3076923077	3713.3076923077	3500.3076923077	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1300	3926.3076923077	3713.3076923077	3500.3076923077	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
Notes:	Use radio buttons to select holding cost as a fixed amount or as percent of price.									1350	3931.8888888889	3718.3888888889	3504.8888888889	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1350	3931.8888888889	3718.3888888889	3504.8888888889	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
	Enter holding cost as a fixed amount in cell E15 or as a percent of price in cell E16.									1400	3937.7142857143	3723.7142857143	3509.7142857143	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1400	3937.7142857143	3723.7142857143	3509.7142857143	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
^Top										1450	3943.7586206897	3729.2586206897	3514.7586206897	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1450	3943.7586206897	3729.2586206897	3514.7586206897	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1500	3950	3735	3520	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1500	3950	3735	3520	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1550	3956.4193548387	3740.9193548387	3525.4193548387	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1550	3956.4193548387	3740.9193548387	3525.4193548387	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1600	3963	3747	3531	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1600	3963	3747	3531	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1650	3969.7272727273	3753.2272727273	3536.7272727273	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1650	3969.7272727273	3753.2272727273	3536.7272727273	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1700	3976.5882352941	3759.5882352941	3542.5882352941	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1700	3976.5882352941	3759.5882352941	3542.5882352941	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1750	3983.5714285714	3766.0714285714	3548.5714285714	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1750	3983.5714285714	3766.0714285714	3548.5714285714	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1800	3990.6666666667	3772.6666666667	3554.6666666667	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1800	3990.6666666667	3772.6666666667	3554.6666666667	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1850	3997.8648648649	3779.3648648649	3560.8648648649	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1850	3997.8648648649	3779.3648648649	3560.8648648649	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1900	4005.1578947368	3786.1578947368	3567.1578947368	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1900	4005.1578947368	3786.1578947368	3567.1578947368	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1950	4012.5384615385	3793.0384615385	3573.5384615385	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		1950	4012.5384615385	3793.0384615385	3573.5384615385	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										2000	4020	3800	3580	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A		2000	4020	3800	3580	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1000								0	1000	3900	3690	3480	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A	ERROR:#N/A
										1000								3480	1000	3900	3600.7692307692	3393.3262123478
										Start:	0	Stop:	2000
										Min Y :	0	Max Y:	5840.9082352941
										Steps:	40	Step:	50

1 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A 4818 4427 4236 4125 4054 4005.8571428571431 3972 3947.6666666666665 3930 3917.181818181818 3908 3901.6153846153848 3897.4285714285716 3895 3894 3894.1764705882351 3895.3333333333335 3897.3157894736842 3900 3903.2857142857142 3907.0909090 90909 3911.3478260869565 3916 3921 3926.3076923076924 3931.8888888888887 3937.7142857142858 3943.7586206896553 3950 3956.4193548387098 3963 3969.727272727273 3976.5882352941176 3983.5714285714284 3990.666666666667 3997.864864864865 4005.1578947368421 4012.5384615384614 4020 2 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 3725 3711.681818181818 3702 3695.1153846153848 3690.4285714285716 3687.5 3686 3685.6764705882351 3686.3333333333335 3687.8157894736842 3690 3692.7857142857142 3696.090909090909 3699.8478260869565 3704 3708.5 3713.3076923076924 3718.3888888888887 3723.7142857142858 3729.2586206896553 3735 3740.9193548387098 3747 3753.227272727273 3759.5882352941176 3766.0714285714284 3772.6666666666665 3779.364864864865 3786.1578947368421 3793.0384615384614 3800 3 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 3480 3482.2857142857142 3485.090909090909 3488.3478260869565 3492 3496 3500.3076923076924 3504.8888888888887 3509.7142857142858 3514.7586206896553 3520 3525.4193548387098 3531 3536.727272727273 3542.5882352941176 3548.5714285714284 3554.666666666667 3560.864864864865 3567.1578947368421 3573.5384615384614 3580 4 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 5 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 6 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 7 1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

1 50 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 950 1000 1050 1100 1150 1200 1250 1300 1350 1400 1450 1500 1550 1600 1650 1700 1750 1800 1850 1900 1950 2000 1000 1000 0 3480

Order Quantity (Q)

Fixed

Percent of Price

Example 7

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand	d =	2			14	ERROR:#NUM!
Std dev demand	sd =	0			14	ERROR:#NUM!
Average lead time	LT =	7			14	ERROR:#NUM!
Std dev lead time	sLT =	0			14	ERROR:#NUM!
Service level	SL =	1			14	ERROR:#NUM!
Increment	DSL =	0.01			14	ERROR:#NUM!
Stock out risk		0			14	ERROR:#NUM!
Average demand during lead time	dLT =	14	14	ERROR:#N/A	14	ERROR:#NUM!
Std dev demand during lead time	sdLT =	0	14	14	14	ERROR:#NUM!
	z =	ERROR:#N/A	14	ERROR:#N/A	14	ERROR:#NUM!
Safety stock	SS =	0			14	ERROR:#NUM!
Reorder point	ROP =	14			14		0
					14		1
					14			0
					14			1

14 14 14 14 14 14 14 14 14 14 14 14 14 14 14 0 0 0 0 0 0 0 0 0 0 0 dLT 14 14 14 14 14 14 14 14 14 14 14 14 14 14 14 0 1 ROP 14 14 14 14 14 14 14 14 14 14 14 14 14 14 14 0 1

Example 8

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand	d =	50			32.5	0.0001745365
Std dev demand	sd =	5			36	0.0015830903
Average lead time	LT =	1			39.5	0.0087967192
Std dev lead time	sLT =	0			43	0.0299454931
Service level	SL =	0.97			46.5	0.0624507867
Increment	DSL =	0.01			50	0.0797884561
Stock out risk		0.03			53.5	0.0624507867
Average demand during lead time	dLT =	50	50	ERROR:#N/A	57	0.0299454931
Std dev demand during lead time	sdLT =	5	50	59.4039680408	60.5	0.0087967192
	z =	1.8807936082	50	ERROR:#N/A	64	0.0015830903
Safety stock	SS =	9.4039680408			67.5	0.0001745365
Reorder point	ROP =	59.4039680408			50		0
					50		0.0797884561
					59.4039680408			0
					59.4039680408			0.0797884561

32.5 36 39.5 43 46.5 50 53.5 57 60.5 64 67.5 50 50 59.403968040756254 59.403968040756254 1.7453653900915202E-4 1.5830903165959939E-3 8.7967191960854393E-3 2.9945493127148972E-2 6.2450786673352257E-2 7.9788456080286549E-2 6.2450786673352257E-2 2.9945493127148972E-2 8.7967191960854393E-3 1.5830903165959939E-3 1.7453653900915202E-4 dLT 32.5 36 39.5 43 46.5 50 53.5 57 60.5 64 67.5 50 50 59.403968040756254 59.403968040756254 0 7.9788456080286549E-2 ROP 32.5 36 39.5 43 46.5 50 53.5 57 60.5 64 67.5 50 50 59.403968040756254 59.403968040756254 0 7.9788456080286549E-2

Example 9

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand	d =	50			85.1507575951	0.0002056933
Std dev demand	sd =	3			88.1206060761	0.0018656898
Average lead time	LT =	2			91.090454557	0.010367033
Std dev lead time	sLT =	0			94.060303038	0.0352911021
Service level	SL =	0.9			97.030151519	0.0735989579
Increment	DSL =	0.01			100	0.0940315973
Stock out risk		0.1			102.969848481	0.0735989579
Average demand during lead time	dLT =	100	100	ERROR:#N/A	105.939696962	0.0352911021
Std dev demand during lead time	sdLT =	4.2426406871	100	105.4371628146	108.909545443	0.010367033
	z =	1.2815515655	100	ERROR:#N/A	111.8793939239	0.0018656898
Safety stock	SS =	5.4371628146			114.8492424049	0.0002056933
Reorder point	ROP =	105.4371628146			100		0
					100		0.0940315973
					105.4371628146			0
					105.4371628146			0.0940315973

85.150757595082496 88.120606076065997 91.090454557049497 94.060303038032998 97.030151519016499 100 102.9698484809835 105.939696961967 108.9095454429505 111.879393923934 114.8492424049175 100 100 105.43716281462093 105.43716281462093 2.0569328383033552E-4 1.865689830159637E-3 1.0367032992909805E-2 3.5291102093636964E-2 7.3598957911936408E-2 9.4031597257959371E-2 7.3598957911936408E-2 3.5291102093636964E-2 1.0367032992909805E-2 1.865689830159637E-3 2.0569328383033552E-4 dLT 85.150757595082496 88.120606076065997 91.090454557049497 94.060303038032998 97.030151519016499 100 102.9698484809835 105.939696961967 108.9095454429505 111.879393923934 114.8492424049175 100 100 105.43716281462093 105.43716281462093 0 9.4031597257959371E-2 ROP 85.150757595082496 88.120606076065997 91.090454557049497 94.060303038032998 97.030151519016499 100 102.9698484809835 105.939696961967 108.9095454429505 111.879393923934 114.8492424049175 100 100 105.43716281462093 105.43716281462093 0 9.4031597257959371E-2

Example 10

Fixed Order Interval Model
<Back
Average demand	d =	30
Std dev demand	sd =	3
Lead time	LT =	2
Amount on hand at reorder time	A =	71
Order interval	OI =	7
Service level	SL =	0.99
Increment	DSL =	0.01
Stock out risk		0.01	199	270	ERROR:#N/A	ERROR:#N/A
Average demand during lead time	dLT =	270	199	270	219.9371308664	ERROR:#N/A
Safety stock	SS =	20.9371308664	199	270	ERROR:#N/A	ERROR:#N/A
Amount to order		219.9371308664

#N/A #N/A #N/A Amount to order #N/A 219.93713086636757 #N/A Average demand during lead time 270 270 270 dLT less Amount on hand 199 199 199

Example 11a

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand	d =	10			26	0.0002181707
Std dev demand	sd =	2			28.8	0.0019788629
Average lead time	LT =	4			31.6	0.010995899
Std dev lead time	sLT =	0			34.4	0.0374318664
Service level	SL =	0.7733745074			37.2	0.0780634833
Increment	DSL =	0.01			40	0.0997355701
Stock out risk		0.2266254926			42.8	0.0780634833
Average demand during lead time	dLT =	40	40	ERROR:#N/A	45.6	0.0374318664
Std dev demand during lead time	sdLT =	4	40	43.0000247037	48.4	0.010995899
	z =	0.7500061759	40	ERROR:#N/A	51.2	0.0019788629
Safety stock	SS =	3.0000247037			54	0.0002181707
Reorder point	ROP =	43.0000247037			40		0
					40		0.0997355701
Note: use Goal Seek to set ROP to 43 by changing SL					43.0000247037			0
					43.0000247037			0.0997355701

26 28.8 31.6 34.4 37.199999999999996 39.999999999999993 42.79999999999999 45.599999999999987 48.399999999999984 51.199999999999982 53.999999999999979 40 40 43.00002470365137 43.00002470365137 2.1817067376144004E-4 1.9788628957449921E-3 1.0995898995106808E-2 3.7431866408936199E-2 7.8063483341690262E-2 9.9735570100358176E-2 7.8063483341690457E-2 3.7431866408936386E-2 1.0995898995106891E-2 1.9788628957450164E-3 2.181706737614441E-4 dLT 26 28.8 31.6 34.4 37.199999999999996 39.999999999999993 42.79999999999999 45.599999999999987 48.399999999999984 51.199999999999982 53.999999999999979 40 40 43.00002470365137 43.00002470365137 0 9.9735570100358176E-2 ROP 26 28.8 31.6 34.4 37.199999999999996 39.999999999999993 42.79999999999999 45.599999999999987 48.399999999999984 51.199999999999982 53.999999999999979 40 40 43.00002470365137 43.00002470365137 0 9.9735570100358176E-2

Example 11b

Fixed Order Interval Model
<Back
Average demand	d =	10
Std dev demand	sd =	2
Lead time	LT =	4
Amount on hand at reorder time	A =	43
Order interval	OI =	12
Service level	SL =	1
Increment	DSL =	0
Stock out risk		0	117	160	ERROR:#N/A	ERROR:#N/A
Average demand during lead time	dLT =	160	117	160	125	ERROR:#N/A
Safety stock	SS =	8	117	160	ERROR:#N/A	ERROR:#N/A
Amount to order		125
Note: SL = 100% at Q = 125 < 171

#N/A #N/A #N/A Amount to order #N/A 125 #N/A Average demand during lead time 160 160 160 dLT less Amount on hand 117 117 117

Example 12

Single Period Model
<Back
Select demand distribution:			Uniform
	1
Shortage cost (revenue - cost)			Cs =	0.6	0.4
Excess cost (cost - salvage)			Ce =	0.2
Minimum demand				300
Maximum demand				500
Optimal Service Level			SLo =	0.75
Optimal Stocking Level			So =	450

Actual Stocking Level			S =	450
Increment			DS =	10
Actual Service Level			SL =	0.7500
Balance:
			Ce * Service Level	0.15
			Cs * (1 - Service Level)	0.15
Discrete Distribution:		Demand	Freq	Cum

0.4 Ce * Service Level 0.15000000000000002 Cs * (1 - Ser vice Level) 0.15

Uniform

Normal

Poisson

Discrete

Example 13

Single Period Model
<Back
Select demand distribution:			Normal
	2
Shortage cost (revenue - cost)			Cs =	0.6	0.4
Excess cost (cost - salvage)			Ce =	0.2
Average demand				200
Std dev demand				10
Optimal Service Level			SLo =	0.75
Optimal Stocking Level			So =	206.744897502

Actual Stocking Level			S =	207
Increment			DS =	10
Actual Service Level			SL =	0.7580
Balance:
			Ce * Service Level	0.1516072696
			Cs * (1 - Service Level)	0.1451781913
Discrete Distribution:		Demand	Freq	Cum

0.4 Ce * Service Level 0.15160726955538539 Cs * (1 - Ser vice Level) 0.14517819133384383

Uniform

Normal

Poisson

Discrete

Example 14

Single Period Model
<Back
Select demand distribution:			Discrete
	4
Shortage cost (revenue - cost)			Cs =	4200	1600
Excess cost (cost - salvage)			Ce =	800


Optimal Service Level			SLo =	0.84
			So =
			Adjust S until SL >= SLo
Actual Stocking Level			S =	2
Increment			DS =	1
Actual Service Level			SL =	0.9000
Balance:
			Ce * Service Level	720
			Cs * (1 - Service Level)	420
Discrete Distribution:		Demand	Freq	Cum
		0	0.2	0.2
		1	0.4	0.6
		2	0.3	0.9
		3	0.1	1

1600 Ce * Service Level 720.00000000000011 Cs * (1 - Ser vice Level) 419.99999999999943

Uniform

Normal

Poisson

Discrete

Example 15

Single Period Model
<Back
Select demand distribution:			Poisson
	3
Shortage cost (revenue - cost)			Cs =	3	4
Excess cost (cost - salvage)			Ce =	2
Average demand				4

Optimal Service Level			SLo =	0.6
			So =
			Adjust S until SL >= SLo
Actual Stocking Level			S =	4
Increment			DS =	1
Actual Service Level			SL =	0.6288
Balance:
			Ce * Service Level	1.2576738704
			Cs * (1 - Service Level)	1.1134891945
Discrete Distribution:		Demand	Freq	Cum

4 Ce * Service Level 1.257673870359747 Cs * (1 - Service Level) 1.1134891944603795

Uniform

Normal

Poisson

Discrete

Solved Problem 1

Basic Economic Order Quantity (EOQ) Model			x	Carry	Order	Total	Q
<Back			1	1.5	ERROR:#N/A	ERROR:#N/A
Annual Demand	D =	32000	160	240	ERROR:#N/A	ERROR:#N/A
Ordering cost per order	S =	120	320	480	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit	H =	3	480	720	8000	8720
Working days per year	D/Y =	240	640	960	6000	6960
Economic Order Quantity	EOQ =	1600	800	1200	4800	6000
			960	1440	4000	5440
Actual Order Quantity	Q =	1600	1120	1680	3428.5714285714	5108.5714285714
Increment	DQ =	10	1280	1920	3000	4920
Number of orders per year	D/Q =	20	1440	2160	2666.6666666667	4826.6666666667
Length of order cycle (days)	Q/D =	12	1600	2400	2400	4800
Average Inventory	Q/2 =	800	1760	2640	2181.8181818182	4821.8181818182
Annual carrying cost	(Q/2) * H =	2400	1920	2880	2000	4880
Annual ordering cost	(D/Q) * S =	2400	2080	3120	1846.1538461539	4966.1538461539
Total Annual Cost	TC =	4800	2240	3360	1714.2857142857	5074.2857142857
			2400	3600	1600	5200
			2560	3840	1500	5340
			2720	4080	1411.7647058823	5491.7647058824
			2880	4320	1333.3333333333	5653.3333333333
			3040	4560	1263.1578947368	5823.1578947368
			3200	4800	1200	6000
			1600				4800
			1600				0
			Start:	0	Stop:	3200
			Min Y :	0	Max Y:	9600
			Steps:	20	Step:	160

Carrying Cost 1 160 320 480 640 800 960 1120 1280 1440 1600 1760 1920 2080 2240 2400 2560 2720 2880 3040 3200 1600 1600 1.5 240 480 720 960 1200 1440 1680 1920 2160 2400 2640 2880 3120 3360 3600 3840 4080 4320 4560 4800 Ordering Cost 1 160 320 480 640 800 960 1120 1280 1440 1600 1760 1920 2080 2240 2400 2560 2720 2880 3040 3200 1600 1600 #N/A #N/A #N/A 8000.0000000000009 6000 4800 4000.0000000000005 3428.5714285714289 3000 2666.6666666666665 2400 2181.818181818182 2000.0000000000002 1846.1538461538462 1714.2857142857144 1600 1500 1411.7647058823532 1333.3333333333333 1263.1578947368421 1200 Total Cost 1 160 320 480 640 800 960 1120 1280 1440 1600 1760 1920 2080 2240 2400 2560 2720 2880 3040 3200 1600 1600 #N/A #N/A #N/A 8720 6960 6000 5440 5108.5714285714294 4920 4826.6666666666661 4800 4821.818181818182 4880 4966.1538461538457 5074.2857142857147 5200 5340 5491.7647058823532 5653.333333333333 5823.1578947368416 6000 Q

1 160 320 480 640 800 960 1120 1280 1440 1600 1760 1920 2080 2240 2400 2560 2720 2880 3040 3200 1600 1600 4800 0

Order Quantity (Q)

Solved Problem 2

Economic Production Quantity (EPQ) Model			x	Carrying	Setup	Total	Q
<Back			1	0.75	ERROR:#N/A	ERROR:#N/A
Annual Demand	D =	11000	101.3	75.975	ERROR:#N/A	ERROR:#N/A
Setup cost	S =	70	202.6	151.95	ERROR:#N/A	ERROR:#N/A
Annual carrying cost per unit	H =	2	303.9	227.925	2533.7282000658	2761.6532000658
Production rate	p =	200	405.2	303.9	1900.2961500494	2204.1961500494
Usage rate	u =	50	506.5	379.875	1520.2369200395	1900.1119200395
Production days per year	D/Y =	220	607.8	455.85	1266.8641000329	1722.7141000329
Economic Run Quantity	Qp =	1013	709.1	531.825	1085.8835143139	1617.7085143139
			810.4	607.8	950.1480750247	1557.9480750247
			911.7	683.775	844.5760666886	1528.3510666886
Actual Run Quantity	Q =	1013	1013	759.75	760.1184600197	1519.8684600197
Increment	DQ =	100	1114.3	835.725	691.0167818361	1526.7417818361
Number of runs per year	D/Q =	10.8588351431	1215.6	911.7	633.4320500165	1545.1320500164
Cycle time	Q/u =	20.26	1316.9	987.675	584.7065077075	1572.3815077075
Run time	Q/p =	5.065	1418.2	1063.65	542.941757157	1606.591757157
Average Inventory	Iave =	379.875	1519.5	1139.625	506.7456400132	1646.3706400132
Maximum Inventory	Imax =	759.75	1620.8	1215.6	475.0740375123	1690.6740375123
Annual carrying cost	Iave * H =	759.75	1722.1	1291.575	447.128505894	1738.703505894
Annual setup cost	(D/Q) * S =	760.1184600197	1823.4	1367.55	422.2880333443	1789.8380333443
Total Annual Cost	TC =	1519.8684600197	1924.7	1443.525	400.0623473788	1843.5873473788
			2026	1519.5	380.0592300099	1899.5592300099
			1013				1519.8684600197
			1013				0
			Start:	0	Stop:	2026
			Min Y :	0	Max Y:	3546.2369200395
			Steps:	20	Step:	101.3

Carrying Cost 1 101.3 202.6 303.89999999999998 405.2 506.5 607.79999999999995 709.09999999999991 810.39999999999986 911.69999999999982 1012.9999999999998 1114.2999999999997 1215.5999999999997 1316.8999999999996 1418.1999999999996 1519.4999999999 995 1620.7999999999995 1722.0999999999995 1823.3999999999994 1924.6999999999994 2025.9999999999993 1013 1013 0.75 75.974999999999994 151.94999999999999 227.92500000000001 303.89999999999998 379.875 455.85 531.82499999999993 607.79999999999995 683.77499999999986 759.74999999999989 835.72499999999991 911.6999999999997 987.67499999999973 1063.6499999999996 1139.6249999999998 1215.5999999999995 1291.5749999999996 1367.5499999999995 1443.5249999999994 1519.4999999999993 SetupCost 1 101.3 202.6 303.89999999999998 405.2 506.5 607.79999999999995 709.09999999999991 810.39999999999986 911.69999999999982 1012.9999999999998 1114.2999999999997 1215.5999999999997 1316.8999999999996 1418.1999999999996 1519.4999999999995 1620.7999999999995 1722.0999999999995 1823.3999999999994 1924.6999999999994 2025.9999999999993 1013 1013 #N/A #N/A #N/A 2533.7282000658111 1900.2961500493584 1520.2369200394867 1266.8641000329055 1085.8835143139192 950.14807502467931 844.57606668860387 760.11846001974357 691.01678183613046 633.43205001645288 584.70650770749501 542.94175715695974 506.74564001316236 475.07403751233977 447.1285058939668 422.28803334430199 400.06234737881238 380.05923000987178 Total Cost 1 101.3 202.6 303.89999999999998 405.2 506.5 607.79999999999995 709.09999999999991 810.39999999999986 911.69999999999982 1012.9999999999998 1114.2999999999997 1215.5999999999997 1316.8999999999996 1418.1999999999996 1519.4999999999995 1620.7999999999995 1722.0999999999995 1823.3999999999994 1924.6999999999994 2025.9999999999993 1013 1013 #N/A #N/A #N/A 2761.6532000658112 2204.1961500493585 1900.1119200394867 1722.7141000329057 1617.7085143139193 1557.9480750246794 1528.3510666886036 1519.8684600197435 1526.7417818361305 1545.1320500164525 1572.3815077074946 1606.5917571569594 1646.3706400131621 1690.6740375123393 1738.7035058939664 1789.8380333443015 1843.5873473788117 1899.559230009871 Q

1 101.3 202.6 303.89999999999998 405.2 506.5 607.79999999999995 709.09999999999991 810.39999999999986 911.69999999999982 1012.9999999999998 1114.2999999999997 1215.5999999999997 1316.8999999999996 1418.1999999999996 1519.4999999999995 1620.7999999999995 1722.0999999999995 1823.3999999999994 1924.6999999999994 2025.9999999999993 1013 1013 1519.8684600197435 0

Run Quantity (Q)

Solved Problem 3

Quantity Discounts

<Back

Notes

Price Level:

1000

Minimum quantity for price

Qmin =

1000

ERROR:#N/A

350200.255

346800.17

ERROR:#N/A

Price

P =

3.00

2.00

70.7

15027.0808338048

ERROR:#N/A

70.7

15027.0808338048

11621.0713338048

ERROR:#N/A

Optimal Q (for each price)

Qopt =

1155

1414

141.4

12640.5831669024

ERROR:#N/A

141.4

12640.5831669024

9228.5641669024

ERROR:#N/A

Number of orders per year

D/Qopt =

2.9437229437

2.4045261669

212.1

11857.1029446016

ERROR:#N/A

212.1

11857.1029446016

8439.0744446016

ERROR:#N/A

Average Inventory

Qopt/2 =

577.5

707

282.8

11474.3770834512

ERROR:#N/A

282.8

11474.3770834512

8050.3390834512

ERROR:#N/A

Annual carrying cost

(Qopt/2) * H =

294.525

240.38

353.5

11251.952966761

ERROR:#N/A

353.5

11251.952966761

7821.905466761

ERROR:#N/A

Annual ordering cost

(D/Qopt) * S =

294.3722943723

240.4526166902

424.2

11109.6797223008

ERROR:#N/A

424.2

11109.6797223008

7673.6227223008

ERROR:#N/A

Annual purchase cost

P * D =

10200

6800

494.9

11013.2069762578

ERROR:#N/A

494.9

11013.2069762578

7571.1404762578

ERROR:#N/A

Total Annual Cost

TC =

10788.8972943723

7280.8326166902

565.6

10945.3595417256

ERROR:#N/A

565.6

10945.3595417256

7497.2835417256

ERROR:#N/A

636.3

10896.5956482005

ERROR:#N/A

636.3

10896.5956482005

7442.5101482005

ERROR:#N/A

Annual Demand

D =

3400

707

10861.1902333805

ERROR:#N/A

707

10861.1902333805

7401.0952333805

ERROR:#N/A

Ordering cost per order

S =

100

777.7

10835.5000758004

ERROR:#N/A

777.7

10835.5000758004

7369.3955758004

ERROR:#N/A

Annual carrying cost per unit:

H (fixed) =

848.4

10817.0963611504

ERROR:#N/A

848.4

10817.0963611504

7344.9823611504

ERROR:#N/A

FALSE

H (% of price) =

17.00%

919.1

10804.2976026004

ERROR:#N/A

919.1

10804.2976026004

7326.1741026004

ERROR:#N/A

Optimal Q (overall)

Qopt =

1414

989.8

10795.9027381289

ERROR:#N/A

989.8

10795.9027381289

7311.7697381289

ERROR:#N/A

1060.5

10791.0309889203

7300.8884889203

ERROR:#N/A

1060.5

10791.0309889203

7300.8884889203

ERROR:#N/A

Actual Order Quantity

Q =

1500

1131.2

10789.0217708628

7292.8697708628

ERROR:#N/A

1131.2

10789.0217708628

7292.8697708628

ERROR:#N/A

Increment

DQ =

1201.9

10789.3699314003

7287.2084314003

ERROR:#N/A

1201.9

10789.3699314003

7287.2084314003

ERROR:#N/A

Price

P =

1272.6

10791.6825741003

7283.5115741003

ERROR:#N/A

1272.6

10791.6825741003

7283.5115741003

ERROR:#N/A

Number of orders per year

D/Q =

2.2666666667

1343.3

10795.6495175687

7281.4690175687

ERROR:#N/A

1343.3

10795.6495175687

7281.4690175687

ERROR:#N/A

Average Inventory

Q/2 =

750

1414

10801.0226166902

7280.8326166902

ERROR:#N/A

1414

10801.0226166902

7280.8326166902

ERROR:#N/A

Annual carrying cost

(Q/2) * H =

255

1484.7

10807.6009920859

7281.4014920859

ERROR:#N/A

1484.7

10807.6009920859

7281.4014920859

ERROR:#N/A

Annual ordering cost

(D/Q) * S =

226.6666666667

1555.4

10815.2202879002

7283.0112879002

ERROR:#N/A

1555.4

10815.2202879002

7283.0112879002

ERROR:#N/A

Annual purchase cost

P * D =

6800

1626.1

10823.7447319046

7285.5262319046

ERROR:#N/A

1626.1

10823.7447319046

7285.5262319046

ERROR:#N/A

Total Annual Cost

TC =

7281.6666666667

1696.8

10833.0611805752

7288.8331805752

ERROR:#N/A

1696.8

10833.0611805752

7288.8331805752

ERROR:#N/A

1767.5

10843.0745933522

7292.8370933522

ERROR:#N/A

1767.5

10843.0745933522

7292.8370933522

ERROR:#N/A

1838.2

10853.7045513002

7297.4575513002

ERROR:#N/A

1838.2

10853.7045513002

7297.4575513002

ERROR:#N/A

Notes:

Use radio buttons to select holding cost as a fixed amount or as percent of price.

1908.9

10864.8825494002

7302.6260494002

ERROR:#N/A

1908.9

10864.8825494002

7302.6260494002

ERROR:#N/A

Enter holding cost as a fixed amount in cell E15 or as a percent of price in cell E16.

1979.6

10876.5498690645

7308.2838690645

ERROR:#N/A

1979.6

10876.5498690645

7308.2838690645

ERROR:#N/A

^Top

2050.3

10888.6558908209

7314.3803908209

ERROR:#N/A

2050.3

10888.6558908209

7314.3803908209

ERROR:#N/A

2121

10901.1567444602

7320.8717444602

ERROR:#N/A

2121

10901.1567444602

7320.8717444602

ERROR:#N/A

2191.7

10914.0142204453

7327.7197204453

ERROR:#N/A

2191.7

10914.0142204453

7327.7197204453

ERROR:#N/A

2262.4

10927.1948854314

7334.8908854314

ERROR:#N/A

2262.4

10927.1948854314

7334.8908854314

ERROR:#N/A

2333.1

10940.6693586001

7342.3558586001

ERROR:#N/A

2333.1

10940.6693586001

7342.3558586001

ERROR:#N/A

2403.8

10954.4117157001

7350.0887157001

ERROR:#N/A

2403.8

10954.4117157001

7350.0887157001

ERROR:#N/A

2474.5

10968.3989952516

7358.0664952516

ERROR:#N/A

2474.5

10968.3989952516

7358.0664952516

ERROR:#N/A

2545.2

10982.6107870501

7366.2687870501

ERROR:#N/A

2545.2

10982.6107870501

7366.2687870501

ERROR:#N/A

2615.9

10997.0288874001

7374.6773874001

ERROR:#N/A

2615.9

10997.0288874001

7374.6773874001

ERROR:#N/A

2686.6

11011.6370087843

7383.2760087843

ERROR:#N/A

2686.6

11011.6370087843

7383.2760087843

ERROR:#N/A

2757.3

11026.4205342001

7392.0500342001

ERROR:#N/A

2757.3

11026.4205342001

7392.0500342001

ERROR:#N/A

2828

11041.3663083451

7400.9863083451

ERROR:#N/A

2828

11041.3663083451

7400.9863083451

ERROR:#N/A

1500

10809.1666666667

7281.6666666667

ERROR:#N/A

1500

7281.6666666667

1500

10809.1666666667

7086.4547837484

Start:

Stop:

2828

Min Y :

Max Y:

16183.3459415584

Steps:

Step:

70.7

1 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A 15027.080833804808 12640.583166902405 11857.102944601604 11474.377083451202 11251.952966760962 11109.679722300802 11013.20697625783 10945.3595417256 10896.595648200535 10861.190233380481 10835.500075800437 10817.096361150401 10804.297602600371 10795.902738128914 10791.03098892032 10789.021770862801 10789.369931400282 10791.682574100267 10795.649517568674 10801.022616690241 10807.600992085943 10815.220287900218 10823.744731904557 10833.061180575201 10843.074593352192 10853.704551300185 10864.882549400178 10876.549869064458 10888.655890820855 10901.156744460161 10914.014220445317 10927.1948854314 10940.669358600146 10954.411715700142 10968.398995251566 10982.610787050133 10997.028887400131 11011.637008784337 11026.420534200122 11041.366308345119 2 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 7300.8884889203209 7292.8697708628006 7287.2084314002832 7283.5115741002674 7281.4690175686737 7280.8326166902407 7281.4014920859436 7283.011287900219 7285.5262319045569 7288.8331805752005 7292.8370933521928 7297.4575513001855 7302.6260494001781 7308.2838690644576 7314.3803908208556 7320.8717444601607 7327.7197204453169 7334.8908854314004 7342.3558586001454 7350.0887157001416 7358.0664952515663 7366.2687870501341 7374.6773874001301 7383.2760087843371 7392.0500342001233 7400.98 63083451201 3 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.70000 00000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 4 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N /A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 5 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000 007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 6 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 7 1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

1 70.7 141.4 212.10000000000002 282.8 353.5 424.2 494.9 565.6 636.30000000000007 707.00000000000011 777.70000000000016 848.4000000000002 919.10000000000025 989.8000000000003 1060.5000000000002 1131.2000000000003 1201.9000000000003 1272.6000000000004 1343.3000000000004 1414.0000000000005 1484.7000000000005 1555.4000000000005 1626.1000000000006 1696.8000000000006 1767.5000000000007 1838.2000000000007 1908.9000000000008 1979.6000000000008 2050.3000000000006 2121.0000000000005 2191.7000000000003 2262.4 2333.1 2403.7999999999997 2474.4999999999995 2545.1999999999994 2615.8999999999992 2686.599999999999 2757.2999999999988 2827.9999999999986 1500 1500 0 7281.666666666667

Order Quantity (Q)

Fixed

Percent of Price

Solved Problem 4

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand	d =	400			1145.4403995616	0.0000559826
Std dev demand	sd =	9			1156.3523196493	0.0005077765
Average lead time	LT =	3			1167.264239737	0.002821549
Std dev lead time	sLT =	0			1178.1761598246	0.0096050214
Service level	SL =	0.98			1189.0880799123	0.0200310992
Increment	DSL =	0.01			1200	0.0255921592
Stock out risk		0.02			1210.9119200877	0.0200310992
Average demand during lead time	dLT =	1200	1200	ERROR:#N/A	1221.8238401754	0.0096050214
Std dev demand during lead time	sdLT =	15.5884572681	1200	1232.0147771328	1232.735760263	0.002821549
	z =	2.0537489106	1200	ERROR:#N/A	1243.6476803507	0.0005077765
Safety stock	SS =	32.0147771328			1254.5596004384	0.0000559826
Reorder point	ROP =	1232.0147771328			1200		0
					1200		0.0255921592
					1232.0147771328			0
					1232.0147771328			0.0255921592

1145.4403995615803 1156.3523196492642 1167.2642397369482 1178.1761598246321 1189.0880799123161 1200 1210.9119200876839 1221.8238401753679 1232.7357602630518 1243.6476803507358 1254.5596004384197 1200 1200 1232.014777132832 1232.014777132832 5.5982620989087836E-5 5.0777645579898768E-4 2.8215489976919175E-3 9.6050213988752835E-3 2.0031099164979888E-2 2.5592159220098928E-2 2.0031099164979888E-2 9.6050213988752835E-3 2.8215489976919175E-3 5.0777645579898768E-4 5.5982620989087836E-5 dLT 1145.4403995615803 1156.3523196492642 1167.2642397369482 1178.1761598246321 1189.0880799123161 1200 1210.9119200876839 1221.8238401753679 1232.7357602630518 1243.6476803507358 1254.5596004384197 1200 1200 1232.014777132832 1232.014777132832 0 2.5592 159220098928E-2 ROP 1145.4403995615803 1156.3523196492642 1167.2642397369482 1178.1761598246321 1189.0880799123161 1200 1210.9119200876839 1221.8238401753679 1232.7357602630518 1243.6476803507358 1254.5596004384197 1200 1200 1232.014777132832 1232.014777132832 0 2.5592159220098928E-2

Solved Problem 5

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand			d =	600			-600	0.0000007272
Std dev demand			sd =	0			240	0.0000065962
Average lead time			LT =	6			1080	0.000036653
Std dev lead time			sLT =	2			1920	0.0001247729
Service level			SL =	0.9			2760	0.0002602116
Increment			DSL =	0.01			3600	0.0003324519
Stock out risk				0.1			4440	0.0002602116
Average demand during lead time			dLT =	3600	3600	ERROR:#N/A	5280	0.0001247729
Std dev demand during lead time			sdLT =	1200	3600	5137.8618786535	6120	0.000036653
			z =	1.2815515655	3600	ERROR:#N/A	6960	0.0000065962
Safety stock			SS =	1537.8618786535			7800	0.0000007272
Reorder point			ROP =	5137.8618786535			3600		0
							3600		0.0003324519
							5137.8618786535			0
b.	ROP / d =	8.5631031311					5137.8618786535			0.0003324519

-600 240.00000000000011 1080.0000000000002 1920.0000000000005 2760.0000000000005 3600.0000000000005 4440.0000000000009 5280.0000000000009 6120.0000000000009 6960.0000000000009 7800.0000000000009 3600 3600 5137.8618786535208 5137.8618786535208 7.2723557920480007E-7 6.5962096524833072E-6 3.6652996650355995E-5 1.2477288802978747E-4 2.6021161113896777E-4 3.3245190033452725E-4 2.602116111389676E-4 1.2477288802978726E-4 3.6652996650355927E-5 6.5962096524832877E-6 7.2723557920479753E-7 dLT -600 240.00000000000011 1080.0000000000002 1920.0000000000005 2760.0000000000005 3600.0000000000005 4440.0000000000009 5280.0000000000009 6120.0000000000009 6960.0000000000009 7800.0000000000009 3600 3600 5137.8618786535208 5137.8618786535208 0 3.324 5190033452725E-4 ROP -600 240.00000000000011 1080.0000000000002 1920.0000000000005 2760.0000000000005 3600.0000000000005 4440.0000000000009 5280.0000000000009 6120.0000000000009 6960.0000000000009 7800.0000000000009 3600 3600 5137.8618786535208 5137.8618786535208 0 3.3245190033452725E-4

Solved Problem 6

Reorder Point (ROP) with EOQ Ordering
<Back
Average demand	d =	25			71.8778509	0.0000171477
Std dev demand	sd =	3			107.50228072	0.0001555342
Average lead time	LT =	10			143.12671054	0.0008642529
Std dev lead time	sLT =	2			178.75114036	0.0029420604
Service level	SL =	0.95			214.37557018	0.0061356141
Increment	DSL =	0.01			250	0.0078389913
Stock out risk		0.05			285.62442982	0.0061356141
Average demand during lead time	dLT =	250	250	ERROR:#N/A	321.24885964	0.0029420604
Std dev demand during lead time	sdLT =	50.8920426	250	333.7099608536	356.87328946	0.0008642529
	z =	1.644853627	250	ERROR:#N/A	392.49771928	0.0001555342
Safety stock	SS =	83.7099608536			428.1221491	0.0000171477
Reorder point	ROP =	333.7099608536			250		0
					250		0.0078389913
					333.7099608536			0
					333.7099608536			0.0078389913

71.877850900007388 107.50228072000591 143.12671054000444 178.75114036000298 214.37557018000149 250 285.62442981999851 321.24885963999702 356.87328945999553 392.49771927999404 428.12214909999255 250 250 333.70996085357535 333.70996085357535 1.7147723896737401E-5 1.5553416955955106E-4 8.6425291132702646E-4 2.9420604476926848E-3 6.1356140845243712E-3 7.8389913239884229E-3 6.1356140845243712E-3 2.9420604476926848E-3 8.6425291132702733E-4 1.5553416955955146E-4 1.7147723896737445E-5 dLT 71.877850900007388 107.50228072000591 143.12671054000444 178.75114036000298 214.37557018000149 250 285.62442981999851 321.24885963999702 356.87328945999553 392.49771927999404 428.12214909999255 250 250 333.70996085357535 333.70996085357535 0 7.83899 13239884229E-3 ROP 71.877850900007388 107.50228072000591 143.12671054000444 178.75114036000298 214.37557018000149 250 285.62442981999851 321.24885963999702 356.87328945999553 392.49771927999404 428.12214909999255 250 250 333.70996085357535 333.70996085357535 0 7.8389913239884229E-3

Solved Problem 7

Fixed Order Interval Model
<Back
Average demand	d =	15.2
Std dev demand	sd =	1.6
Lead time	LT =	5
Amount on hand at reorder time	A =	275
Order interval	OI =	30
Service level	SL =	0.95
Increment	DSL =	0.01
Stock out risk		0.05	257	532	ERROR:#N/A	ERROR:#N/A
Average demand during lead time	dLT =	532	257	532	272.5697364617	ERROR:#N/A
Safety stock	SS =	15.5697364617	257	532	ERROR:#N/A	ERROR:#N/A
Amount to order		272.5697364617

#N/A #N/A #N/A Amount to order #N/A 272.56973646170513 #N/A Average demand during lead time 532 532 532 dLT less Amount on hand 257 257 257

Solved Problem 8

Single Period Model
<Back
Select demand distribution:			Poisson
	3
Shortage cost (revenue - cost)			Cs =	341.347988	682.695976
Excess cost (cost - salvage)			Ce =	500
Average demand				2

Optimal Service Level			SLo =	0.4057155813
			So =
			Adjust S until SL >= SLo
Actual Stocking Level			S =	1
Increment			DS =	1
Actual Service Level			SL =	0.4060
Balance:
			Ce * Service Level	203.0029248549
			Cs * (1 - Service Level)	202.7587080853
Discrete Distribution:		Demand	Freq	Cum
					Note: For S = 1, Actual Service Level = .4060 so use Goal Seek
					to set SLo to .406 by changing Cs, then set S = 2 and repeat.

682.69597600000009 Ce * Service Level 203.00292485491906 Cs * (1 - Service Level) 202.75870808531641

Uniform

Normal

Poisson

Discrete

Problems 1-10

Chapter 13 - Problems 1-10				Note: This worksheet displays results only, you must copy the shaded
<Back				area into the corresponding template to make additional calculations.
1a.	ABC Classification System
		Dollar Amount		Number of Items
	Class	Total	Percent	Total	Percent
	A	5,100	41.91%	1	16.67%
	B	4,900	40.26%	2	33.33%
	C	2,170	17.83%	3	50.00%
	Enter lower limit for dollar amount:
	A	5,100		B	2,400
			Unit	Dollar
	Item	Usage	Cost	Amount	Class
	1	25	100	2,500	B
	2	30	80	2,400	B
	3	60	15	900	C
	4	10	50	500	C
	5	70	11	770	C
	6	85	60	5,100	A
1b.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	4500
	Ordering cost per order			S =	36
	Annual carrying cost per unit			H =	10
	Working days per year			D/Y =	240
	Economic Order Quantity			EOQ =	180
	Actual Order Quantity			Q =	300
	Increment			DQ =	10
	Number of orders per year			D/Q =	15
	Length of order cycle (days)			Q/D =	16
	Average Inventory			Q/2 =	150
	Annual carrying cost			(Q/2) * H =	1500
	Annual ordering cost			(D/Q) * S =	540
	Total Annual Cost			TC =	2040
1c.	Economic Production Quantity (EPQ) Model
	Annual Demand			D =	18000
	Setup cost			S =	100
	Annual carrying cost per unit			H =	40
	Production rate			p =	120
	Usage rate			u =	90
	Production days per year			D/Y =	240
	Economic Run Quantity			Qp =	600
	Actual Run Quantity			Q =	2400
	Increment			DQ =	100
	Number of runs per year			D/Q =	7.5
	Cycle time			Q/u =	32
	Run time			Q/p =	20
	Average Inventory			Iave =	300
	Maximum Inventory			Imax =	600
	Annual carrying cost			Iave * H =	12000
	Annual setup cost			(D/Q) * S =	750
	Total Annual Cost			TC =	12750
2a.	ABC Classification System
		Dollar Amount		Number of Items
	Class	Total	Percent	Total	Percent
	A	55,000	58.43%	3	18.75%
	B	29,700	31.55%	5	31.25%
	C	9,430	10.02%	8	50.00%
	Enter lower limit for dollar amount:
	A	15,000		B	4,800
			Unit	Dollar
	Item	Usage	Cost	Amount	Class
	K34	10	200	2,000	C
	K35	25	600	15,000	A
	K36	36	150	5,400	B
	M10	16	25	400	C
	M20	20	80	1,600	C
	Z45	80	200	16,000	A
	F14	20	300	6,000	B
	F95	30	800	24,000	A
	F99	20	60	1,200	C
	D45	10	550	5,500	B
	D48	12	90	1,080	C
	D52	15	110	1,650	C
	D57	40	120	4,800	B
	N08	30	40	1,200	C
	P05	16	500	8,000	B
	P09	10	30	300	C
2b.	ABC Classification System
		Dollar Amount		Number of Items
	Class	Total	Percent	Total	Percent
	A	126,000	55.26%	1	11.11%
	B	66,000	28.95%	3	33.33%
	C	36,000	15.79%	5	55.56%
	Enter lower limit for dollar amount:
	A	126,000		B	20,000
			Unit	Dollar
	Item	Usage	Cost	Amount	Class
	4021	90	1,400	126,000	A
	9402	300	12	3,600	C
	4066	30	700	21,000	B
	6500	150	20	3,000	C
	9280	10	1,020	10,200	C
	4050	80	140	11,200	C
	6850	2,000	10	20,000	B
	3010	400	20	8,000	C
	4400	5,000	5	25,000	B
3.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	1215
	Ordering cost per order			S =	10
	Annual carrying cost per unit			H =	75
	Working days per year			D/Y =	240
	Economic Order Quantity			EOQ =	18
	Actual Order Quantity			Q =	18
	Increment			DQ =	10
	Number of orders per year			D/Q =	67.5
	Length of order cycle (days)			Q/D =	3.5555555556
	Average Inventory			Q/2 =	9
	Annual carrying cost			(Q/2) * H =	675
	Annual ordering cost			(D/Q) * S =	675
	Total Annual Cost			TC =	1350
	For part d, if Q remains at 18 then TAC goes up by 81,
	if Q is decreased to 17 (new EOQ) then TAC goes up by 78.71.
4.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	10400
	Ordering cost per order			S =	60
	Annual carrying cost per unit			H =	30
	Working days per year			D/Y =	260
	Economic Order Quantity			EOQ =	203.9607805437
	Actual Order Quantity			Q =	204
	Increment			DQ =	1
	Number of orders per year			D/Q =	50.9803921569
	Length of order cycle (days)			Q/D =	5.1
	Average Inventory			Q/2 =	102
	Annual carrying cost			(Q/2) * H =	3060
	Annual ordering cost			(D/Q) * S =	3058.8235294118
	Total Annual Cost			TC =	6118.8235294118
4d.	Actual Order Quantity			Q =	200
	Increment			DQ =	1
	Number of orders per year			D/Q =	52
	Length of order cycle (days)			Q/D =	5
	Average Inventory			Q/2 =	100
	Annual carrying cost			(Q/2) * H =	3000
	Annual ordering cost			(D/Q) * S =	3120
	Total Annual Cost			TC =	6120
5.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	9000
	Ordering cost per order			S =	20
	Annual carrying cost per unit			H =	0.6
	Working days per year			D/Y =	260
	Economic Order Quantity			EOQ =	774.5966692415
	Actual Order Quantity			Q =	775
	Increment			DQ =	1
	Number of orders per year			D/Q =	11.6129032258
	Length of order cycle (days)			Q/D =	22.3888888889
	Average Inventory			Q/2 =	387.5
	Annual carrying cost			(Q/2) * H =	232.5
	Annual ordering cost			(D/Q) * S =	232.2580645161
	Total Annual Cost			TC =	464.7580645161
	Actual Order Quantity			Q =	1500
	Increment			DQ =	1
	Number of orders per year			D/Q =	6
	Length of order cycle (days)			Q/D =	43.3333333333
	Average Inventory			Q/2 =	750
	Annual carrying cost			(Q/2) * H =	450
	Annual ordering cost			(D/Q) * S =	120
	Total Annual Cost			TC =	570
6.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	9600
	Ordering cost per order			S =	28
	Annual carrying cost per unit			H =	3.5
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	391.9183588453
	Actual Order Quantity			Q =	392
	Increment			DQ =	1
	Number of orders per year			D/Q =	24.4897959184
	Length of order cycle (days)			Q/D =	14.7
	Average Inventory			Q/2 =	196
	Annual carrying cost			(Q/2) * H =	686
	Annual ordering cost			(D/Q) * S =	685.7142857143
	Total Annual Cost			TC =	1371.7142857143
	Actual Order Quantity			Q =	800
	Increment			DQ =	1
	Number of orders per year			D/Q =	12
	Length of order cycle (days)			Q/D =	30
	Average Inventory			Q/2 =	400
	Annual carrying cost			(Q/2) * H =	1400
	Annual ordering cost			(D/Q) * S =	336
	Total Annual Cost			TC =	1736
7a.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	100
	Ordering cost per order			S =	55
	Annual carrying cost per unit			H =	2
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	74.161984871
	Actual Order Quantity			Q =	75
	Increment			DQ =	1
	Number of orders per year			D/Q =	1.3333333333
	Length of order cycle (days)			Q/D =	270
	Average Inventory			Q/2 =	37.5
	Annual carrying cost			(Q/2) * H =	75
	Annual ordering cost			(D/Q) * S =	73.3333333333
	Total Annual Cost			TC =	148.3333333333
	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	150
	Ordering cost per order			S =	55
	Annual carrying cost per unit			H =	2
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	90.8295106229
	Actual Order Quantity			Q =	91
	Increment			DQ =	1
	Number of orders per year			D/Q =	1.6483516484
	Length of order cycle (days)			Q/D =	218.4
	Average Inventory			Q/2 =	45.5
	Annual carrying cost			(Q/2) * H =	91
	Annual ordering cost			(D/Q) * S =	90.6593406593
	Total Annual Cost			TC =	181.6593406593
7c.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	100
	Ordering cost per order			S =	45
	Annual carrying cost per unit			H =	2
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	67.082039325
	Actual Order Quantity			Q =	50
	Increment			DQ =	1
	Number of orders per year			D/Q =	2
	Length of order cycle (days)			Q/D =	180
	Average Inventory			Q/2 =	25
	Annual carrying cost			(Q/2) * H =	50
	Annual ordering cost			(D/Q) * S =	90
	Total Annual Cost			TC =	140
	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	150
	Ordering cost per order			S =	45
	Annual carrying cost per unit			H =	2
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	82.1583836258
	Actual Order Quantity			Q =	100
	Increment			DQ =	1
	Number of orders per year			D/Q =	1.5
	Length of order cycle (days)			Q/D =	240
	Average Inventory			Q/2 =	50
	Annual carrying cost			(Q/2) * H =	100
	Annual ordering cost			(D/Q) * S =	67.5
	Total Annual Cost			TC =	167.5
8a.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	27000
	Ordering cost per order			S =	60
	Annual carrying cost per unit			H =	0.18
	Working days per year			D/Y =	20
	Economic Order Quantity			EOQ =	4242.6406871193
	Actual Order Quantity			Q =	4243
	Increment			DQ =	1
	Number of orders per year			D/Q =	6.363422107
	Length of order cycle (days)			Q/D =	3.142962963
	Average Inventory			Q/2 =	2121.5
	Annual carrying cost			(Q/2) * H =	381.87
	Annual ordering cost			(D/Q) * S =	381.80532642
	Total Annual Cost			TC =	763.67532642
	Actual Order Quantity			Q =	4000
	Increment			DQ =	1
	Number of orders per year			D/Q =	6.75
	Length of order cycle (days)			Q/D =	2.962962963
	Average Inventory			Q/2 =	2000
	Annual carrying cost			(Q/2) * H =	360
	Annual ordering cost			(D/Q) * S =	405
	Total Annual Cost			TC =	765
8b.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	27000
	Ordering cost per order			S =	24.3000000279
	Annual carrying cost per unit			H =	0.18
	Working days per year			D/Y =	20
	Economic Order Quantity			EOQ =	2700.0000015504
	Actual Order Quantity			Q =	2700
	Increment			DQ =	1
	Number of orders per year			D/Q =	10
	Length of order cycle (days)			Q/D =	2
	Average Inventory			Q/2 =	1350
	Annual carrying cost			(Q/2) * H =	243
	Annual ordering cost			(D/Q) * S =	243.0000002791
	Total Annual Cost			TC =	486.0000002791
	Note: use Goal Seek to set EOQ=2700 by changing S.
8c.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	27000
	Ordering cost per order			S =	50
	Annual carrying cost per unit			H =	0.18
	Working days per year			D/Y =	20
	Economic Order Quantity			EOQ =	3872.9833462074
	Actual Order Quantity			Q =	2700
	Increment			DQ =	1
	Number of orders per year			D/Q =	10
	Length of order cycle (days)			Q/D =	2
	Average Inventory			Q/2 =	1350
	Annual carrying cost			(Q/2) * H =	243
	Annual ordering cost			(D/Q) * S =	500
	Total Annual Cost			TC =	743
	Justification: carry less inventory.
9.	Economic Production Quantity (EPQ) Model
	Annual Demand			D =	75000
	Setup cost			S =	22
	Annual carrying cost per unit			H =	0.15
	Production rate			p =	5000
	Usage rate			u =	250
	Production days per year			D/Y =	300
	Economic Run Quantity			Qp =	4812
	Actual Run Quantity			Q =	4812
	Increment			DQ =	100
	Number of runs per year			D/Q =	15.5860349127
	Cycle time			Q/u =	19.248
	Run time			Q/p =	0.9624
	Average Inventory			Iave =	2285.7
	Maximum Inventory			Imax =	4571.4
	Annual carrying cost			Iave * H =	342.855
	Annual setup cost			(D/Q) * S =	342.8927680798
	Total Annual Cost			TC =	685.7477680798
10.	Economic Production Quantity (EPQ) Model
	Annual Demand			D =	80000
	Setup cost			S =	100
	Annual carrying cost per unit			H =	0.25
	Production rate			p =	1000
	Usage rate			u =	400
	Production days per year			D/Y =	200
	Economic Run Quantity			Qp =	10328
	Actual Run Quantity			Q =	10328
	Increment			DQ =	100
	Number of runs per year			D/Q =	7.745933385
	Cycle time			Q/u =	25.82
	Run time			Q/p =	10.328
	Average Inventory			Iave =	3098.4
	Maximum Inventory			Imax =	6196.8
	Annual carrying cost			Iave * H =	774.6
	Annual setup cost			(D/Q) * S =	774.5933384973
	Total Annual Cost			TC =	1549.1933384973
	Note: D, H, p, and u have been expressed in bags, not tons.
10e.	Annual Demand			D =	80000
	Setup cost			S =	25
	Annual carrying cost per unit			H =	0.25
	Production rate			p =	1000
	Usage rate			u =	400
	Production days per year			D/Y =	200
	Economic Run Quantity			Qp =	5164
	Actual Run Quantity			Q =	5164
	Increment			DQ =	100
	Number of runs per year			D/Q =	15.4918667699
	Cycle time			Q/u =	12.91
	Run time			Q/p =	5.164
	Average Inventory			Iave =	1549.2
	Maximum Inventory			Imax =	3098.4
	Annual carrying cost			Iave * H =	387.3
	Annual setup cost			(D/Q) * S =	387.2966692486
	Total Annual Cost			TC =	774.5966692486
	Savings =	774.5966692486

Problems 11-20

Chapter 13 - Problems 11-20				Note: This worksheet displays results only, you must copy the shaded
<Back				area into the corresponding template to make additional calculations.
11.	Economic Production Quantity (EPQ) Model
	Annual Demand			D =	20000
	Setup cost			S =	60
	Annual carrying cost per unit			H =	2
	Production rate			p =	200
	Usage rate			u =	80
	Production days per year			D/Y =	250
	Economic Run Quantity			Qp =	1414
	Actual Run Quantity			Q =	1414
	Increment	FALSE		DQ =	100
	Number of runs per year			D/Q =	14.14427157
	Cycle time			Q/u =	17.675
	Run time			Q/p =	7.07
	Average Inventory			Iave =	424.2
	Maximum Inventory			Imax =	848.4
	Annual carrying cost			Iave * H =	848.4
	Annual setup cost			(D/Q) * S =	848.6562942008
	Total Annual Cost			TC =	1697.0562942009
	c.	p - u =	120
	d.	Cycle time - Run Time - 1 =			9.605
11f.	Economic Production Quantity (EPQ) Model
	Annual Demand			D =	20000
	Setup cost			S =	60
	Annual carrying cost per unit			H =	2
	Production rate			p =	200
	Usage rate			u =	80
	Production days per year			D/Y =	250
	Economic Run Quantity			Qp =	1414
	Actual Run Quantity			Q =	1466.6666666667		52.6666666667
	Increment			DQ =	100
	Number of runs per year			D/Q =	13.6363636364
	Cycle time			Q/u =	18.3333333333		0.6583333333
	Run time			Q/p =	7.3333333333		0.2633333333
	Average Inventory			Iave =	440
	Maximum Inventory			Imax =	880
	Annual carrying cost			Iave * H =	880
	Annual setup cost			(D/Q) * S =	818.1818181818
	Total Annual Cost			TC =	1698.1818181818		1.125523981
	f.	Use Goal seek to set the following formula to 10 bychanging Q
		Available time = Cycle time - Run Time - Set-up time
			=	10
		additional units =		52.6666666667
		additional cost =		1.125523981
13.	Quantity Discounts
				Price Level:	1	2	3	4
	Minimum quantity for price			Qmin =	1000	2000	5000	10000
	Price			P =	1.25	1.20	1.18	1.15
	Optimal Q (for each price)			Qopt =	2400	2400	5000	10000
	Number of orders per year			D/Qopt =	7.5	7.5	3.6	1.8
	Average Inventory			Qopt/2 =	1200	1200	2500	5000
	Annual carrying cost			(Qopt/2) * H =	240	240	500	1000
	Annual ordering cost			(D/Qopt) * S =	240	240	115.2	57.6
	Annual purchase cost			P * D =	22500	21600	21240	20700
	Total Annual Cost			TC =	22980	22080	21855.2	21757.6
	Annual Demand			D =	18000
	Ordering cost per order			S =	32
	Annual carrying cost per unit:			H (fixed) =	0.2
	1	TRUE		H (% of price) =
	Optimal Q (overall)			Qopt =	10000
	Actual Order Quantity			Q =	10000
	Increment			DQ =	100
	Price			P =	1.15
	Number of orders per year			D/Q =	1.8
	Average Inventory			Q/2 =	5000
	Annual carrying cost			(Q/2) * H =	1000
	Annual ordering cost			(D/Q) * S =	57.6
	Annual purchase cost			P * D =	20700
	Total Annual Cost			TC =	21757.6
14a.	Quantity Discounts
				Price Level:	1	2	3
	Minimum quantity for price			Qmin =	1	400	600
	Price			P =	10.00	9.00	8.00
	Optimal Q (for each price)			Qopt =	490	490	600
	Number of orders per year			D/Qopt =	10.2040816327	10.2040816327	8.3333333333
	Average Inventory			Qopt/2 =	245	245	300
	Annual carrying cost			(Qopt/2) * H =	490	490	600
	Annual ordering cost			(D/Qopt) * S =	489.7959183673	489.7959183673	400
	Annual purchase cost			P * D =	50000	45000	40000
	Total Annual Cost			TC =	50979.7959183673	45979.7959183673	41000
	Annual Demand			D =	5000
	Ordering cost per order			S =	48
	Annual carrying cost per unit:			H (fixed) =	2
	1	TRUE		H (% of price) =
	Optimal Q (overall)			Qopt =	600
	Actual Order Quantity			Q =	600
	Increment			DQ =	10
	Price			P =	8
	Number of orders per year			D/Q =	8.3333333333
	Average Inventory			Q/2 =	300
	Annual carrying cost			(Q/2) * H =	600
	Annual ordering cost			(D/Q) * S =	400
	Annual purchase cost			P * D =	40000
	Total Annual Cost			TC =	41000
				Price Level:	1	2	3
14b.	Minimum quantity for price			Qmin =	1	400	600
	Price			P =	10.00	9.00	8.00
	Optimal Q (for each price)			Qopt =	400	422	600
	Number of orders per year			D/Qopt =	12.5	11.8483412322	8.3333333333
	Average Inventory			Qopt/2 =	200	211	300
	Annual carrying cost			(Qopt/2) * H =	600	569.7	720
	Annual ordering cost			(D/Qopt) * S =	600	568.7203791469	400
	Annual purchase cost			P * D =	50000	45000	40000
	Total Annual Cost			TC =	51200	46138.4203791469	41120
	Annual Demand			D =	5000
	Ordering cost per order			S =	48
	Annual carrying cost per unit:			H (fixed) =
	2	FALSE		H (% of price) =	30.00%
	Optimal Q (overall)			Qopt =	600
	Actual Order Quantity			Q =	600
	Increment			DQ =	10
	Price			P =	8
	Number of orders per year			D/Q =	8.3333333333
	Average Inventory			Q/2 =	300
	Annual carrying cost			(Q/2) * H =	720
	Annual ordering cost			(D/Q) * S =	400
	Annual purchase cost			P * D =	40000
	Total Annual Cost			TC =	41120
15.	Minimum quantity for price			Qmin =	1	1000	4000	6000
	Price			P =	5.00	4.95	4.90	4.85
	Optimal Q (for each price)			Qopt =	495	1000	4000	6000
	Number of orders per year			D/Qopt =	9.898989899	4.9	1.225	0.8166666667
	Average Inventory			Qopt/2 =	247.5	500	2000	3000
	Annual carrying cost			(Qopt/2) * H =	495	990	3920	5820
	Annual ordering cost			(D/Qopt) * S =	494.9494949495	245	61.25	40.8333333333
	Annual purchase cost			P * D =	24500	24255	24010	23765
	Total Annual Cost			TC =	25489.9494949495	25490	27991.25	29625.8333333333
	Annual Demand			D =	4900
	Ordering cost per order			S =	50
	Annual carrying cost per unit:			H (fixed) =
	2	FALSE		H (% of price) =	40.00%
	Optimal Q (overall)			Qopt =	495
	Actual Order Quantity			Q =	495
	Increment			DQ =	10
	Price			P =	5
	Number of orders per year			D/Q =	9.898989899
	Average Inventory			Q/2 =	247.5
	Annual carrying cost			(Q/2) * H =	495
	Annual ordering cost			(D/Q) * S =	494.9494949495
	Annual purchase cost			P * D =	24500
	Total Annual Cost			TC =	25489.9494949495
16.	Quantity Discounts
				Price Level:	1	2	3
A	Minimum quantity for price			Qmin =	1	200	500
	Price			P =	14.00	13.80	13.60
	Optimal Q (for each price)			Qopt =	468	472	500
	Number of orders per year			D/Qopt =	20.5128205128	20.3389830508	19.2
	Average Inventory			Qopt/2 =	234	236	250
	Annual carrying cost			(Qopt/2) * H =	819	814.2	850
	Annual ordering cost			(D/Qopt) * S =	820.5128205128	813.5593220339	768
	Annual purchase cost			P * D =	134400	132480	130560
	Total Annual Cost			TC =	136039.512820513	134107.759322034	132178
	Annual Demand			D =	9600
	Ordering cost per order			S =	40
	Annual carrying cost per unit:			H (fixed) =
	2	FALSE		H (% of price) =	25.00%
	Optimal Q (overall)			Qopt =	500
	Actual Order Quantity			Q =	500
	Increment			DQ =	10
	Price			P =	13.6
	Number of orders per year			D/Q =	19.2
	Average Inventory			Q/2 =	250
	Annual carrying cost			(Q/2) * H =	850
	Annual ordering cost			(D/Q) * S =	768
	Annual purchase cost			P * D =	130560
	Total Annual Cost			TC =	132178
	Quantity Discounts
				Price Level:	1	2	3
B	Minimum quantity for price			Qmin =	1	150	350
	Price			P =	14.10	13.90	13.70
	Optimal Q (for each price)			Qopt =	467	470	474
	Number of orders per year			D/Qopt =	20.556745182	20.4255319149	20.253164557
	Average Inventory			Qopt/2 =	233.5	235	237
	Annual carrying cost			(Qopt/2) * H =	823.0875	816.625	811.725
	Annual ordering cost			(D/Qopt) * S =	822.2698072805	817.0212765957	810.1265822785
	Annual purchase cost			P * D =	135360	133440	131520
	Total Annual Cost			TC =	137005.357307281	135073.646276596	133141.851582278
	Annual Demand			D =	9600
	Ordering cost per order			S =	40
	Annual carrying cost per unit:			H (fixed) =
	2	FALSE		H (% of price) =	25.00%
	Optimal Q (overall)			Qopt =	474
	Actual Order Quantity			Q =	474
	Increment			DQ =	10
	Price			P =	13.7
	Number of orders per year			D/Q =	20.253164557
	Average Inventory			Q/2 =	237
	Annual carrying cost			(Q/2) * H =	811.725
	Annual ordering cost			(D/Q) * S =	810.1265822785
	Annual purchase cost			P * D =	131520
	Total Annual Cost			TC =	133141.851582278
17.	Quantity Discounts
				Price Level:	1	2	3
	Minimum quantity for price			Qmin =	1	200	801
	Price			P =	1.20	1.10	1.00
	Optimal Q (for each price)			Qopt =	240	240	801
	Number of orders per year			D/Qopt =	15	15	4.4943820225
	Average Inventory			Qopt/2 =	120	120	400.5
	Annual carrying cost			(Qopt/2) * H =	1200	1200	4005
	Annual ordering cost			(D/Qopt) * S =	1200	1200	359.5505617978
	Annual purchase cost			P * D =	4320	3960	3600
	Total Annual Cost			TC =	6720	6360	7964.5505617977
	Annual Demand			D =	3600
	Ordering cost per order			S =	80
	Annual carrying cost per unit:			H (fixed) =	10
	1	TRUE		H (% of price) =
	Optimal Q (overall)			Qopt =	240
	Actual Order Quantity			Q =	240
	Increment			DQ =	10
	Price			P =	1.1
	Number of orders per year			D/Q =	15
	Average Inventory			Q/2 =	120
	Annual carrying cost			(Q/2) * H =	1200
	Annual ordering cost			(D/Q) * S =	1200
	Annual purchase cost			P * D =	3960
	Total Annual Cost			TC =	6360
	Actual Order Quantity			Q =	800
	Increment			DQ =	10
	Price			P =	1.1
	Number of orders per year			D/Q =	4.5
	Average Inventory			Q/2 =	400
	Annual carrying cost			(Q/2) * H =	4000
	Annual ordering cost			(D/Q) * S =	360
	Annual purchase cost			P * D =	3960
	Total Annual Cost			TC =	8320
19.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	300
	Std dev demand				sd =	30
	Average lead time				LT =	1
	Std dev lead time				sLT =	0
	Service level				SL =	0.99
	Increment				DSL =	0.01
	Stock out risk					0.01
	Average demand during lead time				dLT =	300
	Std dev demand during lead tTime				sdLT =	30
	Safety stock				SS =	69.7902578395
	Reorder point				ROP =	369.7902578395
20.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	600
	Std dev demand				sd =	52
	Average lead time				LT =	1
	Std dev lead time				sLT =	0
	Service level				SL =	0.96
	Increment				DSL =	0.01
	Stock out risk					0.04
	Average demand during lead time				dLT =	600
	Std dev demand during lead tTime				sdLT =	52
	Safety stock				SS =	91.035690275
	Reorder point				ROP =	691.035690275
20c.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	600
	Std dev demand				sd =	52
	Average lead time				LT =	1
	Std dev lead time				sLT =	0
	Service level				SL =	0.5
	Increment				DSL =	0.01
	Stock out risk					0.5
	Average demand during lead time				dLT =	600
	Std dev demand during lead time				sdLT =	52
					z =	-1.39213763529183E-16
	Safety stock				SS =	-0
	Reorder point				ROP =	600

Problems 21-28

Chapter 13 - Problems 20-28				Note: This worksheet displays results only, you must copy the shaded
<Back				area into the corresponding template to make additional calculations.
21a.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	21
	Std dev demand				sd =	3.5
	Average lead time				LT =	0.2857142857
	Std dev lead time				sLT =	0
	Service level				SL =	0.9
	Increment				DSL =	0.01
	Stock out risk					0.1
	Average demand during lead time				dLT =	6
	Std dev demand during lead tTime				sdLT =	1.8708286934
	Safety stock				SS =	2.3975619981
	Reorder point				ROP =	8.3975619981
		ROP / d =	0.3998839734	week
		=	2.7991878136	days
21b.	Fixed Order Interval Model
	Average demand				d =	21
	Std dev demand				sd =	3.5
	Lead time				LT =	0.2857142857
	Amount on hand at reorder time				A =	8
	Order interval				OI =	1.4285714286
	Service level				SL =	0.9
	Increment				DSL =	0.01
	Stock out risk					0.1
	Average demand during lead time				dLT =	36
	Safety stock				SS =	5.8728035222
	Amount to order					33.8728035222
22c.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	21
	Std dev demand				sd =	3.5
	Average lead time				LT =	0.2857142857
	Std dev lead time				sLT =	0
	Service level				SL =	0.5825670042
	Increment				DSL =	0.01
	Stock out risk					0.4174329958
	Average demand during lead time				dLT =	6
	Std dev demand during lead time				sdLT =	1.8708286934
	Safety stock				SS =	0.3900028285
	Reorder point				ROP =	6.3900028285
	Use goal seek to set ROP = 6.39 (2 gallons below previous ROP) by changing SL.
22.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	30
	Std dev demand				sd =	18.6462195427
	Average lead time				LT =	4
	Std dev lead time				sLT =	0
	Service level				SL =	0.91
	Increment				DSL =	0.01
	Stock out risk					0.09
	Average demand during lead time				dLT =	120
	Std dev demand during lead tTime				sdLT =	37.2924390855
	Safety stock				SS =	50
	Reorder point				ROP =	170
	Average demand				d =	30
	Std dev demand				sd =	18.6462195427
	Average lead time				LT =	4
	Std dev lead time				sLT =	0
	Service level				SL =	0.97
	Increment				DSL =	0.01
	Stock out risk					0.03
	Average demand during lead time				dLT =	120
	Std dev demand during lead tTime				sdLT =	37.2924390855
	Safety stock				SS =	70.139230417
	Reorder point				ROP =	190.139230417
23.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	85
	Std dev demand				sd =	0
	Average lead time				LT =	6
	Std dev lead time				sLT =	1.1
	Service level				SL =	0.8906409726
	Increment				DSL =	0.01
	Stock out risk					0.1093590274
	Average demand during lead time				dLT =	510
	Std dev demand during lead time				sdLT =	93.5
	Safety stock				SS =	114.9997444827
	Reorder point				ROP =	624.9997444827
24.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	12
	Std dev demand				sd =	2
	Average lead time				LT =	4
	Std dev lead time				sLT =	1
	Service level				SL =	0.96
	Increment				DSL =	0.01
	Stock out risk					0.04
	Average demand during lead time				dLT =	48
	Std dev demand during lead time				sdLT =	12.6491106407
	Safety stock				SS =	22.1446253565
	Reorder point				ROP =	70.1446253565
25a.	Quantity Discounts
				Price Level:	1	2	3
	Minimum quantity for price			Qmin =	1	400	800
	Price			P =	2.00	1.70	1.62
	Optimal Q (for each price)			Qopt =	300	400	800
	Number of orders per year			D/Qopt =	15	11.25	5.625
	Average Inventory			Qopt/2 =	150	200	400
	Annual carrying cost			(Qopt/2) * H =	450	600	1200
	Annual ordering cost			(D/Qopt) * S =	450	337.5	168.75
	Annual purchase cost			P * D =	9000	7650	7290
	Total Annual Cost			TC =	9900	8587.5	8658.75
	Annual Demand			D =	4500
	Ordering cost per order			S =	30
	Annual carrying cost per unit:			H (fixed) =	3
	1	TRUE		H (% of price) =
	Optimal Q (overall)			Qopt =	400
	Actual Order Quantity			Q =	800
	Increment			DQ =	10
	Price			P =	1.62
	Number of orders per year			D/Q =	5.625
	Average Inventory			Q/2 =	400
	Annual carrying cost			(Q/2) * H =	1200
	Annual ordering cost			(D/Q) * S =	168.75
	Annual purchase cost			P * D =	7290
	Total Annual Cost			TC =	8658.75
25b.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	12.5
	Std dev demand				sd =	2
	Average lead time				LT =	3
	Std dev lead time				sLT =	0
	Service level				SL =	0.985
	Increment				DSL =	0.01
	Stock out risk					0.015
	Average demand during lead time				dLT =	37.5
	Std dev demand during lead time				sdLT =	3.4641016151
	Safety stock				SS =	7.5174148313
	Reorder point				ROP =	45.0174148313
26a.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	260
	Ordering cost per order			S =	2
	Annual carrying cost per unit			H =	0.2
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	72.1110255093
26b.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	5
	Std dev demand				sd =	0.5
	Average lead time				LT =	2
	Std dev lead time				sLT =	0
	Service level				SL =	0.9976694316
	Increment				DSL =	0.01
	Stock out risk					0.0023305684
	Average demand during lead time				dLT =	10
	Std dev demand during lead time				sdLT =	0.7071067812
	Safety stock				SS =	2.0007919821
	Reorder point				ROP =	12.0007919821
26c.	Fixed Order Interval Model
	Average demand				d =	5
	Std dev demand				sd =	0.5
	Lead time				LT =	2
	Amount on hand at reorder time				A =	12
	Order interval				OI =	7
	Service level				SL =	0.9772715419
	Increment				DSL =	0.01
	Stock out risk					0.0227284581
	Average demand during lead time				dLT =	45
	Safety stock				SS =	3.0005958251
	Amount to order					36.0005958251
27.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	80
	Std dev demand				sd =	10
	Average lead time				LT =	8
	Std dev lead time				sLT =	1
	Service level				SL =	0.9
	Increment				DSL =	0.0001
	Stock out risk					0.1
	Average demand during lead time				dLT =	640
	Std dev demand during lead time				sdLT =	84.8528137424
	Safety stock				SS =	108.7431908564
	Reorder point				ROP =	748.7431908564
28a.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	3600
	Ordering cost per order			S =	1
	Annual carrying cost per unit			H =	0.4
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	134.16407865
28b.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	10
	Std dev demand				sd =	2
	Average lead time				LT =	3
	Std dev lead time				sLT =	0
	Service level				SL =	0.96
	Increment				DSL =	0.0001
	Stock out risk					0.04
	Average demand during lead time				dLT =	30
	Std dev demand during lead time				sdLT =	3.4641016151
	Safety stock				SS =	6.0645554176
	Reorder point				ROP =	36.0645554176

Problems 31-39

Chapter 13 - Problems 31-39				Note: This worksheet displays results only, you must copy the shaded
<Back				area into the corresponding template to make additional calculations.
31.	Fixed Order Interval Model
	Average demand				d =	40	40	40
	Std dev demand				sd =	3	3	3
	Lead time				LT =	2	2	2
	Amount on hand at reorder time				A =	42	8	103
	Order interval				OI =	14	14	14
	Service level				SL =	0.98	0.98	0.98
	Increment				DSL =	0.01	0.01	0.01
	Stock out risk					0.02	0.02	0.02
	Average demand during lead time				dLT =	640	640	640
	Safety stock				SS =	24.6449869276	24.6449869276	24.6449869276
	Amount to order					622.6449869276	656.6449869276	561.6449869276
32a.	Average demand				d =	60
	Std dev demand				sd =	4
	Average lead time				LT =	2
	Std dev lead time				sLT =	0
	Service level				SL =	0.975
	Increment				DSL =	0.0001
	Stock out risk					0.025
	Average demand during lead time				dLT =	120
	Std dev demand during lead time				sdLT =	5.6568542495
	Safety stock				SS =	11.0872141774
	Reorder point				ROP =	131.0872141774
32b.	Basic Economic Order Quantity (EOQ) Model
	Annual Demand			D =	3000
	Ordering cost per order			S =	70
	Annual carrying cost per unit			H =	4.5
	Working days per year			D/Y =	360
	Economic Order Quantity			EOQ =	305.5050463304
32c.	Fixed Order Interval Model
	Average demand				d =	70
	Std dev demand				sd =	5
	Lead time				LT =	2
	Amount on hand at reorder time				A =	110
	Order interval				OI =	4
	Service level				SL =	0.975
	Increment				DSL =	0.01
	Stock out risk					0.025
	Average demand during lead time				dLT =	420
	Safety stock				SS =	24.004522837
	Amount to order					334.004522837
33a.	ABC Classification Syatem
		Dollar Amount		Annual Demand
	Class	Total	Percent	Total	Percent
	A	2,745,000	72.50%	66,000	31.44%
	B	715,700	18.90%	86,300	41.11%
	C	325,650	8.60%	57,650	27.46%
	Enter lower limit for dollar amount:
	A	945,000		B	195,600
			Unit	Dollar
	Item	Usage	Cost	Amount	Class
	H4-010	20,000	2.50	50,000	C
	H5-201	60,200	4.00	240,800	B
	P6-400	9,800	28.50	279,300	B
	P6-401	16,300	12.00	195,600	B
	P7-100	6,250	9.00	56,250	C
	P9-103	4,500	22.00	99,000	C
	TS-300	21,000	45.00	945,000	A
	TS-400	45,000	40.00	1,800,000	A
	TS-041	800	20.00	16,000	C
	V1-001	26,100	4.00	104,400	C
34.	Single Period Model
	Select demand distribution:			Discrete
		4
	Shortage cost (revenue - cost)			Cs =	1.6
	Excess cost (cost - salvage)			Ce =	0.8


	Optimal Service Level			SLo =	0.6666666667
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	25
	Increment			DS =	1
	Actual Service Level			SL =	0.7300
	Balance:
				Ce * Service Level	0.584
				Cs * (1 - Service Level)	0.432
	Discrete Distribution:		Demand	Freq	Cum
			19	0.01	0.01
			20	0.05	0.06
			21	0.12	0.18
			22	0.18	0.36
			23	0.13	0.49
			24	0.14	0.63
			25	0.1	0.73
			26	0.11	0.84
			27	0.1	0.94
			28	0.04	0.98
			29	0.02	1

35a.	Single Period Model
	Select demand distribution:			Poisson
			FALSE	FALSE	TRUE
	Shortage cost (revenue - cost)			Cs =	88000
	Excess cost (cost - salvage)			Ce =	245
	Average demand				3.2

	Optimal Service Level			SLo =	0.9972236387
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	9
	Increment			DS =	1
	Actual Service Level			SL =	0.9982
	Balance:
				Ce * Service Level	244.5683374723
				Cs * (1 - Service Level)	155.0461323991
35b.	Single Period Model
	Select demand distribution:			Poisson
			FALSE	FALSE	TRUE
	Shortage cost (revenue - cost)			Cs =	10.5203646911
	Excess cost (cost - salvage)			Ce =	245
	Average demand				3.2

	Optimal Service Level			SLo =	0.041172314
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	0
	Increment			DS =	1
	Actual Service Level			SL =	0.0408
	Balance:
				Ce * Service Level	9.9867399747
				Cs * (1 - Service Level)	10.0915314396
	Set Actual Stocking Level to 0 to determine Actual Service Level (.0408).
	Then use goal seek to set Optimal Service Level tp .0408 by changing Cs.
36.	Single Period Model
	Select demand distribution:			Normal
			FALSE	TRUE	FALSE
	Shortage cost (revenue - cost)			Cs =	0.5
	Excess cost (cost - salvage)			Ce =	0.6
	Average demand				80
	Std dev demand				10
	Optimal Service Level			SLo =	0.4545454545
	Optimal Stocking Level			So =	78.8581521393
	Actual Stocking Level			S =	78.8581521393
	Increment			DS =	1
	Actual Service Level			SL =	0.4545
	Balance:
				Ce * Service Level	0.2727273644
				Cs * (1 - Service Level)	0.2727271963
37.	Single Period Model
	Select demand distribution:			Normal
			FALSE	TRUE	FALSE
	Shortage cost (revenue - cost)			Cs =	4.8889581632
	Excess cost (cost - salvage)			Ce =	0.35
	Average demand				40
	Std dev demand				6
	Optimal Service Level			SLo =	0.933192824
	Optimal Stocking Level			So =	49.0000139608
	note: use Goal Seek to set So to 49 by changing Cs
38.	Single Period Model
	Select demand distribution:			Poisson
	Shortage cost (revenue - cost)			Cs =	1
	Excess cost (cost - salvage)			Ce =	1.5
	Average demand				6

	Optimal Service Level			SLo =	0.4
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	5
	Increment			DS =	1
	Actual Service Level			SL =	0.4457
	Balance:
				Ce * Service Level	0.668519462
				Cs * (1 - Service Level)	0.5543203586
39.	Single Period Model
	Select demand distribution:			Normal
	Shortage cost (revenue - cost)			Cs =	0.4
	Excess cost (cost - salvage)			Ce =	0.2
	Average demand				400
	Std dev demand				50
	Optimal Service Level			SLo =	0.6666666667
	Optimal Stocking Level			So =	421.5363797906
	Actual Stocking Level			S =	421.5363797906
	Increment			DS =	1
	Actual Service Level			SL =	0.6667
	Balance:
				Ce * Service Level	0.1333333487
				Cs * (1 - Service Level)	0.1333333025

Problems 40-44

Chapter 13 - Problems 40-44				Note: This worksheet displays results only, you must copy the shaded
<Back				area into the corresponding template to make additional calculations.
40a.	Single Period Model
	Select demand distribution:			Discrete
		4
	Shortage cost (revenue - cost)			Cs =	10	10
	Excess cost (cost - salvage)			Ce =	0.5177605	1.7607217053


	Optimal Service Level			SLo =	0.9507727429	0.8502879543
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	4	3
	Increment			DS =	1	1
	Actual Service Level			SL =	0.9500	0.8500
	Balance:
				Ce * Service Level	0.491872475	1.4966134495
				Cs * (1 - Service Level)	0.5	1.5
	Discrete Distribution:		Demand	Freq	Cum
			0	0.3	0.3
			1	0.2	0.5
			2	0.2	0.7
			3	0.15	0.85
			4	0.1	0.95
			5	0.05	1

	Since SL(4) = .95 and SL(3) = .85, use goal seek to set SLo to these values by changing Ce.
40b.	Single Period Model
	Select demand distribution:			Discrete
	Shortage cost (revenue - cost)			Cs =	189.0778609732	56.722521585
	Excess cost (cost - salvage)			Ce =	10	10


	Optimal Service Level			SLo =	0.9497683974	0.8501255684
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	4	3
	Increment			DS =	1	1
	Actual Service Level			SL =	0.9500	0.8500
	Balance:
				Ce * Service Level	9.5	8.5
				Cs * (1 - Service Level)	9.4538930487	8.5083782377
	Since SL(4) = .95 and SL(3) = .85, use goal seek to set SLo to these values by changing Cs.
41.	Single Period Model
	Select demand distribution:			Discrete
		4
	Shortage cost (revenue - cost)			Cs =	1000
	Excess cost (cost - salvage)			Ce =	150


	Optimal Service Level			SLo =	0.8695652174
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	3
	Increment			DS =	1
	Actual Service Level			SL =	1.0000
	Balance:
				Ce * Service Level	150
				Cs * (1 - Service Level)	0
	Discrete Distribution:		Demand	Freq	Cum
			0	0.1	0.1
			1	0.5	0.6
			2	0.25	0.85
			3	0.15	1

	Since SL(2) = .85 and SL(3) = 1.0, stock 3 spares.
42b.	Single Period Model
	Select demand distribution:			Discrete
		4
	Shortage cost (revenue - cost)			Cs =	27
	Excess cost (cost - salvage)			Ce =	23


	Optimal Service Level			SLo =	0.54
				So =
				Adjust S until SL >= SLo
	Actual Stocking Level			S =	2
	Increment			DS =	1
	Actual Service Level			SL =	0.8000
	Balance:
				Ce * Service Level	18.4
				Cs * (1 - Service Level)	5.4
	Discrete Distribution:		Demand	Freq	Cum
			0	0.15	0.15
			1	0.35	0.5
			2	0.3	0.8
			3	0.2	1

	Since SL(1) = .5 and SL(2) = .8, stock 2 cakes.
43.	Single Period Model
	Select demand distribution:			Normal
	Shortage cost (revenue - cost)			Cs =	99
	Excess cost (cost - salvage)			Ce =	200
	Average demand				18
	Std dev demand				4.55
	Optimal Service Level			SLo =	0.3311036789
	Optimal Stocking Level			So =	16.0122523416
	Actual Stocking Level			S =	16.0122523416
	Increment			DS =	1
	Actual Service Level			SL =	0.3311
	Balance:
				Ce * Service Level	66.220735786
				Cs * (1 - Service Level)	66.220735786
44.	Reorder Point (ROP) with EOQ Ordering
	Average demand				d =	4.6
	Std dev demand				sd =	1.265
	Average lead time				LT =	3
	Std dev lead time				sLT =	0
	Service level				SL =	0.9723802611
	Increment				DSL =	0.01
	Stock out risk					0.0276197389
	Average demand during lead time				dLT =	13.8
	Std dev demand during lead time				sdLT =	2.1910442716
					z =	1.9169878295
	Safety stock				SS =	4.2002052026
	Reorder point				ROP =	18.0002052026
	Use Goal Seek to set ROP = 18

Clear