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.
Table of contents
YouTube Know the answer but want the input
Know the cash payment but not the pre tax amount
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
- which cell you are working towards (the take home pay),
- what value do you want it to become (R750 000 in this case), and
- which 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.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
Related
Slow Spell Check, GoalSeek and Find/ Replace in Excel
Simple way to create scenarios in excel (when you have already built the spreadsheet)