Most stock sheets people download are too clever. They have dashboards and macros, and they break the first time someone types in the wrong cell.
A stock record that survives daily use in a shop is plain. It has three tabs and four formulas. It works the same in Excel and in Google Sheets.
The idea
Never type a balance. Type what happened, and let the sheet work out the balance.
When people type balances by hand, one wrong subtraction stays wrong forever. When you record movements, in and out, the balance is always opening stock plus everything in, minus everything out.
Tab 1: Items
One row per product. Name the tab Items.
| Code | Item | Unit | Cost | Selling price | Reorder level | Opening qty |
|---|---|---|---|---|---|---|
| 1001 | Tin milk 400g | tin | 2,800 | 3,300 | 36 | 48 |
| 1002 | Milo 500g | tin | 3,900 | 4,500 | 24 | 36 |
| 1003 | Bath soap 150g | bar | 450 | 600 | 30 | 40 |
Give every product a short code and never reuse one. "Opening qty" is what you counted on the day you started the sheet.
Tab 2: Movements
One row every time stock changes. Name the tab Movements.
| Date | Code | Type | Qty | Reference | By |
|---|---|---|---|---|---|
| 1 Oct | 1001 | Out | 5 | Day's sales | CN |
| 2 Oct | 1001 | Out | 7 | Day's sales | CN |
| 3 Oct | 1001 | In | 24 | Invoice 0412 | AO |
| 4 Oct | 1001 | Out | 1 | Damaged | AO |
The Type column only ever says In or Out. A sale is Out. Damage is Out. A delivery is In. A customer return is In.
Enter one Out line per item per day, taken from your daily sales record book. You don't need a row for every customer.
You never delete or edit old rows on this tab. A mistake is corrected with a new line that says what it's correcting.
Tab 3: Stock
This is the tab you read. Name it Stock. Columns A to C repeat the code, item and unit from the Items tab. Then:
| Column | Heading | What it shows |
|---|---|---|
| D | Opening | Opening quantity from Items |
| E | In | Everything received |
| F | Out | Everything sold, damaged or removed |
| G | Balance | What should be on the shelf |
| H | Reorder level | From Items |
| I | Status | Says "Reorder" when the balance is low |
| J | Value | Balance at cost |
In row 2, type these formulas, then copy them down the column.
- E2, total in: =SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "In")
- F2, total out: =SUMIFS(Movements!D:D, Movements!B:B, A2, Movements!C:C, "Out")
- G2, balance: =D2+E2-F2
- I2, status: =IF(G2<=H2, "Reorder", "")
- J2, value: multiply the balance in G2 by the item's cost from the Items tab
For D2 and H2, point at the matching cells on the Items tab, or copy the figures across once.
With the sample rows above, tin milk shows Opening 48, In 24, Out 13, Balance 59.
At the bottom of column J, add a total. That's the cost value of everything in your shop, a number most owners have never seen.
Using it day to day
Every evening, add the day's Out lines from the sales book.
Every delivery, add In lines from your goods received note, using the quantity you counted.
Every week, count your key items and compare with the Balance column. If the shelf has 57 and the sheet says 59, add a line: Out, 2, "Count adjustment", with the date. Don't touch the old rows. How to count is in how to take stock in a shop without closing for the day.
Every morning, look down the Status column. Anything saying "Reorder" goes on today's order list. How to set the level is in reorder level formula.
Protecting the sheet
- Lock the formula cells on the Stock tab so nobody types over them.
- Use a drop-down for the Type column so it only accepts In or Out. A typed "IN " with a space will not be counted.
- Use a drop-down for the code too, taken from the Items tab, so nobody enters a code that doesn't exist.
- Back it up. In Google Sheets this is automatic. In Excel, save to a cloud folder, not only to the shop laptop.
- One person enters each day. Their initials go in the By column.
Where a spreadsheet runs out
A sheet like this is a real step up from a notebook, and for one shop with a few hundred items it can serve for years. It does have limits, and it's better to know them now.
It's always behind. The balance is only right as of the last time someone typed in the sales. During the day it's a guess.
It trusts whoever is typing. Anyone with the file can change a number, and nothing shows who did or when.
It's awkward on a phone, which is where most shop owners actually are.
Two branches means two files, and nobody will keep them in step.
It grows slow. After a year of daily rows, the Movements tab is long and the formulas start to drag.
When those start to bite, you've outgrown it. That's the kind of shop we are building Tabs for. It hasn't launched yet. The good news is that nothing here is wasted: a clean Items tab, with codes, costs and prices, is exactly what any stock system will ask you for on the first day.