Goal seek for back calculating inputs

Imagine you had a model set up and you wanted to, for instance, see how far your revenue inputs would have to fall before e.g. a particular financial ratio were breached. You'd likely find yourself playing with the revenue inputs, trying higher and lower values until eventually you got the output you wanted.

Goal seek can save you time

Goal seek could help you with that job! This powerful little tool will work out the input value needed to get the output you want, saving a lot of trial and error. Goal seek?plays one very simple role (shortcutting trial and error) but it does it very well.

Here's an example mini-model which?is a modified version incorporating a lot of what we've seen in the "Top 10" series of posts already.

The example has:

• Headings set up across the top for year and month
• Flexible dates at line 15 courtesy of the EOMonth formula
• Data validation for scenarios at cell A5 and making use of the Choose function at C19
• Yellow input cells clearly isolated
• SumIF being used to amalgamate the Y1 and Y2 data in line 29
• Totals checking that the sum in AA27 (before we've tried to amalgamate the data) matches the sum in E30 i.e. that no data is missed or double counted
• Annual growth rate in cell D32.

The model could get a lot?bigger than this one

It's a very simple model but still has quite a few numbers coming together to calculate the annual growth rate of 64.7% in cell D32.

Imagine for a moment we considered 64.7% as too high a figure for annual growth. Let's pretend that we like the way the model is set up, but we want to reduce the monthly 3.0% in A7 so that annual growth in D32 is more realistic at 20.0%. The percentage input figure in A7 is going to have to drop below the current 3.0% but it's going to take you a fair bit of trial and error to work out exactly what number you should have in A7 so that D32 drops from 64.7% to 20.0%.

Goal seek can help

Goal seek can help whenever you're facing having to try higher or lower input numbers to get a desired result. It's easy to run:

• Step 1: select the cell you want to changed (D32). It must be cell containing a calculation. Go Data > What-if Analysis > Goal Seek and a dialog box pops up.
• Step 2: Set cell is the one with the calculation in it (D32). In the next box down the value is the 20% we are aiming for as a result so type that in, together with the "%" sign. In the next box down the "changing cell" is A7 - an input that affects D32. The final result "Set" cell must be dependent on the input "Changing cell" for Goal seek to be able to do its work.

Set Goal seek running and very quickly a result appears.

You've managed to work out that the base growth has to be much lower than what you previously had in cell A7, and you've managed to do that very quickly.

Goal seek is easy to use

Because so much of what we do in Excel involves simple iteration (trying a different input value to see what happens to the output) Goal seek sits firmly on my list of top 10 Excel functions. Whenever you find yourself trying different inputs in Excel to see what happens to your inputs, you should be thinking "Goal seek". Goal seek speeds the whole process up nicely!