Most small shops start somewhere like this: a notebook under the counter with tally marks in it, and one day somebody moves the whole thing into a spreadsheet. That is not a bad path. A well-built Excel sheet can serve a small shop, bakery or greengrocer for years — if you know what it is for and what it isn't.
The problem is usually not Excel but the structure. In many shops the spreadsheet consists of a single column: "stock", which somebody overwrites now and then. From that you can never tell why it became twelve instead of fifteen — sold, thrown away, taken by someone, or simply typed in wrong. A good spreadsheet doesn't store the number but what happened to it.
This article has three parts: a ready template with columns and formulas, a weekly routine that keeps it alive, and an honest list of where it tends to break. If at the end you find that your shop still fits in a spreadsheet, feel free to stay with it.
When Excel is a good choice
Let's start with a fair statement: Excel is not a beginner's tool that has to be replaced as soon as possible. It is free or very cheap, everybody knows it to some degree, and it does exactly what you put into it. In a small shop that is often enough — the only question is whether your shop is that kind of shop.
- One person keeps it. If the same person always fills in the spreadsheet, there is no version mix-up and no "who changed this" question.
- It covers a few hundred products. At around three hundred rows a spreadsheet is still manageable; above a thousand, searching and scrolling become work in themselves.
- A weekly rhythm is enough. If you sit down once a week and enter the week's movements, the spreadsheet is exactly as fresh as the data you need for ordering.
- There are few outgoing movements other than sales. Where waste and own consumption are rare, less gets forgotten.
- Goods in is simple. A handful of suppliers, one or two deliveries a week — that can be entered from the delivery note in a few minutes.
- There is only one shop. Two shops mean two spreadsheets, and comparing them is a job of its own.
If most of these are true for you, Excel is a good choice, and the next part of the article is for you. If several people already record and waste arrives on a scrap of paper at the end of the shift, it is worth starting with the "Where it breaks" section.
The template: eleven columns, three formulas
The structure below describes a "Stock" sheet in which each row is one product and covers one period — typically a week or a month, the time between two counts. It runs from column A to column K; the formulas refer to the second row, and you drag them down from there.
| Column | What goes in it? | Formula or example |
|---|---|---|
| A — Product | The product's name as you'd look for it on the shelf | Brown loaf |
| B — Code | A short, unique identifier that never changes | BRD01 |
| C — Unit | What you count in: piece, kg, pack, crate | pc |
| D — Opening stock | The counted quantity at the start of the period | Typed number, e.g. 20 |
| E — Goods in | What arrived during the period, from the delivery notes | Typed number or total from Movements |
| F — Sales | What left as sold | Typed number or total from Movements |
| G — Waste | Expired, broken, spoiled, unsellable goods | Typed number or total from Movements |
| H — Own consumption | What staff or the owner took for themselves | Typed number or total from Movements |
| I — Calculated stock | What should be on the shelf on paper | =D2+E2-F2-G2-H2 |
| J — Actual (counted) | What you actually counted at the end of the period | Typed number |
| K — Variance | The difference between counted and calculated; minus = shortage | =J2-I2 |
It is worth adding two helper columns at the end: L — Purchase price (per unit) and M — Variance value, with the formula =K2*L2. That is the third formula, and perhaps the most useful: a unit count on its own is misleading, because three missing packs of chewing gum and three missing bottles of oil are not the same thing. If you sort the table by column M, the top of the list shows where it is worth investigating.
Two sheets: why a movement log beats an overwritten number
You can fill in the table above by hand: at the end of the week you type in how much came in and how much went out. That works, but it has a weakness — behind the total, the when, what and who disappear. If the waste column says fourteen, you don't know whether that was one bad day or two weeks of slow leakage. That is why it is worth adding a second sheet, called "Movements", which you never correct or delete from, only add to.
| Date | Product (code) | Type | Quantity | Recorded by |
|---|---|---|---|---|
| Monday | BRD01 | Goods in | +24 | Anna |
| Monday | BRD01 | Sale | −18 | till |
| Tuesday | MLK02 | Waste (expired) | −3 | Peter |
| Tuesday | COF05 | Own consumption | −1 | Anna |
| Wednesday | MLK02 | Goods in | +30 | Peter |
Signed quantities have one big advantage: on the Stock sheet a single function adds up a product's entire change. In Excel that is SUMIF: =D2+SUMIF(Movements!B:B,B2,Movements!D:D) — in other words, it adds to the opening stock every row of the Movements sheet whose code matches the code in that row. If you also want the breakdown by type (how much was waste, how much own consumption), a PivotTable is the most convenient, refreshed with one click.
The movement log beats the overwritten number because you can trace it back. If the variance is negative at the end of the month, you can see which week it started in, for which products, and on whose shift. The overwritten number only says how much there is now. The log also tells you how it got there.
Setting it up in one afternoon
You don't need a weekend. A quieter afternoon, a laptop and the shelves are enough — most of the time goes not on the spreadsheet but on listing the products and the first count.
- 11. List the products and give each one a codeWalk along the shelves and fill in columns A–C. The code should be short and permanent (BRD01, MLK02) — the name may change, the code never. This is the step that is hardest to fix later, so don't rush it.
- 22. Decide on the unitsDo you count in pieces, kilos or packs? What matters is that each product has one unit and that you enter goods in using it too. If the supplier delivers by the crate but you sell by the piece, convert to pieces at goods in.
- 33. Count the shelf — that is your opening stockAfter closing or before opening, when nothing is moving. The counted quantity goes into column D. If there are too many products, start with the twenty or thirty most important and add the rest over the following weeks.
- 44. Create the Movements sheetFive columns: date, code, type, quantity, who. For the type use a drop-down list (Data Validation), so that "waste", "Waste" and "binned" don't become three separate categories.
- 55. Enter the formulas and test them with three productsAdd a few made-up movements and check that the calculated stock matches what you get by hand. If it does, delete the test rows and drag the formulas down to every product.
- 66. Decide who records what, and whenThis isn't a spreadsheet question, but everything depends on it. Who enters the delivery? Who enters waste? Where does the note go if the laptop isn't to hand? If there is no answer on day one, the columns will be empty by week three.
The weekly routine that keeps it alive
The spreadsheet isn't accurate because the formula is good, but because data goes into it regularly. This routine takes roughly twenty minutes a week if the movements are collected along the way — and an hour if everything has to be gathered up at the end of the week.
- 1Enter the week's delivery notes as goods in. Keep the delivery notes in a tray next to the laptop until they are in.
- 2Enter the sales. If your till can give a per-product summary, copy it from there; if not, this is the spreadsheet's weakest point.
- 3Collect waste and own consumption from the week's notes or the notebook. If the list is empty, ask: was there really none, or did nobody write it down?
- 4Count five to ten products — not all of them. Different ones each week, but expensive and fast-selling products should come round more often.
- 5Look at the variance for the counted products. If a product is short on two counts in a row, deal with that, not with a one-off swing.
- 6Save the file in one place, under one name. Keep the date inside the file, not in the file name.
Where Excel breaks
Excel rarely makes arithmetic mistakes. It breaks where people and the way the shop works meet it: when several people work in it, when the movement happens somewhere other than where the file is, and when a small error goes unnoticed for weeks. These don't appear overnight but gradually — and they typically come to light when nothing adds up at the stock take.
| Symptom | Why does it happen? | What does it cost? |
|---|---|---|
| Several people want to write at once | One file is safely edited by one person at a time; the shared version clashes or overwrites | Lost rows that nobody looks for |
| The file is in the back, sales are at the front | The laptop sits in the office, the movement happens at the counter | Numbers written afterwards, from memory |
| "stock_final_2.xlsx" | Everyone saves to their own computer and emails it on | You don't know which is right, and two have to be merged |
| A dragged formula silently slips | An inserted row, a cell overwritten by hand, a wrong range | Weeks of wrong stock that nobody notices |
| You can't see who changed what | The spreadsheet doesn't log changes | When there is a variance, there is nobody to ask |
| Awkward on a phone | Tiny cells, scrolling, accidental taps | Nobody records during a shift |
| Waste and own consumption get left out | Nobody walks to the back for one expired yoghurt or one coffee | The variance becomes "unknown shortage", though it isn't |
The last row is the most important. Goods in almost always gets entered, because the delivery note is there, and sales get in somehow if the till gives a summary. Waste and own consumption, though, leave no paper: an expired yoghurt goes in the bin, a coffee is drunk at the start of the shift, and nobody walks to the back to enter it. At the end of the month all of this lands in the "shortage" column and looks like theft — while most of it simply wasn't written down.
„The spreadsheet doesn't fail where it calculates, but where it waits: in the back office, until somebody remembers what happened at the front.”
Signs it's time to move to software
There is no single point at which Excel becomes "bad". There are, however, signs that together show you are spending more time maintaining the spreadsheet than it gives back. If three or four of the following are true, it is worth looking around seriously.
- More than one of you records, and somebody's entry has already vanished or appeared twice.
- The waste and own consumption columns are regularly empty or show suspiciously round numbers.
- Several versions of the file exist, and it isn't certain which is current.
- The weekly routine regularly gets skipped, because one hour has turned into two.
- The variance is large every month, but you can't say in which week or on whose shift it arose.
- Because of a second shop or a new counter, movements now happen in several places.
- A formula error only came to light at the stock take, weeks later.
What to look for in software
When switching, the question isn't how many features a program has, but whether it solves the part where your spreadsheet breaks. If for you it's the outgoing movements that get left out, a complex warehouse system won't help at all if recording at the counter is as hard as before.
| Criterion | Why does it matter? | How to test it |
|---|---|---|
| Recording where it happens | What isn't entered straight away typically never is | Try recording a waste item from behind the counter, on a phone |
| Several devices at once | If two of you are at the counter, nobody should have to wait | Record on two phones at the same time and check both are kept |
| Who recorded it | When there's a variance, you know whom to ask | Check whether the name shows next to every item |
| Fast at the counter | There's a queue, no time to dig through menus | Time how long it takes to record a sale or a waste item |
| Works alongside your till | You don't always have to replace existing equipment | Ask whether the cash register has to be replaced for it |
| Exportable data | You don't have to throw out your spreadsheet if you can export the numbers | Check whether you can download a report you can paste in |
Switching doesn't mean throwing the spreadsheet away. In many shops the set-up that works best is one where the hard-to-collect data — sales, waste, own consumption — is recorded where it arises and arrives in the spreadsheet from a report at the end of the week. Goods in and counting stay in the old routine. That way the spreadsheet remains what it is good at: summarising and finding variances.
What not to do
- Don't overwrite the stock figure. When a number is overwritten, the reason it became that number disappears — the log keeps it.
- Don't use the file name for version control. Next to "final_2" and "final_new" it will never be clear which one is right.
- Don't type into a formula cell. An overwritten formula gives no error; it just shows the wrong number from then on.
- Don't count the whole shop every week. Five to ten products regularly are worth more than a full stock take that never happens.
- Don't treat the variance as theft until waste and own consumption are properly entered. Most of a shortage is typically unrecorded movement.
- Don't overcomplicate it. Instead of macros and complex functions, eleven columns and two formulas — somebody will understand that even while you're on holiday.
- Don't wait for a total collapse before switching. If several of the signs are true, the spreadsheet already costs more than it saves.
Summary
- Excel is a good start for one person, a few hundred products and a weekly rhythm.
- Stock is the sum of movements: opening + goods in − sales − waste − own consumption, and you compare the counted quantity against it.
- Two sheets: a movement log you only add to, and a summary that calculates with formulas.
- A twenty-minute weekly routine and five to ten counted products keep it alive.
- It breaks where the movement happens: at the counter, in several hands, at the same time.
- When switching, check whether the program records where data gets lost today — you don't have to throw the spreadsheet away.
Frequently asked questions
Is Excel suitable for stock control in a small shop?
Yes, up to a certain size it works well. If one person keeps it, it covers a few hundred products and it is updated once a week, a well-built spreadsheet can serve you for years. The limit isn't in the calculation but in the recording: once several of you work and the movements happen at the counter, the spreadsheet falls behind more and more often. Until then, feel free to stay with it.
Which columns does a stock control spreadsheet need?
A working minimum: product, code, unit, opening stock, goods in, sales, waste, own consumption, calculated stock, actual (counted) stock and variance. It is worth adding the purchase price and the variance value, because a unit count on its own is misleading. More columns than that typically just slow down filling it in. The key is that every outgoing movement has its own column, not a shared "other".
What formula do I use to calculate stock in Excel?
Calculated stock is opening stock plus goods in, minus sales, minus waste, minus own consumption — in the template =D2+E2-F2-G2-H2. The variance is the difference between counted and calculated: =J2-I2, where a minus means a shortage. The variance value is the variance multiplied by the purchase price. A small shop rarely needs anything more complicated.
Why is a movement log better than a single stock column?
Because an overwritten number only tells you how much there is now, not how it got there. In a movement log every goods-in, sale, waste and own-consumption entry sits on its own row with a date and a name, so when there's a variance you can trace which week and which product it started with. A log is also more forgiving: a wrong row can be corrected, while a wrong overwrite vanishes without a trace. And the Stock sheet can be calculated from it automatically with a SUMIF function.
How do I use SUMIF with the movement log?
If on the Movements sheet incoming quantities have a plus sign and outgoing ones a minus, a product's total change is a single sum. On the Stock sheet the formula is =D2+SUMIF(Movements!B:B,B2,Movements!D:D), which adds every movement for that code to the opening stock. In some language versions of Excel the function has a local name and the argument separator is a semicolon. For a breakdown by type, a PivotTable is more convenient.
How often should I do a stock take if I keep stock in Excel?
A full stock take is typically enough quarterly or yearly if you count five to ten products every week in between. In the weekly sample, expensive and fast-selling products should come up more often, slow and cheap items less often. That way variances show up within weeks, not only at the end of the year. We cover the detailed year-end stock take in a separate article.
What should I do if the spreadsheet shows a shortage but I don't know where it comes from?
First check whether all waste and own consumption for the period got entered — in many shops most of a shortage is simply unrecorded movement. Then find which week it started in and which products it repeats for; a one-off swing rarely means anything. Check the formulas too, because a slipped range looks just like a shortage. Only if all of this is in order is it worth looking for another cause.
Can several people use the spreadsheet at the same time?
With a shared cloud spreadsheet, technically yes, but in practice this is where the trouble starts. If two people write into the same row, or someone works in an older downloaded copy, rows can vanish or be duplicated, and it is hard to work out afterwards who changed what. If you do work this way, everyone should write only on the Movements sheet, on a new row, and only one person should handle the formula sheet. If this regularly stalls, it is one of the strongest signs to switch.
Why do waste and own consumption always get left out of the spreadsheet?
Because they leave no paper and they happen at the counter while the spreadsheet is in the back. Goods in has its delivery note and sales have the till, but nobody walks to the laptop in the back for one expired pot of cream or one coffee. What isn't entered straight away is typically forgotten by the end of the week. A notebook or a note holder under the counter helps, but the lasting solution is for recording to happen where the movement does.
When is it worth moving from Excel to software?
When several of you record, when the waste and own consumption columns are regularly empty, when several versions of the file are going around, or when the weekly routine no longer fits into your time. One sign on its own isn't a reason to switch, but three or four together are. When deciding, don't look at the number of features but at whether the program records where data gets lost today. Many shops don't even throw the spreadsheet away; they just fill the hard-to-collect columns from elsewhere.
What should I look for when choosing stock control software?
That recording is fast at the counter, on a phone or tablet, that several devices work at the same time, and that it shows next to every item who recorded it. It matters that it works alongside your existing till and that you can export the data into a report. Try it in your own shop for a few days, during a genuinely busy hour, before you decide. What is fast in a demo isn't always fast in front of a queue.
Does Boltom App replace my stock spreadsheet?
Not completely, and it wasn't built to. Boltom App records at the counter what leaves the shelf: sales, waste with its reason, and own consumption with the name of who took it, on several devices at once, alongside your existing till. It doesn't do goods receipt, purchase orders, barcode stock counts or a full warehouse ledger. That is why many shops keep their simple stock sheet and fill in the outgoing columns from the app's reports.
Say the product's name and it's there on the till.
Boltom App takes the work behind the counter off your hands. You find a product by looking at its photo or saying its name out loud — no codes, no scraps of paper. Whatever leaves the shelf or goes in the bin is recorded right there, not in a notebook. It works alongside your existing till, not instead of it.
- A quick code with a photo — or just say the product's name and the till finds it.
- Staff consumption in three taps, recorded under the person's name.
- Daily waste with its reason: expired, broken, unsellable — totalled up in money.
No card needed · 2 minutes
