digestVidSign in
summary

excelisfun - Basic Excel Business Analytics #60: Excel Solver: Minimize Transportation Costs, Integer Variable

Thumbnail of the video “excelisfun - Basic Excel Business Analytics #60: Excel Solver: Minimize Transportation Costs, Integer Variable”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Technology Science Psychology
Tags: technology, science, psychology, 60
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

Short Summary

Main recommendation: Use a cross-tabulated decision-variable table, a SUMPRODUCT objective function, and Excel Solver (Simplex LP with integer constraints) to minimize the total shipping cost of moving units from three production plants to four distribution centers. The video works through a transportation problem drawn as a network graph, with supply limits on the origin side and exact order quantities on the destination side.

Key Points

  • Supply limits are Oakland 5,075, Portland 6,150, and Seattle 2,725 units; each origin may ship no more than its maximum.
  • Demand is exact: Spokane 6,150, Las Vegas 4,125, Coeur d'Alene 2,050, and Rapid City 1,625 units must each receive precisely their ordered amount.
  • The decision variables (x11, x21, x31, and so on) represent units shipped from each origin row to each destination column.
  • The objective is SUMPRODUCT of the units shipped array and the shipping cost-per-unit array, which must have identical dimensions (3 rows by 4 columns).
  • Integer constraints are added because partial units cannot be shipped.

Details Worth Noting

  • Concrete instructions:
    • Build a 3 by 4 grid with origins (Oakland, Portland, Seattle) as rows and destinations (Spokane, Las Vegas, Coeur d'Alene, Rapid City) as columns.
    • Enter dummy values (1) in the decision-variable grid and fill them using Control Enter.
    • Create a second grid labeled "Shipping Cost Per Unit" and enter the costs: Oakland 3.5, 2.5, 6.75, 7; Portland 6, 5.5, 2.25, 3.25; Seattle 2.5, 5, 3.75, 5.25.
    • Use Alt equals to sum the rows (supply totals) and columns (demand totals) of the units-shipped grid.
    • Enter demand constraints as totals equal to the ordered amounts, using a leading apostrophe before the equal sign so Excel treats the cell as text for the comparison label, then press Control Enter to populate.
    • Enter supply constraints as totals less than or equal to each plant's maximum.
    • Write the objective with SUMPRODUCT(units array, cost array) and label it total cost.
    • In Solver: set the objective cell to Minimize, select the decision-variable cells, check "Make Unconstrained Variables Non-Negative," choose Simplex LP, add the demand constraints (=), supply constraints (<=), and the integer constraint, then click Solve.
  • The equal and less-than-or-equal signs typed in the sheet are visual references only; Solver uses the constraints entered in its dialog.
  • This setup differs from earlier videos, where constraints were in a separate section; here the constraints relate directly to supply and demand totals.
  • The transportation problem is represented as a network graph, with arc labels giving per-unit cost.

Bottom Line

Setting up a transportation problem in Excel means arranging decision variables, cost data, supply and demand totals, and a SUMPRODUCT objective, then letting Solver minimize cost under exact-demand and capacity limits with whole-unit results.