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)
Let us name the following ranges:
| Beach name | Cells |
|---|---|
| UnitCost | C4: E6 |
| Shipments | C10: E12 |
| TotalIn | C14: E14 |
| Demand | C16: E16 |
| TotalOut | G10: G12 |
| Supply | I10: I12 |
| TotalCost | I16 |
And let's insert the following functions:
Solve the model
Let’s enter the solver parameters:
The optimal solution is:
EN 
FR 
ES