Transportation Planning

Optimization
Operations Research
Linear Programming
Optimize freight shipments from canning plants to regional distribution centers at minimum total cost within plant capacity and market demand.

Transportation Planning

Find the least-cost distribution schedule to ship cases of goods from production plants to regional markets while meeting every market’s demand and respecting each plant’s physical capacity.

[!NOTE] Historical benchmark context: The sample network in this template is George Dantzig’s foundational two-plant, three-market transportation problem (Dantzig, 1963, chapter 3.3), as presented in Richard Rosenthal’s classic GAMS tutorial (Rosenthal, 1988) and the Linopy optimization benchmarks. The published optimal freight cost is $153,675.

Overview

Transportation and distribution planning answers an essential supply chain question: given where products are manufactured and where customer demand exists, which shipping lanes should be utilized and how many units should travel along each lane to achieve the lowest overall logistics expenditure?

In the classic linear programming skeleton, transportation problems were frequently flattened into abstract route lists where every origin-destination pair occupied a separate row with binary 0/1 constraint indicator columns. Real logistics coordinators and operations researchers do not organize distribution networks this way. Instead, they organize freight rates into a transportation tableau: - Rows: Production facilities or supply origins (with plant capacity at the right edge). - Columns: Regional distribution centers or market destinations (with customer demand requirements along the bottom edge). - Matrix cells: The freight rate (or shipping cost per unit) along each origin-destination lane.

This template implements a field-authentic transportation matrix for Dantzig’s canning plant distribution network: - Two supply plants: Seattle (capacity: 350 cases) and San Diego (capacity: 600 cases), providing 950 cases of total network capacity. - Three regional markets: New York (demand: 325 cases), Chicago (demand: 300 cases), and Topeka (demand: 275 cases), requiring 900 cases in total. - Freight costs: Derived from route distances and a baseline freight rate of $90 per case per thousand miles.

When you open the workbook or edit any network parameter, Python for Excel solves the linear program instantly via HiGHS and publishes the optimal shipment schedule, plant utilization metrics, and market fulfillment marginal costs directly to the Results sheet.

Route distances and freight rates

The sample network derives freight rates from highway mileage and a commercial rate of $90 per case per thousand miles:

Origin plant Destination market Distance (thousand miles) Freight rate ($ / case)
Seattle New York 2.5 $225
Seattle Chicago 1.7 $153
Seattle Topeka 1.8 $162
San Diego New York 2.5 $225
San Diego Chicago 1.8 $162
San Diego Topeka 1.4 $126

Before you start

Requirements. Excel with Python for Excel enabled. The template operates strictly within this workbook and requires no external add-ins, network calls, or database feeds.

Solution uniqueness note. In Dantzig’s benchmark, Seattle and San Diego face identical freight rates to New York ($225/case). Total network capacity (950 cases) exceeds total market demand (900 cases) by 50 cases. After satisfying Chicago’s 300 cases at lowest cost from Seattle ($153 vs $162), Seattle has 50 cases of surplus capacity remaining. Consequently, there is an optimal family of solutions: Seattle can ship anywhere from 0 to 50 cases to New York, with San Diego supplying the balance (325 to 275 cases), and all such plans achieve the exact same minimum total cost of $153,675. The Results sheet displays one proven optimal vertex; the total cost and market fulfillments are unique and invariant.

Workbook tour

Sheet and range Description Role
Results (A5:C13, A16:F22, A25:G28, A31:G35, A38:E44) Executive logistics dashboard displaying optimization KPIs with two per-solve checks (largest limit violation and duality gap), active shipping lanes, plant capacity utilization, market fulfillment with marginal costs, and suggested limit changes when a plan is infeasible. Output
Model (A5:E8) Authentic transportation tableau: origin plants along rows, destination markets across columns, freight rates in cells, plant supply capacity at the right edge, and market demand along the bottom edge. Editable input
Validation (A5:E13, F5:H13) 8 scientific benchmark cross-checks verifying the solve against Dantzig’s published figures, demand satisfaction, and zero duality gap. Verification

Using the template

