‘Solvers’ are used to find the maxima and minima of a function subject to any number of constraints. The classic example is that of animal food mixing. Each ingredient has its own combination of nutrients and the overall mix has to meet certain requirements for every nutrient. Since each ingredient has a cost the basic problem is to find the mix of ingredients that meets nutritional requirements but minimises the cost. The four ‘solvers’ listed below cover a variety of needs – What’sBest being the most comprehensive, but also most expensive.
Microsoft Solver Add-In
This is the basic solver utility that comes bundled with Excel. Compared with other options it offers fairly basic functionality, but for many problems it might be all you need. The Microsoft Solver Add-In requires three types of information to be provided:
- The Objective Cell – which is typically a formula whose value you want to minimise or maximise.
- Variable cells – contain the values for the variables used in the objective cell formula.
- Constraint cells – where limits can be imposed on the values the variables can take.
This is really a minimum specification for solver type functionality, but it comes bundled and for simpler optimization problems it is quite adequate.
Frontline Solvers provide a number of Excel Add-Ins, and it is claimed that Premium Solver Pro will solver larger optimization problems with much greater speed (between 2 and 50 times faster). Up to 2000 decision variables can be specified and users can specify their problems with an Excel Solver type utility or a Ribbon and Task Pane interface. Premium Solver Pro automatically chooses the best solver engine based on the model specification. A licence costs US$995.
SolverStudio is a free Add-In for Excel that supports the creation of optimization models using a variety of modelling languages and solvers, including PuLP (included with SolverStudio), AMPL, GMPL, GAMS, Gurobi, COOPR/Pyomo, and SimPy. The models can be created and edited without leaving excel, and the model is stored within the workbook.
This is a an Add-In provided by Lindo Systems and is targeted at large industrial size optimization problems. It addresses linear, nonlinear (convex and nonconvex/Global), quadratic, quadratically constrained, second order cone, stochastic, and integer optimization. Some of the largest problems formulated in What’sBest use over 100,000 variables and it is claimed that execution speeds are still acceptable. A variety of options are available with a starting price of US$495, rising to several thousand dollars if all options are included.