Modeling the transportation assesment with MS excel solver

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.

Full text

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