How to solve a transport problem with Excel (units leaving 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 solve this problem, Excel needs to determine how many 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 performance measure for these decisions? The overall measure of performance is the total cost of 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:
