Chemical Manufacturing Software Problems: The 7 That Cost Real Money, and How to Avoid Them

Why does the get the formulas out of Excel scope failure happen so often?

The brief that arrives is nearly always to replace the formula workbook. That framing sets the scope as a database: take the tabs, make them records, add a login. Every chemical build we have seen fail was scoped that way, and the tell is that six months after go live the technical director is still keeping a private copy of the workbook.

Storing the formula is not the value. Propagating it is. A released formula version has to fire downstream: recalculate the hazard composition bands, flag the safety data sheet for regeneration, update the certificate of analysis spec template, and push new consumption factors to whatever holds purchasing. In our delivery experience that single data flow is the majority of the value in the whole build, and it is the part a database project quietly omits because it looks like integration work rather than core scope.

What makes this specific to chemical manufacturing is the shape of the data. A bill of materials in a general system is a parent record with fixed child quantities. A formula is percentage by weight, it scales to batch size, it carries potency and assay adjustment computed at release from the specific lot on hand, it has theoretical versus actual yield, and it has substitution rules that are conditional on incoming lot properties. Build a fixed quantity list and you have modelled the wrong object, and every downstream calculation inherits the error.

The fix is to write the propagation into the first release scope explicitly, and to make formula versions immutable once released with an effective date, an approver and a change reason. The version is what gets stamped onto the batch, not a copy of it.

What goes wrong when you migrate formulas out of the workbook?

The load itself is fast. The discovery is not, and teams consistently budget the wrong one.

Every formula workbook we have opened carries logic that is not in any cell formula. Substitution notes in comment cells. Yield factors that only apply above a certain batch size. A row that says do not use Supplier B lot after February. A tab that is dead but that somebody still opens out of habit. None of that survives a straight import, and the technical director does not think to mention it because to him it is not logic, it is just how the product is made.

The second failure is version ambiguity. Ask which formula version was active on a given date last August and most operations cannot answer, because the workbook has no history beyond file names. That matters at migration because you are deciding what to seed as version one, and if you seed the current state you lose the ability to reconstruct why a batch from six months ago looks the way it does. Where a trace question could reach back into that period, keep the historic batch tickets addressable even if you do not model their formulas fully.

Third, raw material master data is usually worse than the formula data. The same material appears three times with different supplier names, CAS numbers are missing or transposed, and hazard data was typed from supplier sheets years ago and never refreshed. Migrating formulas onto a dirty material master gives you clean formulas that produce wrong hazard bands. Clean the material master first, using extraction from incoming supplier safety data sheets with a mandatory human review step for anything that changes a classification.

Budget two weeks of discovery with whoever owns the workbook before any code, and run the old workbook in parallel until both agree on a full batch cycle.

Why do scale, instrument and SDS authoring integrations break after launch?

Chemical builds live or die at their integration boundaries, and the boundaries in this industry are old, serial and undocumented. Three break patterns recur.

Scales and indicators. A networked Mettler Toledo or Rice Lake indicator with a clean protocol is straightforward. A serial indicator bolted to a mixer installed decades ago is bespoke work, and the failure after launch is usually physical rather than software: a cable gets moved, a converter loses power, an operator swaps to a spare indicator that was never configured. The system has to degrade gracefully into manual weight entry with an explicit flag, rather than blocking the batch, or the plant will route around it permanently within a week.

Hazard content services. Keeping a Chemwatch, MSDSonline, SAP EHS or Sphera subscription is normally the right call, because their rule engines and regulatory content libraries are worth the fee. The break happens when composition is pushed in a shape their API did not expect, or when a component maps to no entry in their library and the call fails silently. Every push needs a response check and a queue for compositions that did not classify cleanly, otherwise a sheet quietly stops regenerating and nobody notices for a quarter.

Accounting. Writing inventory movements and finished goods cost into QuickBooks, Sage or NetSuite is fine until a period closes and a correction has to land in a closed period. Agree that behaviour with your controller during design, not in month four.

What happens when the SDS and shipping papers are not driven from the released formula?

This is the compliance gap that actually costs money in chemical manufacturing, and it opens the moment authoring is a separate manual step.

