filmov
tv
Know the answer but want the input using an Excel calculation. Use Goalseek to target a value
Показать описание
00:00 Intro
00:08 Goalseek the pre tax salary based on required net salary in the bank
00:25 Using Goalseek on the salary problem
Images on website
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.
If you know the answer but want the input from Excel
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)
If you know the answer but want the input from Excel
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.
If you know the answer but want the input from Excel
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.
If you know the answer but want the input from Excel
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.
00:08 Goalseek the pre tax salary based on required net salary in the bank
00:25 Using Goalseek on the salary problem
Images on website
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.
If you know the answer but want the input from Excel
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)
If you know the answer but want the input from Excel
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.
If you know the answer but want the input from Excel
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.
If you know the answer but want the input from Excel
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.
0:00:57
0:02:38
0:00:12
0:00:11
0:10:49
0:00:14
0:00:08
0:00:12
0:02:22
0:00:31
0:00:42
0:00:18
0:00:15
0:00:29
0:00:23
0:01:09
0:00:21
0:00:29
0:00:27
0:00:28
0:01:02
0:00:29
0:00:27
0:00:40