Goal Seek in Excel

One of the common uses of Excel is to assess the result of different courses of action or different situations arising in the future.

Excel includes several features and techniques to help achieve that and Goal Seek is one such function.

Goal Seek lets us work backwards from a result to an input value. In the following example, we have a simple cash flow forecast for six months.

We want to find out how much we would need to borrow from the bank at the beginning of the period in order to have a certain amount at the end of the period. Rather than just trying different opening values until we close in on our desired result, instead we can use Goal Seek to do all the work for us.

Start by choosing Goal Seek from the Data ribbon, Data Tools group, What If Analysis dropdown and enter the cell containing a formula whose value we want to set.

Enter the new value, and select the cell containing the value we want to change in order to achieve our result. Here we have asked Excel what value we need in cell J12 in order for our closing balance in O13 to be 0:

The result:

Goal Seek seems to misbehave sometimes and uses the active cell as the changing cell regardless of what is chosen in the By changing cell box. To avoid this, select the changing cell before running Goal Seek, or else the contents of the active cell may be incorrectly overwritten.

The following example will take you through how to use Goal Seek step-by-step:

Assume you own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for the highest price, cell D10 calculates a total profit of 60 * $50 + 40 * $20 = $3800.

What if you want to know how many books you need to sell for the highest price, to obtain a total profit of exactly $4700? This is where Goal Seek comes in to play.

1. On the Data tab, click What-If Analysis, Goal Seek. The Goal Seek dialog box will pop up.

2. In the Goal Seek box insert D10 to the “Set cell” box, 4700 in the “To value” box and C4 in the “By changing cell” box.

3. Press enter and you should receive a result of needing to sell 90% of the books for the highest price to obtain a total profit of exactly $4700.

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

AAT Comment offers news and opinion on the world of business and finance from the Association of Accounting Technicians.