Excel’s goal seek function is perfect for iteration: trying an input to see the impact on an output. Here are some examples where you might find goal seek useful:
- Break even analysis: how far could a key input (e.g. revenue growth) drop before profits or cash flow dropped to zero?
- Stress testing: how far could a key input drop before a key financial ratio breached?
- Returns analysis: how far would we need to cut capital expenditure to achieve our target return?
Whenever you find yourself using trial and error (e.g. inputing different assumptions around revenue growth or cap-ex) to see what happens you should start to think “goal seek”. Regularly in modelling we want to iterate or stress test. That’s why we think goal seek needs to be in your Excel survival tool kit.
Using Excel goal seek – an example
You can find goal seek on the “Data” tab, under “Data Tools”, “What If Analysis”.
Here’s a very simple example that illustrates how you could use goal seek in a complex model. Imagine we wanted to answer the question: “How far would gross margin % have to fall before gross profit dropped below 80?”.
You can download the example here: Excel goal seek.
Click on cell B6. Pull up goal seek by heading to the “Data” tab, “Data Tools” “What If Analysis”, “Goal Seek”. Your “set cell” is B6. For “to value” you should enter “80”. The “changing cell” is B5 (it needs to be a hard coded input). Click on “OK”. We have the answer! Once gross margin drops below 40% profits dip below 80.
If you would have been tempted to solve a problem like this using your own trial and error in cell B5, the good news is Excel does an awesome job of speeding up the process. Goal seek is doing a pretty simple job (experimenting with one input cell to see the impact on an output cell) but it does that job very quickly and very well.
Because we often need to iterate with a financial model, looking at the impact of a key input on a key output, we love goal seek. This makes goal seek a “must have” in financial modelling – put it firmly inside your Excel survival toolkit!
About this online financial modelling course material
You’re looking at a free online financial modelling course extract covering Excel functions.