Linear Programming in Transportation Problem


A company currently ships products from 5 plants to 4 warehouses. The company is considering the option of closing down one or more plants. This would increase distribution cost but perhaps lower overall cost. The company’s objective is to minimize the total cost. Formulate this as an optimization problem to be able to find out what plants, if any, should the company close, and the number of products to be shipped from each plant to each warehouse.

Transportation Costs (per product)
 Plant 1Plant 2Plant 3Plant 4Plant 5
Warehouse 1$4$2$3$2.5$4.5
Warehouse 2$2.5$2.6$3.4$3$4
Warehouse 3$1.2$1.8$2.6$4.1$3
Warehouse 4$2.2$2.6$3.1$3.7$3.2
Warehouse 115
Warehouse 218
Warehouse 314
Warehouse 420
 Capacity (x 1000 products)Fixed costs ($)
Plant 12012000
Plant 22215000
Plant 31717000
Plant 41913000
Plant 51816000


To address this problem, we can start with a simple table where we will put all the values in one place so that it will look accessible.

From To->Warehouse 1Warehouse 2Warehouse 3Warehouse 4Supply
Plant 333.42.63.117
Plant 42.534.13.719
Plant 54.5433.218

Here 20 number of relation available

XP1W1    = Number of Product shipped from Plant 1 to Warehouse 1



XP5W4 = Number of Product shipped from Plant 5 to Warehouse 4

XPiWj  = Number of Product shipped from Plant i to Warehouse j

Where, i = Plant 1-5, j = Warehouse 1-4

Objective function

Min [12000 + 4 XP1W1 + 2.5 XP1W2 + 1.2 XP1W3 + 2.2 XP1W4 + 15000 + 2 XP2W1 + 2.6 XP2W2 + 1.8 XP2W3 + 2.6 XP2W4 + 17000 + 3 XP3W1 + 3.4 XP3W2 + 2.6 XP3W3 + 3.1 XP3W4 + 13000 + 2.5 XP4W1 + 3 XP4W2 + 4.1 XP4W3 + 3.7 XP4W4 + 16000 + 4.5 XP5W1 + 4 XP5W2 + 3 XP5W3 + 3.2 XP5W4]


Supply Constraints:

XP1W1 + XP1W2 + XP1W3 + XP1W4   ≤ 20

XP2W1 + XP2W2 + XP2W3 + XP2W4    ≤ 22

XP3W1 + XP3W2 +XP3W3 + XP3W4     ≤ 17

 XP4W1 + XP3W2 + XP3W3 + XP3W4      ≤ 19

XP4W1 + XP3W2 + XP3W3 + XP3W4        ≤ 18

Total Supply ≤ 96

Demand Constraints

XP1W1 + XP2W1 + XP3W1  + XP4W1 + XP4W1  = 15

XP1W2 + XP2W2 + XP3W2 + XP4W2 + XP5W2  = 14

XP1W3 + XP2W3  + XP3W3 + XP4W3 + XP5W3 = 18

XP1W4   + XP2W4   + XP3W4   + XP4W4   + XP5W4   = 20

XPiWj  ≥ 0

Implementation in Excel:

In the first table the Transportation Costs are put as it is given in the question.

Transportation Volume Table:

But there is some work has been done in the second table (Transportation Volume).

We know in general

Total cost = (production cost + Transportation cost) * quantity + fixed cost

Here production cost is not mentioned hence we can write

Total cost = Transportation cost * quantity + fixed cost

To find out total cost we have transportation cost from plants to warehouse and fixed cost of the plants.

In this question plants in total production capacity is higher than the demand. So to use the plants efficiently as well as to meet the demand we have to find out the optimum plant production as not all the plants are equally efficient in terms of the fixed cost and the transportation cost.

So our goal is to find out the optimum production volume of each plant.

To achieve this goal, we will use Microsoft Excel Solver function.

Demand Constraints:

Before using the function, we have to put the constraints into this excel file.

One thing we can take for granted in this question, which is we have to meet the demand, hence we have put the demand in the I column and given a constraint that $H$18 = $I$18 and so on. To make this happen we have to vary the production in the plants as well.

So we made the yellow marked 20 cells as variable cells.

At the end the Demand column will also accumulate the all the plants supply to the warehouses separately (H18 = SUM(C18:G18) and so on) and Supply row will accumulate the different Plants supply quantity to different warehouses (C22 = SUM(C18:C21) and so on)

Transportation Total Cost Table:

We will simply multiply transportation cost with product quantity.

Total Cost Per Plant:

Total cost can be calculated by adding the plant production and the fixed cost.

Capacity Constraints :

Each of the plants can produce up to its given capacity, hence the total transported units from each plant is less than or equal to the plant capacity. Capacity constraints can be implement by this way (C22 ≤ C34 and so on)

Objective function:

Objective function is J39 = SUM(C38:G38) to be minimum

Final Result:

In the second table: Transportation Volume, we can see that the total transported unit from Plant 5 is zero to all warehouse.

Which means for operating cost to be minimum Plant 5 to be shut down.

We can also see that Plant 1 & 2 is operating at its highest capacity and Plant 3 is used lesser than other three plants.