Basic Stock Control Spreadsheet: How to Build One That Doesn't Fall Apart
A basic stock control spreadsheet can run a small operation well if you build it right — the correct columns, a reorder point that does the thinking for you, and a movement log so the count nets itself. Here's the structure that holds up, then the honest signs you've outgrown it and what graduating to a right-sized system looks like.
A basic stock control spreadsheet works if you stop treating it as a list and start treating it as a small system: one tab for items, one tab for movements, a reorder-point column that flags what’s running low before you run out, and a formula that nets your on-hand count from what came in and what went out. That’s the short answer, and for a single-location operation with a few hundred lines it can genuinely hold. The failure mode isn’t the spreadsheet itself. It’s a flat grid of item names and a “Qty” column someone overtypes by hand, which goes stale by Friday and quietly starts lying.
This post gives you the actual structure first, so you can build one today that doesn’t fall apart: the columns, the reorder formula, the movement log. Then the honest part: the four or five concrete signs you’ve outgrown it, and what a right-sized system looks like when you have. No pitch until you’ve had the useful bit.
Key Takeaways
- Split it into two tabs: an item list (one row per SKU) and a movement log (one row per receipt, pick, or adjustment). The count should be calculated from movements, never typed over.
- The reorder point is the one column that earns its keep:
(average daily usage × lead time in days) + safety stock. It turns “I think we’re low” into a flag that fires on its own. - A flat single-tab grid with a hand-edited Qty column is the version that fails — it can’t survive two editors, multiple locations, or any audit of what changed.
- You’ve outgrown the spreadsheet when you hit any of: multiple locations, overselling against open orders, two people editing at once, or no record of who changed what.
- Graduating isn’t a £100k WMS. It’s the same logic — items, movements, reorder points — moved somewhere that captures movements live and stops two people touching the same number.
1The Columns a Stock Control Spreadsheet Actually Needs
Most spreadsheets fail because they track the wrong things: a description and a quantity, and then people wonder why the number can’t be trusted. A stock-control spreadsheet that holds up has two tabs, not one.
Items tab (one row per SKU, the things that don’t change often):
- SKU / item code: a short unique code, not just a name. “Blue widget” and “widget, blue” will become two rows otherwise.
- Description: plain English so the floor recognises it.
- Location / bin: even if you only have one site now, a column you can fill in later beats restructuring everything when you open a second.
- Unit cost: what it costs you, so the sheet can value stock.
- Reorder point: the trigger level (formula in section 2).
- Reorder quantity: how much to buy when it fires.
- Supplier and lead time (days): who you buy it from and how long they take.
Movements tab: one row every time stock moves, with date, SKU, quantity in (positive) or out (negative), reason (received / sold / adjustment / wastage), and who logged it. This second tab is the part almost everyone skips, and the part that makes the whole thing trustworthy. We cover the deeper version in why stock control records matter; the short version is that a count with no record of how it got there is a guess in a nicer font.
2The One Formula That Makes It Useful: the Reorder Point
A list of what you have is hindsight. The thing that makes a spreadsheet earn its place is telling you what to do before it’s too late — and that’s the reorder point.
The standard formula, and it’s genuinely all you need to start:
Reorder point = (average daily usage × lead time in days) + safety stock
Say you sell about 12 units a day, your supplier takes 7 days, and you want a 20-unit buffer for a bad week. Your reorder point is (12 × 7) + 20 = 104. The moment on-hand drops to 104, you reorder, and you’ll still have roughly the buffer left when the delivery lands.
Then add one column that does the watching for you. In the items tab, a status cell like =IF(on_hand <= reorder_point, "REORDER", "OK"), with conditional formatting to turn it red. Now the sheet flags the bestseller before it runs dry instead of after a customer catches the gap. You don’t read the whole grid every morning; you scan for red. That single behaviour, surfacing the leak early rather than discovering it at the point of sale, is most of what a proper inventory dashboard does too. The spreadsheet is just the manual-effort version of the same idea.
Don’t over-engineer the inputs. Average daily usage off the last 30–60 days is fine, and a roughly-right reorder point that fires beats a perfect one you never calculated.
3Make the Count Net Itself From Movements
Here’s the rule that separates a spreadsheet that survives from one that rots: never type over the on-hand number.
If a person edits the quantity cell by hand every time stock moves, you have no idea what it was before, who changed it, or why. The first time two people remember the count differently, you’ve got nothing to settle it. Instead, calculate on-hand from the movement log:
On-hand for a SKU = SUMIFS(movements quantity, movements SKU, this SKU)
Every receipt is a positive row, every sale or pick a negative one, every stocktake correction an “adjustment” row with a reason. The on-hand figure is now a result, not an input. It can’t drift, because there’s nothing to overtype. When it’s wrong, you scroll the movement log and find the row that’s wrong. That’s an audit trail, and it’s the difference between “the number is off, sometimes wildly so” and “the number is off because Tuesday’s delivery was logged twice.”
This is more discipline than a flat grid. It’s also the only version that doesn’t quietly betray you at the worst moment. If even the movement log starts feeling heavy to keep by hand, that’s the early signal from the next section.
4The Signs You’ve Outgrown It (Be Honest)
A well-built spreadsheet is the right tool for a real stage of business, and a stage you grow out of. The cost of staying too long is paid in cancelled orders, not licence fees. If one or more of these is your daily reality, the spreadsheet has stopped being the cheap option:
- You hold stock in more than one location. The moment the same SKU lives in two places, a single Qty column can’t tell you where it is. You start keeping a tab per site, the tabs disagree, and you’re back to reconciling sheets instead of running the warehouse.
- You oversell against open orders. The sheet shows 40 in stock, but 35 are already promised to confirmed orders nobody’s picked yet. A spreadsheet can show on-hand; it can’t easily show available to promise (on-hand minus committed) without becoming a fragile mess of cross-tab formulas. So you sell the same units twice, then cancel and apologise.
- Two people need to edit it at once. Shared editing is where stock spreadsheets go to die. One person’s change overwrites another’s, a paste lands in the wrong row, and the formulas that net your count break silently. If the count depends on nobody making a mistake while editing, it’s already unreliable.
- No record of who changed what. When the count’s wrong and you can’t reconstruct how, you’ve lost the audit trail that made the spreadsheet trustworthy. Manual logs decay first under pressure, exactly when you need them.
- You’re scanning or want to. Once volume justifies a scanner, keying movements by hand is both slow and error-prone. That’s usually the tipping point; see whether you need a barcode inventory system for where that line sits.
You don’t need all five. Multi-location plus overselling is plenty. One person, one site, a few hundred lines, no scanner? Stay on the spreadsheet — building anything more is wasted money.
5What Graduating Actually Looks Like
The trap is believing the only step up from a spreadsheet is a six-figure enterprise WMS. That false choice keeps people limping along on a broken sheet for years, because the alternative looks absurd for their size. There’s a middle, and it’s smaller than you think.
Graduating doesn’t mean throwing away how you think about stock: items, movements, reorder points, audit trail. It means moving that exact logic somewhere that does the parts a spreadsheet structurally can’t. Capture movements live as they happen, hold a count two people can use at once without clobbering each other, show available-to-promise so you stop overselling, and keep a record of every change automatically. Same model you already built, minus the fragility.
For a stock-holding SME that’s a fixed build in the low-thousands-to-tens-of-thousands range, sized to the warehouse you run now, connected to the accounting and order tools you already use, and yours to own outright. Not a per-seat subscription that charges you more for growing, and not an ERP rollout that takes a year. If your stock also drives sales orders, the place it tends to break first is where stock meets fulfilment: the wholesale order management seam, where overselling does the most damage.
Stock Control Spreadsheet vs Right-Sized System
| Well-built spreadsheet | Right-sized built system | |
|---|---|---|
| Best for | One site, low SKU count, one editor | Multi-location, open orders, a team |
| Cost | Free, plus your time | Fixed build (£3k–£25k range), then maintained |
| On-hand accuracy | Good if you keep the movement log by hand | Movements captured live; count nets itself |
| Multiple editors | Breaks — overwrites, broken formulas | Built for concurrent use |
| Overselling control | Hard; available-to-promise is fragile | Available-to-promise built in |
| Audit trail | Only as good as your manual logging | Every change recorded automatically |
| When to choose it | You’re below the section-4 signs | You’ve hit one or more of them |
FAQ
How do I make a basic stock control spreadsheet?
Use two tabs. An items tab with one row per SKU (code, description, location, unit cost, reorder point, supplier, lead time) and a movements tab with one row every time stock moves (date, SKU, quantity in or out, reason, who). Calculate on-hand with SUMIFS over the movements rather than typing it by hand, and add a reorder-point column that flags low items. That holds up far longer than a flat grid with a Qty column you edit manually.
What’s the reorder point formula for a spreadsheet?
(average daily usage × lead time in days) + safety stock. Use average daily usage over the last 30–60 days, lead time from your supplier, and a buffer for a bad week. When on-hand drops to that number, reorder. A simple IF plus conditional formatting can colour the cell red automatically so you don’t have to read the whole sheet.
When should I stop using a spreadsheet for stock control?
When you hit any of: stock in more than one location, overselling against open orders, more than one person editing at once, or no reliable record of who changed what. One site, one editor, a few hundred lines, no scanning? Stay on the spreadsheet. Past those signs, it costs you more in cancelled orders and reconciliation time than a right-sized system would.
Is a stock control spreadsheet better than software?
For a small, single-location, single-editor operation, a well-built spreadsheet is often the right call and you shouldn’t pay to replace it. It stops being better the moment you need live movement capture, concurrent editing, available-to-promise, or an automatic audit trail, which a spreadsheet can’t do safely no matter how cleverly it’s built.
How OpsMavix Can Help
If your spreadsheet is still coping, keep it. Genuinely. The two-tab structure, the reorder-point formula and the movement log above will carry a small operation a long way, and we’d rather you ran a good spreadsheet than bought something you don’t need yet.
When you’ve crossed the signs in section 4, OpsMavix builds the right-sized step up: a custom inventory automation system that keeps the way you already think about stock and adds the live movement capture, available-to-promise and automatic history a spreadsheet can’t. Sized to your warehouse, connected to your existing tools, owned outright.
The honest first move is finding where your stock actually leaks before building anything. Book a Free Operations Leak Audit and we’ll map where your counts, reorder points and orders break down, what it’s costing you, and whether a sharper spreadsheet or a built system is the real fit.