Microsoft Office Tutorials and References

In Depth Information

**Goal Seeking**

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

C10: =(1–C5)*C4

C 11: =PMT(C7/12,C6,–C10)

C12: =C11*C6

C13: =C12–C10

Figure 2-8:
This worksheet presents a simple demonstration of goal seeking.

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

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.

➜

Data Tools

➜

What-If Analysis

➜