Inventory Management in Excel — Reorder Points, Safety Stock & Low-Stock Alerts (2026)

Most inventory spreadsheets are just a count of what you have. A real inventory system tells you what to do next: which products to reorder today, how much to buy, and how much cash is tied up on the shelf. In 50 minutes you’ll build a self-updating Excel inventory tracker with automatic on-hand stock, statistically sound reorder points and safety stock, red low-stock alerts, ABC analysis, and a stock-value dashboard — all with native formulas, no macros.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Inventory Management in Excel?

Inventory management is the discipline of keeping the right amount of stock — enough to meet demand without running out, but not so much that cash sits idle on shelves or products expire. In Excel, that means turning a simple count of what you hold into a decision system: it should tell you, automatically, which items have hit their reorder point, how many units to order, and what your stock is worth.

The two questions every inventory system must answer are when to order and how much to order. The first is governed by the reorder point — the stock level at which you place a new order, set so that a replenishment arrives just as you would otherwise run dry. The second is governed by the economic order quantity (EOQ), which balances the cost of placing orders against the cost of holding stock. Get these two numbers right for every SKU and the day-to-day chaos of “are we about to run out of this?” largely disappears.

Underpinning both is safety stock: a buffer that absorbs the two things that wreck plans — demand that spikes higher than average, and suppliers that deliver later than promised. Too little safety stock and you stock out; too much and you tie up working capital. Excel lets you calculate safety stock statistically, from the actual variability in your own sales history, rather than guessing.

Excel earns its place as the world’s default inventory tool because it is transparent and flexible. You can see exactly how every number is derived, adapt the model to your business, and share it with anyone — no licences, no lock-in. The cost is that Excel will not police your data: a typo in a SKU breaks a lookup, and a manual stock adjustment can silently drift from reality. This guide builds the structure — Tables, logs, and formula-driven status — that keeps an Excel inventory system trustworthy as it scales to thousands of SKUs.

Before You Start — The Data You Need

An inventory model is only as accurate as three inputs: demand, lead time, and cost. Gather these before you build.

1. Demand history

You need recent units-sold-per-day (or per-week) for each SKU, ideally several weeks so you can measure both the average and the variability. Average daily demand drives the reorder point; the standard deviation of daily demand drives safety stock. Without variability data you can still build the model, but your safety stock will be a guess.

2. Lead time

The number of days between placing an order and receiving it, per supplier. Be honest and use the realistic worst case, not the supplier’s optimistic quote — lead-time variability is one of the biggest causes of stockouts.

3. Costs

Unit cost (for valuation), cost per order (admin, shipping, receiving — for EOQ), and annual holding cost per unit (storage, insurance, capital — usually estimated as 15–30% of unit cost). These three feed the financial side of the model.

A clean SKU list beats a messy one

1.Step 1 — The Product Master Table

Start with one row per SKU. A robust column set is: SKU, Product, Category, Supplier, Unit Cost, Lead Time (days), Avg Daily Sales, and On Hand. Enter a handful of real products, then click inside the data and press Ctrl+T. Name the table Inventory in the Table Design tab.

As with any data project in Excel, the Table is non-negotiable. It auto-fills formulas down every column, lets you write readable references like Inventory[On Hand], and grows automatically when you add a SKU so your dashboard never misses new products. Everything downstream — reorder flags, valuation, ABC classes — references this one Table.

2.Step 2 — Track Stock In and Stock Out

Rather than manually editing an On-Hand number (which inevitably drifts from reality), let Excel derive it from movement logs. Create two simple Tables on separate sheets: StockIn (Date, SKU, Qty) for received goods, and StockOut (Date, SKU, Qty) for sales and usage.

Now make the On Hand column in your master table a formula instead of a typed number:

This sums everything received for that SKU and subtracts everything sold. Log a movement and the on-hand figure updates instantly — no manual adjustment, no running total to maintain, no drift. It also gives you a full audit trail: you can always see exactly why stock is where it is.

Derive average daily sales from the log

Instead of typing an average that goes stale, compute it from the StockOut log over the last 30 days:

Now your demand input refreshes itself as new sales are logged, keeping reorder points current automatically.

3.Step 3 — Calculate Safety Stock

Safety stock is the buffer that protects you when demand runs hot or a delivery runs late. The statistically grounded formula multiplies a service-level factor by demand variability, scaled over the lead time:

Break it down: NORM.S.INV(0.95) returns the Z-factor 1.65, meaning you accept a 5% chance of stocking out during a replenishment cycle (raise it to 0.99 for a 2.33 factor and near-zero stockouts). STDEV.P measures how bumpy your daily demand is. SQRT(lead time) scales that day-to-day variability across the whole period a new order takes to arrive. The longer and more variable your lead time, or the spikier your demand, the more buffer you need.

4.Step 4 — The Reorder Point

The reorder point (ROP) is the headline number of inventory management: the on-hand level that should trigger a new purchase order. It is the demand you expect during the lead time, plus your safety buffer:

