Goal Seek in Excel

Businesses must check and brainstorm different strategies to attain the best option for their decision-making. To enable the same, Microsoft Excel facilitates one of the most powerful features known as Goal Seek. In most general terms, Goal Seek enables the users to test out different scenarios and decide a range of possible outcomes by adjusting a few pointers. Most Precisely, it allows you to visualize the result of making a specific change without changing the actual data.

In this particular tutorial, we will focus on the definition of Goal Seek, its uses, implementation, various example to demonstrate the working of Goal seek and its possible outcome, and why Goal seek doesn’t work in certain situations.

What is Goal Seek in Excel?

“Goal Seek is a part of Excel’s inbuilt What-If Analysis tool that displays how altering a single value in a formula can impact another value. In simple terms, it decides what value the user should set in an input cell to get the optimal output in a formula cell.“

Goal Seek is a handy Excel tool that easily fixes the solutions to various problems in different circumstances. The good part about this tool is that it does all tedious calculations behind the scenes, and the user is only asked to specify the below given three parameters:

1. Formula cell
2. Target/desired value
3. The cell to change in order to reach the target output

The Goal Seek is a vital tool for performing sensitivity analysis in financial modeling and is commonly used by management officials and business owners. Whenever you want an Excel formula to return the desired output but are not confident about what internal values you should enter to adjust the output, stop wasting time and utilize the Excel Goal Seek tool!

For example, Goal Seek can help you determine the sales target you must attain in a certain timeframe to reach a $400,000 annual profit. Or, how many marks you should score in different subjects to score an overall passing score of 65% in your last exam. Or how many votes you need to get in order to win the election.

Note. The Goal Seek took can only work upon one value at a time. Suppose you want to maintain an advanced business model and process more than one value or multiple input values at a time. In that case, you can opt another What-If Analysis tool i.e, the Solver add-in tool to find the optimal solution.

Steps to perform Goal Seek

This section will walk you through the step-by-step implementation of the Goal Seek tool. So, we’ll be taking a very simple data set as a reference:

As you can see in the below section, a student has already been given 3 exams, and the last exam is pending. To score first division, he must have a final grade of 7 CGPA. So, what marks should he score in Exam 4 to make the overall Grade 7?

Let’s see the steps to compute the answer of Exam 4 using Excel’s Goal Seek tool:

1. Open your Excel worksheet and enter the data to have a formula cell and a fluctuating cell so you can change the value of Exam 4 accordingly on the formula cell.
2. From your Excel ribbon tab, click to the Data tab option > go to Forecast group, click on What if Analysis button. The window will appear, select the Goal Seek option.
3. As you can see, the Goal Seek dialog box will appear. In this window, we will define the cells/values to test and choose the appropriate values for the fields. This window contains three fields, that are given below:
   • Set cell – In this text field, we will set the reference to the cell holding the formula (F10).
   • To value – In this text field, we will enter the formula output we are trying to achieve. In our case, we want the Grade 7.
   • By changing cell – this is the last text field, here we will enter the reference for the input cell that you want to adjust (B3).

Once you have filled all the three fields, click on the OK button.

Excel will immediately prompt The Goal Seek Status window box. This dialog box displays the result and lets you know whether the solution for the given problem has been found or not. If it succeeds, the “changing cell” value will be replaced with a new value.

Click on the OK button to keep the new value or click the Cancel button to restore the original one.

In this, 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:

1. Excel Goal Seek does not change the formula, it only changes the input value that you supply to the By changing cell box.
2. If Goal Seek is not able to find the solution, it displays the closest value it has come up with.
3. You can restore the original input value by clicking the Undo button or pressing the Undo shortcut (Ctrl + Z).

Examples of using Goal Seek in Excel

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.

# Goal Seek Example 1: Reach the profit goal

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, follow the below steps:

1. Open your Excel worksheet and enter the data to have a formula cell and a fluctuating cell so you can change the value of Exam 4 accordingly on the formula cell.
2. From your Excel ribbon tab, click to the Data tab option > go to Forecast group, click on What if Analysis button. The window will appear, select the Goal Seek option.
3. In the window box, set up the following parameters for the Goal Seek function:
   • Set cell – the formula that calculates the total net profit (D6).
   • To value – the formula result you are looking for ($100,000).
   • By changing cell – the cell to contain the gross revenue for 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: 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:

1. Open your Excel worksheet and enter the data to have a formula cell and a fluctuating cell so you can change the value of Exam 4 accordingly on the formula cell.
2. From your Excel ribbon tab, click to the Data tab option > go to Forecast group, click on What if Analysis button. The window will appear, select the Goal Seek option.
3. In the window box, set up the following parameters for the Goal Seek function:
   • Set cell – the formula that averages the scores of the 3 exams (B5).
   • To value – the passing score (70%).
   • By changing cell – the 3^rd exam score (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 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:

1. Open your Excel worksheet and enter the data to have a formula cell and a fluctuating cell so you can change the value of Exam 4 accordingly on the formula cell.
2. From your Excel ribbon tab, click to the Data tab option > go to Forecast group, click on What if Analysis button. The window will appear, select the Goal Seek option.
3. In the window box, set up the following parameters for the Goal Seek function:
   • Set cell – the formula that calculates the percentage of the current “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:

Excel Goal Seek is not working.

Sometimes Goal Seek cannot 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 exists, check out the following troubleshooting tips.

1. Double-check Goal Seek parameters.

• 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.
• Adjust iteration settings
• In your Excel, click File > Options > Formulas and change these options:
• Maximum Iterations – increase this number if you want Excel to test more possible solutions.
• Maximum Change – decrease this number if your formula requires more accuracy. For example, if you are testing a formula with an input cell equal to 0 but Goal Seek stops at 0.001, setting Maximum Change to 0.0001 should fix the issue.

The below screenshot shows the default iteration settings:

2. No circular references

For Goal Seek (or any Excel formula) to work properly, the involved formulas should not be co-dependent, i.e., there should be no circular references.

That’s how you perform What-If analysis in Excel with the Goal Seek tool.