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

Figure 2-8 shows a mortgage loan worksheet that has four input cells (C4:C7) and four formula cells (C10:C13). The formulas calculate various values using the input cell. The formulas are

Figure 2-8: 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 Data Tools What-If Analysis 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.

More about Goal Seeking

If you think about it, you may realize that Excel can't always find a value that produces the result you're looking for-sometimes a solution 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 even though you believe one exists. In this case, you can adjust the current value of the changing cell to a value closer to the solution, and then reissue the command. If that fails, double-check your logic and make sure that the formula cell does indeed depend on the specified changing cell.

Like all computer programs, Excel has limited precision. To demonstrate this, enter =A1∘2 into cell A2. Then, choose Data Data Tools What-If Analysis Goal Seek to find the value in cell A1 that causes the formula to return 16. Excel returns a value of 4.00002269-close to the square root of 16, but certainly not exact. You can adjust the precision in the Calculation section of the Formulas tab of the Excel Options dialog box (make the Maximum change value smaller).

In some cases, multiple values of the input cell produce the same desired result. For example, the formula =A1∘2 returns 16 if cell A1 contains either −4 or +4. If you use goal seeking when two solutions exist, Excel gives you the solution that is nearest to the current value in the cell.

Perhaps the main limitation of the Goal Seek command is that it can find the value for only one input cell. For example, it can't tell you what purchase price and what down payment percent result in a particular monthly payment. If you want to change more than one variable at a time, use the Solver add-in.