Solver for Optimization Problems

Why optimization and Operations Reserch?

Optimization is now part of our life and even unconsciously we make decisions in order to obtain the best possible result, having started to be studied in a consistent and objective way by allies during the second world war when the task was given to a group of English engineers to find the best solutions on how to use war resources. At the end of the war, the methods then applied in military operations were adapted in order to improve efficiency and production in the civilian sector involved in the recovery effort. These studies were called Operations Research (OR).

An optimization problem or mathematical programming problem is often associated with the linear programming terminology created by George Dantzig, with the publication of the simplex algorithm in 1947, although much of the theory was introduced in 1939 by Leonid Kantorovich. Other optimization methods such as the gradient descent method, or the "steepest descent" method, and the least squares method are optimization techniques that date back to the time of the German mathematician Carl Gauss (1777 - 1855) and the English Isaac Newton (1642 - 1727).

Optimization has since been a tool applied to the most diverse sectors and functional areas to obtain better solutions and support in decision making, and for this reason it is a fundamental part in solving Operational Research problems.

 

Solver to solve optimization problems

Every day we are faced with the need to make decisions that we want to be the best possible, taking into account the means and resources available, with a view to achieving a certain result. The “best” solution, or “optimal” solution, generally means maximizing profits, minimizing costs, or finding the best possible quality for an available budget.

There are countless examples where optimization can be applied from the agricultural sector to industry, from the financial sector to the health sector, in construction, commerce, logistics, supply and distribution, tourism, sport, among others.

As representative examples we can include the allocation of resources such as money, time, space, raw materials, labor, among others, in investments, in choosing the location of new facilities or new warehouses, in choosing more profitable plantations, in determining the scale of medical personnel in a hospital, obtaining shorter routes for transporting goods and much, much more.

Use Excel Solver to find an optimal solution

The construction and resolution of mathematical models capable of describing a physical problem is the key to the optimization process, in which an objective function is formulated, subject to a set of restrictions. The objective function can be linear or non-linear, according  on the dependence of the variables. In the case of linear models with objective function and perfectly linear restrictions, the technique used to solve this type of problem is linear programming. For nonlinear models, there are several methods of solving this type of problem.

As a supplement to excel, Solver is par excellence a powerful optimization tool with which we can build a mathematical model that allows us to achieve a certain objective, by changing the variables, at the same time that a series of imposed restrictions are satisfied. 

Why use Excel Solver?

The great advantage of Solver is its use within excel which allows us to organize the mathematical model in its cells, with all the excel features. After defining the model, just open the Solver function window in excel and indicate the cell where the objective function is located, indicate the cells corresponding to the variables and the cells corresponding to the restrictions, and then the choice of the algorithm for solving the Most appropriate solver and a few seconds later we have the solution, which in principle will be an optimal solution.

Another advantage of Solver is the possibility of solving linear and non-linear models, as well as problems with integer programming, providing three very robust optimization algorithms. The vast majority of optimization problems involve linear programming, however there are several problems in which the objective function, variables or constraints assume a non-linear dependency and therefore have to be solved using Solver's own algorithms.

Solver Excel with VBA takes optimization to the next level

In addition to the advantages of Solver already mentioned, there is another one that is also very important, which is the possibility of using Solver excel and VBA (Visual Basic for Applications). The integration of Solver in Microsoft Excel allows programming of macros in VBA and that takes the resolution of optimization problems to another level. 

We can create VBA macros by automating the call to the Solver function without the need to open the Solver dialog window. This feature opens doors to the automation of problem solving with multiple objective functions, such as the optimization of a portfolio of shares on the stock exchange, allowing the definition of an optimal border line, or efficient frontier.

For optimization problems that consider more than one objective, which are often opposite, such as maximizing the performance of a machine and minimizing consumption and maintenance, there is no single solution that optimizes each objective simultaneously. These types of problems are often called multi-objective optimization, also known as Pareto optimization, which can be solved more quickly with the application of VBA to Solver.

When to use the solver in practice?

This powerful tool can be applied to any area of ​​activity, but it is basically in the areas of Engineering and Management where optimization plays a fundamental role, being therefore an essential ally of Managers and Engineers and consequently of operational research.

In fact, it is not necessary to be a scientist to use Solver, any ordinary user of Excel can use this instrument to solve everyday problems, from the simplest to those with the greatest degree of complexity. Study the problem, build the model and get the solution.

Now you know, when you have an optimization problem use Solver to find a solution.

See the Solver Problems we have for you

Did you like this article? Then share it on the social networks below and see on this site some examples of using Solver and optimization problems solved with this tool.