Let us see how to use MS Excel to solve transshipment problems – to find the number of units to ship from each factory to each of the end users that minimizes the total cost of transportation.
The following hypothetical problem is used to understand this scenario where there are four factories that are located in four parts of the country and there are four markets in other parts of country where the goods have to be sold and find the total minimum cost of transportation.
We require to install “SOLVER” plugin in MS Excel to solve these problems. To install this OPEN MS EXCEL > FILE > OPTIONS > ADD-INS > SOLVER ADD-IN > GO > TICK SOLVER ADD-IN > OK
This procedure will install the SOLVER under DATA Tab of MS Excel.
The following problem is assumed as a hypothetical case.
To formulate this transportation problem, answer the following three questions.
- What are the decisions to be made? For this problem, we need Excel to find out how many units to ship from each factory to each customer. (INPUT)
- What are the constraints on these decisions? Each factory has a fixed supply and each customer has a fixed demand. (SUPPLY AND DEMAND)
- What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the shipments, so the objective is to minimize this quantity. (TOTAL COST)
Insert the following functions under TOTAL OUT column and across TOTAL IN row.
To solve the problem and to find the optimum (in this case minimum cost) solution, follow the below steps
Click on the TOTAL COST CELL (which will be marked 0)
Click on DATA Tab > SOLVER which will open the following window
In this box, enter the following details.
Set Objective: Which is to find the Total Cost. Click on the drop down icon and select the total cost cell as shown below.
To: Select Min button as the aim is to minimize the cost of transshipment.
By changing variable cells: Click on the drop down menu and select the entire row and column under shipments as shown below.
Subject to the Constraints: Click on Add button which will open a dialogue box and fill in the details as shown below. Here you have to equate Total IN to Demand and Total OUT to Supply.
Tick the box for Make Unconstrained Variables Non-Negative
Select a Solving Method: Select Simplex LP from the drop down box.
Click Solve > Keep Solver Solution > OK
You can see the problem being solved, with the Total Minimum Cost and the Shipments to different customers from different factories are being calculated automatically.
For this problem, the total minimum cost of shipment is USD 59190 with different shipment capacities (supply and demand) are being computed.