This chapter provides a description of Excel's GoalSeek, as well as an introduction to Solver. These can both be considered as forms of optimisation tools, in the sense that they search for the input value(s) required so that a model's output has some property or value. Solver's functionality is richer than that of GoalSeek, so that this chapter focuses only on its general application, whilst some further aspects are covered in Chapter 15.
OVERVIEW OF GOALSEEK AND SOLVER
Links to Sensitivity Analysis
Whereas the sensitivity techniques discussed in Chapter 12 can be thought of as “forward-calculating” in nature, GoalSeek and Solver are “backward” approaches:
- GoalSeek (under Data/What-If Analysis) uses an iterative search to determine the value of a single input that would be required to make an output equal to a value that the user specifies.
- Solver determines the values that are required of several inputs so that the output equals some value, or is minimised or maximised. One may also impose restrictions on the allowable value of inputs or calculated values for the set of values to be considered valid. Solver is a free Excel add-in that initially needs to be installed under Excel Options/Add-Ins, and which will then be found under Excel's Data/Analysis menu. It can also be installed (and uninstalled) using the Manage tool for add-ins under Office/Excel Options/Add-ins.
Tips, Tricks and Limitations
Whilst Goal Seek and ...