1. Inspect the baseline solve

  1. Open the workbook and allow Python for Excel to complete the baseline calculation.
  2. View the Results sheet:
    • Status: optimal
    • Total shipping cost: $153,675.00
    • Total cases shipped: 900 cases
    • Total plant capacity: 950 cases
    • Active shipping lanes: 4 routes utilized
    • Plant utilization: Seattle ships 350 cases (100% capacity), San Diego ships 550 cases (91.7% capacity, 50 cases surplus).
    • Market fulfillment: All three markets receive 100% of required shipments.
    • Largest limit violation: 0.0 with status PASS. This is recomputed from your own inputs with an independent check, and PASS means no plant capacity or market demand limit is broken beyond 10^-6.
    • Duality gap: 0.0 with status PASS. PASS means the primal and dual objectives agree within 10^-5.

2. Test an increase in market demand

  1. Switch to the Model sheet.
  2. In cell Model!C8 (Chicago demand), increase the requirement from 300 to 340 cases.
  3. Return to the Results sheet:
    • Total cases shipped rises to 940 cases (within the 950 total network capacity).
    • Total shipping cost increases from $153,675 to $159,795 (an increase of exactly 40 cases \(\times\) $153/case marginal cost = $6,120).
    • San Diego unused capacity drops from 50 to 10 cases.

3. Observe the infeasible condition

  1. On the Model sheet, set cell Model!C8 (Chicago demand) to 400 cases.
  2. Total network demand is now 1,000 cases (325 + 400 + 275), exceeding total plant capacity of 950 cases.
  3. Return to Results:
    • Status reports infeasible.
    • Message displays: "Total demand exceeds what the plants can supply."
    • Shipment tables clear to avoid misleading operational guidance.
    • The two check rows are blank, because there is no optimal plan to check.
    • Suggested limit changes (Results!A38:E44) name the limits to change, in plain words. Each row is labelled Capacity: <plant> or Demand: <market> and shows the current value, the suggested value, and the change. The suggestion is the smallest change once each limit is scaled by its size, and it is one of several equally small fixes.
    • The final row, Total absolute change, equals the shortfall: 1,000 - 950 = 50 cases. Which limits absorb those 50 cases can vary, so the table shows one combination that reaches the total.

4. Evaluate lane rate negotiations

  1. On the Model sheet, reset Chicago demand to 300, and change Seattle to New York freight in cell Model!B6 from $225 to $200 per case.
  2. Return to Results:
    • Seattle now has a strict $25/case cost advantage to New York.
    • The optimum becomes strictly unique: Seattle routes 325 cases to New York and 25 to Chicago.
    • Total cost falls to $148,025.00.

Inputs and settings

Model tableau (Model!A5:E8)

  • Plant (Origin) (col A): Name of the manufacturing facility or warehouse.
  • Destination columns (cols B to D, blue inputs): Freight rate per case from each origin plant to the respective regional market. Values must be non-negative numbers.
  • Supply Capacity (cases) (col E, blue inputs): Maximum number of cases each plant can produce and ship during the planning horizon.
  • Market Demand (cases) (row 8, blue inputs): Minimum number of cases required by each destination market.

How results are calculated

  1. Linear programming formulation: Let \(x_{ij} \ge 0\) denote the number of cases shipped from plant \(i\) to market \(j\), \(c_{ij}\) denote the unit freight cost, \(S_i\) denote plant capacity, and \(D_j\) denote market demand requirement: \[\begin{aligned} \text{minimize} \quad & \sum_{i=1}^{m} \sum_{j=1}^{n} c_{ij} \cdot x_{ij} \\ \text{subject to} \quad & \sum_{j=1}^{n} x_{ij} \le S_i \quad \text{for each plant } i = 1, \dots, m \\ & \sum_{i=1}^{m} x_{ij} \ge D_j \quad \text{for each market } j = 1, \dots, n \\ & x_{ij} \ge 0 \quad \text{for all } i, j \end{aligned}\]

  2. Solver execution: The problem is solved using SciPy’s HiGHS linear programming solver wrapped by the template optimization engine (solve_transportation).

  3. Independent verification: The solution vector is independently evaluated against feasibility tolerances and dual optimality: \[\text{primal violation} < 10^{-6}, \quad \text{duality gap} < 10^{-6}\]

