What is the standing recipe costing you a tonne?
Put in the price and nutrients of each ingredient and get back the cheapest recipe that meets every minimum. Six ingredients, three nutrient minimums and a tonne to fill. The standing recipe over-delivers protein by 1.6 points, which is $12.39 a tonne nobody is being paid for.
Operations Starter Optimization free
After you install, this is the model to open.
The Cheapest Blend That Meets the Spec
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- Cheapest compliant blend
- $288.39 a tonne, against the standing recipe at $300.78
- The saving
- $12.39 a tonne for changing nothing but proportions
- Where it was going
- 1.6 pts of protein over-delivered: 17.6% against the 16% spec
- The solved mix
- 16.0% protein exactly on spec, wheat bran at its 250 kg cap
One tonne of dairy concentrate, six raw materials, and three numbers on the specification that have to be met: 16 percent protein, 8 percent fiber, and 11.4 megajoules of energy a kilo. The sheet opens on the mill's standing recipe, the one that has been in the system for three years, and it costs $300.78 a tonne. Click Run and the optimizer meets the same specification for $288.39, which saves $12.39 a tonne.
On the forty tonnes a week this mill runs that is about $25,800 a year, for changing nothing but the proportions. Look at where the money was going. On the opening recipe the Protein (%) line reads 17.6 against a specification of 16, so the mix has been giving away 1.6 points of the most expensive nutrient on the sheet, every batch, for three years.
That is what an old recipe does: it was right for the prices of the year it was written, and nobody looked again when soya moved. The optimized blend lands protein at exactly 160 kilos and fiber at exactly 80, and the Optimization Report marks both of those Binding, which is the sheet naming the two limits that decided the answer. Energy comes out at 12,181 megajoules against the 11,400 required, so energy was never the constraint here.
Wheat bran sits on its 250 kilo handling ceiling, which is the third limit doing real work, and molasses and the mineral premix sit on their floors because the mixer and the vet put them there rather than because they are cheap. The blend it returns is 377 kilos of maize, 154 of soya bean meal, 250 of wheat bran, 174 of sugar beet pulp, 25 of molasses and 20 of premix.
Run it again next month with today's soya price and see how far the blend moves, because the whole point is that the answer has a shelf life measured in weeks. What the model does not know is palatability, how the mix flows through your own plant, or that a large swing in a ration should be introduced over several days rather than on Monday morning.
To make it yours, put your delivered prices in the Cost a kg column, your own raw material analyses in the three nutrient columns, your specification in the three at-least figures, and your handling limits in the constraint boxes.
The model
It arrives on a tab called Template: Cheapest Compliant Blend, carrying these columns:
- kg in the batch
- Cost a kg ($)
- Cost ($)
- Protein %
- Fiber %
- Energy (MJ/kg)
- Energy (MJ)
with the model computed beside the data:
| Batch size (kg) | 1,000 |
| Protein (kg) | 176.1 |
| Fiber (kg) | 81.95 |
| Energy (MJ) | 12,193 |
| Protein (%) | 17.61 |
| Cost a tonne ($) | 300.8 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Will the changeover fit inside the weekend?How Much Buffer Does the Changeover Need?
- Same average fill. So why is one supplier a risk?Which Supplier Is More Consistent?
- Two crews average the same. Is one of them better?Four Shifts, Ranked Output, One Question
- Does the new layout help on every shift?Layout and Shift: Which One Moves Output?
- How often does the line miss its monthly number?Will the Plant Make the Volume?
- How much cash does the bigger buy tie up?Which Season Plan Should the Chain Commit To?
Every model like this one, and the method behind them: Optimization in Google Sheets.