Transportation models fail in Excel Solver when the shipment table is mis‑aligned, supply and demand are not balanced, SUM ranges reference the wrong cells, or the objective function is not a clean SUMPRODUCT. 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 Requires

A transportation model is extremely sensitive to layout. Solver expects:

• A clean matrix of decision variables (shipments) with **numbers only**.
• Row totals representing **supply**.
• Column totals representing **demand**.
• A cost matrix aligned **exactly** with the shipment matrix.
• An objective cell using **SUMPRODUCT(shipment matrix, cost matrix)**.
• Constraints enforcing row sums = supply and column sums = demand.

If any of these are misaligned, Solver will return infeasible or incorrect results.

2. Common Transportation Table Errors

2.1 Shipment Cells Contain Formulas

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

2.2 Wrong SUM Ranges for Supply or Demand

A single incorrect SUM range (e.g., SUM(B4:C4) instead of SUM(B4:D4)) breaks the model.

2.3 Supply and Demand 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 Shipment Matrix

The cost table must match the shipment table cell‑for‑cell.

2.5 Objective Formula Not Using SUMPRODUCT

SUMPRODUCT is the only reliable way to compute total transportation cost.

3. How to Build a Correct Transportation Table

3.1 Step 1 — Create the Shipment Matrix

        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 numbers (0 is fine).

3.2 Step 2 — Add Row Totals (Supply)

Example for S1: =SUM(B4:D4)

3.3 Step 3 — Add Column Totals (Demand)

Example for D1: =SUM(B4:B6)

3.4 Step 4 — Build the Cost 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: =SUMPRODUCT(B4:D6, B10:D12)

3.6 Step 6 — Add Solver Constraints

• Each row SUM = supply.
• Each column SUM = demand.
• All xᵢⱼ ≥ 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: