In cell B16 we calculate the costs of delivery by multiplying the cost of transport per piece with the numbers actually transported. We copy this formula upto cells G16.
In cell C16 we calculate the total cost of delivery.
In cells I9 to I12 we sum the total quantity delivered from each warehouse to the shops or retail stores.
After having entered this data we click on ‘DATA’ tab and then select Solver.In the ‘Solver Parameters window, we set the target cell to I16 which represents our total costs that we wish to minimize or optimize. In the ‘Equal to: ‘ options we select ‘Min’. In the ‘By Changing Cells’ text box we enter B9 to G12, the cells that will contain the optimized delivered quantities.
We then click on ‘Add’ against the ‘Subject to constraints’ and define the constraints one after the other:
We wish that the delivered quantity must be equal to ordered quantity (B7:G7=B14:G14).
We ensure that only complete pieces of goods and not fractions(!) are delivered by applying the constraint that cells ‘B9:G12 >=int’ (int = integer).
Also the goods delivered must always have a positive value or 0. So we set the value of cells ‘B9:G12>=0’
Lastly, we define that the quantity of goods delivered must be equal to or less than the available stocks in the depots (I9:I12<=I2:I5).
We finally click on solve to see the calculated result.
In summary, using Solver is easy if you arrange your data appropriately and know exactly what calculations you wish to perform.
Using Solver to solve transportation or distribution problems