# Goal Seeking

Many spreadsheets contain formulas that enable you to ask questions, such as, “What would be 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.

Goal seeking serves as a useful feature that works in conjunction with your formulas. If you know what a formula result should be, Excel can tell you which values of one or more input cells you need to produce that result. In other words, you can ask a question such as, “What sales increase is needed to produce a profit of \$1.2 million?”

Single-cell goal seeking (also known as backsolving) represents a rather simple concept. Excel determines what value in an input cell produces a desired result in a formula cell. You can best understand how this works by walking through an example.

### A Goal Seeking Example

This worksheet presents a simple demonstration of goal seeking.

```    C10: =(1-C5)*C4

C11: =PMT(C7/12,C6,-C10)

C12: =C11*C6

C13: =C12-C10```

Imagine that you’re in the market for a new home and you know that you can afford \$1,200 per month in mortgage payments. You also know that a lender can issue a fixed-rate mortgage loan for 6.00 percent, based on an 80 percent loan-to-value (a 20 percent down payment). The question is, “What is the maximum purchase price you can handle?” In other words, what value in cell C4 causes the formula in cell C11 to result in \$1,200? You can plug values into cell C4 until C11 displays \$1,200. A more efficient approach lets Excel determine the answer.

To answer this question, choose Data Image Data Tools Box What-If Analysis Image Goal Seek. Excel displays the Goal Seek dialog box, as shown in Figure 2-9. Completing this dialog box resembles forming the following sentence: Set cell C11 to 1200 by changing cell C4. Enter this information in the dialog box by either typing the cell references or by pointing with the mouse. Click OK to begin the goal seeking process.

Figure 2-9: The Goal Seek dialog box.

Almost immediately, Excel announces that it has found the solution and displays the Goal Seek Status box. This box tells you the target value and what Excel came up with. In this case, Excel found an exact value. The worksheet now displays the found value in cell C4 \$200,150). As a result of this value, the monthly payment amount is \$1,200. Now, you have two options:

• Click OK to replace the original value with the found value.

• Click Cancel to restore your worksheet to its original form before you chose Goal Seek.