USE OF ELECTRONIC SPREADSHEETS AS AN INTERFACE FOR MODELING MATHEMATICAL PROGRAMMING PROBLEMS

The effect of the popularization of computers and their contribution to a greater use of operational research techniques in companies are well known. The reduction of its cost, along with the increase of its performance, allow today that even large optimization problems are treated in personal computers. This, in the rooms of managers and analysts and no longer in the data processing centers where the mainframes are – or used to be.

This evolution of hardware was accompanied by an evolution of software, also contributing significantly to a greater use of operational research in companies. And this was no different for mathematical programming applications, which are the subject of interest in this study. These applications can be seen as a combination of two software, different from each other, but complementary in their functions:

• The first is the solver, which receives as input the matrix representing the mathematical programming problem with its constraints and objective function, applies one or more optimization methods (simplex, revised simplex, dual simplex, interior points, branch and bound and others), returning the optimal values ​​of the variables.
• The second software, the interface, has the basic function of generating this matrix, from a format that will depend on the implementation, which can be an algebraic modeling language, a spreadsheet, etc. The interfaces have instruments for data preparation, analysis of results, alteration and formulation of models.

Seeking to reach an increasing number of users, the providers of these applications sought to develop more user-friendly interfaces whose use was not restricted to specialists. In this way, innovations were incorporated in the sense of offering greater ease and speed in modeling as well as greater and better connectivity to database systems usually used and marketed in the market such as Access, Dbase, or even Excel and Lotus 123 products. that the advances obtained were not limited to a better interface, but also to more efficient solvers, incorporating the latest developments in terms of algorithms, capable of dealing with bigger problems and in less time, even on personal computers.

However, when presenting their products, suppliers have given great emphasis to the ease of development and use of the models, as well as their connectivity, as can be seen through the advertisements of these software in specialized magazines and also through their home-pages, disseminated on the Internet. They demonstrate, therefore, that they are attentive to the needs of their potential customers, as indicated by a survey carried out by Ballou & Masters [1], the main criterion for choosing software for localization and distribution of production, an important subset of programming applications mathematics, is precisely the ease of use. In second and fourth place in the ranking of this research, come the use of graphs for visualization of results and the compatibility between systems (connectivity), characteristics related to the interface. Lastly, computational speed. These results are shown in figure 1 below.

Among the currently available applications are those that use the spreadsheet environment as an interface for building mathematical programming models. These stand out precisely because of the ease of modeling and the fact that they do not require knowledge of a specific language, other than the basic operations used in common spreadsheets. In fact, its use has grown, even in other areas of operational research such as simulation, risk analysis, demand forecasting, taking advantage of the great acceptance that spreadsheets have in the business environment: there are estimates that there are currently around 30 million of users [3].

However, far from becoming a dominant standard, the adoption of spreadsheets as an interface for modeling mathematical programming problems has limitations that must be compared to the advantages offered.

The objective of this study is to evaluate the characteristics of this interface, identifying its advantages and disadvantages, based on a recent experience in the development of an optimization model for a beverage industry. The elaboration of this project made it possible to evaluate the characteristics of the optimizer based on spreadsheets both in terms of aspects of modeling and development of the system, as well as aspects related to its implementation in the company and use by users.

The evaluation was based on subjective criteria, based on the perception of those involved in creating the model. Previous experiences in the development of similar models were also considered, where different interfaces were used for modeling.

II - GENERAL CHARACTERISTICS OF THE PROBLEM STUDYED

The model was developed for a beverage industry with the objective of supporting the short-term planning of the manufacture and distribution of its products in a multi-plant system with national coverage. Its use had the requirements of being simple, easy to understand, not demanding from its users in-depth knowledge of any computational technique or language.

The combined production and transport model is a PPL whose objective function is to maximize the company's net margin. The decision variables refer to the quantity of each product to be produced in each factory, factory-depot allocations and transfers between factories. The company's logistics network comprises 8 factories and 72 warehouses, producing 44 products. The main constraints involved are production capacity constraints and demand constraints, which must be fully met. In total there are 28.150 variables and 3.400 restrictions.

III - THE INTERFACES OF THE EXISTING SOFTWARE

A typical interface uses a modeling language to represent the formulation of the linear programming problem. A variety of these languages ​​that are widely used are algebraic modeling languages. These are based on traditional mathematical notation, to represent the constraints and the objective function of the PPL.

Below is the mathematical formulation of a symbolic PPL.

The modeling of this problem, using an algebraic language (AMPL) is shown below:

One of the reasons for its great use, as pointed out by one of those responsible for the development of one of these algebraic languages ​​[2], is its familiarity to anyone who has notions of algebra and calculus. However, it is precisely because they do not require any kind of knowledge of algebra or calculus or even of traditional mathematical notation, that interfaces based on electronic spreadsheets have increased their popularity.

IV - INTERFACES BASED ON ELECTRONIC SPREADSHEETS

The same PPL formulated above can be represented through a spreadsheet, as shown below, where the spreadsheet-based optimizer What's Best! .

