Logistics Project : How to Solve a Transshipment Problem Using Linear Programming via Excel Solver
What is Transshipment ?
Transshipment refers to the transportation of cargo and containers moved from one vessel to another while in transit to the final destination.When not planned properly, it can cause delays in delivery or damage to the cargo, which may impact the supply chain efficiency. But, in spite of these risks, transshipping is an integral part of the global trade network.
That’s why mastering the planning of transshipments is vital.
The Problem We are Solving : Cost Minimization
When planning transshipment operations, there are a number of different factors that need to be taken into account, including the type of cargo, the destination, the transit time, the available infrastructure and the mode of transportation. For the sake of this project, we’re focusing on optimizing the transshipment process while considering the cost as the major deciding factor.
A US based company is considering its best transshipment planning while factoring its shipping costs to its warehouses, and while balancing its Supply capacity and demand, making this a cost minimization linear programming problem.
The Objective of the Project
The goal is to calculate the miminum required cost for the transshipment operations of this company, all the while establishing a transportation plan.
Solving The Problem via Excel Solver
Excel Solver is an optimization tool that can be used to determine how the desired outcome can be achieved by changing the assumptions in a model. It is a type of what-if anaylysis and is particularly useful when trying to determine the “best” outcome, given a set of more than two assumptions.
First Step : Setting up the Intermediate and Finale Destination Warehouses in One Table
Leaving blank cells in the case of the warehouse facing its same type ( Grey coloured cells in the picture on top) will lead to Excel filling in the cells by default to zero, since this is a minimization problem, the Excel Solver will choose that particular warehouse even when it’s not a logical solution (We can’t depart from and arrive to the same warehouse). To avoid this mix up, we are adding randomely a cost that is higher than all other existing shipement costs in the table, making it impossible for Excel Solver to choose that route.
Second Step : Using the Solver add-in
After the first step, a second table is created taking into consideretion the constaints of the transshipment problem : the demand of a final destination warehouse cannot be more than its supply capacity , and the intermediate centers are neither consumers nor producers, what they receive has to be exactly equal to what they send;
After the calculation, the total cost of the transshipment operations is 12400 $. The optimal transportation plan is also drawn up :
