This article will guide you to calculate the transport problem in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
Transport problem in Excel
Use the solver in Excel to find the number of units to be shipped from each factory to each customer to minimize total costs.
Model building
The model we will deal with looks like this in Excel.
Unit: unit
Factory: factory:
Customer: customer
Shipments: Shipments
Demand: demand
1. To construct this transportation problem, answer the following three questions.
a. What decisions will be made? For this problem, we need Excel to figure out how many units are shipped from each factory to each customer.
b. What are the constraints on these decisions? Each factory has a fixed supply and each customer has a fixed demand.
c. What are the overall performance metrics for these decisions? The overall performance metric is the total cost of Shipments, so the goal is to minimize this amount.
2. To make the model easier to understand, we will create named ranges as follows.
3. Add the following function.
Explanation: The SUM functions calculate the total number of goods shipped from each factory (Total Out) for each customer.Total Cost by product of UnitCost and Shipments.
Trial and error
With this formula, the analysis should be easy.
For example, if we transport 100 units from Factory 1 to Customer 1, 200 units from Factory 2 to Customer 2, 100 units from Factory 3 to Customer 1 and 200 units from Factory 3 to Customer 3, Total Out equals Supply and Total In by Demand. This solution has a total cost of 27800.
Solve the model
1. On the Data panel, click Solver.
Enter the solver parameters. The result should match the figure below.
2. Import TotalCost for Objective.
3. Click Min.
4. Import Shipments into Changing Variable Cells
5. Click Add
6. Click Add
7. Select ‘Make Unconstrained Variables Non-Negative’ and select ‘Simplex LP’.
8. Finally, click Solve.
Result/
Optimal solution:
Conclusion: it is optimal to transport 100 pcs from Factory 1 to Customer 2, 100 pcs from Factory 2 to Customer 2, 100 pcs from Factory 2 to Customer 3, 200 pcs from Factory 3 to Customer order 1 and 100 pcs from Factory 3 to Customer 3. This solution for a minimum cost of 26000. Everything is satisfied.
In addition, you can also view many other excel articles here.
