Deskpro Computer Example

C 500
D 12000
H 100
S 4000
Lot Size Orders per year Cycle Inventory Ordering Cost Holding Cost Total Cost (excluding materials)
100 120 50 $ 480,000.00 $ 5,000.00 $ 485,000.00
200 60 100 $ 240,000.00 $ 10,000.00 $ 250,000.00
300 40 150 $ 160,000.00 $ 15,000.00 $ 175,000.00
400 30 200 $ 120,000.00 $ 20,000.00 $ 140,000.00
500 24 250 $ 96,000.00 $ 25,000.00 $ 121,000.00
600 20 300 $ 80,000.00 $ 30,000.00 $ 110,000.00
700 17 350 $ 68,571.43 $ 35,000.00 $ 103,571.43
800 15 400 $ 60,000.00 $ 40,000.00 $ 100,000.00
900 13 450 $ 53,333.33 $ 45,000.00 $ 98,333.33
1000 12 500 $ 48,000.00 $ 50,000.00 $ 98,000.00
1100 11 550 $ 43,636.36 $ 55,000.00 $ 98,636.36
1200 10 600 $ 40,000.00 $ 60,000.00 $ 100,000.00
1300 9 650 $ 36,923.08 $ 65,000.00 $ 101,923.08
1400 9 700 $ 34,285.71 $ 70,000.00 $ 104,285.71
1500 8 750 $ 32,000.00 $ 75,000.00 $ 107,000.00
1600 8 800 $ 30,000.00 $ 80,000.00 $ 110,000.00
1700 7 850 $ 28,235.29 $ 85,000.00 $ 113,235.29
1800 7 900 $ 26,666.67 $ 90,000.00 $ 116,666.67
1900 6 950 $ 25,263.16 $ 95,000.00 $ 120,263.16
2000 6 1000 $ 24,000.00 $ 100,000.00 $ 124,000.00
2100 6 1050 $ 22,857.14 $ 105,000.00 $ 127,857.14
2200 5 1100 $ 21,818.18 $ 110,000.00 $ 131,818.18
2300 5 1150 $ 20,869.57 $ 115,000.00 $ 135,869.57
2400 5 1200 $ 20,000.00 $ 120,000.00 $ 140,000.00
2500 5 1250 $ 19,200.00 $ 125,000.00 $ 144,200.00
2600 5 1300 $ 18,461.54 $ 130,000.00 $ 148,461.54
2700 4 1350 $ 17,777.78 $ 135,000.00 $ 152,777.78
2800 4 1400 $ 17,142.86 $ 140,000.00 $ 157,142.86
2900 4 1450 $ 16,551.72 $ 145,000.00 $ 161,551.72
3000 4 1500 $ 16,000.00 $ 150,000.00 $ 166,000.00
 Cost by Order Quantity
xcluding materials)
$600,000.00

$500,000.00

$400,000.00

$300,000.00

$200,000.00

$100,000.00

$-
0 500 1000 1500 2000 2500 3000

Order quantity (Q)

Ordering Cost Holding Cost Total Cost (excluding materials)

500 3000
Placement cost per order $ 175.00 LTL
Shipping cost (container) $ 575.00 D 28000
Shipping cost (per unit, LTL) $ 1.75 C $ 81.75
Cost of unit $ 80.00 H $ 16.35
Annual holding cost rate 20% S $ 175.00
Container capacity 1500 Q* 774.20
Annual demand 28000 Order/yr 36.17
Cycle Inv 387.10
Flow Time 0.01
Annual material cost $ 2,289,000.00
Annual ordering cost $ 6,329.10
Annual Holding cost $ 6,329.10
Annual TOTAL cost $ 2,301,658.20

Difference $ 35,658.20
Container
28000
$ 80.00
$ 16.00
$ 750.00
1500.00
18.67
750.00
0.03
$ 2,240,000.00
$ 14,000.00
$ 12,000.00
$ 2,266,000.00
 Machine 1 Machine 2
Changeover cost $ 500.00 $ 300.00 D
Per unit cost $ - $ 1.00 C
Max batch size 1100 N/A H
Prod rate per week 1050 820 S
P
Qp
Material cost per component $ 45.00 Cycle Inventory
Annual holding cost rate 10% Order/yr
Annual demand 10000 Flow Time
Annual material cost
Annual ordering cost
Annual Holding cost
Annual TOTAL cost

Difference
Machine 1 Machine 2
10000 10000
$ 45.00 $ 46.00 =$B$8+B3
$ 4.50 $ 4.60 =$B$9*F3
$ 500.00 $ 300.00 =B2
54600 42640 =B5*52
1100.00 1305.36 =MIN(SQRT(2*F2*F5/((1-F2/F6)*F4)),$B$4)
449.27 499.61 =(1-F2/F6)*F7/2
9.09 7.66 =F2/F7
0.04492673992674 0.049961237932 =F8/F2
$ 450,000.00 $ 460,000.00 =F2*F3
$ 4,545.45 $ 2,298.22 =F5*F9
$ 2,021.70 $ 2,298.22 =F4*F8
$ 456,567.16 $ 464,596.43 =SUM(F11:F13)

$ 8,029.28
Minimum Order Price
0 $ 3.00
5000 $ 2.96
10000 $ 2.92

Demand (D) 120000 120000 120000

Fixed Order Cost (S) $ 100.00 $ 100.00 $ 100.00
Purchase Cost (C) $ 3.00 2.96 2.92
Holding cost fraction (h) 20% 20% 20%
Holding Cost (H) $ 0.60 $ 0.59 $ 0.58
Optimal Lot Size (Q*) 6325 6367 10000
Orders per year 18.97 18.85 12.00
Cycle Inventory 3162.28 3183.57 5000.00
Flow Time 0.03 0.03 0.04
Total Annual Cost $ 363,794.73 $ 358,969.35 $ 354,520.00
 #N/A
#N/A
#N/A
#N/A
=B10*B9
=SQRT(2*B7*B8/B11)
=B7/B12
=B12/2
=B14/B7
=B9*B7+B8*B13+B11*B14