Feed Formulation in Excel Solver: A Reproducible Starter Model

Excel Solver can formulate a linear least-cost feed by minimizing a cost cell while changing ingredient proportions subject to nutrient and inclusion constraints. The most useful starter workbook makes every input, formula, unit, and constraint visible.

This guide builds a small reproducible model. Its ingredient names and nutrient coefficients are deliberately synthetic. The example teaches spreadsheet mechanics; it is not a complete animal diet or a nutritional recommendation.

Build a clearly labeled input table

Create the following table in cells A1:E4. Enter decision variables in column C as fractions, then format those cells as percentages. For example, enter 0.8 for an 80% inclusion, not 80. Protein coefficients in column D also use fractions. Energy coefficients are kcal/kg as-fed.

Row	A: Ingredient	B: $/metric tonne	C: Inclusion fraction	D: Protein fraction	E: Energy, kcal/kg
2	Teaching ingredient A	200	0.8	0.10	3,000
3	Teaching ingredient B	500	0.2	0.40	2,500
4	Teaching ingredient C	350	0	0.20	3,300

The initial values sum to 1 but do not satisfy every target. Solver will change C2:C4. Keep prices and nutrient inputs separate from these decision cells so a solve cannot accidentally alter the reference data.

Add four auditable result cells

Cell	Meaning	Formula
C6	Total inclusion	=SUM(C2:C4)
C7	Cost, $/metric tonne	=SUMPRODUCT(B2:B4,C2:C4)
C8	Protein fraction	=SUMPRODUCT(C2:C4,D2:D4)
C9	Energy, kcal/kg	=SUMPRODUCT(C2:C4,E2:E4)

Format C6 and C8 as percentages. Use ordinary numeric formatting for C7 and C9. A cost formula that divides by 100 again would understate the result because inclusion is already stored as a fraction.

Configure Solver

Enable the Solver add-in if it is not available, then follow Microsoft’s Solver instructions to set the objective and constraints. For this workbook:

1. Set objective C7 to Min.
2. Set changing variable cells to C2:C4.
3. Add C6 = 1 for the complete batch.
4. Add C8 ≥ 0.18 for the teaching protein minimum.
5. Add C9 ≥ 2,800 for the teaching energy minimum.
6. Add C2:C4 ≥ 0, or use the nonnegative-variables setting.
7. Select Simplex LP because every expression in this model is linear.

No practical ingredient maxima, minerals, amino acids, or other production constraints have been included. That omission is intentional for teaching and would be unacceptable for a complete production specification.

Use the known answer to audit the workbook

The optimum is A = 73.3333%, B = 26.6667%, C = 0%. It costs $280/t, supplies 18% protein, and supplies approximately 2,866.67 kcal/kg. This also matches the two-ingredient Pearson-square example.

You can verify optimality without trusting the software. Relative to all-A feed, let b and c represent the fractions of B and C. The protein constraint requires 0.30b + 0.10c ≥ 0.08. Cost is 200 + 300b + 150c. Substituting the protein boundary gives cost ≥ 280 + 50c, so adding C cannot beat $280 in this particular model. The A/B solution also passes the energy constraint.

If your answer differs, inspect fraction-versus-percent entries, formula ranges, the objective direction, and missing constraints. Do not just overwrite the result with the expected answer.

Add real-world detail without losing auditability

For a working model, add a labeled coefficient column and result cell for each required nutrient. Put lower and upper bounds in separate input cells. Use one consistent nutrient basis and energy system. Preserve the source and date for every ingredient value and requirement.

A fixed inclusion requires equality or matching minimum and maximum constraints. A maximum of zero excludes an ingredient. A missing coefficient is not evidence that the nutrient is absent. Validate the matrix before allowing a blank to become zero in a spreadsheet calculation.

Changing the problem to include nonlinear responses requires a different modeling decision. Do not choose Simplex LP for equations involving products of decision variables, variable denominators, or nonlinear performance responses. Solver returning a number is not enough; its assumptions must match the model.

Keep a release copy and a change record

Save the input version before solving. After solving, independently inspect the total, nutrient results, all bounds, and status. Protect formula cells from accidental editing and retain the date, price basis, specification source, and reviewer.

If no solution is found, use our infeasibility checklist. Avoid silently widening limits until the sheet produces a result.

Key takeaways

• Use SUMPRODUCT to link inclusions with cost and nutrient coefficients.
• Keep stored fractions consistent with displayed percentages.
• Use Simplex LP only for a genuinely linear model.
• Audit the solution and retain the inputs that produced it.

Frequently asked questions

Do I need macros for this example?

No. The model uses ordinary worksheet formulas and the Solver add-in.

Why did Solver include none of ingredient C?

C is not economically competitive under these particular prices and constraints. It could enter if inputs or requirements changed.

When should a team move beyond spreadsheets?

When maintaining shared ingredient values, versions, mill-specific data, permissions, and repeated scenario analysis becomes difficult to audit. Those workflow needs are separate from whether an individual workbook can solve a linear model.

Keep the transparent model, improve the workflow

You can start free with DietForge to manage formulation work in a shared application while keeping the same discipline around data, constraints, and review.