Written with AI assistance.
Running out of your best seller is expensive, and so is a shelf of things you ordered too much of. The fix isn't clever. It's knowing, each morning, what's low and how fast it's moving.
Here's a small reorder sheet built from a stock log: you record what comes in and what goes out, and the sheet works out what's on hand and what to reorder. It uses SUMIFS, COUNTIF, INDEX and MATCH, with no macros.
1. Two tabs: Items and Log
Items (from row 5): A SKU, B Item, C Reorder level (reorder when stock is at or below this), D Reorder qty, E Unit cost, F Supplier. Put an As of date in B2 (=TODAY() works).
Log (from row 5): A Date, B SKU, C Type (In, Sale or Return), D Qty. Give C a dropdown (Data validation → List → In,Sale,Return) so nobody types "sold" or "IN " by mistake. Every movement is one row, so you never overwrite a stock number.
2. Stock on hand
Items!G5:
=IF($A5="","",SUMIFS(Log!$D$5:$D$304,Log!$B$5:$B$304,A5,Log!$C$5:$C$304,"In")+SUMIFS(Log!$D$5:$D$304,Log!$B$5:$B$304,A5,Log!$C$5:$C$304,"Return")-SUMIFS(Log!$D$5:$D$304,Log!$B$5:$B$304,A5,Log!$C$5:$C$304,"Sale"))
Stock in plus returns, minus sales. The IF($A5="","",…) guard keeps empty rows blank; without it an empty row shows 0 on hand and gets flagged "Reorder". Use the same guard on every formula in columns G to K, and fill them down 100 rows. Items!H5, the stock value at cost:
=IF($A5="","",G5*E5)
3. Speed, cover and the reorder flag
I5, units sold in the last 30 days:
=IF($A5="","",SUMIFS(Log!$D$5:$D$304,Log!$B$5:$B$304,A5,Log!$C$5:$C$304,"Sale",Log!$A$5:$A$304,">="&($B$2-30)))
J5, days of cover: how many days the stock lasts at that pace:
=IF($A5="","",IF(I5=0,"",ROUND(G5/(I5/30),0)))
K5, the flag:
=IF($A5="","",IF(G5<=C5,"Reorder","OK"))
L5, what reordering would cost:
=IF(K5="Reorder",D5*E5,0)
Days of cover is the number I'd look at first. An item can be above its reorder level and still run out in a week if it sells fast. Treat it as a guide, not a forecast: it assumes the last 30 days repeat.
4. An automatic reorder list
In M5, number the items that need reordering:
=IF(K5="Reorder",COUNTIF($K$5:K5,"Reorder"),"")
Then in a small table, put 1, 2, 3 and 4 in O5:O8 and pull each one up (P5 for the item, Q5 for the supplier, R5 for the quantity):
=IFERROR(INDEX($B$5:$B$104,MATCH(O5,$M$5:$M$104,0)),"")
=IFERROR(INDEX($F$5:$F$104,MATCH(O5,$M$5:$M$104,0)),"")
=IFERROR(INDEX($D$5:$D$104,MATCH(O5,$M$5:$M$104,0)),"")
Fill the three formulas down as far as you want the list to go. It fills itself, and it's blank when nothing needs reordering.
5. Totals and the check
| Cell | Formula |
|---|---|
| T5: Items to reorder | =COUNTIF(K5:K104,"Reorder") |
| T6: Stock value | =SUM(H5:H104) |
| T7: Cost to reorder everything flagged | =SUM(L5:L104) |
| T8: Log rows with a SKU not on Items | =SUMPRODUCT((Log!$B$5:$B$304<>"")*(COUNTIF($A$5:$A$104,Log!$B$5:$B$304)=0)) |
| T9: Net stock movement in the log | =SUMIFS(Log!$D$5:$D$304,Log!$C$5:$C$304,"In")+SUMIFS(Log!$D$5:$D$304,Log!$C$5:$C$304,"Return")-SUMIFS(Log!$D$5:$D$304,Log!$C$5:$C$304,"Sale") |
| T10: Total on hand | =SUM(G5:G104) |
| T11: Check | =IF(AND(T8=0,T9=T10),"OK","CHECK") |
T8 is the important one. A sale logged against a mistyped or new SKU doesn't reduce any item's stock, so everything looks fine and nothing warns you. T9 and T10 should agree; when they don't, the difference is movement the item list never saw.
6. What the sample showed
Six made-up items and 17 log rows, as of 23 Oct 2026 (one sale was logged against a SKU I hadn't added, and one older sale is outside the 30-day window):
| Item | Level | On hand | Sold, last 30 days | Days of cover | Status |
|---|---|---|---|---|---|
| Canvas tote | 10 | 10 | 52 | 6 | Reorder |
| Ceramic mug | 15 | 12 | 36 | 10 | Reorder |
| Phone stand | 8 | 16 | 12 | 40 | OK |
| Notebook | 20 | 30 | 70 | 13 | OK |
| Sticker pack | 25 | 80 | 120 | 20 | OK |
| Candle | 6 | 15 | 9 | 50 | OK |
Two items to reorder, costing 140.00 + 105.60 = 245.60. Stock value 210.70. One log row had a SKU not on the list (T8 = 1), the net movement was 160 against 163 on hand, and the check said CHECK. Add the SKU to Items, or fix the typo in the log, and it goes back to OK.
The Notebook is the one to watch: 30 on hand is comfortably above its level of 20, but at the last 30 days' pace it's about 13 days of stock.
7. Test it before you trust it
Add rows you know the answer to, then confirm:
- a sale that takes an item exactly to its reorder level (should say Reorder, since the rule is "at or below")
- a return (stock should go up by that amount)
- a sale for a SKU that isn't on Items (T8 should say 1 and the check CHECK)
- an item with no sales in 30 days (days of cover should be blank, not an error)
Then delete them.
Written with AI tools. This is general spreadsheet guidance for Excel (.xlsx), not tax, legal or accounting advice.
Practical spreadsheet templates for small businesses, made with AI assistance.
Top comments (0)