Excel solver examples
GRG MultistartĪ nice compromise between the speed of the GRG Nonlinear algorithm and the robustness of the Evolutionary algorithm is GRG Nonlinear Multistart. However, this has diminishing returns because reducing the population size and/or increasing the mutation rate may require even more populations to achieve convergence. For instance, you can choose the Mutation Rate and Population Size to potentially shorten the solution. Now Excel gives you some control over the algorithm through the Solver options window. Also, subsequent “generations” are populated randomly instead of using derivatives and the slope of the objective function to find the next best set of values. What makes this process so time-consuming is that each member of the population must be evaluated individually. This goes on until there is very little change in the objective function from one population to the next. The diagram below hopefully makes it more clear. The second population is then evaluated and a winner is chosen to create the third population. The offspring are a “mutation” of that best set of input values from the first population. The sets of input values that result in a solution that’s closest to the target value are selected to create a second population of “offspring”. These sets of input values are plugged into the model and the results are evaluated relative to the target value.
In simple terms, the solver starts with a random “population” of sets of input values. The Evolutionary method is based on the Theory of Natural Selection – which works well in this case because the optimum outcome has been defined beforehand. However, this solver method is also VERY slow. The Evolutionary algorithm is more robust than GRG Nonlinear because it is more likely to find a globally optimum solution. Any discontinuities caused by IF, VLOOKUP, or ABS functions, for example, will cause problems for this algorithm. The solver will most likely stop at the local optimum value nearest to the initial conditions, giving you a solution that may or may not be optimized globally.Īnother requirement for the GRG nonlinear solver to obtain a good solution is for the function to be smooth. The downside is that the solution you obtain with this algorithm is highly dependent on the initial conditions and may not be the global optimum solution. That speed comes with a compromise though. Of the two nonlinear solving methods, GRG Nonlinear is the fastest. In its most basic form, this solver method looks at the gradient or slope of the objective function as the input values (or decision variables) change and determines that it has reached an optimum solution when the partial derivatives equal zero.
GRG stands for “Generalized Reduced Gradient”.