Reading the results

  • Optimization summary and checks (Results!A5:C13): Status, message, total shipping cost, cases shipped, total plant capacity, active lanes, and two per-solve checks. The Largest limit violation is the biggest amount by which the plan breaks any plant capacity or market demand limit, recomputed from your inputs. The Duality gap is the difference between the primal and dual objective values. Each check shows PASS when it meets its tolerance and FAIL when it does not. When the plan is infeasible, both checks are blank.
  • Optimal shipment plan (Results!A16:F22): Lists the active shipping lanes ordered by shipped volume descending. Each row displays the origin plant, destination market, cases shipped, unit freight rate, total freight expenditure on that route, and share of overall logistics spend.
  • Plant capacity utilization & value (Results!A25:G28):
    • Utilization %: Cases shipped divided by physical plant capacity.
    • Status: Identifies whether a plant is At capacity (binding limit) or has Surplus capacity.
    • Capacity savings ($ / case): The operational value (shadow price) of expanding plant capacity by one case. When total network capacity exceeds demand, surplus plants have a marginal value of $0.00.
  • Suggested limit changes (Results!A38:E44): Filled only when the plan is infeasible. A feasible plan shows the row “No changes needed - all demands can be met”. Each changed limit has a Limit label (Capacity: <plant> or Demand: <market>), a Type (Plant capacity (at most) or Market demand (at least)), its Current and Suggested values, and the Change. The table holds up to five changed limits and always ends with Total absolute change.
  • Market demand fulfillment & marginal costs (Results!A31:G35):
    • Fulfillment %: Received cases divided by required demand.
    • Status: Reports Fulfilled when requirements are satisfied.
    • Marginal cost ($ / case): The additional freight expenditure incurred if market demand increases by one case (the demand shadow price). In the baseline solve, increasing Chicago demand costs $153/case, Topeka costs $126/case, and New York costs $225/case.

Limitations and when to use this

Balanced vs. excess supply requirement

This classical formulation requires that total plant capacity equals or exceeds total market demand: \[\sum_{i=1}^{m} S_i \ge \sum_{j=1}^{n} D_j\] If customer demand exceeds available capacity, the model correctly reports infeasible and the Results sheet lists the plant capacity and market demand limits to change, with the total shortfall. In real-world rationing scenarios where demand must be prioritized under capacity deficits, penalty costs or shortage variables can be introduced.

Linear freight assumption

The model assumes constant per-case freight rates regardless of shipment size. In real-world freight operations: - Less-than-truckload (LTL) rates vary by shipment weight tier. - Full truckload (FTL) operations incur fixed vehicle dispatch costs. - Route minimums or container constraints require mixed-integer linear programming (MILP).

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 transportation model. The sample’s six lanes sit far inside Excel Solver’s Simplex LP limit of 200 decision variables and 100 constraints. This workbook solves the linear program with HiGHS through SciPy’s scipy.optimize.linprog, running in the browser. What it adds is the transportation tableau layout, live recalculation of the plan when supplies, demands or freight rates change, capacity savings and marginal costs with stated meanings, independent feasibility and duality checks, plain-language infeasibility repair (which plant capacity or market demand limit to change, and by how much), and a Validation sheet against Dantzig’s (1963) optimum of $153,675.

Customizing as an author

To adapt this model for your company’s distribution network: - Adding plants or markets: Insert rows for additional production sites or columns for new regional warehouses. Update the bf.inputs range in notebook.py and output anchor dimensions in workbook.py. - Updating freight rates: Edit freight rates directly in the Model sheet cells using quotes from your logistics service providers (3PLs) or contract carriers. - Adjusting seasonal demand: Update market demand rows based on monthly demand forecasts or sales quotas.

Tool commands

  • Regenerate workbook: uv run python .agents/skills/python-template/scripts/generate-workbooks.py templates/optimization/transportation-planning
  • Run offline scenarios: uv run python .agents/skills/python-template/scripts/run-scenarios-offline.py templates/optimization/transportation-planning
  • Run acceptance gate: uv run python .agents/skills/python-template/scripts/template-gate.py transportation-planning --generate

