Transport resolution with Excel

How to solve a transport problem with Excel (units leaving from factories to customers while minimizing the cost of transport).

Formulate the transport problem with Excel

To formulate this transport problem, the following three questions must be answered.

  • What are the decisions to be made? To resolve this problem, Excel must determine the number of units to ship from each factory to each customer. (in yellow)
  • What are the constraints on these decisions? Every factory has a fixed supply and every customer has a fixed demand. (in light blue)
  • What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the shipments. The objective is therefore to minimize this quantity. (in dark blue)
solve transport problem with Excel simplex

Let us name the following ranges:

Beach nameCells
UnitCostC4: E6
ShipmentsC10: E12
TotalInC14: E14
DemandC16: E16
TotalOutG10: G12
SupplyI10: I12

And let's insert the following functions:

solve transport problem with Excel simplex

Solve the model

Let’s enter the solver parameters:

solve transport problem with Excel simplex

The optimal solution is:

solve transport problem with Excel simplex
To share
%d bloggers like this: