Hi Guys,
We hope that you are enjoying our blog posts. Please share it with colleagues & friends in case you find them useful.
“What if analysis” is one of the most powerful tools. In general terms, “what if analysis “allows us to test our possible scenarios and determine a range of possible outcomes. In other words, It enables us to see the impact of making certain changes without changing the real data. In this blog post, we will discuss excel’ s “what if analysis” features- Goal Seek
What is Goal Seek in excel?
Excel’s “what-if analysis” tools show how a value in a formula impact another. The best things about the goal seek is that it works behind the scenes, we must define the only few things, which are as listed below-
- Formula
- To value
- By changing cells
The Goal seek is especially useful for financial modelling, management, and business owner. However, it is useful for others too.
For instance, Goal seek can tell us, to get $120 commission, how much sales we have to make? How many sales we have to make for a certain time period to reach the target profit value of INR100000? How many votes, we need to get in order to win the election?
The above table indicates that commission value is based on the multiplication of Sales and percentage value. The question is, to get the exact `122 commission, how many sales do we have to make? Stop guessing sales amount and use the goal seek function.
- Go to the data tab
- In Forecast group
- What if analysis
- Goal Seek
5. In the goal seek dialogue box, define the cell values
Set cell: The reference to the cell, which contains a formula (B5)
To Value- The result what we are trying to achieve ( `122 )
By Changing cell: the reference of the input cell that you want to adjust ( B3)
Refer the image below-
Click on Ok
- The Goal seeks status will appear to let us know that a solution has been found. Once we confirmed it or click on Ok, Existing values will be replaced by the new one.
According to the Goal seek, If we sale `5,545.45, then we can achieve the exact commission of `122.
- Click Ok if we want to restore the value or if we can cancel this calculation by clicking on cancel option.
Advantages of Goal Seek-
- Excel’s Goal seek does not change the formula, only change the Input cell value.
- Excel’s Goal Seek does reverse calculation without putting any effect on formula.
- If Goal seek does not able to find the solution, it displays the closest values it has come with up.
- We can restore the original input cell by clicking on “Undo button “ or press a short cut key Ctrl+Z
Some more Example of Goal Seek-
1- Reach the target voters- This is the instance of a Political party, in which it got a total number of votes from different sectors. Now, for this election time, It has decided to achieve the 5000 total number of Votes. So, How many voters should vote them from the sector3 to achieve the goal?
- Go to the data tab
- What if analysis
- Goal Seek
- Fill all the details as explained below
- ok
Goal seeks to display the solution, what it has found. Refer the image below –
2- Passing Score- After the completion of all modules, the student is appearing for the exam. The passing marks are 33 which is the average of all the marks. He has appeared 3 out of 4 papers into the exam. The question is How many marks he scores in the last exam so that he achieves the average marks is 33.
- Go to data Tab
- What if analysis
- Goal Seek
- Fill the details
- Ok
Once we click on Ok button, Goal seek to display the solution, displaying below-
If Goal seek is not working-
There can be so many reasons when Goal seeks is not working. Here, we are discussing some of the important causes-
1- Sometimes goal seek is not able to find the solution, simply, maybe that number does not exist. In such situations, Goal seeks will get the closest value or inform us that Goal seeking may not have found a solution.
2- Always remember, set cell contains a formula.
3- For Goal seek to work smoothly, the involved formula should not be co-dependent on each other. I.e. there should not be any circular references.
That’s how you perform what-if analysis with Goal Seek. We are thankful to you to read this blog post. If you have any query feel free to write it down into the comment box.
Stay tuned and connected with Nurture tech academy.
Happy Learning 😊