For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Inventory reorder point tracker in Excel

INV.D8 =IF(OnHand<=ReorderPt,"REORDER","OK")
Inventory reorder point tracker in Excel — Excel example preview

Automated reorder flags comparing current stock on hand against safety stock + lead-time demand.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

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)
Excel For Freelancers

Loading preview…

video / Advanced

Create an Automated Inventory Management System in Excel (FREE Template)

MyOnlineTrainingHub

Open on YouTube (opens in a new tab)
MyOnlineTrainingHub

Loading preview…

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.

=PRACTICE

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

  1. Standardize item IDs and units of measure before calculating anything.
  2. Separate stock-on-hand, reorder logic, and supplier information.
  3. Flag exceptions such as below-reorder, missing supplier, or stale movement date.
  4. 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.