Formulating with a spreadsheet: constraints, least cost, substitution of raw materials
Pearson's square from level 11 balances protein between two ingredients; it knows nothing about price, lysine or the inclusion limit of cottonseed cake. As soon as a workshop has eight or ten raw materials whose prices change every month, you need a tool that holds all the constraints at once and finds the cheapest formula that respects them all. That tool is an ordinary spreadsheet, with its sum-of-products function and its solver. This lesson builds the sheet line by line, solves a complete example in "units" (you will put in your own prices), and shows what the solver sees that the eye does not: the true saving of a substitution, and its break-even point.
15 min read · 5-question quiz at the end
By Abdul Hussein ChoumanFounder of NEXTEKCI · Agnibilékrou hatchery since 2006This lesson is part of Making feed on the farm, expert level specialisation. It opens to every registered student, from free registration. Levels 1 to 5 and the tracks remain readable by everyone.
Pearson's square from level 11 balances protein between two ingredients; it knows nothing about price, lysine or the inclusion limit of cottonseed cake. As soon as a workshop has eight or ten raw materials whose prices change every month, you need a tool that holds all the constraints at once and finds the cheapest formula that respects them all. That tool is an ordinary spreadsheet, with its sum-of-products function and its solver. This lesson builds the sheet line by line, solves a complete example in "units" (you will put in your own prices), and shows what the solver sees that the eye does not: the true saving of a substitution, and its break-even point.
- ✓Build a formulation sheet: ingredients in rows, nutrients in columns, totals by sum of products, minimum and maximum targets
- ✓Write the constraints of a formula (total, nutrients, inclusion limits) and run the solver for least cost
- ✓Calculate by hand the effect of a substitution (cassava for maize) on nutrients and on cost, and find its break-even price
- ✓Read a solver result: binding constraints, marginal cost of a nutrient, infeasible solution, rounding
- Why the spreadsheet, and what exactly it does
- Building the sheet, line by line
- Running the solver: variables, objective, constraints
- Substitution by hand: understanding what the solver does
- Reading the solver's answer like an expert
- The constraint sheet: a model to copy
- Going further
- •Least-cost formulation looks for the cheapest formula that respects all the constraints at once; the spreadsheet calculates it with SUMPRODUCT and its solver (linear method).
- •Sheet: one row per ingredient (kg, as-fed price delivered to the workshop, min, max, contents), one column per nutrient, Total row, Target min and max rows, Cost cell; example C2 = 184.46 u per 100 kg, 0.60 u per 1,000 kcal, 9.22 u per kg of protein.
- •Substitution by hand: gross effect, protein correction (soya), energy correction (oil), compared cost; 10 kg of cassava at 0.70 u cost + 1.10 u once the formula is corrected; break-even price 0.59 u, about 60% of the price of maize.
- •Reading the solver: binding constraints (energy, protein, methionine), marginal cost (0.25 u per 100 kg per hundredth of a point of methionine), infeasible solution = contradictory targets to relax, rounding closed back to 100 kg, inclusion limits never negotiated.
- •Routine: monthly recalculation with prices and analyses up to date; change in the workshop only if the saving exceeds 1 to 2%; each formula has a dated version.