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.