Mathematical optimization: what should a pharma API plant make?

This is Part 2 of a series on mathematical optimization in business. Part 1 explains what it is and why it matters.

An active pharmaceutical ingredient (API) plant is a good first example, because almost everything about it is shared and scarce.

One site makes many molecules on the same reactors and dryers. All of them send waste to the same effluent treatment plant, which can only handle so much. Some raw materials are imported and rationed. Every switch from one product to another means days of cleaning before the next one can start. And there are contracts the plant has to supply whatever else happens.

So every year someone has to answer a simple question: what should we make, and how much of each?

The plant

The model has six products. At one end is Metformin: high volume, cheap, earning ₹65 a kg. At the other is Sertraline: low volume, expensive, earning ₹5,500 a kg. In between are two sartans that share an imported starting material, Pantoprazole and Atorvastatin. The numbers are made up, but the shape of the problem is real.

We have setup the problem on a spreadsheet (link down below) so that you can grasp the problem and work out it parallelly as you read this.

The rule of thumb

Meet the contracts first, then fill the plant with the highest-margin products until something runs out. It’s what most planners would do, and it’s easy to defend in a meeting.

That plan earns an EBITDA of about ₹3.9 crore.

The optimal plan

Solver, working on the same plant with the same limits, finds a plan that earns about ₹7.3 crore. That’s almost double.

It gets there by doing the opposite of what feels right. It makes more than three times as much Metformin, the lowest-margin product, and cuts Sertraline, the highest-margin one, by about two thirds.

Why

The dryer. Sertraline needs 260 dryer hours a tonne. Metformin needs 2. Measured per dryer hour, Metformin earns about ₹32,500 and Sertraline about ₹21,000. The rule of thumb filled the dryer with Sertraline first and starved everything else.

You might think the fix is a smarter rule: rank products by margin per dryer hour instead of per kg. I tried it, and it does worse, about ₹3 crore. Push Metformin that hard and the effluent plant fills up, so the bottleneck has simply moved.

In the optimal plan, three limits run full at the same time: the dryer, the effluent plant and the imported starting material. No ranking of products can balance three bottlenecks at once. That’s exactly the job a solver is built for.

Where to spend money

The model also tells you what each limit is costing you. In this case, one more dryer hour would add about ₹19,000 of EBITDA. One more kilolitre of effluent capacity would add about ₹2,900. More reactor time would add nothing, because the reactors aren’t full.

That last one matters. The reactors are the most visible, expensive equipment in the plant, and the obvious place to ask for capex. In this plant, money spent on them would earn nothing until the dryer and the effluent plant are fixed.

Try it yourself

Get the model: EBITDA Maximizer for an API plant (Excel + Solver). It’s free, or pay what you like if it’s useful.

It opens on the rule-of-thumb plan. Go to Data > Solver > Solve and watch it find the better one. Then change a price, a contract or a capacity and solve again. It’s worth trying a few, because the answer often changes in ways you wouldn’t guess.