Transportation Network Optimization Solver Tutorial

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

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

You may also be interesting Linear Programming Optimization.

The spreadsheet used in this video may be downloaded here.