Code School for Kids

Network Transportation Optimization and Linear Programming in Excel

Transportation optimization is one of the first problems tackled with linear programming, demonstrating the power of this tool for business applications (among others). Of course this was long before we had access to spreadsheet software such as Excel to facilitate modeling and solving this type of problem.

Like general optimization models transportation problems can be either linear or non-linear. This demonstration goes through a classic textbook example of a relatively simple transportation problem scenario, in which minimizing costs is desired, from three origins to four destinations to give an idea of how to work with this type of problem using Excel’s Solver Add-in. The video tutorial includes model development, solver setup (adding constraints discussion of the three Solver reports.

In addition to the characteristics shared by all optimization problems, objective, constraints and variables, transportation problems also have the following characteristics:

  • You are trying to MINIMIZE costs while meeting demand
  • Moving products from plants (origin) to distribution centers (destination)
  • Typically you are given:
    • Capacities (Supply) → what each facility can produce
    • Requirements (Demand) → forecasts of customer needs by “region”
    • Costs to transport products along each path

The spreadsheet used in this video may be downloaded here.