How to use Excel’s Goal Seek analysis tool

If you ask me, Excel’s what-if analysis tools are under-used. I often respond to readers with “Have you tried a data table?” Invariably, they don’t know how to implement the feature–sometimes, they don’t even know what a data table is. So, over the next few months, I’ll introduce you to Excel’s what-if analysis tools:

• Goal Seek
• Scenario Manager
• Data Table

We’ll review the options, discuss when to use them, and then implement all three tools. Each example will be simple, but they will include enough information that you can start using these features on your own.

You might be asking what a what-if tool is; these tools show you the impact of making a change without impacting the actual data. This month, we’re exploring Goal Seek. This tool tells you how variables must change to achieve a specific goal.

I’m using Excel 2013 and Windows 7. For your convenience, you can download the example .xlsx or .xls file.

Example 1

Technically, Goal Seek is a process of calculating a value by performing what-if analysis on a given set of values. For our purposes, Excel’s Goal Seek feature lets you adjust a value used in a formula to achieve a specific goal. Or, put another way, Goal Seek determines input values needed to achieve a specific goal. Use Goal Seek when you don’t have an exact value to use.

Calculating the terms of a loan is a good example of a what-if situation. What if the payment your lender offers is too high for your budget? Using Goal Seek can help by evaluating the payment formula and returning new values for the rate, term, and even the principal to fit your desired payment. Figure A shows a simple mortgage calculator where all variables are known: to repay a 6% loan on $200,000 over 15 years, your monthly payment must be $1,687.71. (Deposits matter, but we won’t complicate this example with more variables than we need.)

Figure A

A simple mortgage calculator.

You want the house, but your ideal payment is $1,200. You could spend a lot of time plugging in new rate and term values until you get lucky. A better solution is to use Goal Seek to determine the rate and term necessary to reduce your payment. First, let’s calculate the necessary rate, as follows:

1. Select the cell that contains the formula. In this case, that’s E4.
2. Click the Data tab. In the Data Tools group, click What-If Analysis and choose Goal Seek (Figure B). In Excel 2003, Goal Seek is on the Tools menu.
   Figure B
   
3. In the resulting dialog, you’ll find three input values. The Set cell value defaults to the selected cell, E4. (That’s the cell that contains the PMT() function.) The To value should equal your goal. In this case, enter the payment you can afford, -1200 (the value is negative because it’s a payment). The By changing cell value points to the input value you want to adjust. Enter B4 (the interest rate) in that control (Figure C).
   Figure C
   
4. Click OK, and Excel will iterate (using built-in settings) until it finds the best result (Figure D). You need a 1% interest rate to reduce your payment to $1,200.
   Figure D
   

If Goal Seek doesn’t work, revisit the To value in step 3. Specifically, you must enter the desired payment as a negative value. To return to the original values, click Cancel in step 4 instead of clicking OK after viewing the result in the dialog. Or, you can click Undo on the Quick Access Toolbar (QAT) after clicking OK. If Goal Seek can’t find a solution for you, Excel enables the Step and Pause buttons, which allows you to force further iterations to reduce the gap between the current values and your goal. (This is a more advanced feature that we won’t visit in this article, but you should know that it’s available.)

Now, you probably know that you can also lengthen the term of the loan to reduce the payment, so let’s use Goal Seek to determine a new term as follows:

1. Select E4, click the Data tab, click What-If Analysis, and choose Goal Seek. In Excel 2003, choose Goal Seek from the Tools menu.
2. Enter -1200 in the To value control and C4 in the By changing cell control.
3. Click OK to see the results (Figure E).
   Figure E
   

This adjustment is as drastic as the rate adjustment. To get the payment you want at 6% interest, you need to double the length of your loan! Clearly, the best route is to seek a lower interest rate if possible. If that’s not possible, a home that’s priced lower might be in your future. Wait! Let’s try that too:

1. Enter the term value of 180 in C4 (if necessary).
2. Select E4, click the Data tab, click What-If Analysis, and choose Goal Seek. In Excel 2003, choose Goal Seek from the Tools menu.
3. Enter -1200 as the To value, enter D4 as the By changing cell value, and click OK (Figure F).
   Figure F
   

Goal Seek didn’t give you the solution–only you can decide what to do with the information Goal Seek provides. Goal Seek offers quick insight.

Example 2

Let’s look at another example. It’s as simple as the first but leans more toward an actual business situation than the first example. Figure G shows a simple quarterly profit sheet, and the story’s a bit grim: summer figures are down, and you have only one quarter remaining to meet your goal of $50,000 to stay solvent.

Figure G

Quarterly profit sheet with $50,000 net profit goal.

Using Goal Seek, you can quickly learn what your fourth quarter revenue must be to reach your $50,000 profit goal. To accomplish this, do the following:

1. Select E7, the cell that contains the formula (a simple SUM() function) that expresses your goal of $50,000, even though it’s not returning $50,000 yet.
2. Click the Data tab, click What-If Analysis, and then choose Goal Seek. In Excel 2003, choose Goal Seek from the Tools menu.
3. In the resulting dialog, enter 50000 as the To value.
4. Enter C6 as the By changing cell value (Figure H).
   Figure H
   
5. Click OK (Figure I).
   Figure I
   

With Goal Seek’s help, you can quickly see that your last quarter must generate $76,667 to meet your goal of $50,000. Knowing ahead of time what you face can help you determine new, perhaps more aggressive strategies for improving revenue.

Meeting goals

Excel’s Goal Seek analysis tool is easy to use and can help you make decisions for the future based on today’s data. Thanks to Goal Seek, the future isn’t as unpredictable as you might think!

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

For a subsequent article on this topic, please read: How to use Excel’s Scenario Manager analysis tool.