Microsoft Office Tutorials and References

In Depth Information

**Chapter 34: Beyond the Goal Seek—More Than One Changing Cell? Use the Solver**

CHAPTER
34

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.

EXAMPLE—BREAK-EVEN POINT

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: