In this guide 6 sections
A spreadsheet can track cosmetic lots while one person ships from one shelf with one live lot per product. It breaks the day you cannot say which lot went into which order, and a customer asks about a jar from last spring. Move to software when a recall lookup takes an afternoon or leans on memory.
What a batch record has to hold
A batch record ties a physical lot of product to a row of data, and every later answer you give a regulator or a customer starts from that row. Start with the code. Every jar carries a lot code, printed or embossed by the contract manufacturer, and your record must hold the same string character for character.
Beyond the code, store dates and status. The manufacture date, the best-before date or open-jar months, the supplier’s certificate of analysis reference, the quantity received and the shelf location all belong in the row. Rules differ by market. Ask your regulator what records you must keep.
Keep one rule from day one. A code is never typed from memory. Copy it from the outer carton at receiving, with a photo of the label saved beside the row.
- Batch code copied letter for letter from the jar and carton
- Manufacture date and best-before date
- Contract manufacturer and certificate of analysis reference
- Quantity received and shelf or bin location
- Retention sample kept, with its shelf position
- Status: quarantine, released or on hold
Where a spreadsheet does the job
A spreadsheet is fine at the start. Use one tab per product, one row per lot, a data validation drop-down for status and conditional formatting for dates, and file each certificate under its code. One window shows the whole business.
It works because the world is small. You ship from one shelf, sell one live lot of each product, reconcile the count every Friday and know the packer by name. Under those conditions the sheet tells the truth.
Add two habits and the sheet lasts longer. Freeze the header row so sorting cannot scramble columns. Save a dated copy each Friday, so that when a cell is overwritten by mistake you can trace the change and put the old value back.
The moment the spreadsheet stops being safe
The sheet records what arrived, not what left. That gap is the problem. A customer emails a photo of a jar with a batch code, and you must list every order that received it, with names, addresses and ship dates. The sheet cannot answer that. Hours of digging follow.
Several things widen the gap at once. Each one lets the lot on the sheet and the lot in the parcel drift apart, and each is common in a growing brand.
Notice what these have in common. None is a failure of care. They are simply what happens when a brand sells more, through more places, with more hands on the stock and the same sheet.
- Two lots of one face wash on the shelf together
- A third-party warehouse or a second packer picking orders
- Gift sets that bundle jars from different lots
- Marketplace and wholesale orders with no batch field
- Returned jars restocked without a check
- Two people editing one file, with the last save winning
What inventory software adds, and what it cannot fix
Inventory software records the lot when the packer picks the order. The packer scans the code or chooses from a list sorted by earliest expiry first, a rule known as FEFO, and the lot is stored against the order line. A recall lookup becomes a query. Batch in, orders and customer emails out.
Quarantined stock can be blocked from sale. Near-date lists build themselves. A movement history shows who received, moved or adjusted a lot, and when, which also helps when stock counts disagree with the shelf.
It cannot fix a code mistyped at the loading dock. It also asks something of you: scan discipline, one product master with a single name per item, and a clean link to your store and marketplaces. Budget time to train the packers.
Run a recall drill before you choose
Pick one lot of body butter or mascara shipped last month. Set a timer. Build the list of customers who received it, using only your sheet, your order export and whatever courier notes you kept, without asking the packer what they remember. Note the minutes. Count the orders you had to guess. List any parcel you could not place.
If the list took an afternoon, you have your answer. If it took a few minutes and held no guesses, keep the sheet and repeat the drill every quarter, each time with a different product and a different month. Schedule the first drill this week. Write the result in cell A1.
Batch record and recall drill sheet
One page in two blocks. The intake block has columns for batch code, product, manufacture date, best-before date, supplier, certificate reference, quantity, location and status, plus a line for who checked it. The drill block records the batch tested, the date, minutes taken, orders found, orders guessed and the gap that caused each guess.
Open a blank worksheet to printQuestions owners ask
Is a spreadsheet enough for a small cosmetics brand?
Yes, at first. While one person ships from one shelf with one live lot per product, the sheet holds up. It stops being enough once you cannot name the lot in each parcel. Run the recall drill to see where you stand.
Should the sheet match the code on the carton or the jar?
Both should carry the same code. When they differ, record both and ask the manufacturer why, because a recall notice that quotes the wrong code would miss customers who hold jars from the other run.
Do we need barcode scanners for batch picking?
Not at low volume. Choosing the batch from a list works if the packer reads the jar, but scanning removes typing errors, which matters once several people pack.
What should happen to a returned jar?
Treat it as not resaleable. Record the batch from the jar, so that complaints, returns and reactions for one batch show up together and a pattern can be spotted early.