Formulation of the problem
Suppose that the company where you work has three warehouses, from where the goods go to five of your stores scattered throughout Moscow.
Each store is able to sell a certain quantity of goods known to us. Each of the warehouses has a limited capacity. The task is to rationally choose from which warehouse to which stores to deliver the goods in order to minimize the total transportation costs.
Before starting the optimization, it will be necessary to compile a simple table on an Excel sheet – our mathematical model describing the situation:
It is understood that:
- The light yellow table (C4:G6) describes the cost of shipping one item from each warehouse to each store.
- Purple cells (C15:G14) describe the quantity of goods required for each store to sell.
- Red cells (J10:J13) display the capacity of each warehouse – the maximum amount of goods that the warehouse can hold.
- Yellow (C13:G13) and blue (H10:H13) cells are the row and column sums for green cells, respectively.
- The total shipping cost (J18) is calculated as the sum of the products of the number of goods and their corresponding shipping costs – for calculation, the function is used here SUMPRODUCT (SUMPRODUCT).
Thus, our task is reduced to the selection of optimal values of green cells. And so that the total amount for the line (blue cells) does not exceed the capacity of the warehouse (red cells), and at the same time each store receives the quantity of goods it needs to sell (the amount for each store in the yellow cells should be as close as possible to the requirements – purple cells).
Solution
In mathematics, such problems of choosing the optimal distribution of resources have been formulated and described for a long time. And, of course, ways to solve them have long been developed not by blunt enumeration (which is very long), but in a very small number of iterations. Excel provides the user with such functionality using an add-in. Search Solutions (Solver) from the tab Data (Date):
If on the tab Data your Excel does not have such a command – it’s okay – it means that the add-in is simply not connected yet. To activate it open File, then select Parameters – Add-ons – About (Options — Add-Ins — Go To). In the window that opens, check the box next to the line we need Search Solutions (Solver).
Let’s run the add-on:
In this window, you need to set the following parameters:
- Optimize target function (Set tmoney cell) – here it is necessary to indicate the final main goal of our optimization, i.e. pink box with the total shipping cost (J18). The target cell can be minimized (if it is expenses, as in our case), maximized (if it is, for example, profit) or try to bring it to a given value (for example, fit exactly into the allocated budget).
- Changing Variable Cells (By changing cells) – here we indicate the green cells (C10: G12), by varying the values of which we want to achieve our result – the minimum cost of delivery.
- Consistent with restrictions (Subject to the Constraints) – a list of restrictions that must be taken into account when optimizing. To add restrictions to the list, click the button Add (Add) and enter the condition in the window that appears. In our case, this will be the demand constraint:
and limit on the maximum volume of warehouses:
In addition to the obvious limitations associated with physical factors (capacity of warehouses and means of transportation, budget and time constraints, etc.), sometimes it is necessary to add restrictions “special for Excel”. So, for example, Excel can easily arrange for you to “optimize” the cost of delivery by offering to transport goods from stores back to the warehouse – the costs will become negative, i.e. we will make a profit! 🙂
To prevent this from happening, it’s best to leave the checkbox enabled. Make Unlimited Variables Non-Negative or even sometimes explicitly register such moments in the list of restrictions.
After setting all the necessary parameters, the window should look like this:
In the Select a solving method drop-down list, you additionally need to select the appropriate mathematical method for solving a choice of three options:
- Simplex method is a simple and fast method for solving linear problems, i.e. problems where the output is linearly dependent on the input.
- General Downgraded Gradient Method (OGG) – for non-linear problems, where there are complex non-linear dependencies between input and output data (for example, the dependence of sales on advertising costs).
- Evolutionary search for a solution – a relatively new optimization method based on the principles of biological evolution (hello Darwin). This method works many times longer than the first two, but can solve almost any problem (nonlinear, discrete).
Our task is clearly linear: delivered 1 piece – spent 40 rubles, delivered 2 pieces – spent 80 rubles. etc., so the simplex method is the best choice.
Now that the data for the calculation is entered, press the button Find a solution (Solve)to start optimization. In severe cases with a lot of changing cells and constraints, finding a solution can take a long time (especially with the evolutionary method), but our task for Excel will not be a problem – in a couple of moments we will get the following results:
Pay attention to how interestingly the supply volumes were distributed among the stores, while not exceeding the capacity of our warehouses and satisfying all requests for the required number of goods for each store.
If the found solution suits us, then we can save it, or roll back to the original values and try again with other parameters. You can also save the selected combination of parameters as Scenario. At the request of the user, Excel can build three types Reports on the problem being solved on separate sheets: a report on the results, a report on the mathematical stability of the solution and a report on the limits (restrictions) of the solution, however, in most cases, they are of interest only to specialists.
There are, however, situations where Excel cannot find a suitable solution. It is possible to simulate such a case if we indicate in our example the requirements of the stores in the amount greater than the total capacity of the warehouses. Then, when performing an optimization, Excel will try to get as close to the solution as possible, and then display a message that the solution cannot be found. Nevertheless, even in this case, we have a lot of useful information – in particular, we can see the “weak links” of our business processes and understand the areas for improvement.
The considered example, of course, is relatively simple, but easily scales to solve much more complex problems. For example:
- Optimization of the distribution of financial resources by item of expenditure in the business plan or budget of the project. The restrictions, in this case, will be the amount of financing and the timing of the project, and the goal of optimization is to maximize profits and minimize project costs.
- Employee scheduling optimization in order to minimize the wage fund of the enterprise. Restrictions, in this case, will be the wishes of each employee according to the employment schedule and the requirements of the staffing table.
- Optimization of investment investments – the need to correctly distribute funds between several banks, securities or shares of enterprises in order, again, to maximize profits or (if more important) minimize risks.
In any case, add-on Search Solutions (Solver) is a very powerful and beautiful Excel tool and worthy of your attention, as it can help out in many difficult situations that you have to face in modern business.