Developer reference

Domain authenticity rationale

Earlier iterations of this template generated by the deprecated skeleton generator used a flat, six-row route table with auxiliary 0/1 binary indicator columns for each constraint. While mathematically equivalent to an LP tableau, that structure fails to reflect standard industry practice in logistics planning.

In real-world logistics engineering and operations research: 1. Visual network intuition: Logistics directors evaluate transportation networks as origin-by-destination matrices. The two-dimensional tableau immediately highlights rate differentials, geographic cost gradients, and network balance (\(\sum \text{supply}\) vs. \(\sum \text{demand}\)). 2. Direct matrix ingestion: The transportation tableau aligns directly with the matrix formulation expected by forms.transportation.solve_transportation(supply, demand, cost, ...), eliminating unnecessary table reshaping. 3. Operational dual framing: Shadow prices are translated into actionable business metrics: “Capacity savings ($ / case)” for origin capacities and “Marginal cost ($ / case)” for destination market fulfillment.

Notebook implementation

notebook.py is authored as a reactive Marimo application: - # engine-modules: repair, transportation: Copies engine/core.py, engine/repair.py, and engine/forms/transportation.py into the notebook via sync_engine.py. - bf.inputs(...): Reads named worksheet ranges from Model!A5:E8 and Validation!A5:E13. - solve_transportation(...): Formulates and solves the primal-dual transportation linear program via HiGHS. When the plan is infeasible, it also runs elastic_repair(...) to compute the suggested limit changes: plant capacity rows are <= and market demand rows are >=. - Per-solve checks: check_solution(...) and duality_gap(...) from engine/core.py compute the Largest limit violation and Duality gap rows on Results. - bf.publish(...): Publishes structured DataFrames to summary, plan, plants, markets, repair, and validation spill anchors.

Validation and expected results

The Validation sheet cross-checks 8 scientific benchmarks against Dantzig (1963) and Linopy literature: 1. Dantzig (1963) benchmark: Total freight cost $153,675.00. 2. Network volume conservation: Total cases shipped 900.0. 3. Network surplus capacity: Total unused capacity 50.0 cases. 4. Total supply capacity: Combined capacity 950.0 cases. 5. New York demand requirement: 325.0 cases received. 6. Chicago demand requirement: 300.0 cases received. 7. Topeka demand requirement: 275.0 cases received. 8. LP optimality condition: Duality gap 0.0 (\(< 10^{-5}\)).

All 8 benchmark checks report PASS.

Changelog

2026-10-28

  • Added elastic infeasibility repair: an infeasible plan now lists the plant capacity and market demand limits to change (current, suggested, change), ending with a Total absolute change row equal to the shortfall.
  • Added per-solve checks to the Results summary: Largest limit violation (check_solution) and Duality gap (duality_gap), each showing PASS or FAIL, or blank when the plan is not solved.
  • Scenarios cover the baseline checks and empty repair state, the higher-demand case, and the infeasible 50-case shortfall total.

2026-10-26

  • Hand-crafted rebuild replacing generic skeleton-generated layout with field-authentic transportation tableau (issue #240).
  • Eliminated deprecated spec.py and moved off the curve-fitting skeleton generator permanently.
  • Structured Model sheet as an authentic 2-plant by 3-market transportation matrix with editable freight rates, plant capacities, and market demands.
  • Integrated validated forms.transportation.solve_transportation engine builder via Marimo # engine-modules: transportation.
  • Designed comprehensive Results dashboard featuring summary KPIs, optimal shipment plan with lane cost contributions, plant capacity utilization with marginal capacity values, and market demand fulfillment with marginal demand costs.
  • Added dedicated Validation sheet with 8 invariant scientific cross-checks against Dantzig’s published 1963 benchmark and Linopy tutorial, all reporting PASS.
  • Authored test scenarios in scenarios.py verifying baseline convergence, demand expansion under marginal pricing, infeasible demand overload, and cost advantage shifts.
  • Accepted advisory pane-clip warning: none (all worksheet tables remain well within the 1250px task-pane boundary).