Fuel Blending Optimization

Optimization
Operations Research
Linear Programming
Optimize refinery gasoline blending by allocating available blend stocks between Regular and Premium products to maximize daily profit while meeting octane, vapor pressure, and benzene limits.

Overview

Fuel Blending Optimization finds the most profitable way to split refinery blend stocks between two finished gasoline products, Regular and Premium. For each stock, it decides how much volume per day to send to each product, subject to how much of each stock is available and to the octane, vapor pressure and benzene rules each product must meet.

The workbook comes loaded with a published benchmark: the gasoline blending problem from the Colorado School of Mines CBEN 409 course (J. Jechura, 2019). Five refinery streams (Butane, Straight-run naphtha, Reformate, FCC naphtha and Alkylate) can be blended into either product. The sample run reproduces the published best daily profit of $100,425.

Units. The source quotes stream availability in barrels per day (bbl/day), but it applies its prices, in dollars per gallon, directly to those volumes, with no 42 gallons-per-barrel conversion. The template follows that arithmetic, so every volume in the model is labelled volume per day, and the profit matches the published figure. If you enter availability in barrels and prices in dollars per gallon, multiply each Objective value on the Model sheet (column B) by 42.

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. Blend stream allocations in column A, profit per unit of volume in column B (product price minus stream cost, in dollars per gallon, applied directly to volume with no 42-gallon factor; if availability is entered in barrels, multiply each value in column B by 42), bounds in columns C and D, integer flag in column E. Columns F to J are the five stream supply limits in volume per day (one row per stream; the source quotes them as bbl/day). Columns K to M are the Regular octane floor, RVP cap and benzene cap, and columns N to P are the same three for Premium. Each quality cell is the stream property minus the product limit: for example, 5.5 in column K for Butane to Regular is octane 92.5 minus 87, and 116.9 in column L is RVP^1.25 146.4 minus 29.5. The octane floors need the blended sum to be zero or more, and the RVP and benzene caps need it to be zero or less. The template reads rows 7 to 36, so it holds up to 30 variables, and columns F to S, holding up to 14 constraints. Blank variable rows and blank constraint columns are ignored.

Sample data. Model A7:P16 holds 10 sample variables and 11 constraints. Rows 17 to 36 are empty and ready for more. The Settings sheet holds goal set to Maximize and time_limit set to 20.

Roles. The technical author configures the linear blending model, property limits and quality thresholds in spec.py. The refinery planning engineer or workbook user adjusts stream availability, product prices or specifications on the Model sheet and reads the best blending plan and the shadow prices on Results, without writing code.

Workbook tour

