Alloy Blending

Optimization
Operations Research
Linear Programming
Choose which whole steel ingots to buy and how many tonnes of alloy and scrap to blend, so the batch meets its carbon and molybdenum targets at the lowest cost. Edit the Model sheet to test other ingots, prices or targets.

Alloy Blending

Choose which whole steel ingots to buy and how many tonnes of alloy and scrap to blend, so a 25 t batch meets its carbon and molybdenum targets at the lowest cost. The sample is a published steel mill problem with a known optimum.

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

Overview

A steel batch has to hit a target chemistry at the lowest possible cost. This template plans a 25 t batch that must contain 5% carbon and 5% molybdenum, which is 1.25 t of each. Four whole steel ingots are on offer. Each ingot is sold as one piece, so you buy it once or not at all. Three alloy grades and one grade of scrap can be bought in any amount.

With the sample data, the lowest cost is $8,495. It is reached by buying ingots 1, 2 and 4, plus 7.25 t of alloy 1, 0.25 t of alloy 3 and 3.5 t of scrap. The sample is adapted from a published steel mill problem, and the Developer reference explains how its numbers were checked.

Before you start

  • Excel with Python for Excel, as listed under Requirements.
  • Your ingot list. For each whole ingot, you need its weight in tonnes, its price per tonne, and its carbon and molybdenum percentages.
  • Your alloy and scrap list, with the price per tonne and the carbon and molybdenum percentages of each grade.
  • Your targets: the total tonnes of the batch, and the tonnes of carbon and of molybdenum it must contain.
  • Units. Enter weights in tonnes and prices in dollars per tonne. In the constraint columns, enter the tonnes of each element contained in one unit of the row. For an alloy bought by the tonne, that is its percentage as a decimal (8% is 0.08). For a whole ingot, it is the ingot’s weight times its percentage (a 5 t ingot at 5% carbon is 0.25).

Workbook tour

