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:

Excel Goal Seek 1

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:

  1. We need to have 2 cells. One which has the formula (output cell) and the other a dependent cell (input cell).
  2. Go to Data tab. Under Forecast group select What If Analysis and then select Goal Seek
    Excel Goal Seek 2
  3. 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.
      Excel Goal Seek 3
  4. 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 4

Please Note:

  1. Excel Goal Seek never changes the formula, it only changes the dependent cell (input value)
  2. 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.

Excel Goal Seek 5

  1. 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.
    Excel Goal Seek 6
  2. Hit OK
  3. Now you can see how much we need to score in fourth term to achieve the overall grade of 80.

Excel Goal Seek 7

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.

Excel Goal Seek 8

  1. 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
    Excel Goal Seek 9
  2. Hit OK
  3. Now you can see how much we need to sell (Quantity) to reach net profit of $50000.
    Excel Goal Seek 10.1
  4. 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 11

Excel Goal Seek 12

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.

Excel Goal Seek 13

  1. 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
    Excel Goal Seek 14
  2. Hit OK

Now you can see how loan we would need to take to pay a monthly payment of $2000
Excel Goal Seek 15

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:

  1. Go to File. Select Options then select Formulas.
     Excel Goal Seek 16
  2. 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.

Conclusion

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.

Excel Categories

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.

Ask a Question or Leave a Reply

Excel Categories

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.

Learn Army Logo
Learnarmy.com Copyright © 2020 All Rights Reserved