Transportation models fail in Excel Solver when the table layout is incorrect, supply and demand are not balanced, formulas reference the wrong cells, or the decision variable grid is not properly defined. This page shows how to build a correct transportation table, how to fix common structural errors, and includes a complete numerical example.

• Linear Programming & Optimization
• Inventory & Supply Chain Analytics
• Fix Solver Infeasible Errors
• Fix Solver No Solution Errors

1. What a Correct Transportation Table Must Look Like

A transportation model requires a very specific table structure. Solver will fail if the layout is not exact. A correct table includes:

• A matrix of decision variables (shipments) with no formulas inside.
• Row sums representing supply.
• Column sums representing demand.
• A cost matrix aligned exactly with the decision variable matrix.
• All constraints referencing the correct row and column totals.

If any row or column total is wrong—or if supply ≠ demand—Solver will return infeasible or incorrect results.

2. Common Transportation Table Errors

2.1 Decision Variable Cells Contain Formulas

Shipment cells must contain numbers only. If they contain formulas, Solver cannot adjust them.

2.2 Row and Column Totals Reference Wrong Cells

A single incorrect SUM range will break the entire model.

2.3 Supply and Demand Are Not Balanced

If total supply ≠ total demand, the model is infeasible unless you add a dummy row or column.

2.4 Cost Matrix Misaligned with Decision Variables

If the cost table does not match the shipment table cell‑for‑cell, the objective formula becomes incorrect.

2.5 Objective Formula Uses Wrong Ranges

The objective must be a SUMPRODUCT of the cost matrix and the decision variable matrix.

3. How to Build a Correct Transportation Model Table

3.1 Step 1 — Create the Shipment Matrix

Example layout:

        D1   D2   D3   (Demand)
    S1  x11  x12  x13   S1_supply
    S2  x21  x22  x23   S2_supply
    S3  x31  x32  x33   S3_supply
        D1    D2    D3
  

All xᵢⱼ cells must be blank numbers (0 is fine).

3.2 Step 2 — Add Row Totals (Supply)

Example: =SUM(B4:D4)

3.3 Step 3 — Add Column Totals (Demand)

Example: =SUM(B4:B6)

3.4 Step 4 — Build the Cost Matrix

The cost matrix must align exactly with the shipment matrix:

        D1   D2   D3
    S1  4    6    8
    S2  5    3    7
    S3  6    4    5
  

3.5 Step 5 — Create the Objective Function

Objective cell formula: =SUMPRODUCT(B4:D6, B10:D12)

3.6 Step 6 — Add Constraints in Solver

• Row sums = supply
• Column sums = demand
• Decision variables ≥ 0

4. Numerical Example: Fixing a Broken Transportation Table

A company ships goods from 3 warehouses to 3 stores. Solver reports infeasible because the table is built incorrectly.

4.1 Incorrect Setup

• Shipment cells contain formulas instead of numbers.
• Row totals reference wrong ranges (e.g., SUM(B4:C4) instead of SUM(B4:D4)).
• Column totals reference wrong rows.
• Cost matrix misaligned with shipment matrix.
• Total supply = 300 but total demand = 280 → infeasible.

5. Fixing the Transportation Table

5.1 Replace Shipment Formulas with Numbers

Set all xᵢⱼ cells to 0.

5.2 Correct Row Totals

Example for S1: =SUM(B4:D4)

5.3 Correct Column Totals

Example for D1: =SUM(B4:B6)

5.4 Align Cost Matrix

Ensure cost table matches shipment table cell‑for‑cell.

5.5 Balance Supply and Demand

If supply = 300 and demand = 280, add a dummy demand of 20 with cost = 0.

6. Solving the Corrected Model

After fixing the table, Solver finds a feasible and optimal solution. Example:

• x₁₁ = 40
• x₁₂ = 20
• x₂₂ = 50
• x₃₃ = 70
• All other shipments = 0

Objective value: \[ Z = \sum c_{ij} x_{ij} \]

The model works because the table is now structurally correct.

7. Additional Tips for Reliable Transportation Tables

• Always balance supply and demand (use dummy rows/columns if needed).
• Ensure SUM ranges are correct for every row and column.
• Use SUMPRODUCT for the objective function.
• Keep decision variables in a clean, contiguous block.
• Use Simplex LP for transportation models.

For more Solver troubleshooting: