Inventory reorder point tracker in Excel
Automated reorder flags comparing current stock on hand against safety stock + lead-time demand.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

How To Create A Complete Inventory Management System In Excel From Scratch + FREE DOWNLOAD
Excel For Freelancers
Open on YouTube (opens in a new tab)
Create an Automated Inventory Management System in Excel (FREE Template)
MyOnlineTrainingHub
Open on YouTube (opens in a new tab)
Stock In Out Balance Tracker Sheet in Excel (Inventory management)
Ashok Subedi
Open on YouTube (opens in a new tab)An inventory tracker becomes useful at exactly the point where it stops listing what you have and starts telling you what to do. Reorder points are the mechanism for that, and they are simple enough to build in an afternoon.
The part that takes longer to get right is keeping the stock figure true, which is a structural question rather than a formula one.
Two tables: items and movements
The common mistake is a single sheet where Current stock is typed over each
time it changes. Do that and you lose the history, you cannot explain a
discrepancy, and two people editing in the same week will overwrite each other.
tblItems — one row per SKU, the reference data that rarely changes:
| Column | Notes |
|---|---|
| SKU | Text format, to protect leading zeros |
| ItemName | |
| Location | Split a SKU across locations into separate rows if stock is held separately |
| Supplier | Dropdown from a Lists sheet |
| LeadTimeDays | Per supplier, and per item where they differ |
| MinOrderQty | What the supplier will actually ship |
| UnitCost | For valuing the reorder |
| Active | Y/N, so discontinued items stop appearing without losing history |
tblMoves — one row per stock movement, append-only:
| Column | Notes |
|---|---|
| Date | |
| SKU | |
| Qty | Positive for receipts, negative for issues |
| Type | Receipt, Sale, Transfer, Adjustment, Return |
| Reference | PO number, order number, stock-count reference |
| By | Who recorded it |
Current stock is then derived, never typed:
=SUMIFS(tblMoves[Qty],tblMoves[SKU],[@SKU])
That single change is what makes the tracker auditable. When somebody asks why stock shows 40 and the shelf has 37, you can show them every movement.
Calculate usage from the movements
Average usage is the input the reorder point depends on, and it should come from the data rather than from memory:
=ABS(SUMIFS(tblMoves[Qty],tblMoves[SKU],[@SKU],tblMoves[Type],"Sale",tblMoves[Date],">="&TODAY()-84))/12
That is sales over the last twelve weeks, divided by 12, to give average weekly
usage. Filtering to Sale matters — including receipts and transfers makes the
number meaningless.
Pick the window deliberately. Too short and one unusual week dominates; too long and it will not react to a genuine change in demand. Twelve weeks is a reasonable default for steady products; use less for anything fast-moving or seasonal.
Safety stock should reflect variability
The simplest version is a flat buffer — a typed number per item. That works and it is what most trackers do.
The better version accounts for how erratic demand is, because an item selling 10 a week every week needs far less buffer than one selling 0, 0, 30, 0, 20:
=NORM.S.INV(ServiceLevel)*STDEV.S(WeeklyUsageRange)*SQRT(LeadTimeWeeks)
With ServiceLevel as a named cell — 0.95 for a 95% service level, giving a
z-factor of about 1.65. If that is more statistics than the situation warrants,
a usable approximation is:
=[@AvgWeeklyUsage]*[@LeadTimeWeeks]*0.5
Either way, put the service level and any multiplier in named cells on an
Assumptions sheet rather than inside the formula, so the trade-off between
stockouts and tied-up cash is visible and adjustable. The reasoning is in
when to use named ranges.
The reorder point and the flag
=([@AvgWeeklyUsage]*([@LeadTimeDays]/7))+[@SafetyStock]
Reorder point is simply the stock you will consume while waiting for a delivery, plus the buffer.
The flag should account for stock already on order — otherwise an item sits in
Reorder for a fortnight and somebody orders it twice:
=IF([@Active]<>"Y","",IF([@CurrentStock]+[@OnOrder]<=[@ReorderPoint],"Reorder","OK"))
OnOrder comes from an open purchase order table, or from receipts expected but
not yet received.
Add days of cover, which is the number people reason with:
=IFERROR([@CurrentStock]/([@AvgWeeklyUsage]/7),"")
“Nine days of stock, twelve-day lead time” is a sentence an operations manager acts on immediately. “Stock 40, reorder point 46” takes a moment longer.
Finally, the order quantity, respecting the supplier’s minimum:
=IF([@Flag]="Reorder",MAX([@MinOrderQty],ROUNDUP([@ReorderPoint]+[@AvgWeeklyUsage]*4-[@CurrentStock]-[@OnOrder],0)),"")
Build the exception view
The tracker’s output is not the item list. It is the short list of things to do today.
Create a sheet filtered to Flag = "Reorder", sorted by supplier then by days of
cover ascending. Sorting by supplier matters because purchasing happens one
supplier at a time — a list ordered by urgency across twelve suppliers becomes
twelve separate conversations.
On Microsoft 365, that view can be a formula rather than a filter:
=SORT(FILTER(tblItems[[SKU]:[OrderQty]],tblItems[Flag]="Reorder"),{4,7},{1,1})
It then updates on its own, with nothing to re-filter.
Two numbers at the top of the sheet:
=COUNTIFS(tblItems[Flag],"Reorder")
=SUMIFS(tblItems[OrderValue],tblItems[Flag],"Reorder")
Count and value of what needs ordering. Drive the row highlighting from the
Flag column rather than repeating the thresholds in the formatting rules —
see
conditional formatting for KPI flags.
Reconcile to a physical count
Every derived stock figure drifts from reality, through breakage, theft, mis-picks, and movements nobody logged. The movement log does not prevent that; it makes it measurable.
Record counts as an Adjustment movement with the difference, never by
overwriting a stock figure, and keep the variance visible:
=SUMIFS(tblMoves[Qty],tblMoves[SKU],[@SKU],tblMoves[Type],"Adjustment")
A SKU with large or repeated adjustments is telling you something about a process, not about a spreadsheet.
Cycle count rather than counting everything at once — a handful of high-value or high-movement items each week, so the whole range gets covered over a quarter without ever stopping operations.
Know where this stops
This design handles a few thousand SKUs comfortably. It does not do batch or serial tracking, multi-stage work in progress, or FIFO/weighted-average costing — those need a real inventory system, and trying to bolt them on is where these workbooks become unmaintainable.
What it does well is give a small operation a reorder discipline it did not have, on data it already keeps. If you would rather start from a working file, the inventory tracker template already has the two-table structure and the exception view in place.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Track operational data so stock, reorder, or process decisions are based on current and consistent numbers. For this article, the goal is to practice: Turn a stock list into an action list — reorder point from usage and lead time, safety stock that reflects variability, days-of-cover, the movement log that keeps stock figures honest, and the exception view the team actually uses.
Practice workbook setup
Create a table with item IDs, locations, quantities, reorder points, suppliers, and recent movement dates.
Practice workflow
- Standardize item IDs and units of measure before calculating anything.
- Separate stock-on-hand, reorder logic, and supplier information.
- Flag exceptions such as below-reorder, missing supplier, or stale movement date.
- Review whether formulas still work when a new item is added.
Quality checks
- Each item has one consistent ID.
- Reorder logic is visible and easy to audit.
- Totals by category or location reconcile to item-level detail.
Common mistakes
- Using item names instead of stable item IDs.
- Mixing cases, packs, and units without conversion logic.
- Letting old discontinued items distort reorder summaries.
Next actions
- Add a small dashboard for stock risk and overdue replenishment.
- Create a weekly refresh checklist for the inventory file.
Formula focus: test IF(), SUMIFS(), IFERROR(), STDEV.S(), AVERAGE(), COUNTIFS() with normal rows, blank inputs, and at least one edge case.






