In this guide 6 sections
A spreadsheet is enough while one chemist owns a few stable recipes, batches are traced by hand and no customer or auditor asks for lot records. An integrated system earns its place once several people edit formulas, lots must be traced to customers, or an auditor asks who changed what and when.
Where a recipe spreadsheet still works
A sheet suits a small plant. If one chemist owns twenty formulas, batch volumes barely change and customers never ask for lot genealogy, a locked workbook on a shared drive does the job.
Its strengths are real. Anyone can read it, it costs nothing to change, and it prints cleanly for the blending floor, where operators work from paper beside the tank. Scaling a 200 litre recipe up to a full blending tank takes one multiplication and about a minute with a calculator beside the sheet.
Be honest about the conditions, though. They hold only while the same people stay and the same products repeat, and the first new hire, new customer audit or second plant tends to end that quietly.
- Few formulas, rarely revised
- One owner of the workbook
- Customers do not ask for lot trace
- No audit or certification pressure
Where sheets start to fail
Access control goes first. Anyone with the file can overwrite a percentage in the active formula, and the old value is gone with no record of who typed the new one. A copy named final_v3_new sits beside it, and the floor cannot tell which one the quality manager approved before the last shift started.
Scaling goes next. Sheets cannot see assay. A sheet scales quantities but does not know that one raw material has a different assay per delivery, or that the drum on the shelf was opened last month. The yield and density corrections that fix it live in the operator’s head, and they leave with the operator when he moves to another shift.
Traceability is the third break. It hurts most. When a customer reports a tacky batch, someone must find which lot of resin and which hardener drum went into it before the next shipment leaves. In a workbook that search takes hours of flipping across five tabs and a paper notebook, with no certainty of an answer.
- No history of who changed a figure
- Copies drift from the approved version
- Raw material assay and expiry live elsewhere
- Lot-to-customer trace takes hours
What an integrated system adds
A chemical ERP keeps the formula as a versioned record with an approval step, and the batch order is created from the approved version only. Only signed-off recipes reach the floor. Changes need a reason and a name.
It also ties materials to the batch. Each charge records the raw material lot, the weight, the operator and the time, so a later question about one drum of solvent has a written answer. Expiry and retest dates block a drum that is out of date, and inventory falls as the batch is made rather than at month-end.
Audit becomes a report. No tabs, no notebook. Ask for every batch that used one lot of solvent, and the answer lists batches, customers and invoices in a minute, ready for the customer’s quality team.
Compare them on the questions that matter
Judge both options on a bad day. Suppose a supplier recalls one lot of solvent: how long does it take to name every affected batch and delivery, and who confirms the list? Suppose a formula changed without sign-off. How would you know?
Count the hours spent each month reconciling stock sheets against the store, and the batches held because nobody could find a certificate of analysis. Write both numbers down. Those hours are its real price.
Ask what your auditor expects. If your certifying body or largest customer audits batch records against ISO 9001 or a responsible care programme, a sheet puts the whole burden on your memory. Short answers will not satisfy them.
- Recall drill: time to name affected lots
- Change control: who approved the edit
- Stock reconciliation hours per month
- What your auditor expects to see
Move in stages instead of switching overnight
Start with the formula library and batch records for two or three products, and keep the sheet read-only as a reference while the team learns the new screens. Lots and expiry come next. Customer shipments follow.
Clean the data first. Merge duplicate raw material names, fix units that mix kilograms and litres, settle one name per product, and check every density figure against the supplier’s data sheet. Dirty names only get stored more neatly.
Next step: run a mock recall on your current sheets with a stopwatch, and write down the minutes you needed to name every affected batch. That time is your answer.
Spreadsheet or system scorecard
A single page of ten yes or no rows, for example edits are logged, a recall can be run in an hour, expiry blocks use and scaling handles assay. Two columns score the current sheet and a candidate system, and a notes column holds the evidence behind each answer, such as a screenshot or a dated record. A last block holds the recall time.
Open a blank worksheet to printQuestions owners ask
Can a small chemical plant run on spreadsheets?
Yes, while recipes are few and stable, one chemist owns the workbook, and no customer or auditor has asked for lot trace on a delivered batch. Trouble starts when several people edit formulas. Or when a contract demands lot trace.
What do auditors look for in batch records?
Auditors typically look for the approved formula version, raw material lots, quantities, operators, dates, deviations and the release sign-off, each tied to one batch number. A system shows them in a minute.
Is a chemical ERP too heavy for a small plant?
Not necessarily. A smaller setup covering formulas, batches, stock and dispatch can be enough, and the first modules should match the records an auditor or customer already asks you to produce. Add the rest later.
Can we keep spreadsheets alongside a system?
Yes, for analysis and one-off calculations. Keep formulas and batch records in one place only, so there is a single approved version that the floor, the lab and the auditor all see.