If the allowable increase (or decrease) is less than unity, the shadow-price rate applies only up to that fractional change. Shadow Price is the rate of change in the value of the objective function per unit increment in the RHS of that constraint. "1E+30" reflects sloppy report documentation. Thus, from the point of view of the spreadsheet model, Solver is a very useful black box that eliminates the need for what-if guesswork altogether. A "black box" is a modular subsystem with known input/output characteristics but whose internal details remain hidden from view. Determination of the optimal solution is a higher-level mathematical problem implemented as a black box by the Solver. Linear programming, however, obtains not just any solution but the optimal solution: the absolute best with respect to a given objective. Traditional what-if analysis is a rudimentary method that uses this very same spreadsheet to examine a subset of possible solutions. The inputs are the quantities to be placed in cells B4 and C4 (to be determined by Solver), the output is given in cell D9, and the computational processes are embodied by the model's formulas.
However, it is very robust, because if the problem you are solving is linear you can be assured that the solution obtained by the Simplex LP method is always a globally optimum solution.Comment: Seen as a computational system, the spreadsheet expresses a simple Input-Process-Output (IPO) model. And when they are linear, I prefer to solve them as a matrix equation instead. Many times, the problems I’m solving are nonlinear. It’s limited in its application because it can be applied to problems containing linear functions only. Of the three solving methods, I use Simplex LP the least. The algorithm creates a randomly distributed population of initial values that are each evaluated using the traditional GRG Nonlinear algorithm.īy starting multiple times from different initial conditions, there is a much greater chance that the solution found is the global optimum. You can enable this option through the Solver Options window, under the GRG Nonlinear tab. 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”.
0 kommentar(er)
