Operations Research (OR) is a state of art approach used for problem-solving and decision making. OR helps any organization to achieve their best performance under the given constraints or circumstances. The prominent OR techniques are,
One of the problems the organizations face is the transportation problem. It originally means the problem of transporting/shipping the commodities from the industry to the destinations with the least possible cost while satisfying the supply and demand limits. It is a special class of linear programming technique that was designed for models with linear objective and constraint functions. Their application can be extended to other areas of operation, including
The notations of the representation are:
m sources and n destinations
(i , j) joining source (i) and destination (j)
cij 🡪 transportation cost per unit
xij 🡪 amount shipped
ai 🡪 the amount of supply at source (i)
bj 🡪 the amount of demand at destination (j)
Transportation problem works in a way of minimizing the cost function. Here, the cost function is the amount of money spent to the logistics provider for transporting the commodities from production or supplier place to the demand place. Many factors decide the cost of transport. It includes the distance between the two locations, the path followed, mode of transport, the number of units that are transported, the speed of transport, etc. So, the focus here is to transport the commodities with minimum transportation cost without any compromise in supply and demand. The transportation problem is an extension of linear programming technique because the transportation costs are formulated as a linear function to the supply capacity and demand.
Also checkout how IT is transforming industries?
Dive into the Top 5 Examples of How IT Uses Analytics to Solve Industry Problems in our new blog.
Transportation problem exists in two forms.
It is the case where the total supply equals the total demand.
It is the case where either the demand is greater than the supply, or vice versa.
In most cases, the problems take a balanced form. It is because usually, the production units work, taking the inventory and the demand into consideration. Overproduction increases the inventory cost whereas under production is challenged by the demand. Hence the trade-off should be carefully examined. Whereas, the unbalanced form exists in a situation where there is an unprecedented increase or decrease in demand.
Let us understand this in a much simpler way with the help of a basic example.
Let us assume that there is a leading global automotive supplier company named JIM. JIM has it’s production plants in many countries and supplies products to all the top automotive makers in the world. For instance, let’s consider that there are three plants in India at places M, N, and O. The capacity of the plants is 700, 300, 550 per day. The plant supplies four customers A, B, C, and D, whose demand is 650, 200, 450, 250 per day. The cost of transport per unit per km in INR and the distance between each source and destination in Kms are given in the tables below.
Here, the objective is to determine the unknown while satisfying all the supply and demand restrictions. The cost of shipping from a source to a destination is directly proportional to the number of units shipped.
Also, read our complete case study on Tapping Twitter Sentiments: A Complete Data Analytics Case-Study on 2015 Chennai Floods and see how sentiment analysis can shed light on critical events
Many sophisticated programming languages have evolved to solve OR problems in a much simpler and easier way. But the significance of Microsoft Excel cannot be compromised and devalued at any time. It also provides us with a greater understanding of the problem than others. Hence we will use Excel to solve the problem.
It is always better to formulate the working procedure in steps that it helps in better understanding and prevents from committing any error.
Steps to be followed to solve the problem:
Creating a transportation matrix:
A transportation matrix is a way of understanding the maximum possibilities the shipment can be done. It is also known as decision variables because these are the variables of interest that we will change to achieve the objective, that is, minimizing the cost function.
| From / To | A | B | C | D | Supply |
| M | xma | xmb | xmc | xmd | 700 |
| N | xna | xnb | xnc | xnd | 300 |
| O | xoa | xob | xoc | xod | 550 |
| Demand | 650 | 200 | 450 | 250 |
Define the objective function:
An objective function is our target variable. It is the cost function, that is, the total cost incurred for transporting. It is known as an objective function because our interest here is to minimize the cost of transporting while satisfying all the supply and demand restrictions.
The objective function is the total cost. It is obtained by the sum product of the cost per unit per km and the decision variables (highlighted in red), as the total cost is directly proportional to the sum product of the number of units shipped and cost of transport per unit per Km.
The column “Total shipped” is the sum of the columns A, B, C, and D for respective rows and the row “Total Demand” is the sum of rows M, N, and O for the respective columns. These two columns are introduced to satisfy the constraints of the amount of supply and demand while solving the cost function.
Formulate the constraints:
The constraints are formulated concerning the demand and supply for respective rows and columns. The importance of these constraints is to ensure they satisfy all the supply and demand restrictions.
For example, the fourth constraint, xma + xna + xoa = 650 is used to ensure that the number of units coming from plants M, N, and O to customer A should not go below or above the demand that A has. Similarly the first constraint xma + xmb + xmc + xmd = 700 will ensure that the capacity of the plant M will not go below or above the given capacity hence, the plant can be utilized to its fullest potential without compromising the inventory.
Solve using LP method:
The simplest and most effective method to solve is using solver. The input parameters are fed as stated below and proceed to solve.
This is the best-optimized cost function, and there is no possibility to achieve lesser cost than this having the same constraints.
From the solved solution, it is seen that plant M ships 100 units to customer A, 350 units to C and 250 units to D. But why nothing to customer B? And a similar trend can be seen for other plants as well.
What could be the reason for this? Yes, you guessed it right! It is because some other plants ship at a profitable rate to a customer than others and as a result, you can find few plants supplying zero units to certain customers.
So, when will these zero unit suppliers get profitable and can supply to those customers? Wait! Don’t panic. Excel has got away for it too. After proceeding to solve, there appears a dialogue box in which select the sensitivity report and click OK. You will get a wonderful sensitivity report which gives details of the opportunity cost or worthiness of the resource.
Basic explanation for the report variables,
Cell: The cell ID in the excel
Name: The supplier customer pairing
Final value: Number of units shipped (after solving)
Reduced cost: How much should the transportation cost per unit per km should be reduced to make the zero supplying plant profitable and start supplying
Objective coefficient: Current transportation cost per unit per Km for each supplier customer pair
Allowable Increase: It tells us the maximum cost of the current transportation cost per unit per Km can be increased which doesn’t make any changes to the solution
Allowable Decrease: It tells how much maximum the current transportation cost per unit per Km can be lowered which doesn’t make any changes to the solution
Here, look into the first row of the sensitivity report. Plant M supplies to customer A. Here, the transportation cost per unit per Km is ₹14 and 100 units are shipped to customer A. In this case, the transportation cost can increase a maximum of ₹6, and can lower to a maximum of ₹1. For any value within this range, there will not be any change in the final solution.
Now, something interesting. Look at the second row. Between MB, there is not a single unit supplied to customer B from plant M. The current shipping cost is ₹22 and to make this pair profitable and start a business, the cost should come down by ₹6 per unit per Km. Whereas, there is no possibility of increasing the cost by even a rupee. If the shipping cost for this pair comes down to ₹16, we can expect a business to begin between them, and the final solution changes accordingly.
The above example is a balanced type problem where the supply equals the demand. In case of an unbalanced type, a dummy variable is added with either a supplier or a customer based on how the imbalance occurs.
Need data for your next analytics project?
Discover Free Data Sets for Analytics/Data Science Project in our latest blog.
Thus, the transportation problem in Excel not only solves the problem but also helps us to understand how the model works and what can be changed, and to what extent to modify the solution which in turn helps to determine the cost and an optimal supplier.
If you found this helpful, and wish to learn more such concepts, head over to Great Learning Academy and enroll in the free online courses today.
