Maximum flow minimum cost. Excel Solution

In response to a similar post that spurred me to write this ...



Since I finished quite recently a railway university, and the logistics course was taking place there, pleasant memories flooded with me. As always, all the calculations were done manually, of course, afterwards, having thought a little, a simple little program was written, so to speak, to help classmates ...

but what was my surprise when I learned that all this, as they say, was invented without me, and besides, no one time))).

The article will focus on solving the transport problem using Microsoft Excel.

as always, everything ingenious is simple, there is such a menu item - Search for solutions ...



Let's create a new project in Excel, in it there are three tables: consumers, suppliers and cost.





Next, somewhere nearby, create 3 similar tables, where table A is empty, elements of table B will be equal to the sum of the corresponding rows of table A (from K to O), table B is similar to table B, only with columns of table A



')

Further it will be more difficult, we need to create a table D (this is the resulting table, which will reflect the optimal costs of transportation), each element of which will be equal to the product of the corresponding element of the cost table and table A





Well, I think, until now, it was all so hard, too ...

Now, for further optimization calculations, we need a target, so say, function.

To do this, select an empty place on the sheet, and write the formula there:





Inside the “sum of works” function, we have the addresses of our already fell into two tables: the value table and table A.

Well, all the preparatory moments have been passed, now it's up to excel ...

I use the 2007 version, although the difference is only in the location of the key elements ... and so, we go to the Data tape and find there

such a fad , most likely it will not be there)))), you just need to add it there. It is located in D construction sites, from there and pull it onto the panel.

Running the "search for solutions" we will see the following window





where as the target cell we specify our cell with the target function; then you need to think about the purpose of the problem. My goal is to get maximum profit, therefore I choose the maximum value (at this point everything is clear); as variable cells we set our table A.

Now we set limits. It can be seen from the figure that we have them. 3. Elementary restrictions are added.





Limitations:

1) table suppliers = table B

2) table consumers = table B

3) we say that the elements of our table A must be integers

Adding 3 restrictions, press the parameters button





Here it is important to check the linear model and non-negative values.

EVERYTHING!

Click OK, execute ...

and before us is the result-solution of our transportation problem in Excel



upd. How to add a solution search menu