Elements of Solver Models

[Dividing Line Image]

The basic purpose of the Solver is to find a solution -- that is, values for the variables or changing cells in your model -- which satisfies the constraints and which maximizes or minimizes the objective or target cell value. Let us examine this framework more closely.

The model you create for use with the Solver is no different from any other spreadsheet model. It consists of input values; formulas which calculate values based on the input values or on other formulas; and other elements such as formatting. You can practice "what if" with a Solver model just as easily as with any other spreadsheet model. This familiar concept can be very useful when you wish to present your results to managers or clients, who are usually "spreadsheet literate" even if they are unfamiliar with Solvers or optimization.

Parameters of the Model

The input values may be fixed numbers associated with the problem, which we'll call parameters of the model. They may enter into the calculation of the objective function and constraints, but they are constant in the Solver problem: Although you may change the values of these cells, the Solver will never change them automatically. Often you will have several "cases" or variations of the same problem to solve, and the parameter values will change in each problem variation. Such parameter values may be conveniently captured using your spreadsheet program's Scenario Manager.

Decision Variables (Changing Cells)

Alternatively, the input values may be quantities which are variable, or under the control of the decision maker. We'll refer to these as the variables, decision variables, or changing cells. These are the cells that the Solver will change automatically in order to maximize or minimize the objective or target cell. Very often, the same cell values you use to play "what if" are the ones for which you'll want the Solver to find solution values. These cells are listed in the Changing Cells edit box of the Solver Parameters dialog.

The Objective Function (Target Cell)

The quantity you want to maximize or minimize is called the objective function or target cell. This cell is listed in the Set Cell edit box of the Solver Parameters dialog.

You may have a Solver model which has nothing to maximize or minimize, in which case the Set Cell edit box will be blank. In this situation the Solver will simply find a solution which satisfies the constraints.

The standard spreadsheet Solvers also permit you to enter a specific value which you want the objective function or Set Cell to achieve. This feature was included for compatibility with the spreadsheet's Goalseek or Backsolver command menu, which allows you to seek a specific value for a cell by adjusting the value of one other cell on which it depends. In fact, entering a specific value for the Solver's Set Cell is exactly the same as leaving the Set Cell blank and entering an equality constraint for the Set Cell in the Constraint List Box.

There is rarely a good reason to use the Set Cell Value of edit box in the Solver Parameters dialog. If your problem requires only a single Set Cell value and a single variable or Changing Cell, we recommend that you use the Goalseek... command. If you have nothing to maximize or minimize, we recommend that you leave the Set Cell blank and enter any constraints you need in the Constraint List Box.

Constraints

Constraints are relations such as A1 >= 0. A constraint is satisfied if the condition it specifies is true within a small tolerance. This is a little different from a logical formula such as =A1>=0 evaluating to TRUE or FALSE which you might enter in a cell. In this example, if A1 were -0.0000001, the logical formula would evaluate to FALSE, but with the default Solver Precision setting, the constraint would be satisfied. Because of the numerical methods used to find solutions to Solver models and the finite precision of computer arithmetic, it would be unrealistic to require that constraints like A1 >= 0 be satisfied exactly -- such solutions would rarely be found.

Constraints are specified by giving a cell reference such as A1 (the "left hand side"), a relation (<=, = or >=), and an expression for the "right hand side." The left hand side may, and often will be a range of cells such as A1:A10 (Excel) or A1..A10 (1-2-3). Although the Excel and 1-2-3 Solvers allow you to enter any numeric expression on the right hand side, we strongly encourage you to use only constants, or references to cells which contain constant values on the right hand side. (A constant value to the Solver is any value which does not depend on any of the decision variables.) Using constant right hand sides in constraints will simplify your model, and is essential to obtain the benefits of fast problem setup in the enhanced solvers.

Integer Constraints

Another type of constraint is of the form A1 = integer, where A1 is one of the decision variables. This specifies that the solution value for A1 must be an integer or whole number such as -1, 0 or 1 to within a small tolerance. The presence of even one such integer constraint in a Solver model makes the problem an integer programming problem, which may be much more difficult to solve than the equivalent problem without the integer constraint (See Algorithmic methods used by the Solver).

Feasible and Optimal Solutions

A solution (values for the decision variables) for which all of the constraints in the Solver model are satisfied is called a feasible solution. The Solver proceeds by first finding a feasible solution, and then seeking to improve upon it, changing the decision variables to move from one feasible solution to another feasible solution until the objective function has reached its maximum or minimum. This is called an optimal solution.

How does the Solver know (more important, how do you know) that the solution is optimal? The answer to this question depends on the type of problem (linear/quadratic, nonlinear or integer) you are trying to solve, which is discussed at some length in Algorithmic methods used by the Solver.