The goal seek function in Excel is a finance person’s secret weapon, and I will help
you understand how to use it!
Have you ever been in one of those grueling budgeting or forecasting sessions, where you
have endless discussions about things like revenue and headcount?
Usually, the finance person is the one picking up the pieces after the meeting, to make sure
everything is correctly reflected in the spreadsheets.
Here’s one such spreadsheet: the cash flow forecast spreadsheet.
The months are at the top here, then the opening cash balance by month, various types of cash
movements (inflows and outflows) during the month, and the expected cash ending balance
down here.
Now you’ve probably noticed the number in red for June.
A negative cash balance.
That’s not supposed to happen.
That number needs to be at least zero.
You bring it up to your manager, who responds “aaah, just plug it”,
or “just fix the spreadsheet”.
That’s where you take out the secret weapon called “goal seek”: it’s a tool that
gives you the ability to calculate backward to obtain an input that would result in a
given output.
Let’s see how goal seek works.
Cell G23 is the one that needs to be “fixed”.
There’s a formula in there, by double-clicking on the cell we see that G23 is the sum of
four other cells: the opening balance in cell G2 plus or minus the total of the cash from
operating, investing and financing activities.
If you want to make that even more visual, then go to Formulas – Trace Precedents.
Let’s see what’s going on in one of those precedents, G10: it’s the sum of the movements
above in G5 through G9.
Most of those cells contain negative numbers, how about we “plug” some more positivity
into G5, which will work its way into G10, and then fix our red cell G23.
Let’s go back to G23, go to the Data tab, What-If Analysis on the far right, Goal Seek.
“Find the right input for the value you want”.
Sounds like a plan!
Click on goal seek, and out pops a dialog box.
Set cell G23 to value zero, by changing cell G5.
Ready?
Go!
Alas, that doesn’t work.
We get an error message “cell must contain a value”.
Let’s click OK to that error message and then cancel the goal seek operation, and figure
out what is going on in G5.
There’s a formula in there, not a value.
The formula simply links to G31, the expected collections in the month of June.
This cell in turn links to F29, the expected revenue in May.
F29 is the product of multiplying the expected revenue for the year in B27 by the percentage
of the total year revenue allocated to May in cell F28.
We could use goal seek on cell F28 as the input cell.
What-if, goal seek, set cell G23 to value zero, by changing cell F28.
OK, that fixes our cash balance problem, but we now have errors in cells N28 and N29: the
sum of the revenue split over the months is bigger than the annual revenue.
This we can also fix using goal seek, as N28 (the target variable) also has a formula,
which is a requirement for goal seek to work.
Set N28 to 100%, by changing G28.
Goal seek works with absolute numbers in the value field, as well as with percentages.
Goal seek only works on one-to-one relationships between cells, it cannot handle one-to-many
or many-to-one.
If we don’t want to adjust the monthly split of the revenue, then click “Undo” twice,
and put a plug into cell B27, the expected revenue for the year.
You could start typing various numbers into that cell directly, like $850K or $820K, using
“trial and error”, and see if that fixes G23, the ending cash balance in June.
But it’s much easier to have Excel do the calculating for you by using our goal seek
secret weapon: Excel goal seek performs all calculations for you behind the scenes.
Goal seek doesn’t change the formulas, it uses the existing formulas.
What if analysis, goal seek, set cell G23 to zero, by changing cell B27, OK.
Goal seek found a solution for you: instead of the value of $800K revenue that was in
B27 before, there’s now $827K in there.
If you think that’s a reasonable estimate for the year, then click OK to accept.
If you think that’s a completely outrageous number, then hit Cancel to indicate you don’t
accept the solution.
If you hit OK, then goal seek overwrites the previous data.
If you do change your mind, there’s always the “Undo” button to save you.
You don’t necessarily have to change revenue, you could decide to plug a number elsewhere
to make your negative expected cash balance in G23 go away.
How about experimenting with the headcount plan.
There’s hiring going on in April: headcount increases from 8 in cell D34
to 9 in cell E34.
Let’s see what happens if we use the goal seek secret weapon to change E34.
What-if analysis, goal seek, set cell G23 to zero, by changing cell E34.
This shows that Excel can help you with the calculation, but not with the thinking!
There’s a difference between spreadsheet reality and business reality.
Would it make sense to you to fire someone in April (headcount going from 8 to 7), and
then hire two people in May (headcount going from 7 to 9)?
No, not really!
Secret weapons like goal seek can be very dangerous if they are in the wrong hands.
Goal seek doesn’t evaluate for you whether the cells you choose to include
have any logical relationship.
I could take the very silly step of trying to set G23 (the cash ending balance in June)
to zero, by changing cell M19 (debt repayment in December).
Goal seek doesn’t question whether that is remotely logical,
it just does as it is told.
When you click OK, you see that Excel is running many iterations of goal seek, and concluding
after a while that it may not have found a solution.
If you make the mistake of clicking OK (accepting the latest iteration), goal seek overwrites
the previous number in cell M19 with a nonsensical number.
What we learn from this is that goal seek is sometimes not able to find a solution because
that solution simply does not exist!
Just like the input cell must contain a value, the output cell must contain a formula.
If I want to set cell B30 to 50000, by changing cell B27, then goal seek cannot do as it is
told as B30 does not contain a formula.
Use Goal Seek to find the result you want by adjusting an input value.
If you know the result that you want from a formula, but are not sure what input value
the formula needs to get that result, use the Goal Seek feature!