Goal Seek is a feature that included in Excel. Do you know what is the excellence of Excel? It is the formula, with reference cell. If you change any reference cell value, then it will automatically changed the result where formula entered. Goal Seek works similar like this. The users who don't know about the use of a Goal Seek, are no need to afraid. Just remember my below thing:
Now if you change the value of A2 cell to 4, then in D2 cell it will automatically return 9 (i.e. 4+5). This is the point I'm trying to explain. While you change a cell value, your result in D2 changed automatically just because of formula entered in D2 cell.
Bullet Point of Goal Seek:
Goal Seek is a similar process to find the result like a formula explained above. But the fact is, in above example you have changed the value in A2 from 3 to 4 to get the result 9 in D2. In Goal Seek option, you need to mention 2 important things:
(1) The result you need
(2) The cell value which you want to change.
Example:
It is not necessary to explain the Goal Seek feature through a complex thing like "loan calculation". My goal is to make you excellent in Excel with simple examples like above. If you got it then you can calculate yourself anything. Such as "loan calculation". So, let's start by following below steps:
Step 1:
Assume that below is your data table that shows the Area wise Total Sale Quantity of Product 1. In D4 cell there is a formula =B4+C4 which returns 13. No formula is in B4 and C4.
Step 2:
If you look this report, You will understand how the total of B4+C4 is placed in D4. Now, if you change Sale Quantity of Dhaka Area in B4 cell to 7, then in D4 cell it will automatically returns 17 (due to 7+10 = 17).
For example, your Boss told you Set Total Sale Quantity Target 216 which is now 13. And add the rest of the increased quantity in Dhaka Area, as they will sale more.
So, what will you do? In generally, you change the value of B4 to 100 or 200 if not matched then again change and change and then finally you will set to 206 in B4 cell.
Now think, changing values + or -, can loss your times in office which is very much important for MIS Team as they always need to provide report within time. To reduce the time you can take help from Goal Seek to find the 206 value through it. But how?
Step 3:
Just Click the the Data | What-If Analysis | Goal Seek. A dialog box will appear like below image:
Now notice that, Goal Seek feature has 3 options. Set cell, To value and By changing cell. Keep in mind that:
Set cell: This is the single cell reference where the formula entered. In the above image, D4 cell contains a formula =B4+C4. So, Set cell reference should be D4.
To value: This is the area where you need to enter the result. As your Boss said, you have to set National Target 216, then just enter 216 in To Value text area.
By changing cell: Enter the single cell reference in this text box, where you want to change the value. As your Boss said, you need to change only B4 cell that means "change the Dhaka Area Target". So, enter B4 in By changing cell.
Now all are ready to calculate and show the beauty of working with Excel.
Just Click on OK button. You will see the below image:
As I have told you before starting this example, You will "change the value of B4 to 100 or 200 if not matched then again change and change and then finally you will set to 206 in B4 cell." But see the above image (Image 4). It has done automatic calculation and changed the B4 cell to 206 and due to formula present in D4 cell it also changed to 216.
So this is the beauty of Goal Seek I'd like to explain. Now I hope you can easily understand how to calculate any Loan Payment, National Sales Target, Area Manager's Target, Incentive Amount etc. I use Goal Seek for Forecasting National Sales Data. So, have fun with Goal Seek and destroy your fears about using this. It's very simple.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
Assume that, you have a value 3 in A2 cell and 5 in B2 cell. If you enter =A2+B2 in D2 cell, then it will return 8 in D2 cell.
Bullet Point of Goal Seek:
Goal Seek is a similar process to find the result like a formula explained above. But the fact is, in above example you have changed the value in A2 from 3 to 4 to get the result 9 in D2. In Goal Seek option, you need to mention 2 important things:
(1) The result you need
(2) The cell value which you want to change.
Example:
It is not necessary to explain the Goal Seek feature through a complex thing like "loan calculation". My goal is to make you excellent in Excel with simple examples like above. If you got it then you can calculate yourself anything. Such as "loan calculation". So, let's start by following below steps:
Step 1:
Assume that below is your data table that shows the Area wise Total Sale Quantity of Product 1. In D4 cell there is a formula =B4+C4 which returns 13. No formula is in B4 and C4.
Image 1: Area wise Total Sale Quantity of Product 1
If you look this report, You will understand how the total of B4+C4 is placed in D4. Now, if you change Sale Quantity of Dhaka Area in B4 cell to 7, then in D4 cell it will automatically returns 17 (due to 7+10 = 17).
But the problem is, sometimes you know the result but you don't know how to separate it.
For example, your Boss told you Set Total Sale Quantity Target 216 which is now 13. And add the rest of the increased quantity in Dhaka Area, as they will sale more.
So, what will you do? In generally, you change the value of B4 to 100 or 200 if not matched then again change and change and then finally you will set to 206 in B4 cell.
Now think, changing values + or -, can loss your times in office which is very much important for MIS Team as they always need to provide report within time. To reduce the time you can take help from Goal Seek to find the 206 value through it. But how?
Step 3:
Just Click the the Data | What-If Analysis | Goal Seek. A dialog box will appear like below image:
Image 2: Goal Seek dialog box
Now notice that, Goal Seek feature has 3 options. Set cell, To value and By changing cell. Keep in mind that:
Set cell: This is the single cell reference where the formula entered. In the above image, D4 cell contains a formula =B4+C4. So, Set cell reference should be D4.
To value: This is the area where you need to enter the result. As your Boss said, you have to set National Target 216, then just enter 216 in To Value text area.
By changing cell: Enter the single cell reference in this text box, where you want to change the value. As your Boss said, you need to change only B4 cell that means "change the Dhaka Area Target". So, enter B4 in By changing cell.
Now all are ready to calculate and show the beauty of working with Excel.
Image 3: After entering the references the Goal Seek dialog box will looks like this
Just Click on OK button. You will see the below image:
Image 4: Result of Goal Seek
As I have told you before starting this example, You will "change the value of B4 to 100 or 200 if not matched then again change and change and then finally you will set to 206 in B4 cell." But see the above image (Image 4). It has done automatic calculation and changed the B4 cell to 206 and due to formula present in D4 cell it also changed to 216.
So this is the beauty of Goal Seek I'd like to explain. Now I hope you can easily understand how to calculate any Loan Payment, National Sales Target, Area Manager's Target, Incentive Amount etc. I use Goal Seek for Forecasting National Sales Data. So, have fun with Goal Seek and destroy your fears about using this. It's very simple.
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw