Most small shops run out of a fast seller at the worst moment, not because nobody noticed, but because nobody had a number that said "order today". A reorder level gives you that number. This guide builds a simple stock sheet in Excel that works out current stock from your in/out entries, compares it with a reorder level, and flags what to buy.
Two tables, not one
Keep a Products table (one row per item) and a Movements table (one row per stock-in or stock-out). Never overwrite a "current stock" number by hand; let Excel work it out from movements. That way every change has a date and a reason.
Movements sheet (Mov), from row 2:
| Col | Field |
|---|---|
| A | Date |
| B | Item code |
| C | Type (In / Out) |
| D | Quantity |
| E | Note (supplier, bill no.) |
Put a dropdown on column C: Data > Data Validation > List, source In,Out. Typos like "out " or "IN" are the most common reason stock totals drift.
Products sheet (Items), from row 2:
| Col | Field |
|---|---|
| A | Item code |
| B | Item name |
| C | Opening stock |
| D | Avg daily sales |
| E | Supplier lead time (days) |
| F | Safety stock |
| G | Current stock |
| H | Reorder level |
| I | Status |
| J | Suggested order qty |
Current stock
In G2:
=IF($A2="","",C2+SUMIFS(Mov!$D:$D,Mov!$B:$B,$A2,Mov!$C:$C,"In")-SUMIFS(Mov!$D:$D,Mov!$B:$B,$A2,Mov!$C:$C,"Out"))
Opening stock plus everything in, minus everything out. Fill it down a few hundred rows; the IF($A2="","",…) wrapper keeps empty rows blank.
Average daily sales
You can type this by hand, but it's better to work it out from the last 30 days of "Out" movements. In D2:
=IF($A2="","",SUMIFS(Mov!$D:$D,Mov!$B:$B,$A2,Mov!$C:$C,"Out",Mov!$A:$A,">="&TODAY()-30)/30)
For seasonal items, change 30 to 14 so the number reacts faster.
The reorder level
The classic formula is:
Reorder level = average daily sales × lead time + safety stock
In H2:
=IF($A2="","",ROUNDUP(D2*E2+F2,0))
If you sell 4 units a day and the supplier takes 5 days, you'll sell about 20 units while waiting. Safety stock (say 8) covers a slow delivery or a busy week. So you reorder when stock falls to 28.
How to pick safety stock without statistics: start with two or three days of sales. If you still run out, raise it. If stock piles up, lower it.
The status flag
In I2:
=IF($A2="","",IF(G2<=0,"OUT",IF(G2<=H2,"Reorder","OK")))
Then colour it: select I2:I500, Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, "OUT" in red, "Reorder" in amber.
Suggested order quantity
A simple rule: order enough to get back to a target, for example 15 days of sales plus safety stock. In J2:
=IF(OR($A2="",I2="OK"),"",MAX(0,ROUNDUP(D2*15+F2-G2,0)))
If your supplier sells in packs of 12, wrap it: =CEILING(…,12).
A one-glance reorder list
On a new sheet, if you have Excel 365 or 2021:
=FILTER(Items!A2:J500,Items!I2:I500<>"OK","Nothing to order")
In older Excel, use Data > Filter on the Items table and untick "OK" in the Status column. Either way you get a shopping list you can send to the supplier.
Stock value
Add a Cost price column to Items (K) and a stock value column:
=IF($A2="","",G2*K2)
=SUM(L2:L500) at the top tells you how much money is sitting on your shelves. Sorting by it often shows that a few slow items tie up more cash than the fast sellers.
Common mistakes
- Editing current stock by hand. Add an adjustment movement instead ("Out", note "damaged" or "count correction").
- Item codes typed differently in the two sheets. Use a dropdown on Mov!B pointing at the Items code list.
- Lead time in weeks, sales per day. Keep both in days.
- Forgetting returns. A customer return is an "In" with a note.
A weekly routine
Once a week: do a quick physical count of your top 10 items, post any differences as adjustments, then open the reorder list. Ten minutes, and you stop finding out about stock-outs from customers.
This article was drafted with AI tools. It's general spreadsheet guidance, not accounting, tax or legal advice.
Top comments (0)