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.

CodeItemUnitCostSelling priceReorder levelOpening qty
1001Tin milk 400gtin2,8003,3003648
1002Milo 500gtin3,9004,5002436
1003Bath soap 150gbar4506003040

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.

DateCodeTypeQtyReferenceBy
1 Oct1001Out5Day's salesCN
2 Oct1001Out7Day's salesCN
3 Oct1001In24Invoice 0412AO
4 Oct1001Out1DamagedAO

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:

ColumnHeadingWhat it shows
DOpeningOpening quantity from Items
EInEverything received
FOutEverything sold, damaged or removed
GBalanceWhat should be on the shelf
HReorder levelFrom Items
IStatusSays "Reorder" when the balance is low
JValueBalance 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.