Transport task in Excel with example and description

Almost all transport problems have a unified mathematical model. The classical version of the solution illustrates the most economical plan for transporting identical or similar products from the production facility to the point of consumption.

The planning of transportation using mathematical and computational methods gives a good economic effect.



Kinds of transport tasks

The conditions and limitations of the transport task are quite extensive and varied. Therefore, special methods have been developed to solve it. With the help of any of them, you can find a bearing solution. And then improve it and get the best option.

Conditions for the transport problem can be represented in two ways:

  • in the form of a scheme;
  • in the form of a matrix.

In the process of finding a solution, there can be limitations (or the task is solved without them).

The following types of transport tasks are distinguished by the nature of the conditions:

  • opened transport tasks (the stock of the goods from the supplier does not coincide with the consumer's need for the goods);
  • closed (the total inventories of products from suppliers and consumers is coinciding).

Closed transport problem can be solved by the method of potentials. It is always solvable. The open type is reduced to the closed type by adding the missing units to the total stock or the demand for the goods in order to achieve equality.



Example of solving the transport task in Excel

Enterprises Producer1, 2, 3 and Producer4 produce a homogeneous product. Conventional units (volume of production) are 246, 186, 196 and 197. Then the goods arrive at five destinations: Consumer1, 2, 3, 4, and Consumer5. They are consumers. They are ready to take 136, 171, 71, 261 and 186 items a day.

The cost of product transporting (per unit) taking into account the distance from the destination:

The task is to minimize transportation costs for products shipping

  1. Let's see if the model of the transport task is balanced. For this purpose, let’s compare the total quantity of the produced goods with the total volume of the demand for the products: 246+186+196+197=136+171+71+261+186. Now we can make a conclusion that the model is balanced.
  2. Let's formulate the restrictions: the volume of transported products cannot be negative and all goods must be delivered to the destination points (because the model is balanced).
  3. Let's add the formulas for calculating the total demand for the product. This will be the first restriction.
    4. Data.
  4. Let's add the cost of product transporting per unit into Excel work cells.
    5. cost of product transporting.
  5. Let’s add the formulas for calculating the total volume of production. This will be the second restriction.
    6. calculating the total volume.
  6. Then we add the known values of the demand and the volume of production.
    7. SUMPRODUCT.
  7. We enter the formula of the function SUMPRODUCT(B3:F6;B9:F12) where the first array (B3:F6) is the cost of goods transportation per unit. The second one (B9:F12) is the values of transportation costs which we want to find.
    8. Range.
  8. Then select range: B9:E12 and call the "Solver" command on the "DATA" tab. We fill the dialog box. In the box "Set Objective" set a link to the function. We tick the "To: Min". In the field "By Changing Variable Cells:" set an array of criteria which we are looking for. In the "Subject to the Constraints" field: the desired array>=0, integers; " Limitation 1" = Volume of consumption; "Limitation 2" = Volume of production.
    9. Solve.
  9. Click "Solve". The operation will select the optimal variables for the given restrictions.
Report.

So this is a "raw" version of how this tool actually works. Experimenting with the obtained data, we find the appropriate values. We find the appropriate values experimenting with the obtained data.

Solution for the open transport task in Excel

Two options are possible in this type of task:

  • -the total volume of production exceeds the total demand;
  • -the total demand is greater than the volume of stocks.

An open transport task leads to a closed type. In the first case, introduce a fictitious consumer. His demand is equal to the difference in the total output and the amount of existing demand.

In the second case, introduce a fictitious supplier. The volume of its production is equal to the difference between the total demand and the total inventories.

The cargo volume for a fictitious participant is equal to 0.

Download example transport task in Excel

The transport task becomes closed when all conversions are completed. Then it should be solved in the usual way.

Read also on: the solution of the transport task using the method of potentials in Excel.


en ru