The tutorial explains how to use Goal Seek in Excel 2016, 2013 and 2010 to get the formula result you want by changing an input value.
What-If Analysis is one of the most powerful Excel features and one of the least understood. In most general terms, What-If Analysis allows you to test out various scenarios and determine a range of possible outcomes. In other words, it enables you to see the impact of making a certain change without changing the real data. In this particular tutorial, we will focus on one of Excel's What-If Analysis tools - Goal Seek.
Goal Seek is Excel's built-in What-If Analysis tool that shows how one value in a formula impacts another. More precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell.
The best thing about Excel Goal Seek is that it performs all calculations behind the scenes, and you are only asked to specify these three parameters:
The Goal Seek tool is especially useful for doing sensitivity analysis in financial modeling and is widely used by management majors and business owner. But there are many other uses that may prove helpful to you.
For instance, Goal Seek can tell you how much sales you have to make in a certain period to reach $100,000 annual net profit (example 1). Or, what score you must achieve for your last exam to receive an overall passing score of 70% (example 2). Or, how many votes you need to get in order to win the election (example 3).
On the whole, whenever you want a formula to return a specific result but are not sure what input value within the formula to adjust to get that result, stop guessing and use the Excel Goal Seek function!
Note. Goal Seek can process only one input value at a time. If you are working on an advanced business model with multiple input values, use the Solver add-in to find the optimal solution.
The purpose of this section is to walk you through how to use the Goal Seek function. So, we'll be working with a very simple data set:
The above table indicates that if you sell 100 items at $5 each, minus the 10% commission, you will make $450. The question is: How many items do you have to sell to make $1,000?
Let's see how to find the answer with Goal Seek:
In this example, Goal Seek has found that 223 items (rounded up to the next integer) need to be sold to achieve a revenue of $1,000.
If you are not sure you will be able to sell that many items, then maybe you can reach the target revenue by changing the item price? To test this scenario, do Goal Seek analysis exactly as described above except that you specify a different Changing cell (B2):
As the result, you will find out that if you increase the unit price to $11, you can reach $1,000 revenue by selling only 100 items:
Tips and notes:
Below you will find a few more examples of using the Goal Seek function in Excel. The complexity of your business model does not really matter as long as your formula in the Set cell depends on the value in the Changing cell, directly or through intermediate formulas in other cells.
Problem: It is a typical business situation - you have the sales figures for the first 3 quarters and you want to know how much sales you have to make in the last quarter to achieve the target net profit for the year, say, $100,000.
Solution: With the source data organized like shown in the screenshot above, set up the following parameters for the Goal Seek function:
Result: The Goal Seek analysis shows that in order to obtain $100,000 annual net profit, your fourth-quarter revenue must be $185,714.
Problem: At the end of the course, a student takes 3 exams. The passing score is 70%. All the exams have the same weight, so the overall score is calculated by averaging the 3 scores. The student has already taken 2 out of 3 exams. The question is: What score does the student need to get for the third exam to pass the entire course?
Solution: Let's do Goal Seek to determine the minimum score on exam 3:
Result: In order get the desired overall score, the student must achieve a minimum of 67% on the last exam:
Problem: You are running for some elected position where a two-thirds majority (66.67% of votes) is required to win the election. Assuming there are 200 total voting members, how many votes do you have to secure?
Currently, you have 98 votes, which is quite good but not sufficient because it only makes 49% of the total voters:
Solution: Use Goal Seek to find out the minimum number of "Yes" votes you need to get:
Result: What-If analysis with Goal Seek shows that to achieve the two-thirds mark or 66.67%, you need 133 "Yes" votes:
Sometimes Goal Seek is not able to find a solution simply because it does not exist. In such situations, Excel will get the closest value and inform you that Goal Seeking may not have found a solution:
If you are certain that a solution to the formula you are trying to resolve does exist, check out the following troubleshooting tips.
First off, make sure the Set cell refers to the cell containing a formula, and then, check if the formula cell depends, directly or indirectly, on the changing cell.
In your Excel, click File > Options > Formulas and change these options:
The below screenshot shows the default iteration settings:
For Goal Seek (or any Excel formula) to work properly, the involved formulas should not be co-dependent on each other, i.e. there should be no circular references.
That's how you perform What-If analysis in Excel with the Goal Seek tool. I thank you for reading and hope to see you on our blog next week!
Table of contents