The Goal Seek function in Excel allows you to adjust an input value in a formula to determine a desired outcome. This What-If Analysis tool is ideal for situations where you know the outcome you want, but aren’t sure of the values needed to reach that outcome.
In this tutorial, we’ll show you how to use the Goal Seek function, as well as provide some real-world use cases.
We’ll cover:
- What is the Goal Seek function in Microsoft Excel?
- Goal Seek function use cases
- How to use Goal Seek in Excel: A step-by-step tutorial
- Forward-looking decision making based on data
1. What is the Goal Seek function in Microsoft Excel?
Microsoft Excel comes with three built-in What-If Analysis tools: Data Tables, Scenarios, and Goal Seek. All three tools are used in “what-if” scenarios to understand how changing certain input values will affect the output value, or the desired outcome.
The Goal Seek function is used to understand what input value is needed in order to realistically achieve your set goal.
One caveat that you need to be aware of—the Goal Seek function only works if there is one input value you want to define. If there are multiple input values you want to define, you can use the Solver add-in instead.
For example, if you want to take out a loan and you know the output value, which is how much you can afford to pay each month, and you know the input values of (1) how much money you need to borrow and (2) how many months you require to pay the loan back, you can use the Goal Seek function to determine (3) the interest rate needed to keep the monthly payment within your budget.
However, if you want to determine what the interest rate you need is and the amount of time required to pay the loan back in order to keep the monthly payment at your desired amount, you would need to use Solver.
2. Goal Seek function use cases
There are any number of situations in which you may want to consider using Goal Seek. Here are few hypothetical situations in which the Goal Seek function would come in handy.
Business
Let’s say your company has set a goal of $2,000,000 USD net profit for the current year. Q3 just closed, so you have the net profit data for Q1, Q2, and Q3. Your current net profit for the year is $1,518,750.
You can use Goal Seek to determine what net profit you need to close Q4 with in order to reach your annual goal of $2,000,000.
Education
We’ve all had that one subject in school that, no matter how hard we studied, we just weren’t very savvy in. Perhaps you have a failing grade, and you want to determine what score you need on your final exam to reach that passing threshold. Or, perhaps you have a B and want to raise it to an A.
You can use Goal Seek to determine what score you need to make on the final exam to receive the desired final score.
Personal
The most widely used example of a personal use case of Goal Seek is the loan example. You know the amount you need, you know how many months you want to pay it back, and you know the amount you can afford to pay back each month.
You can use Goal Seek to determine the interest rate you need to secure in order to stay within your monthly budget.
3. How to use Goal Seek in Excel: A step-by-step tutorial
In this how-to guide, we’ll show you how to use the Goal Seek function in Microsoft Excel with our previously-mentioned business scenario.
To repaint the scenario, the company set a net profit goal of $2,000,000 USD and Q3 just concluded. So, we know the desired outcome, and we have all of the data except one value: Q4.
Current Quarterly Data:
- Q1: $585,000
- Q2: $217,500
- Q3: $416,250
- Q4: ?
Current Annual Net Profit:
- $1,518,750
Annual Net Profit Goal:
- $2,000,000
Based on the data we currently have, we can use the Goal Seek function to determine what the net profit of Q4 needs to be in order to achieve the set goal.
Open the Excel sheet that contains your data. Here’s what ours looks like in this example.
Before we go further, there are a few basic Excel functions that we are using in this example. In D6, we are using the =SUM() function to calculate the sum of cells D2, D3, D4, and D5. So we entered =SUM(D2:D5) in cell D6.
Additionally, you’ll want to automate the values received in cells D2-D5. For example, to automatically calculate the data in B2 and C2 and produce the final result in D2, click the D2 cell and enter: =B2*C2 and press Enter. Do this for rows 3, 4, and 5, as well.
Now that those basic Excel functions are in place, we can use Goal Seek to determine what we need to close Q4 with to meet our annual goal.
Click the cell that is using the =SUM() function (in our example, cell D6). This is the cell that we want to change to our desired goal. Once selected, a box will appear around the cell and the respective row number and column letter will be highlighted in gray.
Next, click the Data tab, click the What-If Analysis option in the Forecast group, and then click the Goal Seek option from the drop-down menu.
The Goal Seek dialog window will appear. There are three values here we need to input.
- Set cell: The cell that contains the value in which you want to add the desired outcome.
- To value: The value of your goal.
- By changing cell: The cell containing the input value that will determine the outcome.
Here are the figures we need to input to reach the goal set in our example.
- Set cell: D6
- To value: 2000000
- By changing cell: B5
Enter the required information and then click OK. Excel will start analyzing the data and spit out the number required in B5 to meet the desired goal.
So in our example, the analysis determined that, in order for us to reach our $2,000,000 goal, we need to reach a gross profit of $3,208,333.33, which, at 15%, nets us a profit of $481,250.
4. Forward-looking decision making based on data
Using the Goal Seek function is essential when you need to determine the input value required to achieve your goal. No matter if it’s determining the gross/net profit you need in Q4 to meet the company’s annual profit goal, the score you need on the final exam to secure a passing grade, or the interest rate you need to negotiate in order to lower the monthly payment to a number you can afford, the Goal Seek function will quickly give you the data you need to make a realistic, data-based plan.
This is just the tip of the iceberg. To further your expertise in data analytics, try out our free introductory short course. If you found this tutorial helpful and want to explore more of our Excel guides, check out these articles: