Inventory Management in Excel: the 3 Formulas That Matter (2026)

Key points: For the first hundred SKUs, Excel is a perfectly good inventory system — provided you work with formulas instead of gut feeling. Three calculations make the difference: the reorder point (when to order), the safety stock (how much buffer) and the order advice (how much to order). All three are worked out below, ready to copy. Just as important: we honestly mark the point where Excel starts costing you money — and how much, you can check with our free forecast calculator.

Formula 1: the reorder point

Good inventory management comes down to one question: when do I order, and how much? The reorder point answers the first half — the stock level at which you must place an order:

Reorder point = (sales per week ÷ 7 × lead time in days) + safety stock

If you sell 21 units a week and your supplier delivers in 14 days, you sell 42 during the lead time. Once stock drops below 42 + safety stock, you will run out before the replenishment arrives.

In Excel, with lead time in column E, stock in F, weekly sales in G and safety stock in H:

=ROUNDUP(G2/7*E2,0)+H2

Add a status column that flags it:

=IF(F2<=I2,"ORDER","OK")

Formula 2: the safety stock

The buffer for setbacks — a busy week, a late delivery:

Safety stock = safety factor × sales per week × lead time ÷ 7

The factor is your dial: 0.5 (half a lead time of extra buffer) is a normal starting point. Reliable supplier and stable sales? Set it to 0.3 and free up capital. Volatile demand or one critical bestseller? 1.0. Put the factor in a single cell on a settings tab so you can adjust it centrally:

=ROUNDUP(Settings!$B$4*G2*E2/7,0)

Formula 3: the order advice

How much to order when the time comes — not a reflexive "one extra box", but exactly enough for the coverage you want to hold:

Advice = (target coverage in weeks × sales per week) + lead-time demand − current stock

With a target coverage of, say, 4 weeks on the settings tab:

=MAX(0,ROUNDUP(Settings!$B$3*G2+G2/7*E2-F2,0))

Five rules to keep it working

1. Update stock levels at least weekly — a reorder point on stale figures is false certainty.
2. Use weekly sales from your POS or webshop export, not an estimate. Take the average of the last 8-12 weeks.
3. Keep lead times current per supplier. One outdated lead time = one empty shelf.
4. One owner of the file. Once three people work in copies simultaneously, no version is true anymore.
5. Review seasonal items separately. The summer average does not predict December demand — adjust the sales column before the season starts (or read how demand planning solves this structurally).

The hidden assumption: your sales column is a forecast

Notice what sits inside all three formulas: "sales per week". That number is secretly a prediction — you assume the coming weeks will look like the past ones. For stable items that holds up reasonably. For anything with seasonality, promotions or trend it is systematically wrong, and that error translates into euros: too much buffer on declining items (locked capital), too little on risers (lost sales).

How much that adds up to at your scale takes ten seconds with our free calculator: what is a better forecast worth? — freed working capital, lower holding costs and recovered lost sales, conservatively calculated.

The honest limit: when Excel costs you money

We earn our living with custom work, so judge this paragraph accordingly — but the limit is remarkably consistent in practice. Excel stops working once:

• You carry more than 100-200 active SKUs: updating becomes a day job, so it stops happening.
• You sell through multiple channels (own webshop, marketplaces, B2B): the sales column structurally lags reality.
• Seasonality and promotions drive your demand: a 12-week average misses every peak and trough — exactly the moments when inventory is worth money.
• Several people work with it: version control becomes the biggest source of errors.

Recognise two or more of these? Then read our overview of inventory management software: comparing packages and the tipping point to custom. Its core: registration is fine everywhere — the difference is the quality of the ordering decision, which improves the more it runs on your own sales data instead of an average.

Want to know how much is locked up in overstock or leaking away through lost sales? We make it visible with a short data scan on your sales and inventory data — book an introduction.