When you are working with complex calculations you sometimes know what you want the answer to be, but you can’t calculate backwards to determine the answer. So for example, if you know what the take home pay needs to be, but are not sure what the pre tax income needs to be to achieve it, you can get Excel to Goalseek it. This way you build/ use a calculator that logically goes from income to take home pay, but you get Excel to go backwards.
Below the Tax Tables in South Africa. We want to know what the blue input cell (A31) needs to be so that the Take Home Pay (answer cell J31) is R750 000. The red box is the calculations required to determine this.
We can manually type in some numbers into cell A31 and see how close we come (so trial and error) or get Excel to do it for you.
So click on:
- Data Ribbon, then
- What If Analysis, then
- Goalseek (as shown below)
Now follow the dialogue box. Tell it
- what cell you are working towards (the take home pay),
- what value do you want it to become (R750 000 in this case), and
- what cell is Excel allowed to change to achieve this number.
Excel will then run through all the possibilities until it either finds an answer, or tells you that what you want can’t be achieved. As shown below, you need to start on R1 133 262.54 to end up with R750 000 assuming just tax deductions.
This works for any calculation. So if you have a complex spreadsheet that takes an input and turns it into an output, Excel can work backwards so that it will tell you what the input needs to be in order to achieve a desired output.
You can learn more about Goalseek in the What If Analysis Course