Content uploaded by Margarita Gocheva

Author content

All content in this area was uploaded by Margarita Gocheva on Mar 13, 2021

Content may be subject to copyright.

AIP Conference Proceedings 2333, 150005 (2021); https://doi.org/10.1063/5.0042520 2333, 150005

© 2021 Author(s).

Modeling the transportation assesment with

MS excel solver

Cite as: AIP Conference Proceedings 2333, 150005 (2021); https://doi.org/10.1063/5.0042520

Published Online: 08 March 2021

Velika Kuneva, Marian Milev, and Margarita Gocheva

Modeling the Transportation Assesment with MS Excel

Solver

Velika Kuneva1, a), Marian Milev2, b) and Margarita Gocheva 1, c)

1Department of Mathematics and Informatics, Faculty of Economics, Agricultural University - Plovdiv, Blvd.

“Mendeleev”12, 4000, Bulgaria

2Department of Mathematics and Physics, Faculty of Economics, University of food technologies-Plovdiv, 26

Maritza Blvd, 4000, Bulgaria

a) kuneva@au-plovdiv.bg

b) Corresponding author: marianmilev2002@gmail.com

c) gocheva@au-plovdiv.bg

Abstract. In this article, using a computer program - electronic spreadsheet, a transport task is solved. One of the

advantages of algorithmic tasks is that it can be solved by software that significantly extends their practical application

related to data processing and execution of numerous computational operations.

INTRODUCTION

Optimization methods are considered as mathematical models of the main economic-production tasks, which

contributes to the clarification of their significance for the practice. Much of the economic tasks can be formulated as

linear models, and it is imperative to have appropriate mathematical skills for compiling an appropriate economic-

mathematical model as well as knowledge related to the methods of solving them. The most effective solution from

the set of all possible solutions is called optimal.

Some classes of tasks that have certain structural features can be separated from the tasks of linear optimization.

For these tasks, the general methods of linear optimization can be considerably simplified or constructing methods

can be more efficient than the simplex method.

The task we are looking at was formulated in 1939 by several authors, and this task is also known as a classical

transport task.

Despite its name, originally from problem solving, the Transport Task method is used for solving various types of

tasks related to route identification not only in transport, but also in computer networks, resource handling and resource

management, inventory management, working schedules and shifts, etc.

Such an approach has also been used in the development of (Marinov and Marinova, 2016) to solve a transport

problem, and the approaches of (Dimova, 2019) for basic foods and costs are interesting.

EXPOSE

Task Formulation

The transport task is formulated as it follows:

Applications of Mathematics in Engineering and Economics (AMEE’20)

AIP Conf. Proc. 2333, 150005-1–150005-10; https://doi.org/10.1063/5.0042520

Published by AIP Publishing. 978-0-7354-4077-7/$30.00

150005-1

Consider the following task. At the points m21 ,...,

(which we will call producers) is produced output in

quantity, respectively а1, а2,…..аm. Points n21 ,...,

(we will call them users) need the same output in quantities

respectively b1, b2,……, bn. There is a balance between production and consumption i.e.

m

1i

n

1j

ji ba . The

transport costs per unit from point

m,...,2,1i

i to point

n,...,2,1j

j

are cij. To devise up a plan for the

procurement of points n21 ,..., of production from the points , so that the needs of consumers are

met, the goods of the producers to be disposed of and the total transport costs to be minimal.

This task is called transport task and was first examined by LV Kantorowicz in 1939.

Typically, the transport task (T3) is set with the so-called transport table (Table 1), which is sufficient to define

each specific task.

Bj

AiB1B2… Bn a

i

A1 c11 c12 … c1n a1

A2 c21 c22 … с2n a2

… … … … … …

Am cm1 cm2 … cmn am

bjb1b2… bn

TABLE 1. Transport table.

The matrix

mnmm

n

n

ccc

ccc

ccc

C

...

............

...

...

21

22221

11211

is called Transport Cost Matrix.

Let’s create the mathematical model of the task.

For this purpose let’s denote with ij

х the quantity of production, sent from the producer i

A to consumer

njmiB j,1;,1 .

The matrix

mnmm

n

n

xxx

xxx

xxx

X

...

............

...

...

21

22221

11211

is called matrix of transportations.

m21 ,...,

150005-2

If every element ij

x of this matrix is put in the cell (i, j) matrix of transportations, we have the so-called extended

matrix of transportations.

The target function, expressing general transportation cost, will be linear and will be:

n1n112121111 xc...xcxcXL

n1n112121m1m

n2n222222121

xc...xcxc

..............................................

xc...xcxc

The restrictive conditions imposed on the variables ij

x, will be linear equations:

1. Because each producer's production must be fully sold, the equations are in effect:

mmn2m1m

2n22221

1n11211

ax...xx

...............

ax...xx

ax...xx

2. Because the needs of all users must be met, the equations are in effect:

nmnn2n1

22m2212

11m2111

bx...xx

...............

bx...xx

bx...xx

All transported quantities of production ij

x should be non-negative, i.e.

0xij , mi ,1, nj ,1

Similarly, quantities

miai,1 and

njb j,1 should always be considered positive. Given that the goal is

to minimize total transport costs, we obtain the following mathematical model of the transportation task:

To find the minimum of linear function

m

i

n

j

ijij xcXL

11

under the following restrictive conditions:

and under the conditions of a balance between produced and consumed output:

m

i

n

j

ji bа

11

150005-3

So formulated transport task with the balance condition is called a closed transport task.

Algorithm of Solving a Transport Task with the Capabilities of MS Excel Solver

(using MS EXCEL 2016)

MS Excel has a module for solving optimization tasks. The module is called Solver and can be found in the Data

