Stock control is knowing what you have, what you have used, and when to reorder — before a bestseller sells out or cash sits in slow-moving units. A spreadsheet handles this well when SKU counts and locations stay manageable.
This guide explains the fields, simple reorder logic, and habits that keep numbers trustworthy.
Stock control fields explained
| Field | Role |
|---|---|
| Opening stock | Quantity at the start of a period |
| Stock received | Units added from purchases or production |
| Stock sold / used | Units leaving available stock |
| Current quantity | Opening + received − sold/used |
| Reorder level | Minimum quantity before triggering a reorder |
| Reorder quantity | Typical order size from supplier |
| Supplier | Who fulfills the reorder |
| Lead time | Days from order to delivery |
| Unit cost | Purchase cost per unit |
| Stock value | Current quantity × unit cost (estimate) |
Simple reorder logic
Reorder when current quantity is at or below reorder level. Set reorder level to cover expected sales during lead time plus a small buffer.
Example: you sell four units per week and supplier lead time is two weeks. Minimum stock before reorder might be eight units, plus buffer — say ten. Reorder quantity might be four weeks of stock if storage and cash allow.
Recording movements accurately
Log stock received when goods arrive, not when the invoice is paid — unless your process ties them intentionally. Log stock out when items ship or are consumed in production.
Adjustments (damage, samples, shrinkage) deserve their own notes rather than silent edits to totals. Silent edits destroy trust in the sheet during stock counts.
Monthly stock check routine
- Count physical stock for each SKU
- Compare count to spreadsheet current quantity
- Investigate variances above your tolerance
- Update reorder levels if sales velocity changed
- Note supplier lead time changes in the supplier column
Limits of spreadsheet stock control
Spreadsheets do not scan barcodes at a warehouse door or sync automatically with every marketplace listing. They excel at decision support for owners who touch stock regularly.
Stock value on the sheet supports planning — not formal valuation for accounts. Discuss inventory accounting with a professional when required.
Example: a Norfolk farm shop with chilled and dry goods
A farm shop selling own produce and bought-in chutneys faces different stock rules in one building. Dry goods — jars, honey, tote bags — tolerate monthly counts. Chilled lines need shorter cycles and a “Use by” column for anything with a printed date.
Their spreadsheet uses one tab for dry SKU rows with standard reorder logic, and a second tab for short-life items sorted by expiry. Each morning someone scans the expiry tab for products within three days and marks them for promotion before write-off. That habit reduced waste more than any formula.
Peak summer tourism doubles footfall for eight weeks. They duplicate June’s weekly sales average into a “Seasonality note” column and temporarily raise reorder levels in April — visible on the sheet so the winter team understands why cash sits in extra chutney stock in May.
Simple ABC stock classification for small shops
| Class | Typical share of SKUs | Control approach | Count frequency |
|---|---|---|---|
| A — high value or fast movers | Top 20% driving most revenue | Tight reorder levels; weekly spot checks | Weekly |
| B — moderate sellers | Middle 30–40% | Standard reorder rules; monthly full count | Monthly |
| C — slow or low-value lines | Long tail of occasional sales | Minimal stock; review for discontinuation | Quarterly |
Dead stock, seasonality and cash tied up in shelves
Stock control is not only preventing stock-outs — it is freeing cash from lines that stopped moving. Add a “Last sale date” column updated from your sales log monthly. SKUs with no sale in ninety days during normal trading deserve a markdown plan, not another reorder.
Seasonal businesses should tag SKUs as “Seasonal = summer / winter / year-round” so reorder logic does not treat Christmas crackers like everyday milk. After the season ends, transfer unsold seasonal rows to an Archive tab with quantity written off or stored for next year — do not leave phantom available stock in the active tab.
Multi-location sellers — shed plus shop plus market crate — need a location column or separate tabs per location. Otherwise you reorder because the sheet shows zero while half the stock sits in the van.
Returns, exchanges and supplier minimum orders
Customer returns increase stock in — log them with reason codes (faulty, changed mind, wrong size) so quality issues surface in notes after repeated entries for the same SKU. Exchanges are effectively a return plus a sale; two movements beat editing net quantity silently.
Supplier minimum order values distort reorder logic. You might need six units but the wholesaler ships in cases of twelve. A “Order multiple” column prevents suggesting orders the supplier will reject, and helps cash planning when you must buy a full case to restock one fast mover.
If you use dropship for some lines, keep those SKUs in the sheet with a “Fulfilment = dropship” flag and zero local quantity — otherwise you reorder stock you never physically hold.
Photograph shelf labels during stock counts if SKU codes are easy to misread — paste image links in notes temporarily until barcodes or clearer labels arrive.
Quarterly stock control review
- Reclassify A/B/C items based on last three months’ sales velocity
- Adjust reorder levels for supplier lead-time changes
- Identify dead stock and plan clearance or donation with notes
- Reconcile total stock value on sheet to a rough physical walk-through
- Update unit costs if supplier prices shifted materially
- Archive discontinued SKUs with final quantity and reason