Suppose you sell 8 units a day, your supplier takes 10 days, and your safety stock is 20. Your reorder point is 8 × 10 + 20 = 100. The moment on-hand stock drops to 100, you order — in the ten days before the delivery lands you expect to sell ~80, leaving the 20-unit buffer intact even if demand or the delivery surprises you.

Round up to a whole unit and you are done. Because every input is itself a live formula (average sales from the log, safety stock from variability), the reorder point recalculates as your business changes — no annual “let’s revisit the min levels” spreadsheet exercise.

5.Step 5 — Automated Low-Stock Alerts

A reorder point is useless if a human has to eyeball every row to spot breaches. Add a Status column that compares on-hand to the reorder point:

For a three-tier signal, nest a second condition for items that are getting close:

Then make it impossible to miss. Select the whole table, go to Home → Conditional Formatting → New Rule → Use a formula, and enter =$I2<=$J2 (On Hand ≤ Reorder Point) with a red fill so the entire row of any SKU needing a reorder lights up. Add an amber rule for the LOW band.

6.Step 6 — Economic Order Quantity (EOQ)

The reorder point answers when; EOQ answers how much. Ordering in tiny batches means constant admin and shipping costs; ordering huge batches ties up cash and warehouse space. EOQ is the order size that minimises the two combined:

Annual demand is average daily sales × 365. Cost per order is the fixed admin and shipping cost of placing one order. Holding cost is the annual cost of keeping one unit in stock (often estimated at 20% of unit cost). The square-root shape means EOQ is forgiving — being roughly right on the inputs gets you a near-optimal order size.

7.Step 7 — ABC Analysis

Not every SKU deserves equal attention. ABC analysis — an application of the Pareto principle — sorts your catalogue by how much value it represents so you can manage the vital few tightly and the trivial many cheaply.

Add an Annual Value column (=[@[Unit Cost]]*[@[Avg Daily Sales]]*365), then sort the table descending by it. Add a cumulative-percentage column:

Finally classify with a nested IF on the cumulative percentage:

Typically ~20% of SKUs (class A) account for ~80% of value — these get tight reorder discipline and frequent review. Class C items can run on simple, generous min levels. ABC tells you where to spend your scarce management attention.

8.Step 8 — Valuation and Dashboard

Total stock value — the cash sitting on your shelves — is one SUMPRODUCT:

Build a small dashboard with the figures that drive decisions: total stock value, count of SKUs to reorder (COUNTIF on Status), count of stockouts (=COUNTIF(Inventory[On Hand],0)), and value held by ABC class (SUMIFS). Add a slicer on Category or Supplier so a buyer can filter the whole view to one supplier before placing a consolidated order.

FIFO valuation for accounting

The SUMPRODUCT above uses a single unit cost. If purchase prices vary and you need accurate cost of goods sold, build a FIFO layer table: each purchase batch is a layer with its own cost, and sales consume the oldest layers first. It is more involved, but it is what most accounting standards expect for the value of remaining stock.

Try It: Reorder Point & Safety Stock Calculator

Drag the sliders to see exactly how demand, demand variability, lead time, and service level drive your safety stock and reorder point — the same maths your Excel formulas perform. Notice how raising the service level or the lead time pushes safety stock up sharply: that is the cost of certainty.

Advanced Inventory Tips

Days of stock remaining

Add a column =[@[On Hand]]/[@[Avg Daily Sales]] to see how many days each SKU will last at current demand. Sort ascending and the items closest to running out rise to the top — a sharper prioritiser than the binary reorder flag.

Inventory turnover

Turnover — how many times you sell through your average stock in a year — is a key health metric: =AnnualCOGS/AverageInventoryValue. Low turnover means cash trapped in slow stock; very high turnover may mean you are stocking out and losing sales.

Self-sorting reorder list (Microsoft 365)

On a separate sheet, =SORT(FILTER(Inventory[[SKU]:[On Hand]],Inventory[Status]="REORDER"),1) produces a live, always-current purchase list of exactly the SKUs that need ordering — nothing to refresh or filter by hand.

When to graduate from Excel

Excel handles a few thousand SKUs and a single location well. When you need barcode scanning, multi-warehouse sync, several people editing at once, or a live link to your point-of-sale, keep your data in Excel but connect it to DataHub Pro for a shareable, always-current stock dashboard — you keep your formulas and stop emailing screenshots.

Common Errors & Fixes

On-hand stock is wrong

Almost always a SKU mismatch between the master table and a log — a trailing space, different capitalisation, or a typo. Use Data Validation to pick SKUs from a list in the logs, and =TRIM() to clean existing values.

Safety stock returns #DIV/0! or 0

Your sales range has no variability (every day identical) or is empty. Check the range reference and ensure it points to real daily figures, not a single cell.

Reorder point seems far too high

Usually a units mismatch — weekly sales multiplied by a lead time in days, or vice versa. Keep every rate and duration in the same unit (per day, days) throughout the model.

EOQ looks unrealistic

Check that holding cost is annual per unit and order cost is per order, not per unit. Mixing per-unit and per-order costs is the classic EOQ mistake.