761
CHAPTER
Analyzing Data Using
Goal Seeking and
Solver
IN THIS CHAPTER
What-if analysis — in reverseSingle-cell goal seekingIntroducing SolverSolver examplesT
he preceding chapter discusses what-if analysis — the process of
changing input cells to observe the results on other dependent cells.
This chapter looks at that process from the opposite perspective:
finding the value of one or more input cells that produces a desired result in
a formula cell.
This chapter covers two tools: Goal Seeking and the Solver add-in.
What-If Analysis, in Reverse
Consider the following what-if question: “What is the total profit if sales
increase by 20 percent?” If you set up your worksheet model properly, you
can change the value in one cell to see what happens to the profit cell. The
examples in this chapter take the opposite approach. If you know what a for-
mula result should be, Excel can tell you the values that you need to enter in
one or more input cells to produce that result. In other words, you can ask a
question such as “How much do sales need to increase to produce a profit of
$1.2 million?” Excel provides two tools that are relevant:
l (^) Goal Seek: Determines the value that you need to enter in a single
input cell to produce a result that you want in a dependent (for-
mula) cell.
l Solver: Determines the values that you need to enter in multiple
input cells to produce a result that you want. Moreover, because
you can specify certain constraints to the problem, you gain signifi-
cant problem-solving ability.