s on using the EOQ template: All values in blue cells are to be entered by the user, all other values are automatically calculated, and do not need to be altered. Each worksheet has a sample value set entered, make sure to clear all values before proceeding with your own data. he user, all d do not need value set proceeding Basic Economic Order Quantity (EOQ) Model Economic O Annual demand Ordering cost per order (Setup) Annual carrying cost per unit Working days per year Economic Order Quantity Actual order quantity Increment Number of orders per year Length of order cycle (days) Average inventory Annual carrying cost Annual ordering cost Total annual Cost D= 10000 S= 150 H= 1.33 = 365 EOQ = 1501.878523 units/year $/order $/unit-year days/year units Q= 1500 DQ = D/Q = 6.7 orders/year Q/D = 54.8 days Q/2 = 750 units (Q/2) * H = $ 997.50 (D/Q) * S = $ 1,000.00 TC = $ 1,997.50 $12,000.00 $10,000.00 $8,000.00 $6,000.00 $4,000.00 $1,997.5 $2,000.00 $0 500 1000 Ord Carrying Cost Chart Data Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 3600 3450 3300 3150 3000 2850 2700 2550 2400 2250 2100 1950 1800 1650 1500 1350 1200 1050 900 750 600 450 300 150 0 Carrying Cost $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 2,394.00 2,294.25 2,194.50 2,094.75 1,995.00 1,895.25 1,795.50 1,695.75 1,596.00 1,496.25 1,396.50 1,296.75 1,197.00 1,097.25 997.50 897.75 798.00 698.25 598.50 498.75 399.00 299.25 199.50 99.75 - Ordering Cost $ 416.67 $ 434.78 $ 454.55 $ 476.19 $ 500.00 $ 526.32 $ 555.56 $ 588.24 $ 625.00 $ 666.67 $ 714.29 $ 769.23 $ 833.33 $ 909.09 $ 1,000.00 $ 1,111.11 $ 1,250.00 $ 1,428.57 $ 1,666.67 $ 2,000.00 $ 2,500.00 $ 3,333.33 $ 5,000.00 $ 10,000.00 ### Total Cost $ 2,810.67 $ 2,729.03 $ 2,649.05 $ 2,570.94 $ 2,495.00 $ 2,421.57 $ 2,351.06 $ 2,283.99 $ 2,221.00 $ 2,162.92 $ 2,110.79 $ 2,065.98 $ 2,030.33 $ 2,006.34 $ 1,997.50 $ 2,008.86 $ 2,048.00 $ 2,126.82 $ 2,265.17 $ 2,498.75 $ 2,899.00 $ 3,632.58 $ 5,199.50 $ 10,099.75 $ - Quantity Ordered Total Cost at EOQ 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 1500 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 $ 1,997.50 0 Economic Order Quantity 00 00 00 00 00 $1,997.50 00 0 500 1000 1500 2000 2500 3000 3500 Order Quantity Q Carrying Cost Ordering Cost Total Cost 4000 Reorder Point (ROP) with EOQ Ordering Reorder P Average daily demand Average lead time Std dev demand during lead time Service level Increment Stock out risk z associated with service level Average demand during lead time Safety stock Reorder point d= L= sL = units days units SL = DSL = Probability 1 #VALUE! dL = 0 units SS = #VALUE! units ROP = #VALUE! units 0.0 5.0 10.0 15. Daliy De daily deman Chart Data 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 daily demand cumulative probability daily demand probability mass ROP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Reorder Point 5.0 10.0 15.0 20.0 Daliy Demand daily demand ROP 25.0 30.0 Reorder Point (ROP) with EOQ Ordering Reorder P Average daily demand Average lead time Std dev demand during lead time Service level Increment Stock out risk z associated with service level Average demand during lead time Safety stock Reorder point d= L= sL = units days units SL = DSL = Probability 1 #VALUE! dL = 0 units SS = #VALUE! units ROP = #VALUE! units 0.0 5.0 10.0 15. Daliy De daily deman Chart Data 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 daily demand cumulative probability daily demand probability mass ROP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 Err:502 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Reorder Point 5.0 10.0 15.0 20.0 Daliy Demand daily demand ROP 25.0 30.0