expert

tulip78
QuantitativeInventoryAnalysisTemplate.xlsm

Instructions

Notes 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. Cells with red marks in the upper right corner have comments. Let the mouse hover over those cells to read the comments to further explain the template model. This spreadsheet is locked to keep the cells with equations from being changed.

Basic EOQ

Basic Economic Order Quantity (EOQ) Model
Annual demand D = 10,000 units/year
Ordering cost per order (setup) S = 150 $/order
Annual carrying cost per unit H = 1.32 $/unit-year
Working days per year = 365 days/year
Economic order quantity EOQ = 1507.5567228888 units
Actual order quantity Q = 1500
Increment DQ = 500
Number of orders per year D/Q = 6.7 orders/year
Length of order cycle (days) Q/D = 54.8 days
Average inventory Q/2 = 750 units
Annual carrying cost (Q/2) * H = $ 990.00
Annual ordering cost (D/Q) * S = $ 1,000.00
Total annual cost TC = $ 1,990.00
Chart Data
Q Carrying Cost Ordering Cost Total Cost Quantity Ordered Total Cost at EOQ
1 3600 $ 2,376.00 $ 416.67 $ 2,792.67 1500 $ 1,990.00
2 3450 $ 2,277.00 $ 434.78 $ 2,711.78 1500 $ 1,990.00
3 3300 $ 2,178.00 $ 454.55 $ 2,632.55 1500 $ 1,990.00
4 3150 $ 2,079.00 $ 476.19 $ 2,555.19 1500 $ 1,990.00
5 3000 $ 1,980.00 $ 500.00 $ 2,480.00 1500 $ 1,990.00
6 2850 $ 1,881.00 $ 526.32 $ 2,407.32 1500 $ 1,990.00
7 2700 $ 1,782.00 $ 555.56 $ 2,337.56 1500 $ 1,990.00
8 2550 $ 1,683.00 $ 588.24 $ 2,271.24 1500 $ 1,990.00
9 2400 $ 1,584.00 $ 625.00 $ 2,209.00 1500 $ 1,990.00
10 2250 $ 1,485.00 $ 666.67 $ 2,151.67 1500 $ 1,990.00
11 2100 $ 1,386.00 $ 714.29 $ 2,100.29 1500 $ 1,990.00
12 1950 $ 1,287.00 $ 769.23 $ 2,056.23 1500 $ 1,990.00
13 1800 $ 1,188.00 $ 833.33 $ 2,021.33 1500 $ 1,990.00
14 1650 $ 1,089.00 $ 909.09 $ 1,998.09 1500 $ 1,990.00
15 1500 $ 990.00 $ 1,000.00 $ 1,990.00 1500 $ 1,990.00
16 1350 $ 891.00 $ 1,111.11 $ 2,002.11 1500 $ 1,990.00
17 1200 $ 792.00 $ 1,250.00 $ 2,042.00 1500 $ 1,990.00
18 1050 $ 693.00 $ 1,428.57 $ 2,121.57 1500 $ 1,990.00
19 900 $ 594.00 $ 1,666.67 $ 2,260.67 1500 $ 1,990.00
20 750 $ 495.00 $ 2,000.00 $ 2,495.00 1500 $ 1,990.00
21 600 $ 396.00 $ 2,500.00 $ 2,896.00 1500 $ 1,990.00
22 450 $ 297.00 $ 3,333.33 $ 3,630.33 1500 $ 1,990.00
23 300 $ 198.00 $ 5,000.00 $ 5,198.00 1500 $ 1,990.00
24 150 $ 99.00 $ 10,000.00 $ 10,099.00 1500 $ 1,990.00
25 0 $ - 0 $ - 0 $ - 0 1500 0

Economic Order Quantity

