Microsoft Office Tutorials and References
In Depth Information
Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver
Beyond the Goal Seek—More Than One
Changing Cell? Use the Solver
The Solver Add-in is generally used in Excel for optimizations. The Solver for
optimizations will be covered in the next chapter. However, the Solver can be put
to good use for multiple variable, hypothetical scenario evaluation.
As you saw in Chapter 16, the Goal Seek was used to find/seek a single decision
variable or a changing cell when you needed to evaluate the effect on the outcome of
a varying parameter. The Goal Seek is limited to a single changing cell.
What if you need to change more than one input variable? The Goal Seek is not
sufficient. We can use the Solver. The Solver can work with up to 200 changing cells.
This is a review of the example we had in Chapter 16.
In Figure 34.1 you can see a budget that projects losses of $140,000.
The objective here is to have this project at least break-even. In other words,
what do you have to change to eliminate the $140,000 negative gap and change it to
0? The tool to use is the Goal Seek. You can use the Goal Seek and check the results
of changing a few of the inputs, one at a time: either the Sales Volume, Price per Unit,
or any one of the other inputs. Again only one input at a time.
To repeat the example, start with the number of units sold. How many units do you
have to sell in order to break even? If you use the Goal Seek tool, you will find out that
the sales volume must reach at least 59,333 units in order to break even. See Figure 34.2.
You can try the Goal Seek for the other input variables. I tried Goal Seek for the
Price per Unit in Figure 34.3 and the reduction of the marketing budget in Figure
34.4. In each case only one input variable was changed at a time.
What if you wanted to use more than one input variable at a time in the changing
cell? For example, finding out how varying both the unit price and the number of
units sold would influence the outcome. The Goal Seek has the limit of one changing
cell and cannot do it. The alternative is to use the Solver.
USING THE SOLVER
To activate the Solver do the following bulleted steps: