Feed Ration Optimization

Formulate a least-cost feed blend that meets energy, protein, and amino acid requirements. Edit ingredient costs, nutrient analyses, and diet constraints on the Model sheet.

Overview

Feed Ration Optimization determines the least-cost mixture of feedstuffs that satisfies every nutritional requirement for livestock. Formulating animal rations is one of the foundational real-world applications of linear programming: livestock producers must balance energy, crude protein, and essential amino acids (such as lysine, methionine, and tryptophane) at minimum total ingredient cost while ensuring the total blend proportions sum exactly to 100%.

The workbook ships with a classic agricultural economics benchmark from W.C. Pfeiffer (1976), University of Guelph Bulletin AEEE/76/12, Table 8. In this sample, a hog feeder selects from six candidate feedstuffs (barley, corn, wheat, oats, fish meal, and soybean meal) to produce a 100-pound hundredweight (cwt) ration meeting five nutrient floors. Under default conditions, the least-cost blend combines 89.36% wheat and 10.64% fish meal at an optimal ration cost of $4.26 per cwt.

• Python for Excel workbook

Before you start

Requirements. Excel with Python for Excel. The template uses only the sheets in this workbook and requires no external data connection. To install and open the add-in, and for how Edit and App mode work, see the Boardflare Notebook Reference.

Data shape. Enter variables, objective coefficients, bounds and constraint coefficients on the Model sheet with headers in row 4. One ingredient per row. Objective is cost in dollars per cwt divided by 100 (cost per cwt per percent of ration). Nutrient columns F to J are percent nutrient in ingredient divided by 100. The template reads rows 7 to 36, so it holds up to 30 variables, and columns F to O, holding up to 10 constraints. Blank variable rows and blank constraint columns are ignored.

Sample data. Model A7:K12 holds 6 sample variables and 6 constraints. Rows 13 to 36 are empty and ready for more. The Settings sheet holds goal set to Minimize and time_limit set to 20.

Roles. The agricultural nutritionist or farm manager edits ingredient purchase costs, nutrient analyses, and minimum ration specifications on the Model sheet. The workbook runs locally and publishes the optimal ration plan, binding nutrient floors, and economic shadow prices to the Results sheet.

Workbook tour

Sheet and range	What it holds	Kind
Model, A4:O36 (Ingredient, Objective, Lower, Upper, Integer, limits…)	The problem definition: variables, bounds, objective coefficients, and constraint matrix. Rows 7 to 12 hold the sample variables, and rows 13 to 36 are free.	Editable input
Settings, A4:B24 (Setting, Value)	Named settings: goal in B5 (Minimize), and time_limit in B6 (default 20 seconds, max 50).	Editable input
Results, A4:C12 (Result, Value, Status)	Solution summary: status, message, Ration cost, variables in the plan, binding limits, and per-solve checks: largest limit violation, duality gap (LP) or MIP gap (MILP). Checks show PASS or FAIL in Status.	Output
Results, A15:E45 (Ingredient, Percent of ration, Contribution to Ration cost, Reduced cost, Position)	Optimal plan: variable amounts, objective contributions, reduced costs, and status in the plan.	Output
Results, A49:H59 (Limit, Type, Used, Limit value, Slack, Binding, Shadow price, Suggested limit)	Constraint analysis: usage, slack, binding status, shadow prices, and suggested limit relaxations if infeasible.	Output

Using the template

What the percent amounts mean

In this workbook, each decision variable represents the percentage of that ingredient in 100 pounds (one hundredweight or cwt) of finished ration. Because the amounts represent percentages: - The optimal values in the Plan table (Percent of ration) directly represent pounds of ingredient per 100 pounds of finished feed. For example, 89.36% Wheat means 89.36 pounds of wheat in a 100-pound batch. - All ingredient percentages must sum to exactly 100%. This is enforced by the Total (percent) equality constraint (= 100). - The Objective column on the Model sheet is entered as the ingredient cost in dollars per cwt divided by 100 (which is dollars per cwt per 1% inclusion). For example, Wheat priced at $3.50/cwt is entered as 0.035. When multiplied by the 89.36% inclusion rate, Wheat contributes \(89.36 \times 0.035 = \$3.13\) to the 100-pound ration cost. The sum of all contributions gives the total finished ration cost in dollars per cwt ($4.26/cwt). - Nutrient contents on the Model sheet are entered as the nutrient’s percentage in the raw ingredient divided by 100. For example, Wheat contains 75% TDN, entered as 0.75. The resulting weighted sum directly represents the total percentage of that nutrient in the finished ration (e.g. \(89.36 \times 0.75 + 10.64 \times 0.60 = 73.40\%\) TDN), which must meet or exceed the dietary floor of 70%.

First-use walk-through with the sample feed problem

1. Open the workbook and inspect the Model sheet:
   • Rows 7 to 12 contain six candidate feedstuffs: Barley ($4.20/cwt), Corn ($4.80/cwt), Wheat ($3.50/cwt), Oats ($3.75/cwt), Fish meal ($10.60/cwt), and Soya meal ($11.30/cwt).
   • Columns F through J specify five minimum nutrient floors: Total Digestible Nutrients (TDN \(\ge 70\%\)), Crude protein (\(\ge 16\%\)), Lysine (\(\ge 0.80\%\)), Methionine (\(\ge 0.30\%\)), and Tryptophane (\(\ge 0.12\%\)).
   • Column K enforces the mixture balance: Total (percent) \(= 100\%\).
2. Switch to the Results sheet. The summary reports optimal status with an optimal Ration cost of $4.2553/cwt (rounds to $4.26).
3. Review the Plan table:
   • Wheat: 89.36% of the ration, contributing $3.13/cwt.
   • Fish meal: 10.64% of the ration, contributing $1.13/cwt.
   • Barley, Corn, Oats, Soya meal: 0.0% (at lower limit).
4. Review the Limits table:
   • Lysine (%): exactly 0.80% used with 0.0 slack (Binding: Yes, shadow price $1.51).
   • Total (percent): exactly 100.0% used with 0.0 slack (Binding: Yes, shadow price $0.03).
   • TDN, Crude protein, Methionine, and Tryptophane: all exceed their required minimums (slacks of 3.40%, 1.64%, 0.18%, and 0.02% respectively) and show Binding: No.

How to replace the ingredients with your own

1. On the Model sheet, replace the names in column A (rows 7 to 12) with your available feedstuffs (e.g. Sorghum, Canola meal, Alfalfa).
2. Enter each feedstuff’s purchase cost in column B as dollars per cwt divided by 100 (e.g. $5.50/cwt is entered as 0.055).
3. If an ingredient has availability limits, set a lower bound in column C (e.g. minimum 5% to use up on-farm stock) or an upper bound in column D (e.g. maximum 15% due to fiber or palatability limits).
4. Update columns F through J with the laboratory nutrient analyses of your ingredients, entering each percentage divided by 100 (e.g. 14% protein is entered as 0.14).
5. In column K (Total (percent)), ensure each ingredient row has a coefficient of 1.0.
6. To add more ingredients, use rows 13 through 36.
7. Switch to the Results sheet to view your revised optimal ration.

How to add a maximum-inclusion limit (the wheat cap)

In livestock feeding, relying too heavily on a single grain can cause digestive or palatability issues. In Pfeiffer’s Example 2, the nutritionist caps wheat inclusion at a maximum of 30%:

1. Locate the first spare constraint column on the Model sheet: column L.
2. Set the header cell L4 to Wheat limit (or Max wheat).
3. Set the relation type in cell L5 to <=.
4. Set the limit value in cell L6 to 30.
5. In column L, enter 1.0 in the Wheat row (cell L9). Leave all other ingredient cells in column L blank (treated as 0).
6. Switch to the Results sheet. The solver finds the new optimal ration:
   • Barley: 46.96%
   • Wheat: 30.00% (held at the 30% maximum-inclusion limit)
   • Oats: 13.40%
   • Fish meal: 9.64%
   • Ration cost: $4.5466/cwt ($4.55).
   • Capping wheat forces the inclusion of barley and oats, increasing the ration cost from $4.26 to $4.55 per cwt.

Change the goal and time limit

1. Select the Settings sheet.
2. In cell B5 (goal), verify that the setting is Minimize.
3. In cell B6 (time_limit), enter the maximum solve duration in seconds (default is 20 seconds, capped at 50).

Inputs and settings

Model sheet parameters

• Ingredient (Column A, rows 7 to 36): Name of each feedstuff. Rows with blank names in column A are ignored.
• Objective (Column B, rows 7 to 36): Purchase cost per cwt divided by 100 ($/cwt per 1% inclusion).
• Lower (Column C, rows 7 to 36): Minimum percentage inclusion (default 0%).
• Upper (Column D, rows 7 to 36): Maximum percentage inclusion (leave blank for unlimited).
• Integer (Column E, rows 7 to 36): Optional integer flag. For feed blending, amounts are continuous percentages; leave blank.
• Nutrient floor columns (Columns F to J):
  • Header (Row 4): Nutrient name and unit, e.g. TDN (%), Crude protein (%), Lysine (%).
  • Type (Row 5): >= for nutrient minimums.
  • Limit (Row 6): Minimum percentage of the nutrient required in the finished ration.
  • Coefficients (Rows 7 to 36): Percent of the nutrient in the raw feedstuff divided by 100.
• Total blend column (Column K):
  • Header (Row 4): Total (percent).
  • Type (Row 5): =.
  • Limit (Row 6): 100.0.
  • Coefficients (Rows 7 to 36): 1.0 for all candidate ingredients.
• Spare constraint columns (Columns L to O): Up to 4 additional diet constraints (such as ingredient caps or nutrient ceilings).

Settings sheet

• goal (cell B5): Optimization sense. Set to Minimize for least-cost formulation.
• time_limit (cell B6): Solver timeout in seconds (default 20, max 50).

How results are calculated

1. Read model data: The template reads active ingredient rows from Model A7:E36 and constraint definitions from Model F4:O36.
2. Validate inputs: Confirms that ingredient costs and nutrient levels are valid numbers, lower bounds do not exceed upper bounds, and constraint types are valid (<=, >=, =).
3. Formulate LP: Builds the linear program minimizing total ration cost subject to the nutrient floors and 100% mixture equality.
4. Solve: Executes the HiGHS simplex solver via SciPy.
5. Verify solution: Evaluates the plan against raw ingredient data to verify that every nutrient floor is met and percentages sum to 100%.
6. Sensitivity analysis: Calculates reduced costs for excluded feedstuffs and shadow prices for active diet constraints.
7. Infeasibility diagnosis: If constraints conflict (e.g. a protein floor higher than any ingredient can provide), computes a small weighted relaxation of the limits that restores feasibility and displays the target values in the Suggested limit column.

Reading the results

Summary table (Results, A4:B10)

• Status: Solver outcome (optimal, infeasible, unbounded, etc.).
• Message: Readable summary of the solver result.
• Ration cost: The minimum total cost of 100 pounds of finished feed in dollars per cwt.
• Variables in the plan: Count of feedstuffs selected in positive amounts.
• Binding limits: Count of constraints where usage exactly equals the specified limit.
• Gap: Blank for continuous linear programs solved to exact optimality.

Plan table (Results, A13:E43)

• Ingredient: Feedstuff name.
• Percent of ration: Recommended percentage inclusion (pounds per 100 lbs of feed).
• Contribution to Ration cost: Cost contributed by this ingredient ($ $).
• Reduced cost: How much cheaper an unused ingredient’s price must become (in the same units as the Objective column, dollars per cwt per percent of the ration; multiply by 100 for dollars per cwt of that ingredient) before it enters the least-cost blend.
• Position: in the plan (included), at lower limit (excluded or at minimum), or at upper limit.

Limits table (Results, A47:H57)

• Limit: Name of the nutrient or balance rule.
• Type: >=, <=, or =.
• Used: Actual percentage delivered by the optimal mix.
• Limit value: The required threshold from Model row 6.
• Slack: Excess above minimum for >= limits, or unused room for <= limits.
• Binding: Yes if the constraint limits the plan (slack is 0); No if surplus exists.
• Shadow price: The rate of change in ration cost per unit change in the constraint limit.
• Suggested limit: Recommended relaxed limit if the model is infeasible.

What shadow prices mean in plain words

A shadow price tells the farm manager the exact economic cost of tightening or relaxing a nutritional specification:

• For a nutrient floor (>=), a positive shadow price represents the increase in ration cost ($ per cwt) incurred if you raise the required nutrient minimum by 1 percentage point:
  • In the baseline ration, Lysine (%) has a shadow price of $1.51 per cwt per percentage point. If the nutritionist raises the lysine requirement by 0.01% (from 0.80% to 0.81%), the ration cost will increase by approximately \(0.01 \times \$1.51 = \$0.0151\) (about 1.5 cents per cwt). Conversely, relaxing lysine to 0.79% would save 1.5 cents per cwt.
• For non-binding nutrients (TDN, Crude protein, Methionine, Tryptophane), the shadow price is $0.00. These nutrients are already supplied in surplus by the wheat and fish meal chosen to satisfy lysine and total weight; tightening them slightly has zero effect on ration cost.
• For an ingredient maximum (<=), such as the 30% wheat cap in Example 2, the shadow price is -$0.0091 per cwt per percentage point (-0.91 cents per cwt). Tightening the cap (reducing allowed wheat from 30% to 29%) forces substitution with more expensive grains, increasing ration cost by 0.91 cents per cwt. Relaxing the cap (allowing 31% wheat) lowers ration cost by 0.91 cents per cwt.

