Chapter 37: Analyzing Data Using Goal Seeking and Solver
763
l (^) Enter 20% in cell C5 (the down payment percent)
l Enter 360 in cell C6 (the loan term, in months)
l (^) Enter 6.5% in cell C7 (the annual interest rate)
Next, choose Data ➪ Data Tools ➪ What-If Analysis ➪ Goal Seek. Excel displays the Goal Seek
dialog box, shown in Figure 37.2. Completing this dialog box is similar to forming a sentence. You
want to set cell C11 to 1800 by changing cell C4. Enter this information in the dialog box either
by typing the cell references or by pointing with the mouse. Click OK to begin the goal-seeking
process.
FIGURE 37.2
The Goal Seek dialog box.
In less than a second, Excel displays the Goal Seek Status box, which shows the target value and
the value that Excel calculated. In this case, Excel found an exact value. The worksheet now dis-
plays the found value in cell C4 ($355,974). As a result of this value, the monthly payment
amount is $1,800. At this point, you have two options:
l (^) Click OK to replace the original value with the found value.
l Click Cancel to restore your worksheet to the form that it had before you chose Goal Seek.
More about goal seeking
Excel can’t always find a value that produces the result that you’re seeking. Sometimes, a solution
simply doesn’t exist. In such a case, the Goal Seek Status box informs you of that fact.Other times, however, Excel may report that it can’t find a solution, but you’re pretty sure that one
exists. If that’s the case, you can try the following options:l (^) Change the current value of the By Changing Cell field in the Goal Seek dialog box (refer
to Figure 37.2) to a value that is closer to the solution and then reissue the command.