menu. Before it is used for the first time, it must be enabled. This happens in the following order: Start MS Excel;

from menu File choose Options;

Dialog box Options of Excel appears;

submenu Add-Ins → Manage -> choose Excel Add-ins;

click GO;

Dialog box Add-ins appears;

Check Solver Add-in;

Click OK.

FIGURE 1. Dialog box to activate Solver

150005-4

In this way, we already have a program for solving optimization tasks.

In Excel, we open a worksheet and enter data from the transport task we optimize. The mandatory conditions for

non-negative solutions are entered later.

Task. To solve the following closed-type transport task.

В

j

A

i

B

1

B

2

B

3

В

4

a

i

A

1

2 3 2 4 30

A

2

3 2 5 1 40

A

3

4 8 2 6 20

b

j

10 40 30 10

In Fig. 2 is shown a spreadsheet with the mathematical model of the given task.

FIGURE 2.

A spreadsheet with the task model

Filling out the Spreadsheet

1. We enter the transport costs in the cells $С$3:$F$5, and cells $G$3:$G$5 – the constants from matrix А(ai), and

in cells $C$6:$F$6 – constants from matrix B (b

i

) from the task.

2. In cell $I$8 we enter the formula which will help us calculate the target function, i.e.

=SUMPRODUCT(C3:F5;C11:F13).

3. In the second spreadsheet C14 we enter the formula = SUM(C11:C13) and after we position the cursor in the

cell, a zero appears.

150005-5

FIGURE 3. A dialog box expressing the function of summing up the quantities of users.

Then we copy the formula in the next cells D14:F14 where zero value also appears. Similarly, a formula is

introduced for the cell G11 and copy it in cells G12 и G13.

FIGURE 4. A dialog box expressing the function of summing up the quantities of producers

It is necessary to use absolute addressing of the cells that contain the task variables when compiling the formula

(put the character $ in front of the column name and / or the row number).

For large-scale tasks, it's convenient to use the built-in feature SUMPRODUCT, which calculates a scalar product

of two unidirectional vectors.

After entering the input data and activating the formula from the menu Data → Solver blank, a dialog box Solver

Parameters opens where the formula for calculating the value of the target function is pre-entered, as shown on Fig.5.

150005-6

FIGURE 5. Dialog box “Solver Parameters”

It is recommended before starting Solver, current cell to be the target function, in our case $I$8.

By Equal To, the radio button is selected according to the model criterion, i.e. Min.

By Changing Cells, we choose the cells containing the values of the task. In the specific task these are the cells

$C$11:$F$13 where the target function gets its minimum (Fig.6).

FIGURE 6. Dialog box with scoppe of cells with changing values

150005-7

In the field Subject to the Constraints we enter the constraint conditions with the Add button, or we modify it with

button Change, or we remove already entered conditions with the button Delete.

When we click on the Add button a dialog box appears with constraint (Fig.7).

FIGURE 7. Dialog box “Add Constraint”

Dialog box Add Constraint consists of following fields:

Cell Reference – we enter the cells on the left side of the constraint conditions. Simultaneously, several

constraints of the same type may be entered.

type of the constraint: <=, =, >=.

Constraint – we enter the cells from the right side of constraint.

When entering the actual constraint is complete, we click on the Add button, if new constraint should be

entered or OK, if the entry is completed.

We mark the option Make Unconstrained Variables Non-Negative, because all variables in the task should be

non-negative. Changing other values is not recommended. Finally, with the button OK we return in the dialog box

Solver Parameters.

In Fig.8 Solver Parameters dialog box for the current task is represented.

150005-8

FIGURE 8. Dialog box Solver Parameters for the current task

From the dialog box Solver Parameters, we click on Solve, to get the solution of the task. The solution of the task

is shown in fig.9.

FIGURE 9. Spreadsheet and dialog box Solver Results

150005-9

Dialog box Solver Results contains data about the result from the performed optimization. In the present

case all the constraint conditions and conditions for optimality are fulfilled. The optimal solutions are from the cells

$C$11:$F$13 and we get optimal plan 180.

Via the dialog box Solver Results it is possible to save the found optimal (with the button Keep Solver

Solution) or to reset the original look of the worksheet (with the radio-button Restore Original Values).

From this dialog box it is possible to provide a reference to the solution of the task (Answer Report).

CONCLUSION

Based on the proposed description and the presented solution of a transport task in the environment of MS Excel,

using the capabilities of MS Excel Solver, the following conclusions can be made:

The proposed algorithm for describing a transport task and its subsequent solution using MS Excel Solver

is easy to apply;

Using software applications greatly accelerates computational procedures;

The utilized functionality of the "Solver" add-on can be used to solve transport-type tasks as well as to solve

tasks in other areas.

REFERENCES

1. D. Dimova, “Studying the consumption of some basic foods” in International scientific conference

“Unitech 2019”, Gabrovo, Bulgaria, (2019), pp. III-29-III-32, ISSN 1313-230X.

2. D. Dimova, “Analyzing the expenditure on tourist trips of Bulgarian citizens”, in International scientific

conference “Unitech 2019”, Gabrovo, Bulgaria, (2019), pp. III-33-III-36, ISSN 1313-230X

3. I. Ivanova, M. Milanova and V. Kuneva, “Handbook on Applied Mathematics” in Academic Press AU,

Plovdiv (2011).

4. I. Ivanova and V. Kuneva, “Handbook on Optimization Methods” in Academic Press AU, Plovdiv (2018).

5. M. Marinov and L. Marinova, “Algorithm for calculating the transportation problem and its solution in

Excel” in Scientific Works in Rousse University, Russe (2016), vol. 55. Issue 3.3, pp. 19-24.

150005-10