What is the reorder point formula?
Reorder point = average usage per period × lead time in the same periods + safety stock. In most ERPs the reorder point is the min. When stock position falls to or below min, you order enough to bring it back up to max.
| Term | Formula |
|---|---|
| Lead time demand | d × L |
| Safety stock | Z × √(L × σd² + d² × σL²). See safety stock calculation |
| Min (reorder point) | d × L + safety stock |
| Order quantity | Economic order quantity, rounded to pack size and minimum order. See economic order quantity |
| Max | Min + order quantity |
| Stock position | On hand + on order − reserved for work orders |
| Quantity to order | Max − stock position, when stock position is at or below min |
d is average net usage per period, L is lead time, σd and σL are their standard deviations and Z is set by the service level (1.645 for 95%).
How do you set min and max for one stock line, step by step?
Work out net usage, real lead time and safety stock, then add the order quantity on top. Here is a full example with made-up numbers for a conveyor belt scraper blade, unit cost $145.
- Get 12 months of usage, net of returns. Gross issues were 180 and returns were 36, so net usage is 144 a year. Monthly net usage: 13, 12, 14, 12, 10, 14, 11, 12, 15, 11, 9, 11.
- Average and spread. d = 144 ÷ 12 = 12 a month. σd = 1.76 a month, so σd² = 3.09.
- Real lead time from PO history. PO date to receipt date averages 2.0 months, with a standard deviation of 0.5 months.
- Lead time demand. 12 × 2.0 = 24 units.
- Safety stock at 95%. 2.0 × 3.09 = 6.18. 12² × 0.5² = 144 × 0.25 = 36. √(6.18 + 36) = √42.18 = 6.49. 1.645 × 6.49 = 10.68, round up to 11.
- Min. 24 + 11 = 35.
- Order quantity. EOQ with order cost $120 and holding rate 25% (H = $36.25): √(2 × 144 × 120 ÷ 36.25) = √953.4 = 30.9, so 31.
- Max. 35 + 31 = 66.
- Check against the current setting and get the stores lead and maintenance planner to agree it before loading.
In use: if on hand is 30, nothing is on order and 4 are reserved for a work order, stock position is 30 + 0 − 4 = 26. That is at or below 35, so order 66 − 26 = 40.
Why should you net returns off usage before setting min max?
Because returned parts were never consumed. Counting them as usage pushes min and max up on every line where maintenance draws parts and brings some back.
Same scraper blade, calculated on gross issues instead of net:
| Input or result | Net of returns | Gross issues |
|---|---|---|
| Annual usage | 144 | 180 |
| Average a month | 12 | 15 |
| Standard deviation a month | 1.76 | 2.26 |
| Safety stock | 11 | 14 |
| Min | 35 | 44 |
| Order quantity | 31 | 35 |
| Max | 66 | 79 |
That is 13 extra units at max, or $1,885 on one low value line. Repeat it across every line with regular returns and the store is carrying stock for usage that never happened.
It also affects trust. On an international tier 1 mining operator's stock level work, one rule change (netting returns off usage) took agreement on disputed items from 73% to 98.4%. Most of the arguments about "the system's numbers are wrong" were really about returns.
How do you set min max for lumpy, critical and rotable spares?
The standard formula only suits steady usage. Class every line first, then use the method that fits the class.
| Line type | Method | Example result |
|---|---|---|
| Steady consumable or wear part | Reorder point formula plus EOQ | Scraper blade above: min 35, max 66 |
| Lumpy (rare issues, several at once) | Model issue events and issue sizes separately, then set order quantity to at least the largest single issue | Pump part, 6 issues in 24 months of 1 to 4 units, 6 month lead time: min 10, max 14 |
| Critical or insurance spare | Risk decision by maintenance, reviewed yearly | Min 1, max 1 |
| Rotable (repaired and reused) | Pool size covering failures during one repair turnaround | 0.5 failures a month, 3 month repair turnaround: 1.5 units out on average, 4 units in the pool for about 98% cover |
| Planned shutdown only | No min. Buy against the shutdown work order | Min 0, max 0 |
The lumpy example comes from the safety stock guide, where four methods give reorder points from 5 to 10 for the same part. The rotable pool uses a Poisson model: with 1.5 failures expected per repair turnaround, 3 units cover about 93% of outcomes and 4 cover about 98%. More in critical and rotable spares.
What mistakes do mining sites make with min max levels?
The common ones are bad inputs and settings nobody has touched for years.
- Counting transfers as usage. If a central warehouse issued 60 units, but 20 were transfers to a satellite store, its usage is 40. Count usage where the part goes to a job.
- Ignoring the review period. If the reorder check only runs monthly, stock has to last lead time plus review period. For the scraper blade, protecting 3 months instead of 2 takes min from 35 to 48.
- Comparing on hand, not stock position. Ignoring stock on order causes double ordering. Ignoring reserved stock causes stockouts on the next job.
- Using the quoted lead time. Use PO date to receipt date from your own history, including freight and receipting at site.
- Stale settings. Levels set for a fleet or a production rate that has since changed. Re-run at least every quarter.
- Insurance spares on the formula. Zero usage gives a min of zero, and the spare is not reordered after it is used.
- Units of measure. Min set in each, order placed in boxes.
- Blanket changes. "Cut every max by 20%" removes stock from lines that were already short. Change line by line.
How do you calculate min max in Excel and load it into your ERP?
Calculate levels for every line in one sheet, compare them to current ERP settings, review the biggest changes with the people who run the store, then load them back.
- Build net monthly usage per stock code (issues minus returns, transfers excluded) in columns B to M.
- Add lead time and its standard deviation from PO history, and safety stock, as in the safety stock guide. Say d is in N, L in P and safety stock in S.
- Min in T2:
=ROUNDUP(N2*P2+S2, 0). - Order quantity in U2:
=MAX(minimum order, CEILING.MATH(EOQ, pack size), 1)using your EOQ column. - Max in V2:
=T2+U2. - Bring in current ERP min in W and max in X. Value of change in Y2:
=(V2-X2)*unit cost. - Override with the class rules: insurance lines to 1 and 1, shutdown lines to 0 and 0, rotables to pool size.
- Sort by the size of the change and review the top lines with the stores lead and maintenance planners.
- Load approved changes. Most ERPs hold a reorder point plus either a maximum stock level or a fixed reorder quantity. If yours uses reorder quantity, load the order quantity, not the max.
How does Source Digital automate min max levels for mining spares?
Source Digital builds a stock level engine inside your own data platform. It calculates min, max and order quantity for every line on rules agreed with your stores team, re-runs monthly, and gives a person a change list to approve. The AI never changes a level on its own.
- Matches your experts: at an international tier 1 mining operator, the engine matched the site inventory expert's own spreadsheet 99.8% of the time. See the min max inventory engine case study.
- Rules your stores team agrees with: returns netted, transfers excluded, lines classed before any formula is applied.
- Every number links back to the usage, return and PO lines it came from.
- Read-only and in your environment: it runs in your Azure, Databricks or Snowflake and reads your ERP data without changing it.
- Questions in Teams: ask "why did the max on this line drop?" and get the answer with its source lines.
Getting started is a free 15 minute call with Tristan, or a quick enquiry. No obligation and no pitch. He asks what you run and what is costing you, then tells you straight whether there is something worth fixing. Work is charged at a consulting day rate. The price depends on your sites, systems and data, so you talk first, then Tristan sets out what he would build, how many days it takes and what it costs. See how the stock level engine works on the mining spares optimisation page.
