Microsoft Office Tutorials and References

In Depth Information

**Using Goal Seeking**

Using Goal Seeking

Many Excel worksheets are set up to do what-if analyses. For example, you might have a sales

projection worksheet that allows you to answer questions such as “What is the total profit if sales

increase by 20 percent?” If you set up your worksheet properly, you can change the value in one

cell to see what happens to the profit cell.

Excel offers a useful tool that can best be described as a what-if analysis in reverse. If you know

what a formula result
should
be, Excel can tell you the value that you need to enter in an input

cell 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?”

Figure 86-1 shows a simple worksheet that calculates mortgage loan information. This worksheet

has four input cells (C4:C7) and four formula cells (C10:C13).

Figure 86-1:
Goal Seeker can tell you the value of an input cell that will result in a desired result.

Assume that you’re in the market for a new home and you know that you can afford an $1,800

monthly mortgage payment. You also know that a lender can issue a fixed-rate mortgage loan

for 6.50 percent, based on an 80 percent loan-to-value (that is, a 20 percent down payment).

The question is “What is the maximum purchase price I can handle?” In other words, which value

in cell C4 causes the formula in cell C11 to result in $1,800? One approach is to simply plug a

bunch of values into cell C4 until C11 displays $1,800; however, Excel can determine the answer

much more efficiently.

To answer this question, follow these steps:

1.
Choose Data➜Data Tools➜What-If Analysis➜Goal Seek.

Excel displays the Goal Seek dialog box.

2.
Complete the three fields in the dialog box (shown in Figure 86-2) similar to forming a

sentence: You want to set cell C11 to 1800 by changing cell C4.