Excel Tip: Using Goal Seek

Before we get into this week's post, I'd just like to say a big thank you for the magnificent response to my survey last week. I will reflect your feedback in the new training product I am currently developing, and which I expect to launch in the next few weeks.

This week's post is a short one on a useful little tool that Excel has tucked away. The Goal Seek tool is particularly useful when working with financial forecasts. Here we will show how to use it to find the break-even point.

Say you have a complex forecasting model with many interlinked variables, and you want to know what your break-even sales volume is. Goal Seek is designed for exactly this type of scenario.

What Goal Seek actually does is adjust one cell, to achieve a desired result in another cell that is ultimately dependent on the first cell. In the break-even example, we need to know what sales volume returns zero net profit.

We will use an incredibly simple example to illustrate the tool, but it really comes into its own the more complex the model is.

In the following spreadsheet, the white cells represent entered variables and the rest of the spreadsheet is calculated from these:

So, the formulae in cells B8 to B12 are as follows:

B8: =B3*B4
B9: =B3*B5
B10: =B8-B9
B11: =B6
B12: =B10-B11

We want to know what value in cell B3 will result in zero Net Profit in cell B12 (assuming the other variables stay the same). This will give us the break-even sales volume.

We select the Goal Seek tool by going to the Data ribbon, clicking What-If Analysis and selecting Goal Seek... We then populate it as follows:

Excel puts the dollar signs in when we click in the appropriate cells.

When we click OK, the Goal Seek tool will adjust cell B3 until cell B12 is equal to zero as follows:

Job done!

Obviously with our simple model we could have arrived at this ourselves quite easily, however the tool also works with complex models involving multiple sheets and many interlinked formulae.

Happy Goal-Seeking!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".