Carrying Cost 3600 3450 3300 3150 3000 2850 2700 2550 2400 2250 2100 1950 1800 1650 1500 1350 1200 1050 900 750 600 450 300 150 2376 2277 2178 2079 1980 1881 1782 1683 1584 1485 1386 1287 1188 1089 990 891 792 693 594 495 396 297 198 99 Ordering Cost 3600 3450 3300 3150 3000 2850 2700 2550 2400 2250 2100 1950 1800 1650 1500 1350 1200 1050 900 750 600 450 300 150 416.66666666666663 434.78260869565219 454.54545454545456 476.19047619047615 500 526.31578947368 416 555.55555555555554 588.23529411764707 625 666.66666666666674 714.28571428571433 769.23076923076928 833.33333333333326 909.09090909090912 1000 1111.1111111111111 1250 1428.5714285714287 1666.6666666666665 2000 2500 3333.333333333333 5000 10000 Total Cost 3600 3450 3300 3150 3000 2850 2700 2550 2400 2250 2100 1950 1800 1650 1500 1350 1200 1050 900 750 600 450 300 150 2792.6666666666665 2711.782608695652 2632.5454545454545 2555.1904761904761 2480 2407.3157894736842 2337.5555555555557 2271.2352941176468 2209 2151.666666666667 2100.2857142857142 2056.2307692307695 2021.3333333333333 1998.090909090909 1990 2002.1111111111111 2042 2121.5714285714284 2260.6666666666665 2495 2896 3630.333333333333 5198 10099

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 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 1990 0

Order Quantity Q

ROP no SS

Reorder Point (ROP) with EOQ Ordering
Average daily demand d = 27.397260274 units
Average lead time L = 10 days
Std dev demand during lead time sL = 10.4 units
Service level SL = 0.5
Increment DSL =
Stock out risk 0.5
z associated with service level 0.00
Average demand during lead time dL = 273.9726027397 units
Safety stock SS = 0.0 units
Reorder point ROP = 274.0 units
Chart Data
Daily Demand Cumulative Probability Daily Demand Probability Mass ROP
1 238 0.000
2 241 0.001 239.8 0.001 274.0
3 244 0.002 242.8 0.001 274.0
4 247 0.005 245.7 0.003 274.0
5 250 0.011 248.7 0.006 274.0
6 253 0.023 251.7 0.012 274.0
7 256 0.043 254.7 0.020 274.0
8 259 0.077 257.6 0.033 274.0
9 262 0.127 260.6 0.050 274.0
10 265 0.196 263.6 0.069 274.0
11 268 0.284 266.5 0.088 274.0
12 271 0.388 269.5 0.104 274.0
13 274 0.500 272.5 0.112 274.0
14 277 0.612 275.5 0.112 274.0
15 280 0.716 278.4 0.104 274.0
16 283 0.804 281.4 0.088 274.0
17 286 0.873 284.4 0.069 274.0
18 289 0.923 287.3 0.050 274.0
19 292 0.957 290.3 0.033 274.0
20 295 0.977 293.3 0.020 274.0
21 298 0.989 296.3 0.012 274.0
22 301 0.995 299.2 0.006 274.0
23 304 0.998 302.2 0.003 274.0
24 307 0.999 305.2 0.001 274.0
25 310 1.000 308.1 0.001 274.0

Reorder Point

Daily Demand 239.80117416829745 242.77260273972604 245.7440313111546 248.71545988258316 251.68688845401175 254.65831702544031 257.62974559686893 260.60117416829746 263.57260273972599 266.54403131115464 269.51545988258317 272.48688845401171 275.45831702544035 278.42974559686888 281.40117416829742 284.37260273972606 287.34403131115459 290.31545988258313 293.28688845401177 296.25831702544031 299.22974559686884 302.20117416829748 305.17260273972602 308.14403131115455 5.3315393825812546E-4 1.3008296190101156E-3 2.9266282946090764E-3 6.0714942049210413E-3 1.1614642468562761E-2 2.0488000798653712E-2 3.3325592763002253E-2 4.9985228963722372E-2 6.9134014680218625E-2 8.8171613944900834E-2 0.10369389799931472 0.112451518902008 33 0.11245151890200833 0.10369389799931472 8.8171613944900806E-2 6.9134014680218625E-2 4.9985228963722372E-2 3.3325592763002287E-2 2.048800079865376E-2 1.1614642468562586E-2 6.0714942049211462E-3 2.9266282946091016E-3 1.3008296190101243E-3 5.3315393825814628E-4 ROP 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 27 3.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 273.97260273972603 5.3315393825812546E-4 1.3008296190101156E-3 2.9266282946090764E-3 6.0714942049210413E-3 1.1614642468562761E-2 2.0488000798653712E-2 3.3325592763002253E-2 4.9985228963722372E-2 6.9134014680218625E-2 8.8171613944900834E-2 0.10369389799931472 0.11245151890200833 0.11245151890200833 0.10369389799931472 8.8171613944900806E-2 6.9134014680218625E-2 4.9985228963722372E-2 3.3325592763002287E-2 2.048800079865376E-2 1.1614642468562586E-2 6.0714942049211462E-3 2.9266282946091016E-3 1.3008296190101243E-3 5.3315393825814628E-4

