Microsoft Office Tutorials and References

In Depth Information

**Forecasting with the Goal Seek Command**

Click the OK button on the column’s drop-down list or the Custom AutoFilter

dialog box to filter your list.

To see all the data in the list again — to
unfilter
the list — click the Clear

button on the Data tab.

Forecasting with the Goal Seek Command

In a conventional formula, you provide the raw data and Excel produces the

results. With the Goal Seek command, you declare what you want the results

to be and Excel tells you the raw data you need to produce those results.

The Goal Seek command is useful in analyses when you want the outcome to

be a certain way and you need to know which raw numbers will produce the

outcome that you want.

Figure 5-5 shows a worksheet designed to find out the monthly payment

on a mortgage. With the PMT function, the worksheet determines that the

monthly payment on a $250,000 loan with an interest rate of 6.5 percent

and to be paid over a 30-year period is $1,580.17. Suppose, however, that

the person who calculated this monthly payment determined that he or she

could pay more than $1,580.17 per month? Suppose the person could pay

$1,750 or $2,000 per month. Instead of an outcome of $1,580.17, the person

wants to know how much he or she could borrow if monthly payments —

the outcome of the formula — were increased to $1,750 or $2,000.

To make determinations such as these, you can use the Goal Seek command.

This command lets you experiment with the arguments in a formula to

achieve the results you want. In the case of the worksheet in Figure 5-5, you

can use the Goal Seek command to change the argument in cell C3, the total

amount you can borrow, given the outcome you want in cell C6, $1,750 or

$2,000, the monthly payment on the total amount.

Figure 5-5:

Experimenting with the

Goal Seek

command.