Production & Quality · November 2026
Batch Traceability for Small Chemical and Lubricant Manufacturers: Start With a Spreadsheet
By Kishan Bhatt · Updated
Short answer: Batch traceability means being able to answer two questions quickly: which raw material lots went into this batch, and which customers received it. You can start with a spreadsheet and four sets of records: raw material receipts, batches, packing, and dispatch. The test is a timed drill: pick a batch and see how long it takes to answer both questions. If it takes more than an hour, your records have a gap.
A customer calls about a product that did not perform. Or a raw material supplier tells you a lot was off-spec. Either way you need to know which batches and which customers are affected. In many small plants that answer lives in a notebook, a few WhatsApp messages and one person's memory. This article is about getting it onto paper in a form you can search.
A note on where this comes from. I run the same kind of operation at Lukeron Lubricants, which makes products across many brands and pack sizes. I have not built batch traceability as a proven system for a client, and my chemical manufacturers page says so. What follows is the record structure I would start with, and I mark it as my suggestion, not a rule.
The two questions
- Backward: for this finished batch, which raw material lots went in, from which supplier, received on which date?
- Forward: for this batch or this raw material lot, which customers received it, in which invoice, how many packs?
If you can answer both from your records within minutes, you have traceability. If either answer needs someone to remember, you do not yet.
Four sets of records to keep
| Record | What to write | Why it matters |
|---|---|---|
| Raw material receipt | Supplier, date, supplier's lot or batch number, quantity, your own internal lot number | Gives every incoming lot a number you control. Supplier lot numbers repeat or are missing |
| Batch record | Batch number, date, product, formula version, the internal lot numbers of every raw material used, quantity made, who made it | Links a finished batch back to raw material lots. This is the backward answer |
| Packing record | Batch number, pack size, number of packs, label or carton marking | One batch usually becomes several pack sizes. Without this, a pack cannot be traced to its batch |
| Dispatch record | Invoice number, customer, batch numbers shipped, quantity | Links a batch forward to customers. This is the forward answer |
Three habits that make it work
- One batch number, written on every pack. A simple pattern is product code, date and a running number, for example the product code followed by the date and a counter. The exact pattern matters less than using it every time and printing it on the pack or carton.
- Your own lot number on every incoming raw material. Write it on the drum or bag at receipt. Otherwise two deliveries from the same supplier get mixed up in the records.
- Put the batch number on the dispatch line. If your invoice or delivery challan does not carry batch numbers, the forward trace stops there. Many small sellers skip this step.
The timed drill
Once a quarter, pick one finished batch at random and ask someone who did not make it to answer both questions, with a clock running. Write down how long it took and where it got stuck. Food regulators and auditors use a similar idea, often called a mock recall, and the targets people quote vary from source to source, so I will not give you a number. My own rule of thumb for a small plant is that if the drill takes more than an hour, find the missing record and fix it.
Where a spreadsheet stops being enough
A spreadsheet works for one plant, a handful of products and one person entering the data. It starts to strain in these cases:
- One raw material lot feeds many batches, or one batch uses several lots. The links multiply.
- One batch becomes many pack sizes that then go to many dealers, and some come back as returns.
- Rework or blending of an old batch into a new one.
- More than one person entering data, so numbers are typed differently.
At that point a small tool helps by making the batch number a field you pick instead of type, and by pulling the dispatch line from the invoice automatically. Even then, the tool is only as good as the habits above.
What I am not saying
- I am not saying a spreadsheet meets any legal requirement for your product. Which rules apply depends on what you make and who you sell to, so confirm with a qualified adviser.
- I am not offering a validated or regulated system. I build record-keeping tools.
- I am not saying you need software. For many small plants the four records and the drill are enough for a year or more.
Where I fit
If you want records built around your own products, my production, packaging and quality systems work is where batch records sit, and a tool for one workflow is usually ₹40,000 to ₹80,000, fixed before work starts. What moves that price is in the software cost guide. Related: why multi-brand manufacturers need a packaging lookup system.
This article is general information as of 17 November 2026 and is not legal or compliance advice. Confirm what your products require with a qualified adviser.
Questions people ask
- What is batch traceability?
- Being able to answer two questions quickly: which raw material lots went into a finished batch, and which customers received it. It needs records at four points: raw material receipt, the batch, packing, and dispatch.
- Can a small manufacturer start with a spreadsheet?
- Yes, for one plant, a few products and one person entering data. The key is a batch number on every pack, your own lot number on every incoming raw material, and the batch number on the dispatch line.
- How do I test whether my traceability works?
- Run a timed drill. Pick one finished batch at random and ask someone who did not make it to find the raw material lots and the customers, with a clock running. If it takes more than an hour, find the missing record.
- When do I need software for batch tracking?
- When one raw material lot feeds many batches, one batch becomes many pack sizes and dealers, you do rework or blending, or several people enter data and the numbers get typed differently.
- Does a spreadsheet meet legal requirements for batch records?
- I cannot say. It depends on what you make and who you sell to. Confirm the requirements for your products with a qualified adviser.
Want to talk through how your plant records batches today? A short call, no obligation.
Get in touch →