Sheet and range What it holds Kind
Model, A4:S36 (Blend, Objective, Lower, Upper, Integer, limits…) The problem definition: variables, bounds, objective coefficients, and constraint matrix. Rows 7 to 16 hold the sample variables, and rows 17 to 36 are free. Editable input
Settings, A4:B24 (Setting, Value) Named settings: goal in B5 (Maximize), and time_limit in B6 (default 20 seconds, max 50). Editable input
Results, A4:C12 (Result, Value, Status) Solution summary: status, message, Daily profit, 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 (Blend, Volume per day, Contribution to Daily profit, Reduced cost, Position) Optimal plan: variable amounts, objective contributions, reduced costs, and status in the plan. Output
Results, A49:H63 (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

First-use walk-through with the sample problem

  1. Open the workbook. The Model sheet has 10 decision variables, one for each of five streams sent to each product (the streams with no availability are left out, as explained under Developer reference):
    • Stream availabilities (row 6): Butane 30,000, Straight-run naphtha 35,000, Reformate (high octane) 60,000, FCC naphtha 70,000, and Alkylate 40,000 volume per day (the source quotes these as bbl/day).
    • Regular product: price $2.75/gal; octane at least 87; RVP\(^{1.25}\) at most 29.5; benzene at most 1.1 vol %.
    • Premium product: price $2.85/gal; octane at least 91; RVP\(^{1.25}\) at most 29.5; benzene at most 1.1 vol %.
  2. Switch to the Results sheet. The summary shows status optimal and a Daily profit of $100,425.00.
  3. Review the Plan table. The best plan sends 120,750 volume per day to Regular and 114,250 volume per day to Premium. That is the full 235,000 volume per day of available stock, so no stream is left over.
  4. Review the Limits table. All five availability rows are binding (slack 0, Yes). The Regular and Premium octane floors are also binding, with a shadow price of -$0.025 each.

How specifications are written as linear deviations

In blending, octane, vapor pressure and benzene mix by volume. For a product that receives volumes \(x_1, \dots, x_n\) of streams with property values \(p_1, \dots, p_n\), the blended property is the volume-weighted average:

\[\bar{p} = \frac{\sum_{i=1}^n p_i x_i}{\sum_{i=1}^n x_i}\]

Asking that this average meet a floor \(p_{\text{floor}}\) means \(\bar{p} \ge p_{\text{floor}}\). Multiplying both sides by the total volume, which is never negative, removes the division and gives a rule that is linear in the volumes:

\[\sum_{i=1}^n (p_i - p_{\text{floor}}) \, x_i \ge 0\]

A cap \(p_{\text{cap}}\) works the same way, with the sign reversed: \(\sum_{i}(p_i - p_{\text{cap}}) x_i \le 0\).

So each entry on the Model sheet is a deviation: the stream’s property minus the product limit. A stream with a deviation above zero helps a floor and one below zero hurts it. For a cap, the sign is reversed. The limit in row 6 is 0 for every quality column, because the rule is already written as “sum of deviations is zero or more” (floors) or “zero or less” (caps).

  • Octane floor (columns K and N). For example, Butane has octane 92.5, so its Regular entry is 92.5 minus 87, which is +5.5. Straight-run naphtha has octane 77, so its Regular entry is 77 minus 87, which is -10.0. Type >= in row 5.
  • RVP cap (columns L and O). Vapor pressure is entered as RVP raised to the power 1.25. For example, Butane has RVP 54.0 psi, which gives 146.4, so its entry is 146.4 minus 29.5, which is +116.9. Type <= in row 5.
  • Benzene cap (columns M and P). Entries are stream benzene (vol %) minus the cap. For example, Straight-run naphtha has 0.73, so its Regular entry is 0.73 minus 1.10, which is -0.37. Type <= in row 5.

To adapt the template to a different specification, subtract the new limit from each stream’s property value and enter the differences in the matching column. Keep row 6 at 0. The benzene cap in the tighter-benzene test scenario uses this method with a cap of 0.60 (see Developer reference).

Understanding the degenerate optimum

A linear program is degenerate when more than one combination of amounts reaches the same best profit. This blending problem is degenerate in a specific way:

  • The best daily profit ($100,425.00) is unique.
  • The totals sent to Regular (120,750 volume per day) and Premium (114,250 volume per day) are unique.
  • The split of each stream between the two products is not unique. The solver used here sends 17,901 volume per day of Butane to Regular and 12,099 to Premium. The published breakdown (PDF page 23, printed slide 25) is 17,925 and 12,075. Both splits reach the same profit and meet every specification.

When you compare this template with another solver or a published table, compare profit and product totals, not individual stream amounts.

Enter a new problem or modify blends

  1. On the Model sheet, edit stream availabilities in row 6 (columns F through J).
  2. To change prices or margins, update the profit coefficients in column B (product price minus stream cost, in dollars per gallon).
  3. To change a quality limit, enter the new deviations in columns K to P (stream property minus the new limit) and keep row 6 at 0.
  4. Unused rows (17 to 36) and spare constraint columns (Q, R, S) are free for more streams, products or constraints.
  5. Switch to the Results sheet to see the updated plan and shadow prices.

Change the goal and time limit

  1. Select the Settings sheet.
  2. In cell B5 (goal), keep Maximize to maximize daily profit. Minimize finds the lowest profit the same limits allow. It is not a useful planning view, but it can help check the model.
  3. In cell B6 (time_limit), adjust the solver timeout if the problem grows (default 20 seconds, capped at 50).

Inputs and settings

Model sheet parameters

  • Blend (Column A, rows 7 to 36): Label for the stream and product pair (e.g. “Butane to Regular”, “Alkylate to Premium”).
  • Objective (Column B, rows 7 to 36): Profit per unit of volume: product price minus stream cost, in dollars per gallon, applied directly to the volume with no 42-gallon factor. This follows the source’s arithmetic, which reproduces the published $100,425. If availability is entered in barrels and prices in dollars per gallon, multiply each Objective value in this column by 42.
  • Lower (Column C, rows 7 to 36): Minimum volume per day (default 0.0).
  • Upper (Column D, rows 7 to 36): Maximum volume per day (optional).
  • Integer (Column E, rows 7 to 36): Integer flag. Leave blank for continuous volumes, which is the case here.
  • Constraint columns (Columns F to S):
    • Header (Row 4): Name of the availability limit or quality rule.
    • Type (Row 5): <= for supply limits and caps, >= for floors.
    • Limit (Row 6): Supply limit in volume per day for availability columns; 0 for quality columns.
    • Coefficients (Rows 7 to 36): Contribution per unit of volume. For supply columns this is 1 for every product pair that uses that stream and 0 otherwise (Butane supply is 1 for both Butane to Regular and Butane to Premium). For quality columns it is the deviation described above.

Settings sheet

  • goal (cell B5): Optimization direction. Allowed values: Maximize or Minimize. Default: Maximize.
  • time_limit (cell B6): Maximum solver run time in seconds. Allowed values: positive numbers up to 50. Default: 20.

How results are calculated

  1. Read model data: Reads the stream labels, profit coefficients and bounds from Model A7:E36 and the constraint columns from Model F4:S36. Blank rows and empty constraint columns are skipped.
  2. Read settings: Reads the goal (Maximize or Minimize) and the time limit from Settings.
  3. Set up the problem: Builds the linear program with 10 variables and 11 constraints (5 supply limits and 6 quality rules).
  4. Solve: Passes the problem to the HiGHS solver through SciPy.
  5. Read the sensitivities: For a continuous problem, takes shadow prices from each constraint and reduced costs from each variable.
  6. Repair infeasible limits: If no plan meets every limit, runs a relaxation that finds the smallest changes to the limits that would allow a plan, and reports them under Suggested limit. Other repairs may also work.

Reading the results

Summary table (Results, A4:B10)

  • Status: Solver outcome (optimal, infeasible, or another status listed under Errors, empty states and limits).
  • Message: Plain-language explanation of the status.
  • Daily profit: Net profit per day in dollars.
  • Variables in the plan: How many stream and product pairs have a nonzero amount. Because the optimum is degenerate, this count can differ between solvers.
  • Binding limits: How many constraints are at their limit. Like the variable count, this can differ between solvers when the optimum is degenerate.
  • Gap: Blank for a continuous problem solved to optimality.

Plan table (Results, A13:E43)

  • Blend: Name of each stream and product pair.
  • Volume per day: Amount sent to that product.
  • Contribution to Daily profit: Amount times profit per unit of volume.
  • Reduced cost: For a pair left at zero, how much its profit per unit of volume would have to improve before it was worth making. Zero for pairs in the plan.
  • Position: in the plan or at lower limit.

Limits table (Results, A47:H61)

  • Limit: Name of each supply limit or quality rule.
  • Type: <= or >=.
  • Used: How much of the limit the plan uses, or the quality sum it reaches.
  • Limit value: The limit, from Model row 6.
  • Slack: How far the plan is from the limit. Zero means binding.
  • Binding: Yes when the limit is reached with no room to spare, No otherwise.
  • Shadow price: The change in daily profit from raising that limit by one unit, with everything else unchanged.
  • Suggested limit: A limit change that would make an infeasible problem feasible. Filled only when the problem is infeasible.

What shadow prices mean in plain words

A shadow price is the change in daily profit for a one-unit change in one limit. Here, the unit is volume per day for supply rows and the deviation sum for quality rows.

  • Butane supply ($2.0375 per unit per day): Butane is cheap ($0.85/gal) against product prices of $2.75 to $2.85/gal, so each extra unit of volume per day of Butane adds $2.0375 to daily profit. The more-butane test raises the limit by 5,000 volume per day and profit rises by exactly 5,000 × 2.0375.
  • Straight-run naphtha supply ($0.45 per unit per day): Each extra unit of volume per day adds $0.45 to daily profit.
  • Reformate supply ($0.1275 per unit per day): Each extra unit per day of high-octane Reformate adds $0.1275.
  • FCC naphtha supply ($0.09 per unit per day): Each extra unit per day adds $0.09.
  • Alkylate supply ($0.24 per unit per day): Alkylate has no benzene and is worth $0.24 per unit of volume at the margin.
  • Regular and Premium octane floors (-$0.025): Raising a floor by one unit of the deviation sum lowers daily profit by $0.025. Lowering a floor raises profit by the same amount.

Saving, sharing and App mode

What persists. Stream availabilities, profit coefficients, quality limits and settings on Model and Settings are saved with the Excel workbook.

What is recalculated. The Results sheet shows outputs from the embedded Python notebook. When the workbook is opened in Excel with the add-in active, the notebook solves the problem and refreshes the plan and the sensitivity values.

Sharing. Anyone who opens the workbook can read the Model and Results sheets. Solving new cases needs Excel with Python for Excel enabled.

App mode. In App mode, users interact with the workbook without viewing internal code cells. App mode modifies presentation rather than permissions, source access, or workbook trust.

Errors, empty states and limits

Solver statuses

  • optimal: The best plan has been found and proven best.
  • infeasible: No plan meets every availability limit and quality rule. The Message says so, the profit and plan cells are blank, and the Suggested limit column shows one way to relax the limits.
  • unbounded: Profit can grow without limit. The availability limits normally prevent this. If it appears, check that every availability row has a limit.
  • time_limit_feasible: The solver stopped at the time limit with a valid plan that may not be the best one.
  • time_limit_no_solution: The solver stopped at the time limit before finding a plan.
  • error: The input could not be read, for example a text entry where a number is needed or a lower bound above the upper bound.

Empty state

If the Model sheet has no stream rows or quality columns filled in, the Results sheet shows a message asking for them.

Limitations and when to use this

Use this template for a linear blending plan, where each property mixes by volume and each limit can be written as a deviation rule. It is a good fit for a refinery planning exercise or a teaching example.

Do not use it as is for blends where a property does not mix linearly by volume (for example, some properties blend by mole fraction or by a nonlinear index), for plans that change over time, or for planning with integer batch sizes. Those need a different model.

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 fuel blending model. Excel Solver’s Simplex LP engine accepts up to 200 decision variables and 100 constraints, which covers this sample’s 10 stream-to-product variables and 14 quality and supply limits. This workbook solves the model as a linear program with HiGHS through SciPy’s scipy.optimize.linprog, running in Python for Excel. What it adds is the field-standard blending layout, live recalculation when inputs change, plain-language limit suggestions (elastic repair) when blending limits conflict, and per-solve checks on the Results sheet (largest limit violation and duality gap).

  • The model reads up to 30 variables (Model rows 7 to 36) and up to 14 limits (columns F to S).
  • Rows below row 36 and columns past column S 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, workbook.py or the .xlsx file. The workflow is:

  1. spec.py: Defines the title, the stream and product variables, the coefficients as deviations, the supply limits, the sample data and the tracking issue.
  2. Generator: Run uv run python templates/optimization/skeleton/generate.py templates/optimization/fuel-blending to regenerate notebook.py, workbook.py and the generated regions of index.md.
  3. Workbook rebuild: Run uv run python .agents/skills/python-template/scripts/generate-workbooks.py templates/optimization/fuel-blending to build the .xlsx file.
  4. Expected results: Update scenarios.py for any change to the sample results. Each expected value must come from an independent calculation, not from the workbook.
  5. Scenario check: Run uv run python .agents/skills/python-template/scripts/run-scenarios-offline.py --template fuel-blending.

Developer reference

Notebook implementation

The generated notebook reads the model and settings from the workbook:

inputs = bf.inputs(
    model=bf.ref('Model!A4:S36'),
    settings=bf.ref("Settings!A4:B24", headers=True),
    overflow=bf.ref('Model!A37'),
    overflow_col=bf.ref('Model!T4'),
)

The model is converted into the objective vector, bounds, constraint matrix and limits, and then solved:

result = solve_model(model, time_limit=time_limit)

The plan, summary and limits tables are written to the anchored ranges on the Results sheet.

How the calculation/model works

The problem is a continuous linear program. Each variable is the volume of one stream sent to one product:

\[\begin{aligned} \text{maximize} \quad & Z = \sum_{s \in S} \sum_{p \in P} (\text{Price}_p - \text{Cost}_s) \, x_{s,p} \\ \text{subject to} \quad & \sum_{p \in P} x_{s,p} \le \text{Availability}_s \quad \forall s \in S \\ & \sum_{s \in S} (\text{Octane}_s - \text{OctaneFloor}_p) \, x_{s,p} \ge 0 \quad \forall p \in P \\ & \sum_{s \in S} (\text{RVP}^{1.25}_s - \text{RVPCap}_p) \, x_{s,p} \le 0 \quad \forall p \in P \\ & \sum_{s \in S} (\text{Benzene}_s - \text{BenzeneCap}_p) \, x_{s,p} \le 0 \quad \forall p \in P \\ & x_{s,p} \ge 0 \end{aligned}\]

The profit term uses the price and cost in dollars per gallon directly, with no 42-gallon factor (see Validation basis). The solver is SciPy’s HiGHS interface, scipy.optimize.linprog(method='highs'). Shadow prices are the constraint duals \(y\), and reduced costs are \(r = c - A^T y\).

When no plan is feasible, the notebook solves an elastic relaxation. It adds a penalty for each limit violation, minimizes the total penalty, and reports the adjusted limits as suggested repairs.

Validation basis

The template is checked against the gasoline blending example in:

  • John Jechura, “Blending Optimization”, CBEN 409 lecture slides, Colorado School of Mines (2016-2019): CBEN409_11_Blending_Optimization-1.pdf. PDF page 20 (printed slide 22) gives the stream properties, costs, availabilities and product specifications. PDF page 23 (printed slide 25) gives the combined best solution, with a daily profit of $100,425.

The source was used for its numbers only. Where the source and the request differed, the template follows the source and this section records the difference:

  1. Butane RVP. PDF page 20 (printed slide 22) gives 54.0 psi (RVP\(^{1.25}\) 146.4). PDF page 9 (printed slide 10) gives 52.0. PDF page 20 is used.
  2. Premium RVP cap. The slides print 29.5 for both products. Fifteen psi raised to the power 1.25 is 29.52, which rounds to 29.5. Both products use 29.5.
  3. No 42-gallon factor. The source quotes availability in bbl/day but applies its prices, in dollars per gallon, directly to those volumes. Its own Regular revenue, 120,750 × $2.75 = $332,062.50, is computed this way (PDF page 23 prints $332,063). Multiplying each profit coefficient by 42 would give about $4.2 million per day, not the published $100,425. The template therefore follows the source arithmetic, labels its volumes volume per day, and leaves the factor out. Users who enter availability in barrels must multiply each Objective value by 42 (see Inputs and settings).
  4. Streams left out. Isomerate and Reformate (low benzene) have zero availability and are not modelled. The product volume limits on PDF page 23 (1 and 1,000,000 per day) never bind at this optimum and are not modelled either.
  5. Degenerate optimum. Stream splits between products are not unique. Only the profit and product totals are unique (see Understanding the degenerate optimum).

Test scenario choices. The table below lists the test scenarios. Two differ from the values originally requested:

  • tighter-benzene uses a Regular benzene cap of 0.60 instead of 0.90. A cap of 0.90 leaves the best profit at $100,425, so the test could not show that the change had taken effect. Profit changes for caps below about 0.70.
  • infeasible-octane is an artificially infeasible test. It sets the Premium octane floor to a deviation sum of 1.0, and sets each Premium octane deviation to stream octane minus 98. Every such deviation is negative, so no plan reaches a sum of 1.0, including a plan with zero Premium volume (sum 0). It exists to exercise the infeasible path. With a limit of 0 instead, a plan with no Premium volume is feasible and the profit is $91,365.08.

Minimize. minimize-guard is included. Minimizing profit is bounded by the supply limits and gives a unique result: Reformate to Regular at 58,666.67 volume per day and Alkylate to Regular at 40,000 volume per day, for a profit of -$2,933.33 per day. The earlier expectation of zero volume was wrong.

Test scenario Edits Expected outcome
Baseline None (sample data) Status optimal; Daily profit $100,425.00; all five availability rows binding with shadow prices 2.0375 (Butane), 0.45 (Straight-run), 0.1275 (Reformate), 0.09 (FCC naphtha) and 0.24 (Alkylate); Regular and Premium octane floors binding with shadow price -$0.025
more-butane Model!F6 = 35000 Status optimal; Daily profit $110,612.50 (5,000 more than baseline times 2.0375); Butane used 35,000, binding, shadow price 2.0375
premium-price-up Model!B8 = 2.10, Model!B10 = 0.90, Model!B12 = 0.15, Model!B14 = 0.35, Model!B16 = 0.20 Status optimal; Daily profit $111,850.00 (114,250 Premium volume per day times 0.10 more than baseline)
tighter-benzene Model!M7 = -0.60, Model!M9 = 0.13, Model!M11 = 1.25, Model!M13 = 0.46, Model!M15 = -0.60 Status optimal; Daily profit $98,929.93
infeasible-octane Model!N6 = 1.0, Model!N8 = -5.5, Model!N10 = -21.0, Model!N12 = -3.9, Model!N14 = -13.4, Model!N16 = -1.4 Status infeasible; Message “No combination satisfies all the limits.”; Daily profit blank; Premium octane floor limit value 1.0
minimize-guard Settings!B5 = Minimize Status optimal; objective -$2,933.33 per day

The shared engine (core.py and table.py) is in templates/optimization/engine/. This template is tracked under GitHub issue #240.

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 from spec.py. All text outside these markers is hand-written.

Changelog

2026-10-28

  • Upgraded in place with per-solve checks on Results: Largest limit violation and Duality 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

  • Initial release of the Fuel Blending Optimization template, based on the Colorado School of Mines CBEN 409 benchmark (issue #240).
  • Profit uses no 42-gallon factor, so the sample reproduces the published $100,425 per day. Premium RVP cap is 29.5 as printed on the source slides.
  • Tests: tighter-benzene uses a 0.60 Regular benzene cap, infeasible-octane uses a Premium octane limit of 1.0, and minimize-guard is included.