How to use goal seek in excel for what if analysis
If you know the formula and the end result you want, you can reverse engineer to find the input value for the formula which will you the desired output value. To do this, we use Excel goal seek which is also the part of What if analysis in excel.
Rather than going from input to output, you go from output (desired result) to input. You figure out for what input value the output be X. In this article, we’ll go through What If Analysis in excel using goal seek in excel.
What is Goal Seek in Excel?
What If Analysis gives us the option to test our various scenarios and determine a range of outcomes. Goal Seek Excel is part of the What If Analysis, it helps you understand what value we should enter into an input cell to get a desired result in the formula cell.
Use of goal seek in excel, is majorly done for financial modelling, sales forecasting etc. But it can be used for a number of different reasons as well.
Example: You can use Goal Seek Analysis in Excel to find how much you need to score in your last exam to get an overall grade of 80 (Example 1). Or you can use it to find how many units you will have to sell in a month to reach net profit of $50000 (Example 2). Or find the loan amount at a given interest rate which produces a monthly payment of $2000 (Example 3)
How to use Goal Seek in Excel?
Let’s see how we can use the goal seek function in excel. We have a simple data set:
We have sales for three years and we want to find out, how much sales we need to do in the fourth year to reach a net profit of $25000. Let’s see where is goal seek in excel and how to use it:
- We need to have 2 cells. One which has the formula (output cell) and the other a dependent cell (input cell).
- Go to Data tab. Under Forecast group select What If Analysis and then select Goal Seek
- In the dialog box, define the following cells value and hit OK.
- Set cell: The cell which contains the formula
- To value: The result you want to achieve in the formula cell
- By changing cell: The input cell whose values would be changed to get to the desired result.
- The Goal Seek status box will appear. If the function succeeded the value in dependent cell would be changed. That will tell you the Sales (Input value) you need to achieve to get the Net Profit (Output value) as $25000
- Excel Goal Seek never changes the formula, it only changes the dependent cell (input value)
- It goal seek isn’t able to find the exact input value, it’ll give you the closed one.
Excel Goal Seek Example 1
We need to find how much should we score in our fourth term to get an overall grade of 80. The calculation of grade here is done by calculating the average of grades of each term.
- Open the Goal Seek dialog box. Set the following values:
Set cell: B7 which has the formula to calculate overall grade
To value: 80 as that’s the overall grade we want to achieve
By changing cell: B5 which would have the score of fourth term.
- Hit OK
- Now you can see how much we need to score in fourth term to achieve the overall grade of 80.
This was a very simple example, let’s look at some more complex or fun ones.
Excel Goal Seek Example 2
Here from the given data set, we want to find how many units we will have to sell in a given month to reach the net profit of $50000 for that month. Now here we have a couple of extra variables i.e. Product Price, Quantity and Profit Margin.
- Open the goal seek dialog box. Set the following values:Set cell: B6 which has the formula of calculating the net profit
To value: 50000 which is the net profit we want to achieve
By changing cell: C6 which would have the quantity we need to sell to achieve NP of $50000
- Hit OK
- Now you can see how much we need to sell (Quantity) to reach net profit of $50000.
- But let’s say you know that selling so many units would be not possible, then you actually change the value of ‘By changing cell’ to B6.
Which will tell you for a given quantity which you feel you can sell easily what should be the price at which you should sell.
Excel Goal Seek Example 3
We want to find what should be the loan amount which will result in the monthly payment of $2000 which we can afford to pay for mortgage.
To calculate the monthly payment, we use the PMT function in excel.
- Open the goal seek dialog box. Set the following values:Set cell: B5 which has the PMT function
To value: 2000 which is the monthly payment we can afford
By changing cell: B3 which would have the amount of loan we need to take
- Hit OK
Now you can see how loan we would need to take to pay a monthly payment of $2000
Beyond Goal Seek
The limitation will Goal Seek is that it requires a single input cell and single output cell. You can actually use Solver in Excel to create scenarios or get output value with multiple input cells.
To adjust the iteration settings, follow the below steps:
- Go to File. Select Options then select Formulas.
- Change the value of maximum iterations and maximum change.
Maximum Iterations: If you increase the number, excel will test for more possible solutions
Maximum Change: Decrease the number if you’re looking for more accuracy.
What If Analysis in excel if one of the most used feature in forecasting and scenario building. Goal seek is just a part of it. I recommend do read What If Analysis to understand more about scenario building.
I hope this article was helpful to you and explained you what is goal seek in excel and how to use goal seek in excel.
If you have any questions or tips, I’d love to hear them in the comment section below.
Free Excel Resources
Get top 10 free excel templates, excel shortcuts and other free excel resources to help you learn excel.
Excel Training Courses
Learn Pivot Tables, Vlookup, Power BI and much more.
Our training courses are coming soon.