We tracked lots, serials, and expiry dates for twenty-five years as a medical equipment distributor, across a warehouse in Sarajevo and consignment stock in hospitals we did not control. For part of that time we did it the way most distributors still do: in spreadsheets, maintained by the one person who understood them. We know exactly how that story goes, which is why we now build the systems that end it.
This article walks the full chain: receiving, storage, shipping, the install base, consignment, and the recall that eventually tests all of it. At the end we describe the data model that makes the spreadsheet unnecessary.
Where the spreadsheet breaks
A traceability spreadsheet has three structural defects, and none of them are the fault of the person maintaining it. First, it is not connected to physical stock movements, so it records what someone remembered to type, not what happened. Second, it is single-user in practice: the moment two people edit it, versions fork. Third, it concentrates institutional knowledge in one head. When that person is on leave during a recall notice, the company is blind.
The failure is silent. The spreadsheet does not error when a receipt is skipped or a serial is fat-fingered. It diverges from reality by one row at a time, and you discover the gap precisely when you can least afford to: during a recall, an audit, or a consignment count that a hospital disputes.
Traceability is a chain, and it starts at goods-in
Receiving
Everything downstream depends on capture at the dock. Lot number, serial number, and expiry date are recorded when the goods arrive, against the purchase order line, before the goods touch a shelf. Barcode scanning makes this fast; GS1 labels from manufacturers usually carry lot and expiry in the barcode itself. The rule that matters is temporal: capture happens at receipt, never "later." Later is where chains break.
Storage and picking
Expiry-dated stock needs a FEFO picking rule: first expired, first out. A warehouse picking by convenience will reliably strand short-dated stock at the back of the shelf until it is worthless. The system should propose the pick by expiry date, and it should hold quarantine locations for goods pending inspection or blocked by a manufacturer notice, so that blocked stock physically cannot be picked.
Shipping
The delivery order records which serials and lots went to which customer, on which date, against which order. This is the half of traceability most spreadsheets lose first, because shipping is the busiest moment of the flow. Done correctly, it costs one scan per unit, and it produces the record a recall depends on.
The install base is the second half of traceability
For equipment, the story continues after delivery. A serial number that leaves the warehouse should automatically become an installed asset: which customer, which department, warranty end date, service history, parts consumed. When a manufacturer bulletin names a serial range, this record answers the only question that matters: which of our customers are affected, and where is the machine standing. We describe how we build install bases on our medical equipment practice page; the point here is that it must be generated by the delivery, not maintained by hand afterwards.
Consignment stock at hospitals
Consignment is where spreadsheet tracking goes to die quietly. The stock is at the hospital, but it is yours: your asset, your expiry risk, your count. A clean model treats each consignment site as a stock location you own, at someone else's address. Deliveries to the site are internal transfers, not sales. Usage reported by the hospital triggers the invoice and the replenishment against agreed minimums. Cycle counts reconcile the site against the system, per lot and serial.
Expiry matters doubly here. Short-dated stock at a consignment site is invisible to a warehouse-only view, and hospitals do not rotate your stock for you. The system should report expiring lots across all locations, including consignment, with enough lead time to swap or redeploy. In our distribution years, the stock we wrote off was almost never in our own warehouse. It expired in cupboards eighty kilometers away.
The recall drill
A field safety notice arrives naming a lot or a serial range. The questions are fixed: How many did we receive, and when? How many are on our shelves? How many sit at which consignment sites? Which customers received the rest, on which delivery notes? Which installed machines carry an affected component? With a connected system this is one query per question, minutes in total, with delivery documents attached. With a spreadsheet it is days, and every day is exposure.
Regulatory frameworks such as the EU MDR expect economic operators to identify who supplied them and whom they supplied. We do not give legal advice, and no software makes you compliant by itself. What a clean system gives you is the practical substance behind those obligations: the trace exists, it is complete, and you can produce it on demand with documentation to match.
What a clean data model looks like
Strip away vendor vocabulary and the model is five parts. One: products carry a tracking policy: by serial, by lot, or untracked, decided once per product, not per transaction. Two: every stock move references the lot or serial it carries; there is no anonymous movement of tracked goods. Three: locations are first-class records, and "locations" includes your warehouse zones, quarantine, each consignment site, and each customer install position. Four: expiry is a set of dates on the lot: expiry itself, plus an alert date and a removal date that drive FEFO and early warnings. Five: the install base links serial to customer to service history.
The consequence of this structure is that traceability stops being a task. Nobody "maintains the traceability spreadsheet" anymore, because the trace is a by-product of receiving, transferring, and delivering. The spreadsheet becomes what it always should have been: a report you can regenerate at will, not a source of truth you have to protect.
Getting there from here
The obstacle is rarely software. It is the state of the data: product records without tracking policies, historical stock without lot numbers, consignment quantities nobody has counted since the agreement was signed. Our advice, learned the hard way: do not try to backfill history. Count what exists, capture lots and expiry at the count, and start the clean chain from a known baseline. Discipline at receiving carries the rest.
Sizing that cleanup is most of what our fixed-price operations audit does: a data quality assessment and a roadmap, in two weeks. The implementation that follows is priced fixed per phase, and the details are on our pricing page.
Every distributor we meet believes their spreadsheet is the exception: carefully built, faithfully maintained. Ours was too. It still could not answer a recall in an afternoon. The system that can is not more work. It is the same work, recorded once, where it happens.