Description

This downloadable virtual product contains a case study solving a multi-modal transport model with a transport mode Excel Solver mixed-integer (MIP) model. The case study assigns shipping volumes to transport modes and routes in a cost optimal way, subject to capacity constraints. The transport cost model comprises fixed and variable transportation costs. The transport modes are rail, road, air, and sea. The fixed transportation costs are rental fees for the transport vehicle or vessel. Fixed costs are only relevant if a vehicle and vessel transports cargo.

In detail, this virtual product contains:

• PDF file with a presentation of the case study and description of the transport problem.
• Excel sheet with Excel Solver model implementation of the blend optimization model.
• Instructions for how to activate the Solver add-in in Excel.

Who will benefit from this transport mode  Excel Solver optimization model?

This downloadable product suits supply chain analysts, operations researchers, transport planners, and students that want to:

• Get a hands-on example on a multi modal transport problem.
• Learn how blender problems are solved using mathematical programming.
• Obtain an mixed integer programming template in Excel.
• Learn Excel Solver.
• Learn mixed-integer programming and the use of integer and binary optimization variables.

Brief case study description of transport mode problem

A large volume manufacturer has produced a large amount of product on stock. The products go to three customers. The manufacturer ships product by rail, road, air, and sea. Not all customers can receive products via all of these options.

Both fixed and variable transport costs apply. Fixed transport costs are the rental fees for the vessels and vehicles used for transport. These fixed transport costs only apply if the vehicle is used, i.e. rented. All transport modes have defined capacity constraints.

The problem: Minimize transport costs (sum of variable and fixed transport costs), ensuring that customer demand is satisfied subject to capacity constraints.

Relevant KPIs traced and outputted by this model

The model contained by this product tracks relevant KPIs.

The following KPIs supported by the model:

• Amount of product shipped by transport mode to each customer.
• Transport costs.
• Transport modes used.

Learn more about mathematical programming and analytics

You can find several other contributions on SCDA related to mathematical programming.

Here are some exemplary articles for you to read:

• Link: Using solvers for optimization in Python
• Link: Mixed-integer programming for a transport planning problem (video)