The methods of solving optimization problems using Microsoft Excel macros

Abstract

The aim of the article is to automate the calculation algorithm of the distribution method for the transition from static to dynamic calculations. The paper investigates various economic and mathematical methods and models used in various fields of economics and logistics systems. Based on the analysis of scientific research, it was found that in economic and mathematical modeling, the most common methods are methods of linear programming. Recently, logistics has been widely used. Logistics is implemented by management bodies, logistics departments and equipment, which is expressed in the combination of transport, technical, material support. Mathematical modeling is used in the study of processes in different areas of the economy. It is utilized to determine the optimal combination of areas, balancing the production and use of resources to ensure optimal use of available reserves. The system of models is based on the principle of purposeful development of economic sectors and aims to optimize the production program of enterprises. Economic and mathematical models can be an important tool for the managers in various fields to predict the possible consequences of any action taken. The work is devoted to forecasting models of logistical support of various needs, which are realized by applying the distribution method to optimize the use of resources. The analysis and evaluation of the means of supply and procuring was carried out. This made it possible to model the situation, set a specific goal, choose ways to achieve it and get the optimal result. Visual Basic for Applications (VBA) tools in Microsoft Excel was used to automate calculations and practically implement the reference plan for solving the optimization problem. The programming in the VBA for Excel of the reference plan calculation by the method of the north-west corner allows user to change the quantity of suppliers’ stock, the level of customers demand and tariffs dynamically. It was determined the direction of further research which is to expand the developed coding to automate subsequent calculations of the distribution method. The practical value of the article is that program code can be used by any user by inserting it into Excel macros.