How to use Solver to minimize the delivery costs

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.

Microsoft Excel training home How to use Solver to minimize delivery costs
	In the example video we have demonstrated how a business can use Solver to mnimize delivery costs by optimizing the delivery of ordered goods from the correct depot. Here is a company in New Delhi that has 4 depots and it delivers the products to its various shops in the city. The costs of delivery from each depot to the shops is tabulated in the worksheets cells from B2:G5. The stocks for the products in the deopts is entered in cells I2:I5. The orders received by the company's shops is entered in cell B7:G7. In the cells B9 to G12 we enter the initial values of the delivery quantities (0). In the cells B14 to G14 we enter the total quantities that would be delivered. The formula in cell B14 = sum(B9:B12). This formula is then copied to cells C14 upto G14.
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 depot to the shops. After having entered this data we click on 'DATA' 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. In the 'Equal to: ' options we slect 'Min'. In the 'By Changing Cells' textbox 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.


How to use Solver to minimize delivery costs

How to use Solver to minimize delivery costs