When I had taken course on LinkedIn Learning for "Transportation Problems Using Excel", I learned about the Transportation Problem. I thought this concept has to be shared in my blog as it bit confusing. I will be explaining what is meaning of transportation with diagrams, so that each and every one of you can understand the concept.
Transportation Problem:
To understand the transportation problem, it is necessary to understand the meaning of Linear Programming. Because, Transportation Problem will use the Linear Programming.
Linear Programming is basically finding how many goods are transported from source to destination having a balance between demand and supply and total cost of transportation is minimised. When demand and supply is equal, it is called balanced problem. When demand and supply is not equal, we convert into balanced problem by adding a row or column.
Linear programming has three important terms,
Objective Function: This tells us, what is the objective of the problem. Such as, minimising the cost for transporting the goods.
Constraints: These are the equations which states the limitation of each variable. Such as we cannot ship to Customers more than the capacity of the factory. Thus, Supply should be equal to demand. This is one constraint. Linear programming problem can have no constraints or more than one constraints.
Decision variables: These are the variables that we want to find. Basically in transportation problem, decision variables will be how much quantity of product we should ship from source (Factory) to destination (Customers) to minimise.
Now, let's understand how to analyse the problem in Excel. In supply chain transportation, we have to make sure all the demand has been met by the origin factory. In this type of problem, we satisfy the demand using direct transport from source to destination. Source can be Manufacturing Plant or Factory, Distribution Center, Company, Supplier etc. Destination can be Customer, Another Factory, End User etc.
Transportation Model in Excel:
In above figure, I have developed cost matrix(Blue Cells) which shows rates for transportation of Rice Bags from Factories to Customers. The yellow cell gives the cost that is minimised to ship after we run the solver. This we will see in coming section. Now, let's see how to develop a problem.
Objective Function: ( Yellow Cell)
In this problem, we have objective to minimise the cost of shipping.
Constraints:
This problem has 2 constraints, one is for supply and other is for demand. Thus, we have 2 constraints. First, constraint is sum of bags going to each Customer is equal to supply from factory. i.e. D10:G10 is equal to D11:G11 and H7:H9 is equal to I7:I9. This problem is the balanced problem because Supply equal to Demand as you can see in J15 and J16.
Decision Variable (Green Cells):
Decision variables in this problem are the quantity of bags shipped from each factory to each customers. We have to select D7:G9 cells in the Excel file. These indicates the cells which are decision variables.
Running Excel Solver (Linear Programming):
When we run the Solver, Solver gives a satisfied solution which states that all the constraints are satisfied. The solution states that, we can ship 17 rice bags from Factory 3 to Customer 1. Also, 10 rice bags from Factory 1 to Customer 2. And in the same way, I all the quantity shipped in given in the matrix above.
The optimal cost of shipping the rice bags is given in yellow cell. $ 1980 is the MINIMUM COST of shipping.
In this way, we model the problem pf transportation problem and find the minimum cost of shipping satisfying all the constraints. It helps us to know, what is the exact amount of goods that needs to be shipped from source to destination.
Thanks or reading this blog!
Have a nice day!
Soham S. Shinde