Track sales and stock in a spreadsheet, properly

A spreadsheet for stock and sales works or fails on its structure, decided before any data goes in. Three sheets. The first is a product list: one row per item you sell, each with a unique code, a name, a unit, a purchase cost, a selling price and the stock you held on the day you started. The second is a sales log: one row per sale, with a date, the product code, the quantity and the amount. The third calculates current stock from the other two.

The rule that makes this work is that each fact is recorded once, in one place. The product list holds facts about products; the sales log holds facts about events. The third sheet contains no typed data at all, only formulas. This sounds fussy and it is what prevents the common outcome, where stock is typed in two places, updated in one, and quietly wrong for months. It also means the file can answer questions you have not thought of yet — what sold last Tuesday, which items have not moved, what your stock is worth — because the underlying records are intact rather than summarised away.

Products must be identified by a short unique code rather than by name, and this is the single most important detail in the whole exercise. Names get typed differently every time — "Blue shirt L", "blue shirt (L)", "Shirt blue lge" — and every formula that groups by name will treat those as three products. The result is undercounting that is invisible: each variant shows a plausible small number, the total looks lower than it should, and nothing indicates an error.

Codes solve this only if they are impossible to mistype, which means choosing them from a list rather than typing them. Set the product code column in the sales log to a dropdown built from the product sheet, so only existing codes can be entered. This takes two minutes to configure and eliminates the error category entirely. Codes should also be specific to the exact thing you count: if size or colour affects stock, they are separate codes, because a single code covering three sizes cannot tell you which one has run out. Being strict here is what allows a stock figure per item to mean anything.

The arithmetic is deliberately simple: closing stock equals opening stock, plus everything received, minus everything sold, minus everything written off. Each term needs a source. Opening stock sits on the product sheet and is set once, at the start. Sales come from summing the sales log for that product code. Receipts need their own log — a purchases sheet with a date, a code and a quantity — because stock arriving is an event exactly like stock leaving, and trying to handle it by editing the opening figure destroys the audit trail and the ability to reconstruct anything.

With those in place, the third sheet is one row per product and a formula per term. The important property is that no one ever edits a stock number directly. If the figure is wrong, the cause is a missing or incorrect event, and the fix is to correct the event. This is what makes the file trustworthy over time: any stock figure can be traced to the transactions that produced it. A file where people occasionally overwrite the stock figure to make it match reality has lost that property permanently, and after a few such corrections nobody can explain any number in it.

Real trading involves movements that are not straightforward sales, and each needs a defined treatment rather than an improvised one. A return where the goods come back saleable is a negative sale: a row in the sales log with a negative quantity and a negative amount. That keeps the revenue and the stock correct simultaneously, which is why it is better than deleting the original sale — deleting destroys the record that the sale happened, and your revenue figures for the earlier period will change retrospectively.

Goods that come back damaged are two events, not one: a return that restores the revenue position and a write-off that removes the item from stock. Keeping them separate is what lets you see how much you are losing to damage, which is invisible if write-offs are mixed into returns. Damage, theft, expiry, samples given away and personal use are all write-offs, and they need a reason column, because the total is far less useful than knowing which category it came from. A stock discrepancy with no reason recorded is the beginning of a file nobody believes, and 'adjustment' is not a reason.

Every stock system drifts from reality, because entries get missed, quantities get mistyped and things leave without being recorded. The only remedy is to count physical stock and compare it with what the file says. Doing this weekly, on a subset rather than everything, is far more effective than a full annual count: pick your fastest-moving or highest-value items, count them, and record both the counted figure and the file's figure.

The comparison is the point. A small discrepancy tells you the system is broadly working. A large one, caught within a week, can usually be explained — someone remembers the sale that was not logged, or the delivery that arrived on the day the counter was busy. The same discrepancy found eleven months later is unexplainable and therefore useless, because the information needed to understand it has gone. When you find a gap, record the correction as a write-off with a reason of stock count adjustment rather than editing the stock figure, so the file retains the history of how accurate it has been. That history is itself valuable: a product with repeated large adjustments is telling you something about either your process or your losses.

It will not stop someone from failing to log a sale, and unlogged sales are the largest source of error in every manual system. It has no live connection to anything, so if you sell in more than one place — a shop and an online store — the file is only as current as the last person to update it, and two people editing simultaneously will overwrite each other unless it is a cloud file with proper version handling. It does not enforce anything: nothing prevents a negative stock figure, or a sale of an item you do not have, unless you add a check that flags it.

It also stops being the right tool at some point. The signs are specific rather than a matter of feel: many thousands of rows making the file slow, several people needing to enter data at once, stock in multiple locations, or the same reconciliation error recurring despite the weekly count. At that stage the honest assessment is that you need software with validation and an audit trail, and the good news is that a well-structured spreadsheet is exactly the right preparation — you already know your product codes, your movement categories and your actual process, which is most of the work of a migration. What a spreadsheet never provides is assurance that what was recorded is what happened, and no amount of formula work changes that.