Daliy Demand

Probability

ROP w SS

Reorder Point (ROP) with EOQ Ordering
Average daily demand d = 27.397260274 units
Average lead time L = 10 days
Std dev demand during lead time sL = 10.4 units
Service level SL = 0.95
Increment DSL =
Stock out risk 0.05
z associated with service level 1.64
Average demand during lead time dL = 273.9726027397 units
Safety stock SS = 17.1 units
Reorder point ROP = 291.1 units
Chart Data
Daily Demand Cumulative Probability Daily Demand Probability Mass ROP
1 238 0.000
2 241 0.001 239.8 0.001 291.1
3 244 0.002 242.8 0.001 291.1
4 247 0.005 245.7 0.003 291.1
5 250 0.011 248.7 0.006 291.1
6 253 0.023 251.7 0.012 291.1
7 256 0.043 254.7 0.020 291.1
8 259 0.077 257.6 0.033 291.1
9 262 0.127 260.6 0.050 291.1
10 265 0.196 263.6 0.069 291.1
11 268 0.284 266.5 0.088 291.1
12 271 0.388 269.5 0.104 291.1
13 274 0.500 272.5 0.112 291.1
14 277 0.612 275.5 0.112 291.1
15 280 0.716 278.4 0.104 291.1
16 283 0.804 281.4 0.088 291.1
17 286 0.873 284.4 0.069 291.1
18 289 0.923 287.3 0.050 291.1
19 292 0.957 290.3 0.033 291.1
20 295 0.977 293.3 0.020 291.1
21 298 0.989 296.3 0.012 291.1
22 301 0.995 299.2 0.006 291.1
23 304 0.998 302.2 0.003 291.1
24 307 0.999 305.2 0.001 291.1
25 310 1.000 308.1 0.001 291.1

Reorder Point

Daily Demand 239.80117416829745 242.77260273972604 245.7440313111546 248.71545988258316 251.68688845401175 254.65831702544031 257.62974559686893 260.60117416829746 263.57260273972599 266.54403131115464 269.51545988258317 272.48688845401171 275.45831702544035 278.42974559686888 281.40117416829742 284.37260273972606 287.34403131115459 290.31545988258313 293.28688845401177 296.25831702544031 299.22974559686884 302.20117416829748 305.17260273972602 308.14403131115455 5.3315393825812546E-4 1.3008296190101156E-3 2.9266282946090764E-3 6.0714942049210413E-3 1.1614642468562761E-2 2.0488000798653712E-2 3.3325592763002253E-2 4.9985228963722372E-2 6.9134014680218625E-2 8.8171613944900834E-2 0.10369389799931472 0.1124515189020083 3 0.11245151890200833 0.10369389799931472 8.8171613944900806E-2 6.9134014680218625E-2 4.9985228963722372E-2 3.3325592763002287E-2 2.048800079865376E-2 1.1614642468562586E-2 6.0714942049211462E-3 2.9266282946091016E-3 1.3008296190101243E-3 5.3315393825814628E-4 ROP 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291. 07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 291.07908046002132 5.3315393825812546E-4 1.3008296190101156E-3 2.9266282946090764E-3 6.0714942049210413E-3 1.1614642468562761E-2 2.0488000798653712E-2 3.3325592763002253E-2 4.9985228963722372E-2 6.9134014680218625E-2 8.8171613944900834E-2 0.10369389799931472 0.11245151890200833 0.11245151890200833 0.10369389799931472 8.8171613944900806E-2 6.9134014680218625E-2 4.9985228963722372E-2 3.3325592763002287E-2 2.048800079865376E-2 1.1614642468562586E-2 6.0714942049211462E-3 2.9266282946091016E-3 1.3008296190101243E-3 5.3315393825814628E-4

Daliy Demand

Probability