Goal Seeking

On some occasions, when working with data in Excel, you already have the outcome that you want to achieve in mind, and you would like to know the various input values that will achieve that outcome or goal. For example, you may have a goal of $600,000 in revenue and to achieve that goal you need a certain amount for your sales against the cost of expenses. This is where the Goal Seek feature in Excel comes in.

The Goal Seek command in Excel enables you to set a goal in one cell and then choose the cell whose value you would like Excel to adjust in other to meet your goal. So, this is like working backwards, stating the results first and allowing Goal Seek to determine the inputs needed to meet that goal. The goal cell will have a formula which is based on the input from other cells, including the cell that Excel will be changing.

For example, let’s say we want to find how much sales we need to generate in order to reach a certain level of income. Instead of making several adjustments to the sales figure in trying to produce the desired result, we can simply set the result we want and let Goal Seek work out the sales figure required to achieve the result.

To demonstrate the goal seeking feature in Excel, we’ll use an example to forecast the income based on a range of input values.

In the table above, cell B8 has a formula that calculates the Income , which is the sum of the Gross Profit and the Expenses (this is a negative value as denoted by brackets).

=B6+B7

Our goal seeking question is, what amount should our sales be if we want to generate an income of $500,000?

Once the data structure has been created in the worksheet, follow the steps below to get to perform the goal seeking:

1. On the Excel Ribbon, click the Data tab, then In the Forecast group, click the What-If Analysis command button and select Goal Seek from the drop-down menu. This will display the Goal Seek dialog box.
   
2. Click in the Set cell text box and select the cell in the worksheet that contains the formula that will return the value you’re seeking. For our example, this is cell B8 .
3. In the To value text box, enter the value that you want to set the cell to. For our example, the value will be 500,000.
4. Click in the By changing cell text box and select the cell that contains the value you want Excel to adjust to achieve the goal. For this example, this will be cell B4 .
5. Click OK when done.
   Excel will display the Goal Seek Status dialog box which informs you that a solution has been found and that the result value is now the same as the target value.
   
   If the Goal Seek does not find a solution it will enable the Step and Pause buttons to enable you to step through different options to find a solution.
6. If you want to keep the solution found as a result of the goal seeking process, click OK to dismiss the Goal Seek Status dialog box. If you don't want to keep the result, simply click the Cancel button.

Note that if you accept the value by clicking OK, it will overwrite your existing values. You can switch back to the old the value by clicking the Undo button on the Quick Access Toolbar or by using the Ctrl+Z keystroke.

To switch back and forth between the previous value and the value returned by Goal Seek, you can use the Undo and Redo buttons on the Quick Access Toolbar. Alternatively, you can press Ctrl+Z to display the original values and Ctrl+Y to display the solution returned by the goal seeking command.