By the end of this article, you’ll get to know how to solve transportation problems using the Solver tool in Excel.
Transportation problem in Excel
Let’s look at a linear programming problem to minimize the transportation cost. Three companies C1, C2, C3 are expected to deliver goods to three destinations D1, D2, D3.
Form a table of values that includes the goods needed, goods that are available in the companies, goods that are reached, and goods that are to be delivered to the destinations.
The to-be-delivered goods are the sum of goods that are to be delivered to a destination by all the three companies and the reached goods are the sum of goods delivered by a company to all three destinations.
In the optimal solution cell enter the formula =SUMPRODUCT(array1,array2).
After entering all the details and the formulas go to the Data tab, under analyze group click on the solver tool.
Now the solver parameters dialog box opens up. In the set objective field select the cell to display the optimal solution.
In to field select Min so as to minimize the transportation cost. In the By changing variable cells field select the range of cells which depends upon the data of goods to be delivered, goods available, goods reached, and goods needed, In this problem C8:E10.
In subject to the constraints, field click on add, set to be delivered goods as less than or equal to available goods in the cell reference field and set reached goods as equal to the needed goods.
In select a solving method field select simple LP and click on solve.
The optimal solution gets displayed in the cell where you’ve set the objective.
This is how you solve a transportation problem in Excel using the solver tool.