Saving, sharing and App mode

What persists. All ingredient names, costs, bounds, nutrient analyses, and constraint thresholds are saved directly in the workbook.

What is recalculated. When opened in Excel with the add-in active, the embedded Python notebook recalculates the least-cost formulation and refreshes the Results sheet.

App mode. App mode presents the solved plan and limits cleanly without displaying Python calculation code.

Errors, empty states and limits

Solver statuses

• optimal: The lowest-cost nutritionally compliant ration was found.
• infeasible: No blend of ingredients can satisfy all nutrient specifications and weight restrictions simultaneously. Check the Suggested limit column for one small relaxation of the limits that restores feasibility.
• unbounded: Ingredient costs or limits allow an infinite or meaningless solution (e.g. missing equality balance).
• time_limit_feasible / time_limit_no_solution: Solver reached the time limit before finishing.
• error: Model inputs contain text where numbers are expected, inverted bounds, or duplicate ingredient names.

Empty state

If no ingredients or constraints are specified on the Model sheet, the Results summary displays an error indicating that at least one ingredient and one constraint are required.

Limitations and when to use this

• The model reads up to 30 variables (Model rows 7 to 36) and up to 10 limits (columns F to O).
• Rows below row 36 and columns past column O are not read, and the status line notes it if data is found beyond the range.
• Linear programming (LP) and mixed-integer linear programming (MILP) with continuous or integer variables and linear relations only.
• Solves are capped at a 50-second timeout.
• Shadow prices and reduced costs are computed only for models without integer variables.
• When a model is infeasible, the suggested relaxation repair is one of possibly several valid ways to restore feasibility.

Customizing as an author

This template is generated from a structured specification. Do not hand-edit notebook.py or workbook.py. The generation workflow:

1. spec.py: Defines metadata, ingredient variables, nutrient constraints, capacity, and tracking issue #240.
2. Generator: Run uv run python templates/optimization/skeleton/generate.py templates/optimization/feed-ration to update notebook.py, workbook.py, and the generated regions of index.md.
3. Workbook build: Run uv run python .agents/skills/python-template/scripts/generate-workbooks.py templates/optimization/feed-ration to create feed-ration.xlsx.
4. Validation: Test offline with uv run python .agents/skills/python-template/scripts/run-scenarios-offline.py --template feed-ration.

Developer reference

Notebook implementation

The generated notebook coordinates workbook input reading, model validation, solver invocation, and publication through the Boardflare runtime:

inputs = bf.inputs(
    model=bf.ref("Model!A4:O36", headers=False),
    settings=bf.ref("Settings!A4:B24", headers=True),
)

Problem structure is parsed from the grid into a structured Model instance by parse_model. It extracts variable bounds, objective coefficients, constraint types, and limits, then calls solve_model:

result = solve_model(parsed_model, time_limit=time_limit)

The resulting plan, summary statistics, and limits analysis are formatted into pandas DataFrames and published to the anchored spill ranges on the Results sheet:

publication = bf.publish(
    outputs={
        "summary": summary_table,
        "plan": plan_table,
        "limits": limits_table,
    }
)

How the calculation/model works

The feed ration formulation solves a standard linear program:

\[\begin{aligned} \text{minimize} \quad & \sum_{j=1}^{n} c_j x_j \\ \text{subject to} \quad & \sum_{j=1}^{n} A_{ij} x_j \ge b_i \quad \forall i \in \text{Nutrient floors} \\ & \sum_{j=1}^{n} x_j = 100 \\ & x_j \ge 0 \quad \forall j \in \{1, \dots, n\} \end{aligned}\]

where: - \(x_j\) is the percentage of ingredient \(j\) in the finished ration. - \(c_j\) is the ingredient cost in dollars per cwt divided by 100 ($ per cwt per 1% inclusion). - \(A_{ij}\) is the percent content of nutrient \(i\) in ingredient \(j\) divided by 100. - \(b_i\) is the minimum required percent of nutrient \(i\) in the finished ration.

The solver computes primal decision variables \(x_j\) and dual shadow prices \(y_i = \partial (\text{Cost}) / \partial b_i\) using HiGHS (scipy.optimize.linprog(method='highs')).

Validation / expected results

