Excel Solver Transportation Problem: Ship at Minimum Total Cost
Solve the excel solver transportation problem: shipments, supply, demand, SUMPRODUCT cost, Simplex LP minimize.
Factories, customers, unit costs, and balance constraints — a classic LP that belongs in every advanced Solver practice set.
Cluster home: Excel Solver Example 2 Template.
Transportation is a second advanced Solver scenario beside Goal/degrees Example 2 practice.
Key takeaway: An excel solver transportation problem minimizes SUMPRODUCT of unit costs × shipments subject to supply and demand balances using Simplex LP.
Model Shape
Excel Easy’s transportation tutorial uses named ranges:
- UnitCost matrix (factory × customer)
- Shipments decision matrix (constants Solver changes)
- TotalOut / Supply — row sums
<=or=supply - TotalIn / Demand — column sums
=demand (balanced) or<=/>=when unbalanced - TotalCost —
SUMPRODUCT(UnitCost, Shipments)→ Minimize
Decisions: how many units to ship on each lane. Constraints: respect supply and meet demand. Objective: minimize total shipping cost.
Balanced vs Unbalanced
Balance types
| Case | Supply vs demand | Modeling tip |
|---|---|---|
| Balanced | Equal totals | Use = on supply and demand |
| Extra supply | Supply > demand | Allow unused supply or dummy customer |
| Extra demand | Demand > supply | Dummy supplier or declare infeasible |
Unbalanced problems need dummy nodes or relaxed constraints — do not force = on both sides when totals disagree.
Steps: Solve in Solver
excel solver transportation problem
- Enter unit cost matrix and blank shipment matrix.
- Row sums = TotalOut; column sums = TotalIn; TotalCost = SUMPRODUCT.
- Data → Solver → Objective TotalCost → Min.
- Changing cells = Shipments range.
- Add TotalOut vs Supply and TotalIn vs Demand constraints.
- Non-Negative + Simplex LP → Solve.
Excel Easy’s sample optimal plan ships a specific pattern at cost 26000 versus a feasible trial at 27800 (Excel Easy). Always compare to a manual feasible trial so students trust the optimum.
Integers and Real Trucks
If shipments must be whole pallets, add int on the Shipments range. That turns the LP into an integer program — still fine for small classroom sizes; large networks may need specialized solvers beyond Excel’s limits.
Link to Example 2 and Profit Max
Transportation is Min-cost LP; product mix is Max-profit LP (maximize profit). Example 2’s Goal workbook trains dialog literacy; transportation trains matrix modeling. Use both.
Structured practice file: PlanoraNest Excel Solver Example 2 Template — instant download, one-time purchase; live price on the product page. Free blogs are enough for the shipping matrix itself; you do not need outbound links to competitor template shops.
Modeling reinforcement
Keep decision cells as constants, never formulas, so Solver can freely change them. Name the objective cell clearly and document Max versus Min before you click Solve. Put right-hand-side limits in cells, not hardcoded dialog numbers, so scenarios stay editable. After Solve, skim the Answer Report for binding constraints before you trust the mix. If the model is linear, choose Simplex LP; GRG Nonlinear is the wrong default for LP. Non-negativity belongs in Make Unconstrained Variables Non-Negative or explicit >= 0 constraints. Save a Load/Save model block on the sheet so classmates and teammates can reload parameters. Treat infeasible results as a signal to relax demand or add capacity, not as a Solver bug. Integer and binary constraints belong only on decision variables, not on calculated totals. Print Start Here notes so the next user knows which cells are Goal, variables, and limits. Keep decision cells as constants, never formulas, so Solver can freely change them. Name the objective cell clearly and document Max versus Min before you click Solve. Put right-hand-side limits in cells, not hardcoded dialog numbers, so scenarios stay editable. After Solve, skim the Answer Report for binding constraints before you trust the mix. If the model is linear, choose Simplex LP; GRG Nonlinear is the wrong default for LP. Non-negativity belongs in Make Unconstrained Variables Non-Negative or explicit >= 0 constraints. Save a Load/Save model block on the sheet so classmates and teammates can reload parameters. Treat infeasible results as a signal to relax demand or add capacity, not as a Solver bug. Integer and binary constraints belong only on decision variables, not on calculated totals. Print Start Here notes so the next user knows which cells are Goal, variables, and limits. Keep decision cells as constants, never formulas, so Solver can freely change them. Name the objective cell clearly and document Max versus Min before you click Solve. Put right-hand-side limits in cells, not hardcoded dialog numbers, so scenarios stay editable. After Solve, skim the Answer Report for binding constraints before you trust the mix. If the model is linear, choose Simplex LP; GRG Nonlinear is the wrong default for LP. Non-negativity belongs in Make Unconstrained Variables Non-Negative or explicit >= 0 constraints. Save a Load/Save model block on the sheet so classmates and teammates can reload parameters. Treat infeasible results as a signal to relax demand or add capacity, not as a Solver bug. Integer and binary constraints belong only on decision variables, not on calculated totals. Print Start Here notes so the next user knows which cells are Goal, variables, and limits. Keep decision cells as constants, never formulas, so Solver can freely change them. Name the objective cell clearly and document Max versus Min before you click Solve. Put right-hand-side limits in cells, not hardcoded dialog numbers, so scenarios stay editable. After Solve, skim the Answer Report for binding constraints before you trust the mix. If the model is linear, choose Simplex LP; GRG Nonlinear is the wrong default for LP. Non-negativity belongs in Make Unconstrained Variables Non-Negative or explicit >= 0 constraints. Save a Load/Save model block on the sheet so classmates and teammates can reload parameters. Treat infeasible results as a signal to relax demand or add capacity, not as a Solver bug. Integer and binary constraints belong only on decision variables, not on calculated totals. Print Start Here notes so the next user knows which cells are Goal, variables, and limits. Keep decision cells as constants, never formulas, so Solver can freely change them. Name the objective cell clearly and document Max versus Min before you click Solve. Put right-hand-side limits in cells, not hardcoded dialog numbers, so scenarios stay editable. After Solve, skim the Answer Report for binding constraints before you trust the mix. If the model is linear, choose Simplex LP; GRG Nonlinear is the wrong default for LP. Non-negativity belongs in Make Unconstrained Variables Non-Negative or explicit >= 0 constraints. Save a Load/Save model block on the sheet so classmates and teammates can reload parameters. Treat infeasible results as a signal to relax demand or add capacity, not as a Solver bug. Integer and binary constraints belong only on decision variables, not on calculated totals. Print Start Here notes so the next user knows which cells are Goal, variables, and limits. Keep decision cells as constants, never formulas, so Solver can freely change them. Name the objective cell clearly and document Max versus Min before you click Solve. Put right-hand-side limits in cells, not hardcoded dialog numbers, so scenarios stay editable. After Solve, skim the Answer Report for binding constraints before you trust the mix. If the model is linear, choose Simplex LP; GRG Nonlinear is the wrong default for LP. Non-negativity belongs in Make Unconstrained Variables Non-Negative or explicit >= 0 constraints. Save a Load/Save model block on the sheet so classmates and teammates can reload parameters. Treat infeasible results as a signal to relax demand or add capacity, not as a Solver bug. Integer and binary constraints belong only on decision variables, not on calculated totals. Print Start Here notes so the next user knows which cells are Goal, variables, and limits.
FAQ
Which engine? Simplex LP for linear cost × shipment models.
Can I maximize profit on lanes instead of minimize cost? Yes — put profit coefficients in the matrix and choose Max; constraints stay the same.
Is this Example 1 or 2? Treat transportation as an advanced companion to Example 2, not the beginner “enable Solver” lesson.
CTA
Keep a labeled Solver practice workbook handy: Excel Solver Example 2 Template. Return to the pillar guide.
