Lesson 264: Transport problems in Excel

This article will guide you to calculate the transportation problem in Excel.

Transport problem in Excel

Use Excel’s solver 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 see many other excel articles here.