Sheet and range What it holds Kind
Model, A4:O36 (Material, Objective, Lower, Upper, Integer, limits…) The problem definition: variables, bounds, objective coefficients, and constraint matrix. Rows 7 to 14 hold the sample variables, and rows 15 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, Blend 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 (Material, Amount (units or tonnes), Contribution to Blend 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

Most of your work happens on the Model sheet. The notebook reads the Model and Settings sheets and writes the Results sheet, so after each change, check the new status in Results B5 before you read the results.

Using the template

Read the sample result

  1. Open the workbook and check that the Status in Results B5 shows optimal.
  2. Read the Blend cost in Results B7.
  3. Use the Plan table in Results A13:E43 to see the amount of each material. For an ingot, 1 means buy the whole ingot and 0 means skip it. For alloy and scrap, the amount is in tonnes.
  4. Use the Limits table in Results A47:H57 to confirm that each target is met.

Change a price or a target

  1. On the Model sheet, change an Objective value in column B to a new price for that material, or change a Limit in row 6 to a new target.
  2. Check the new status in Results B5, then read Blend cost in B7, the Plan table and the Limits table.

Add an ingot or alloy

  1. Type the name in column A of the next empty row. Rows 15 to 36 are free.
  2. Enter its Objective, Lower, Upper and Integer values. For a whole ingot, set Upper to 1 and Integer to 1. For an alloy or scrap grade, leave Integer at 0 and leave Upper blank.
  3. Enter its contribution to each constraint in columns F to O, using the units described under Before you start.

Try a what-if

Change the carbon target in Model G6 from 1.25 to 1.5. The batch now needs more carbon, and Blend cost rises to $9,630.

Inputs and settings

Data shape. Enter variables, objective coefficients, bounds and constraint coefficients on the Model sheet with headers in row 4. One material per row. Ingot rows are whole-ingot buy decisions (Integer, 1 = buy, 0 = skip) and their objective is the ingot price times its weight. Alloy and scrap rows are tonnes, bought in any amount. Each constraint column gives tonnes of steel, carbon or molybdenum per unit of the row. 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:H14 holds 8 sample variables and 3 constraints. Rows 15 to 36 are empty and ready for more. The Settings sheet holds goal set to Minimize and time_limit set to 20.

Whole ingots and tonnes. Ingot rows are yes or no decisions. The Plan table shows 1 when an ingot is bought and 0 when it is not. You cannot buy part of an ingot. Alloy and scrap rows are amounts in tonnes, and they can take any value from their Lower bound (0 in the sample) upward.

Targets. Each constraint column is a target, and its Type sets how the batch must relate to it. The sample uses = for total steel (25 t), carbon (1.25 t) and molybdenum (1.25 t). The batch must hit each target exactly. To require at least a target, use >= in row 5 instead.

Settings. On the Settings sheet, goal (B5) is Minimize, because the template minimizes cost. time_limit (B6) is the number of seconds the solver may use, with a default of 20 and a maximum of 50. See Errors, empty states and limits for what happens when the time runs out.

How results are calculated

The template chooses how many tonnes of each material to use, and which whole ingots to buy, so that the batch meets each target at the lowest total cost.

Cost and chemistry both add up in proportion to the amounts used. Each ingot contributes its full weight, carbon and molybdenum only when it is bought. Each alloy or scrap grade contributes in proportion to the tonnes bought. The one choice that cannot be split is the ingot decision, which is why this is a mixed-integer problem. The solver must first decide which ingots to take, and then set the alloy and scrap tonnages around them. It cannot get the answer by rounding a partial solution, so it searches the ingot combinations and proves which one gives the lowest cost.

The solver has up to the time limit to prove the cheapest plan. The default is 20 seconds, and the maximum is 50. The sample solves quickly, so the limit matters mainly for larger models.

Reading the results

  • Status (Results B5): optimal means the solver found the cheapest plan and proved it. Other statuses are explained under Errors, empty states and limits.
  • Blend cost (Results B7): the total cost of the purchase.
  • Plan table: the amount of each material. For ingots, 1 means buy the whole ingot and 0 means skip it. The Contribution column shows the dollar amount each material adds to Blend cost. The Reduced cost column is blank for this model, because reduced costs are computed only for models without whole-number variables.
  • Position (Plan column E): at upper limit means a whole ingot is bought. at lower limit means the material is not used. in the plan means an alloy or scrap grade is used at an amount between its bounds. fixed means its lower and upper bounds are equal.
  • Variables in the plan (Results B8): the number of rows with a nonzero amount, which includes bought ingots, scrap and every alloy in use. The sample shows 6: three bought ingots, scrap, alloy 1 and alloy 3. The alloy tonnages are not unique at the optimum, so this count can differ between equally good plans.
  • Limits table: the amount Used compared with the Limit value for each target. Binding = Yes means the target is exactly met. All three sample targets are binding. Slack shows any unused headroom.
  • Shadow price: shadow prices are not computed for this model, because the ingot decisions are whole-number choices. To see what an extra tonne of carbon would cost, change the carbon target and compare the Blend cost.
  • Alternative tonnages: the sample’s ingot choice is the only one that reaches the lowest cost. The alloy tonnages can trade off at equal cost, while the scrap tonnage is 3.5 t in every lowest-cost plan. For example, 7 t of alloy 1, 0.5 t of alloy 2 and 3.5 t of scrap also gives a Blend cost of $8,495. If your plan differs from the sample only in these tonnages, the cost is still correct.

Saving, sharing and App mode

Save the workbook as usual. The Model and Settings values you enter are stored in it. The Results sheet shows what the notebook last wrote, so check Results B5 in a shared copy before you rely on the figures. To share a model, send the workbook with the values you want the recipient to see. For how App mode works, see the Boardflare Notebook Reference linked under Requirements.

Errors, empty states and limits

  • 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.

Solver statuses

  • optimal: The cheapest plan was found and proven.
  • infeasible: No combination of purchases meets every target. Check the targets against the chemistry of the available materials, and check whether an ingot you need has been turned off by setting its Upper to 0. For example, a molybdenum target of 2.5 t cannot be reached with the sample ingots and alloys.
  • unbounded: The cost could fall without limit. This usually means a negative price or a missing bound in the Model sheet.
  • time_limit_feasible: The time limit ran out after a plan that meets every target was found, but the solver did not finish proving it is the cheapest. This plan is feasible, but it is not proven optimal. Raise time_limit in Settings B6, up to 50 seconds, or simplify the model.
  • time_limit_no_solution: The time limit ran out before any plan meeting every target was found. Raise time_limit in Settings B6, or simplify the model.
  • error: The solver stopped with an error. Read the Message in Results B6, then correct the inputs.

Empty state

If the Model sheet has no variable rows filled in, the template solves the built-in steel sample instead and says so in the results. Enter your own rows on the Model sheet to replace it.

Limitations and when to use this

Use this template to plan one batch when the choices are whole pieces (ingots) and bulk materials that can be bought in any amount, and when costs and chemistry add up in proportion to the amounts used. It suits deciding which ingot lots, alloy grades and scrap to combine for a single heat.

It has these limits:

  • Costs and chemistry are treated as straight-line. A yield loss that depends on batch size, or a price that falls with volume, is not modeled.
  • Each batch is planned on its own. Planning many batches, furnace time or stock held over several weeks needs a wider planning tool.
  • Only the targets you enter are checked. Other limits, such as a maximum for an impurity, can be added as extra constraint columns, up to 10.
  • Shadow prices are not available for models with whole-ingot decisions. If every material can be bought in any amount, the template reports shadow prices instead.

Free alternatives

Excel Solver, built into desktop Excel (it is not available in Excel for the web), and the free OpenSolver add-in for Windows Excel (a COIN-OR CBC add-in with no stable release since 2020) can both solve this same mixed-integer linear programming model. Excel Solver accepts up to 200 decision variables and 100 constraints, with up to 100 integer variables, which covers this sample’s 8 variables (4 binary ingots, 3 continuous alloys, 1 scrap) and 3 target limits. This workbook solves the model as a MILP with HiGHS through SciPy’s scipy.optimize.milp, running in Python for Excel. What it adds is the interactive layout, live recalculation when inputs change, plain-language limit suggestions (elastic repair) when blending targets conflict, and per-solve checks on the Results sheet (largest limit violation and MIP gap).

Customizing as an author

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

  1. spec.py: defines the labels, the ingots, the alloys, the scrap grade, the targets and the tracking issue.
  2. Generator: run uv run python templates/optimization/skeleton/generate.py templates/optimization/alloy-blending to regenerate notebook.py, workbook.py and the generated regions of this page.
  3. Workbook rebuild: run uv run python .agents/skills/python-template/scripts/generate-workbooks.py templates/optimization/alloy-blending to build the .xlsx.
  4. Scenario checks: run uv run python .agents/skills/python-template/scripts/run-scenarios-offline.py --template alloy-blending to check the sample results.

To adapt the template to your own mill, replace the ingot weights, prices and chemistries first. Then replace the alloy and scrap rows and the three target limits. Keep the ingot rows with Integer = 1 and Upper = 1.

Developer reference

Notebook implementation

  • notebook.py is generated from the skeleton and spec.py. Its engine block (core.py and table.py) is synchronized from templates/optimization/engine. Do not edit it by hand.
  • Imports: numpy, pandas, scipy.optimize (the milp solver, which uses HiGHS), boardflare and marimo.
  • Flow: check the Settings and Model inputs, read the variable rows and constraint columns, build the cost vector, the equality constraint matrix, the bounds and the integrality vector (1 for ingot rows, 0 otherwise), call milp with the time limit, map the outcome to one of the status strings in core.py, then write the summary, Plan and Limits tables.
  • Shadow prices and reduced costs are not computed when any variable is integer.
  • An empty Model sheet falls back to the built-in example.

How the calculation/model works

The model is a mixed-integer linear program:

minimize    sum over j of c_j * x_j
subject to  sum over j of a_ij * x_j = b_i      for each target i
            l_j <= x_j <= u_j
            x_j in {0, 1}   for each whole-ingot row
            x_j >= 0        for each alloy and scrap row

Here b holds the targets: total steel 25 t, carbon 1.25 t and molybdenum 1.25 t.

For a whole ingot with weight w, price p per tonne, and carbon and molybdenum fractions f and g:

  • cost coefficient: p * w
  • total steel coefficient: w
  • carbon coefficient: w * f
  • molybdenum coefficient: w * g

For an alloy or scrap grade bought by the tonne with price p and fractions f and g:

  • cost coefficient: p
  • total steel coefficient: 1
  • carbon coefficient: f
  • molybdenum coefficient: g

For example, ingot 1 is 5 t at 5% carbon and 3% molybdenum, priced at $350 per tonne. Its cost coefficient is 1,750, and its coefficients are 5 for steel, 0.25 for carbon and 0.15 for molybdenum.

The solver returns an outcome that maps to the status strings described under Errors, empty states and limits. A time-limited result is reported as feasible but not proven optimal when a feasible plan was found.

Validation basis

Source check. The sample data comes from the MathWorks mixed-integer linear programming example: https://www.mathworks.com/help/optim/ug/mixed-integer-linear-programming-basics-problem-based.html. The live page returned 403 to automated fetches, so the check used the Wayback Machine capture of 2026-04-24: https://web.archive.org/web/20260424082215/https://www.mathworks.com/help/optim/ug/mixed-integer-linear-programming-basics-problem-based.html. Every ingot weight, percentage and price, the alloy and scrap prices and percentages, and the 25 t batch with 5% carbon and 5% molybdenum matched the capture. The published optimum was reproduced by an independent SciPy milp solve, and enumerating all 16 ingot patterns found that ingots 1, 2 and 4 is the only pattern that meets the targets.

Baseline. The sample is expected to return:

Check Expected
Status optimal
Blend cost $8,495
Ingots bought ingots 1, 2 and 4; ingot 3 not bought
Scrap 3.5 t
Targets all three binding (total steel, carbon and molybdenum)

The ingot decisions and the scrap tonnage are unique in the optimum. The alloy tonnages can trade off at the same cost, so the baseline check does not assert them. One alternative with the same cost is 7 t of alloy 1, 0.5 t of alloy 2 and 3.5 t of scrap.

Scenarios. Each scenario was solved independently with SciPy milp (HiGHS). Over 20 repeated solves on one development machine, the median solve time was 3.7 to 4.4 milliseconds for the baseline, higher-carbon, scrap-price-up and infeasible-target runs, and about 0.3 milliseconds for ingot-1-unavailable. The first call in each run took about 8 to 11 milliseconds. Times will vary by machine.

Scenario Change Expected
higher-carbon Carbon target 1.5 t (Model G6) Blend cost $9,630, carbon 1.5 t
scrap-price-up Scrap price $300 per tonne (Model B14) Blend cost $9,195
infeasible-target Molybdenum target 2.5 t (Model H6) infeasible
ingot-1-unavailable Ingot 1 Upper set to 0 (Model D7) infeasible

The last scenario uses ingot 1 rather than ingot 3. Ingot 3 is not part of the sample optimum, so removing it leaves the Blend cost unchanged and would not test anything. Ingot 1 is required by the optimum, so removing it makes the problem infeasible.

Changelog

2026-10-28

  • Added per-solve checks to Results: Largest limit violation and MIP gap with PASS/FAIL status.
  • Added plain-language limit suggestions (elastic repair) to the Results limits table.
  • Added free-alternative note and updated scenario expectations for the new summary and limits layout.

2026-10-24 (Issue 240)

  • First release: buy-whole-ingot and any-amount alloy and scrap blending for a 25 t steel batch, with the published optimum of $8,495 as the sample result.