A consignment spreadsheet, and where it breaks
The columns a consignment sheet needs, why the obvious layout bills the wrong amount, and the three points at which a spreadsheet stops coping.
Most people tracking consignment are doing it in a spreadsheet, and a spreadsheet is a perfectly good answer for a while. This is the layout that works, and — more usefully — the three specific places it stops working, so you can see them coming instead of discovering them in an argument with a shop.
The layout
One row per shop per visit. Not one row per shop: the whole point is that a shop’s position changes every time you stand in front of it, and overwriting last visit’s numbers destroys the only record of what happened.
| Column | Typed or calculated | What it is |
|---|---|---|
| Date | typed | The day you stood in the shop |
| Shop | typed | Which shop |
| Product | typed | Which line |
| On shelf last visit | carried forward | What you recorded leaving there |
| Still there today | typed | The count you take, standing in the shop |
| Replaced free | typed | Damaged or expired, swapped at no charge |
| Delivered today | typed | What you left today |
| Price each | typed | Your consignment price |
| Sold | calculated | On shelf last visit − Still there today |
| Owed | calculated | Sold × Price each |
The two bold rows are the ones that must be formulas. The moment anybody types a number into “Sold” the sheet has stopped being a record and become an opinion.
One
Number you actually collect
Everything else is either carried forward from last time, a price you already know, or arithmetic. The only thing the visit produces is the count of what is still on the shelf.
The mistake the layout is designed to prevent
Look again at what is not in the owed formula: Delivered today.
This is the single most common error in a consignment sheet, and it is an easy one to make, because on every other kind of round the delivery is the thing you bill for. Here it is not. What you dropped off today has not sold yet. It gets charged on the next visit, when the count tells you how much of it went.
Push on the numbers
- On the shelf last visit
- 24
- Still there today
- 9
- Delivered today
- 12
- Sold
- 15
- Price per unit
- MX$30
- Owed
- MX$450
Change what is still there. Then change what you delivered.
Change what you delivered and the amount owed does not move. That is not a quirk of this table — it is the definition of consignment, and a sheet that behaves otherwise is billing shops for stock still sitting on their shelves.
Free replacements are the same trap from the other side. A jar you swapped because it was damaged is not a sale, so it must not be charged — but it is also not nothing, because it cost you a jar. It gets its own column so you can see what replacements are costing you across a year without them ever touching the bill.
Where it breaks
A spreadsheet does not fail all at once. It fails in three specific places, and each one has a tell.
1. The gap between the shop and the sheet
The tell: you are typing numbers in the evening.
You counted at eleven in the morning. You are entering it at seven at night, from a photo, a note on your hand, or memory. Everything in the layout above is correct and the numbers going into it are already a day old and a little bit wrong.
This is the failure that matters most, because it is invisible. The sheet is not complaining. It is faithfully recording an approximation.
2. Nothing adds up across shops
The tell: you cannot answer “how much am I owed” without adding it up by hand.
One tab per shop is the natural way to build this, and it is the reason totals stop existing. Fifteen shops is fifteen files or fifteen tabs, and the number you most want — everything outstanding, right now — is not in any of them. It has to be assembled, which means it gets assembled rarely, which means you do not actually know.
3. Two people, one file
The tell: someone asks you to send them a copy.
The moment a second person makes deliveries, the sheet has to be in two places, and the copy is stale the second it is sent. The usual fix — sharing the live file — means the person driving your route can also see every other shop’s prices and balances, which is a decision you have made by accident rather than on purpose.
If you are staying on the sheet
Reasonable. Plenty of good businesses run on one. Three things worth doing today:
- Lock the calculated columns. Protect the two formula cells so nobody — including you, in a hurry — can overwrite one with a number that looks right.
- Enter it standing in the shop. A sheet on your phone that is filled in badly at the counter beats a beautiful one filled in from memory at home.
- Add a totals row that sums every shop, even if it is ugly. The number you cannot see is the number you cannot chase.
And if you want to check the arithmetic against your own figures before deciding anything, the calculator does exactly this sum with nothing to set up.
Common questions
- What columns does a consignment spreadsheet need?
- One row per shop per visit, with the date, what was on the shelf at the start, what is still there now, what you replaced free of charge, what you delivered today, and the unit price. What sold and what is owed are calculated from those, never typed in.
- Why does my spreadsheet give the wrong amount owed?
- Almost always because what you delivered today has been added into the amount charged. On consignment the drop-off is not billable until the next visit tells you how much of it sold.
- Can I just use one sheet per shop?
- You can, and it works until you have more shops than you can open in a morning. The failure is not the arithmetic, it is that nothing adds up across sheets, so you never see a total of what you are owed.
- When should I stop using a spreadsheet?
- When you are entering numbers from memory hours after the visit, when two people need it at once, or when you cannot answer what a shop owes you without opening a file. Those are the three points at which the sheet is costing more than it saves.