Microsoft Office Tutorials and References
In Depth Information
Solver, Scenarios, and Goal Seek are extremely powerful tools for quantitative anal-
ysis. Yet, we must be careful to use these tools with caution. In the words of US
President Reagan— Trust, but verify . We have seen how a complex goal seek func-
tion can lead to problems if some forethought is not applied to our analysis. The
nature of the search algorithms that are used in the Solver and Goal Seek tools, and
the possible non-linear nature of the problem structure can bafﬂe the search algo-
rithm; it can lead to uncertainty in the veracity of the answer, or it can also lead to
wrong answers. Although we did not spend much time discussing non-linear pro-
grams in our Solver section, other than to say they were very difﬁcult to solve, it
is not wise to assume that an optimal solution is always optimal. If the objective
function and/or the constraints of a problem are non-linear, you might experience
a solution that is a local optimum . A local optimum occurs when the search algo-
rithm assumes that it need not search any further for a better solution, but in doing
so, it has actually ignored other regions of the function where better solutions are
How are we to know when we have a local optimum, or that a solution that
has been identiﬁed as optimal is possibly not optimal? A little common sense is
invaluable in making this determination. Here are a few tips that might help you
avoid accepting the claim of an optimal solution when it is not, or help you verify
whether an uncertain solution is in fact optimal:
1. If you have a non-linear target cell or objective function for a formulation in a
single variable, attempt to plot the function by using successive values of inputs
to see if the function might be a candidate for a local optimum. You can do this
by copying the function to a long column of cells and placing consecutive values
of input in an adjacent column. Then plot the results and note the shape of the
curve. Of course, this is only possible for a single variable and in most problems
we have far more that one input variable.
2. In the case of multi-variable problems, you may want to resort to simulation of
inputs and to see if you can ﬁnd some combination that outperforms the so-called
3. If a solution is uncertain, but appears to be correct, investigate by examining
values near the solution that is proposed. Be careful to consider a local optimum
4. Be careful to note any odd solutions—negative values where none are possible
and values that are either too large or too small to accept as possible.
5. Verify that the constraints that are imposed on a formulation are satisﬁed.
6. Remember that in spite of your best efforts, you may still, on rare occasions,
have problems dealing with these issues.
There is nothing more embarrassing than presenting a solution that contains a
clear inconsistency in a solution which you have overlooked. Veriﬁcation of an
analysis is much like editing—it is not a pleasant task, but it is foolhardy to avoid it.