Validation basis. The baseline model and test scenarios are validated against W.C. Pfeiffer, “Basic Methods and Guidelines for Least-Cost Livestock Feed Formulation”, School of Agricultural Economics and Extension Education, University of Guelph, Extension Bulletin AEEE/76/12 (1976), Table 8 (available at https://ageconsearch.umn.edu/record/245014/files/guelph-aeee-76-12.pdf). The ingredient costs, nutrient analyses, and minimum floors in spec.py were re-checked against Table 8 in that PDF. Every expected value was computed with scipy.optimize.linprog(method="highs") directly on the Table 8 data, and each quoted shadow price was confirmed by perturbing its limit by plus and minus 0.001 and checking that the forward and backward differences agree. The optima asserted in the scenarios are unique, so no shadow price was left out for degeneracy.

• Example 1 (Baseline): The published optimum reports Wheat 89.4%, Fish meal 10.6%, and Ration cost $4.26 per cwt. Independent SciPy HiGHS calculation on the raw data yields Wheat 89.3617%, Fish meal 10.6383%, and Ration cost $4.255319 per cwt, which matches the published optimum to rounding. Table 9 in the bulletin reports nutrient “Analysis” values that do not agree with Table 8. This template does not reproduce them; it evaluates nutrient intakes directly from the Table 8 analyses.
• Example 2 (wheat-cap-30): When wheat inclusion is capped at \(\le 30\%\), the published bulletin reports an approximate mix of Oats 13%, Barley 47%, Wheat 30%, Fish meal 10%, at a cost of $4.55 per cwt. However, this published printed mix is rounded and is mathematically infeasible against the TDN floor (\(0.70 \times 47 + 0.75 \times 30 + 0.66 \times 13 + 0.60 \times 10 = 69.98\% < 70.0\%\)). Solving the exact LP with HiGHS yields the true optimum: Barley 46.9588%, Wheat 30.0000%, Oats 13.4021%, Fish meal 9.6392%, with optimal cost $4.546598 per cwt. These values match the published mix to rounding, and the template asserts these exact SciPy values.
• Nutrient floor increase (higher-protein): Raising the Crude protein floor from 16% to 18% increases the ration cost to $4.3038 per cwt (Wheat 88.68%, Fish meal 11.32%), activating the protein constraint with shadow price $0.1340 per percentage point.
• Infeasible formulation (infeasible-protein): Requiring 80% crude protein is provably infeasible because the highest-protein ingredient (Fish meal) contains only 65% protein. The solver reports infeasible and suggests relaxing the crude protein floor to 75.83%. That suggestion is not a usable target: the engine’s diagnostic repair also relaxes the Total (percent) row, to 116.67%. With the other floors held at their values, the highest crude protein the ingredients can reach while the Total stays at 100% is 55.0% (Fish meal 33.33%, Soya meal 66.67%).
• Ingredient removal (drop-fish-meal): Removing fish meal by clearing its row forces substitution with soybean meal (Wheat 70.59%, Soya meal 29.41%), increasing the ration cost to $5.7941 per cwt.

Test scenario	Edits	Expected outcome
Baseline	None (sample data)	Status optimal, Ration cost $4.2553/cwt; Wheat 89.36%, Fish meal 10.64%; Lysine binding (Yes, shadow price $1.51); Total weight binding (Yes, shadow price $0.030)
wheat-cap-30	Model!L4 = Wheat limit, Model!L5 = <=, Model!L6 = 30, Model!L9 = 1	Ration cost $4.5466/cwt; Barley 46.96%, Wheat 30.00%, Oats 13.40%, Fish meal 9.64%; Wheat limit binding (Yes, shadow price -$0.0091); TDN binding (Yes, shadow price $0.0737); Lysine binding (Yes, shadow price $1.5515)
higher-protein	Model!G6 = 18	Ration cost $4.3038/cwt; Wheat 88.68%, Fish meal 11.32%; Crude protein binding (Yes, shadow price $0.1340)
infeasible-protein	Model!G6 = 80	Status infeasible; plan values blank; Suggested limit for Crude protein displays 75.83%
drop-fish-meal	Clear Model!A11:O11	Ration cost $5.7941/cwt; Wheat 70.59%, Soya meal 29.41%; Lysine binding (Yes, shadow price $4.5882)

Generated-region policy

notebook.py and workbook.py are generated whole by generate.py. In index.md, content between <!-- BEGIN GENERATED: <region> --> and <!-- END GENERATED: <region> --> markers (frontmatter, tour, data-shape, sample-data, and limits) is generated deterministically from spec.py. All text outside these markers is hand-authored documentation.

Changelog

2026-10-24

• Initial release of the Feed Ration Optimization template based on W.C. Pfeiffer (1976), Table 8 (Issue #240).
• Hand-authored user guide covering percentage conventions, ingredient customization, wheat capping, and nutrient shadow price interpretations.
• Implemented and verified baseline and scenario test vectors with independent SciPy HiGHS calculations.