How to track stock in a spreadsheet, and when to stop
Almost every one-person business tracks stock in a spreadsheet, and for a while that is exactly the right tool. This is how to build one that does the job properly — and the honest list of signs that it has stopped doing it.
The six columns worth having
Most stock spreadsheets have too many columns and too few of the right ones. Six will carry you a long way: 1. Name — what you call the thing when you are looking for it, not the supplier's catalogue name. 2. Barcode or code — even if you never scan it, it is how you tell two similar things apart. 3. Count — how many you have right now. 4. Unit cost — what one costs you, not what you sell it for. This is the column people leave out, and it is the one that turns a list into a decision. 5. Lead time — how many days between ordering and it being on your shelf. 6. Supplier — so the reorder list sorts itself into orders. Everything else (location, colour, season) is a nice-to-have that you will stop updating within a month.
The one formula that earns its place
A stock sheet becomes useful the moment it tells you something you did not already know. That thing is: what is about to run out. Add a column called something like 'Reorder at' and fill it with your average weekly usage multiplied by your lead time in weeks, plus a week of buffer. If you sell ten clasps a week and your supplier takes two weeks, that is 10 × (2 + 1) = 30. Then conditional-format the count column to go red when it drops to or below that number. That is it. No safety-stock statistics, no forecasting. The arithmetic is simple enough that you can sanity-check it in your head, which matters, because you will want to override it sometimes and you should be able to see what you are overriding.
Keeping the count true
The counts are the part that rots. A spreadsheet has no idea what left your shelf, so every number in it is only as fresh as the last time you sat down and typed. Two habits keep it honest. First, update on the way out, not at the end of the week: the moment a thing is sold or used, change the number. Second, keep a movement log — a second tab with date, item, and how many moved, one row per event. It feels like extra work and it is the only way the average weekly usage above can be a real number rather than a guess.
Four signs the spreadsheet has stopped working
The spreadsheet is the right tool right up until it is not. The signs are consistent: - You have stopped opening it on the day. If updating stock means 'when I get home and have the laptop out', the counts are always a few days stale, which is exactly when they are wrong. - You are counting the same shelf twice because you do not trust the number. - You have started keeping a paper list next to it for the things the sheet does not handle — a market day's sales, the order you meant to place. - You have a finished product made out of other things you also stock, and selling one means editing three rows by hand. This is where craft and maker businesses lose an hour a week and eventually just stop.
What to move to, and what not to
The obvious next step is full inventory software, and for most one-person businesses it is the wrong one. It arrives with purchase orders, cost-of-goods-sold reporting, multi-warehouse locations, user permissions and a monthly bill that assumes a team. Reviewers of craft-inventory tools say the same thing over and over: powerful, and more than they needed. What you actually want is the spreadsheet with two things added: a way to change a count in one action, wherever you are standing, and a reorder list computed from your own movement history. Keep the export. Any tool that cannot hand your stock back to you as a CSV has taken something from you that the spreadsheet never did.
Frequently asked questions
Is Google Sheets or Excel better for stock?
For one person, Sheets, because it is on your phone at the shelf and saves itself. Excel wins if your stock list is thousands of rows or you need heavy formulas — which, if you are a single trader, it probably is not.
How often should I do a full physical count?
Once a quarter is enough for most small stockholdings, plus a spot count of your five fastest-moving lines once a month. The fast lines are where drift happens; the slow ones sit still and stay accurate.
Should the sheet hold what I paid or what I sell it for?
What you paid. Reordering is a buying decision, and the number that tells you whether an order is affordable is the cost. Selling price belongs with your sales records, not your stock list.
Can I keep using the spreadsheet alongside an app?
Yes, and it is a good idea for the first month. Import the CSV, run both for a few weeks, and compare. If the app's counts match your sheet, stop updating the sheet. If they do not, you have learned something useful about how you actually count.
More guides
Other things we made
Also in English, made by the same people.
Also in English, made by the same people.
Also in English, made by the same people.
Also in English, made by the same people.