The formula =WB (… refers to one of the functions added to the basic Excel functions when installing the What's Best! software. In this case, it is used to represent the constraints of the problem. The variables, unless defined by otherwise, they are positive.

The sumproduct formula is one of the standard Excel formulas and returns as a result the sum of the product of the corresponding elements of two or more matrices; in this case the matrices cj and Xj.

In this example, cell G1 has been defined as the cell whose value is to be maximized. Cells D4 and D5 were defined as the decision variables, that is, the cells that will have their values ​​adjusted in order to maximize the value of cell G1.

V - OBSERVED ADVANTAGES

V.1 - Ease of understanding

In most applications, model users are not necessarily the same as those who developed them. In this way, unless the user's performance is extremely limited in relation to interaction with the model, it is desirable that it be as transparent and accessible as possible so that its understanding and subsequent operation are facilitated. These are undoubtedly features of this interface.

By using only standard spreadsheet formulas, it allows its users to understand its logic, without requiring knowledge of any specific modeling language. In addition, the formulation and assembly of constraints and objective function follows the same rules as spreadsheets commonly used in companies.

V.2 - High connectivity

Connectivity refers to the ability of the interface to establish connections between different databases, facilitating the import and export of data.

In operational applications such as the one developed for the beverage industry, the model parameters vary very frequently and the responsibility for updating them is carried out by several different sectors of the company:

• Transportation cost coefficients are updated by the transportation industry, which frequently monitors and evaluates freight costs;
• Production yield indexes for each product are measured and updated by the industrial planning and control sector;
• The unitary net margins of each product-market binomial are established and updated by the marketing sector.

In this case, it is necessary to have efficient means of importing and exporting data. It is relevant, therefore, to have high connectivity so that parameter updates can be done quickly and safely.

In spreadsheets, communication between databases takes place quite easily, either through dynamic links, where one spreadsheet references another, or even through importing files, as these support a wide range of formats from different databases. data.
V.3 - Ease of customization

Ease of customization refers to the ease of changing and adding features to the system in order to adapt it to specific user needs.

An example of these characteristics, based on the model developed for the beverage industry, refers to the automatic generation of management reports linked to the model's decision variables. In this way, a series of performance measures could be easily obtained, according to specific interests. For example, reports were developed on the idle capacity of the factories after the allocations were made, reports informing the total quantities transferred between factories and the production of each one. From an economic point of view, reports were developed informing the aggregated net margin by product, or by factory or even by warehouse.

In addition to reports, a series of particular procedures such as checking for inconsistencies in input data, automatic updating of data, checking available capacity, could be implemented from the resources of macros included in electronic spreadsheets.

It should be noted that what stands out here is not the exclusivity of the spreadsheet environment in the ability to develop such resources, since other interfaces also allow it, either through exporting data to more flexible data handling systems, or through the use of generic programming languages. What stands out is the low cost of this development, since the necessary tools are available in the spreadsheets themselves and their use is widespread.

VI – THE DISADVANTAGE: DIFFICULTY OF CHANGING THE LOGIC AND DIMENSIONS OF THE MODEL

The great shortcoming of using interfaces based on spreadsheets was noticed right at the beginning of the development of the model for the beverage industry. Initially designed to consider the existence of 60 warehouses, it had to be reformulated to accommodate 12 more due to changes in the company's structure. This modification involved rewriting practically the entire model, involving several hours of work. Likewise, given changes in the company's marketing policy, the problem was repeated when the mix of products was changed, making it necessary to remove some and include others.

This is therefore the weak point of this interface. When including or excluding an item from the model, in this case a warehouse or a product, the references between cells are changed, making it necessary to rewrite the formulas that define the relationships in the model.

When comparing with other interfaces, for example those that use algebraic modeling languages, in which changing the scale of the model basically consists of changing the cardinality of indices, the limitation of spreadsheets is evident due to their lack of flexibility, both in changing the dimension of the model, as well as changes in the interrelationships of the model's elements, that is, in its logic.

VII - CONCLUSION

The observed advantages are intrinsically related to the fact that electronic spreadsheets are already widely used in companies. As a consequence, it is easier to implement solutions based on this environment, mainly justified by the fact that users have less difficulties in using them.

The great and practically only disadvantage observed refers to the little flexibility in changing the logic and dimension of the models developed in spreadsheets. If this disadvantage is more or less critical, and may or may not contraindicate its application, it will depend on the type and frequency of changes that are necessary in the structure and dimension of the model.

The development of friendlier interfaces, such as those based on electronic spreadsheets, undoubtedly represents an impulse towards increasing the use of mathematical programming techniques, since these interfaces minimize the difficulties of use by non-specialists. However, its use does not contribute towards reducing the performance of professionals specialized in building models, but rather towards increasing the number of professionals who are not specialized in operating the applications.

VIII - BIBLIOGRAPHY

1. BALLOU, H. Ronald; MASTERS, James M. Commercial software for locating warehouses and other facilities. Journal of Business Logistics, vol. 14, no 2, 1993, p. 71 – 107.2. FOURER; GAY; KERNIGAHAN. AMPL: a modeling language for mathematical programming. The Scientific Press, 1993.
2. FOURER, Robert. Software survey: linear programming. OR/MS Today – On Line Edition, vol. 24, no. 1, February, 1997.
   URL: http://lionhrtpub.com/orms/orms-2-97/Linear-Programming.html4. Sam, Savage; Weighing the pros and cons of decision technology in spreadsheets. OR/MS Today – On Line Edition., vol. 24, no. 1, February, 1997.
   URL: http://lionhrtpub.com/orms/orms-2-97/savage.html
3. What's Best! User's Manual. Edited by Lindo Systems, Inc., 1996.

IX – ONLINE REFERENCES

AMPL (Compass Modeling Solutions, Inc.): http://www.modeling.com Frontline Systems (spreadsheet optimizers): http://www.frontsys.com AIMMS (Paragon Decision Technology) http://www.paragon.nl What's Best! – spreadsheet optimizer (LINDO Systems, Inc.):  http://www.lindo.com

CPLEX (CPLEX Optimization, Inc.): http://www.cplex.com

Authors: Leonardo Lacerda and Rafael Vasconcellos