The mechanism is mundane. Somebody in environment, health and safety types the composition into the authoring tool once. When the formula moves, nobody retypes it, because retyping across dozens of products in multiple languages is a long job that never reaches the top of anyone list. Section 3 drifts from reality. The classification band moves without the sheet moving with it, and now the label and the transport documents inherit the same stale answer, because at the shipping desk the classification is decided by someone reading the sheet.

The fix is to make the safety data sheet a generated artefact rather than a maintained document. Composition flows from the released formula version, the classification call goes out to your hazard content subscription, the returned phrases render into your own sections, and the output is versioned against the formula version that produced it. Change the formula, the sheet regenerates into an approval queue. There is no human retyping step left to skip.

The same source should drive transport classification, so the shipping paper, the label and the sheet cannot disagree. Threshold reporting then becomes a query rather than an annual scramble, because daily on hand positions per material per location are already in the database.

Should you build custom or configure what you already own?

Configure, genuinely, if you run one plant, under roughly 15 to 20 active products, blend to fixed recipes rather than to spec, and ship domestically into lightly regulated channels. BatchMaster, Deacom and Datacor Chempax are built for process manufacturers and they model formulas far better than a general accounting first system does. In the quotes our clients have shown us they land in a range that gets you most of the way at that size, and a build to serve twenty products is ego rather than economics.

Configure also if the pain you actually have is authoring rather than propagation. If your formulas genuinely do not move much and your problem is producing sheets in more languages, a hazard content subscription plus a competent coordinator is the cheaper answer.

Build when the formula model is the constraint. The clearest signal is a vendor demo that ends with you could handle that with a custom field, said more than twice. Others: a trace request takes more than an hour, you employ somebody whose real job is retyping numbers between systems, or you have customised your existing system so heavily that you are afraid to upgrade it, which means you are already paying to maintain custom software on somebody else platform.

The hybrid is usually right. Keep accounting. Keep the hazard content subscription. Build the formula, batch, quality control and generation layer, and integrate outward.

How do hidden costs get into the quote?

Instrument integration is the biggest one and it is almost never priced per device. Ask for a line item per scale, per indicator and per instrument, with the protocol named. A quote that says scale integration as one line has not looked at your plant floor.

Multi jurisdiction sheets are the second. Shipping into the European Union, Canada or Latin America means language variants and jurisdiction specific classification, and that is not a translation cost, it is separate content and separate rules per destination.

Validation is the third, and it cuts both ways. If you touch pharmaceutical intermediates or regulated food contact, 21 CFR Part 11 requirements add audit trail rigour, electronic signature controls and installation, operational and performance qualification documentation. In our experience that is a meaningful adder on the affected modules and it is not optional when it applies. Equally, a firm that reflexively quotes Part 11 at a lubricant blender who never touches regulated product is padding. Make them tell you what does not apply to you.

Then the quiet ones: continuous or semi continuous process rather than discrete batch changes the data model and the genealogy arithmetic, formula discovery runs long when the workbook has undocumented logic, and rugged tablets, label stock and barcode hardware for the plant floor are capital nobody put in the software budget.

What separates a build that works from one that fails here?

The builds that work capture consumption at the charge rather than at the batch. The operator scans the lot barcode on the tote before charging and the scale writes actual weight against that specific lot, so when a tote runs out mid charge the system records two consumption lines automatically. That single design choice turns genealogy from a story a person reconstructs out of banker boxes into a graph the database walks, and it is the difference between a trace answered in seconds and a trace answered in days.

They show provenance everywhere. A generated safety data sheet should name the formula version that produced it. A certificate of analysis should name the results and the spec revision it was evaluated against. When a customer or an auditor asks how a number was arrived at, the answer is on screen.

They keep the human in the release decision. Prediction over historic batch results can usefully tell a plant manager which of this week batches deserve a second look before the panel runs, and on high volume blending we have seen that surface a real signal. It should never make a release call, and on low volume specialty work with a handful of batches a year per product there is not enough history to bother.

They survive the plant. Offline queueing on tablets, because the blending floor network will drop and an operator will not wait. Graceful fallback when an indicator is unreachable. Screens usable with gloves on.

And they settle ownership before kickoff. You are encoding the formulas that are effectively the company. The contract should say you hold the repository, the deployment and the right to bring in another firm, and a developer who hesitates on that is describing their retention strategy rather than their engineering.