Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

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.

FIGURE 34.1 The Break-Event Point Example

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 ...