How to use Goal Seek in Excel for What-If analysis
This tutorial shows how to use Goal Seek in Excel to get the result you want by changing an input value.
What-If Analysis is one of Excel’s most powerful but least understood features. Simply put, it lets you try out different scenarios and see possible outcomes. In other words, it helps you see how changes affect your data without actually changing the real data. In this tutorial, we’ll focus on one of Excel’s What-If Analysis tools—Goal Seek
Table of Contents
What is Goal Seek in Excel?
Goal Seek is a built-in Excel tool that helps you see how changing one value affects another. It finds out what value you need to enter in one cell to get the result you want in another cell.
The best part of Excel Goal Seek is that it does all the calculations for you. You only need to provide these three pieces of information
- formula cell
- Target/desired value
- The cell to change in order to achieve the target
Goal Seek is especially useful for financial modeling and is popular among business students and owners. But it has many other helpful uses.
For example, Goal Seek can tell you how much sales you need to make to reach $100,000 in annual net profit (example 1). It can show what score you need on your last exam to get an overall passing score of 70% (example 2). It can even calculate how many votes you need to win an election (example 3).
In short, if you want a formula to give a specific result but aren’t sure what input value to change to get that result, stop guessing and use Excel’s 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.
How to use Goal Seek in Excel
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 table shows that selling 100 items at $5 each, minus a 10% commission, will make you $450. The question is: How many items do you need to sell to make $1,000?
To find the answer using Goal Seek, follow these steps:
- Set up your data with a formula cell and a changing cell that depends on the formula cell.
- Go to the Data tab, find the Forecast group, click What-If Analysis, and select Goal Seek…”
3.In the Goal Seek dialog box, define the cells/values to test and click OK:
- Set cell– the reference to the cell containing the formula (B5).
- To value – the formula result you are trying to achieve (1000).
- By changing cell– the reference for the input cell that you want to adjust (B3).
The Goal Seek Status dialog box will show if a solution has been found. If it worked, the value in the “changing cell” will be updated. Click OK to keep the new value or Cancel to go back to the original one.
In this example, Goal Seek has determined that you need to sell 223 items (rounded up) to make $1,000.
If you think you might not be able to sell that many items, you could try reaching your target revenue by changing the item price instead. To test this, use Goal Seek as described above, but this time, choose 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:
Excel Goal Seek doesn’t change the formula itself; it only changes the input value you provide in the ‘By changing cell’ box.
If Goal Seek can’t find a perfect solution, it will show the closest value it found.
You can go back to the original input value by clicking the Undo button or pressing Ctrl + Z.
Examples of using Goal Seek in Excel
Here are a few more examples of using the Goal Seek function in Excel. Your business model can be as simple or complex as needed. What matters is that the formula in the Set cell depends on the value in the Changing cell, either directly or through other formulas.
Example 1: Reach the profit goal
Problem: You have sales figures for the first three quarters and want to find out how much you need to sell in the last quarter to reach a target net profit of $100,000 for the year
Solution: With your data set up as shown in the screenshot, use these settings for the Goal Seek function:
- Set cell: The cell with the formula that calculates the total net profit (D6).
- To value: The result you want ($100,000).
- By changing cell: The cell for the gross revenue in quarter 4 (B5).
Result: The Goal Seek analysis shows that in order to obtain $100,000 annual net profit, your fourth-quarter revenue must be $185,714.
Example 2: Determine the exam passing score
Problem: A student takes 3 exams at the end of a course. To pass, they need an average score of 70%. All exams are equally weighted. The student has already taken 2 exams. The question is: What score does the student need on the third exam to pass the course?
Solution: Use Goal Seek to find the minimum score needed on exam 3:
- Set cell: The cell with the formula that averages the 3 exam scores (B5).
- To value: The passing score (70%).
- By changing cell: The score for the 3rd exam (B4).
Result: In order get the desired overall score, the student must achieve a minimum of 67% on the last exam.
Example 3: What-If analysis of the election
Problem: You are running for an elected position that requires a two-thirds majority (66.67% of the votes) to win. There are 200 total voters, so how many votes do you need?
Currently, you have 98 votes, which is good, but not enough since it’s only 49% of the total votes
Solution: Use Goal Seek to find the minimum number of ‘Yes’ votes you need:
- Set cell: The cell with the formula that calculates the percentage of ‘Yes’ votes (C2).
- To value: The required percentage of ‘Yes’ votes (66.67%).
- By changing cell: The number of ‘Yes’ votes (B2).
Result: What-If analysis with Goal Seek shows that to achieve the two-thirds mark or 66.67%, you need 133 “Yes” votes:
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!
