What do we mean by Goal Seek in Microsoft Excel?

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

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

What is Goal Seek in Microsoft Excel?

"Goal Seek is a part of Microsoft Excel's inbuilt What-If Analysis tool that usually 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."

Moreover, the respective "Goal Seek" is a handy Microsoft Excel tool that quickly 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 or desired value.
3. The cell changes to reach the target output as well.

The "Goal Seek" is a vital tool that can be used for performing sensitivity analysis in financial modeling and is commonly used by management officials and business owners. And whenever we want an Excel formula to return the desired output but need more confidence about what internal values we should enter to adjust the output, save time, and utilize the Excel Goal Seek tool respectively.

For example, Goal Seek can help us determine the sales target that we must attain in a specific timeframe to reach a target of $400,000 annual profit. Or, how many marks should we score in different subjects to score an overall passing score of 65% in our last exam? Or how many votes we need to get to win the election as well.

Important Note: The Goal Seek took can only work upon one value at a time. Let us assume that we want to maintain an advanced business model and process more than one value or multiple input values simultaneously. In that case, we can easily opt for another What-If Analysis tool, the Solver add-in tool, to find the optimal solution.

What is the primary step to perform Goal Seek in Microsoft Excel?

This section will walk us through the step-by-step implementation of the "Goal Seek tool." So, we will be taking a straightforward data set as a reference:

As we see in the below section, a student has already been given 3 exams; the last is pending. And 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?

Now let us see the below steps to compute the answer to Exam 4 by just making use of Microsoft Excel's Goal Seek tool:

Step 1: First, we need to open our Microsoft Excel worksheet and enter the data to have a formula cell and a fluctuating cell so that we can easily change the value of Exam 4 accordingly on the formula cell, respectively.

Step 2: And from our respective Microsoft Excel ribbon tab, we need to click on the Data tab option > go to Forecast group, and then will click on the What if Analysis button. The window will get appear and select the Goal Seek option.

Step 3: As we can see, the "Goal Seek" dialog box will appear on the screen. In this window, we will define the cells or the values to test and choose the appropriate values for the fields. This window contains three fields, which are efficiently mentioned below respectively:

• Set cell: In this text field, we will reference 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 respectively.
• By changing cell: This is the last text field; here, we all need to enter the reference for the input cell we want to adjust (B3).

And once we have filled all three fields, we must click on the OK button.

Microsoft Excel will immediately prompt out "The Goal Seek Status" window box. And this dialog box will effectively display the result and let us know whether the solution for the given problem has been found. If it succeeds, the "changing cell" value will be replaced with a new value respectively.

Next, we will click on the OK button to keep the new value or the Cancel button to restore the original one respectively.

This particular "Goal Seek" has found that 223 items (rounded up to the next integer) need to be sold to achieve revenue of $1,000.

If we are not sure we can sell that many items, we can reach the target revenue by changing the price. And to test this scenario, do Goal Seek analysis precisely as described above, except that we usually specify a different Changing cell (B2):

As a result, we will find out that if we increase the unit price to $11, we can reach $1,000 in revenue by just selling only 100 items respectively:

What are the essential Tips and notes associated with the Goal Seek in Microsoft Excel:

1. Microsoft Excel Goal Seek does not change the formula; instead, it only changes the input value we might supply By changing the cell box.
2. If Goal Seek cannot find the solution, then it will only display the closest value it has come up with.
3. And we can quickly restore the original input value by just clicking on the Undo button or pressing the Undo shortcut, which is none other than Ctrl + Z, respectively.

Examples of using Goal Seek in Microsoft Excel

Below we will find a few more examples of using the Goal Seek function in Microsoft Excel. And the complexity of our business model only matters if our formula in the Set cell depends upon 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 - we have the sales figures for the first 3 quarters, and we usually want to know how much sales we have to make in the last quarter for the purpose of achieving the target net profit for the year, say, $100,000.

Solution: With the source data organized like shown in the screenshot above, we will be following the below-mentioned steps very carefully:

Step 1: First, we need to open our Microsoft Excel worksheet, and then we will enter the data to have a formula cell and a fluctuating cell so that we can easily change the value of Exam 4 accordingly on the formula cell.

Step 2: Now, in this step, from our Microsoft Excel ribbon tab, we need to click on the Data tab option > go to Forecast group, and then we will click on the What if Analysis button. The window will get appear and select the Goal Seek option respectively.

Step 3: And in the window box, we need to set up the following parameters for the Goal Seek function:

1. Set cell: The formula calculates the total net profit (D6).
2. To value: We are looking for the formula result ($100,000).
3. By changing cell: And the respective cell to contain the gross revenue for quarter 4 (B5), respectively.

Result: The particular "Goal Seek analysis" shows that to obtain a $100,000 annual net profit, our fourth-quarter revenue must be $185,714 effectively.

Example 2: To Determine the exam passing score

Problem: At the end of the course, a student must take 3 exams. The passing score is 70%. All the exams have the same weight, so the overall score is calculated by just 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 us now do the "Goal Seek" to determine the minimum score on exam 3 as well:

Step 1: First of all, we need to open our Microsoft Excel worksheet, and then we need to enter the data to have a formula cell and the fluctuating cell so that we can easily change the value of Exam 4 accordingly on the formula cell.

Step 2: Just after that, from our respective "Excel ribbon tab," we need to click on the Data tab option > then go to Forecast group, and will click on the What if Analysis button. The window will appear on the screen, and from that, we will select the Goal Seek option.

Step 3: In this step, in the respective "window box," we are required to set up the following parameters for the Goal Seek function:

1. Set cell: The formula that averages out the scores of the 3 exams (B5).
2. To value: It is representing the passing score (70%).
3. By changing cell: The 3 ^rd exam score (B4) respectively.

Result: To get the desired overall score, the student must also achieve a minimum of 67% on the last exam.

Example 3: What-If Analysis of the Election

Problem: If we are running for some elected position, a two-thirds majority (66.67% of votes) is required to win the election. And assuming that there are 200 total voting members, how many votes do we need to secure?

Currently, we have 98 votes, which is quite suitable but not sufficient because it only makes up 49% of the total voters:

Solution: We generally make use of the "Goal Seek" to find out the minimum number of "Yes" votes we need to get it as well:

Step 1: We must open our Microsoft Excel worksheet and enter the data to have a formula cell and the fluctuating cell so that we can easily change the value of Exam 4 accordingly on the formula cell, respectively.

Step 2: And from our "Microsoft Excel ribbon tab," we need to click on the Data tab option > then go to Forecast group, and will click on the What if Analysis button. The window will appear on the screen, and then we will select the Goal Seek option.

Step 3: Now, in the window box, we need to set up the following parameters for the Goal Seek function:

1. Set cell: The formula that calculates the percentage of the current "Yes" votes (C2).
2. To value: The required percentage of "Yes" votes (66.67%).
3. 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%, we 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, Microsoft Excel will get the closest value and inform us that the respective Goal Seeking may not have found a solution:

If we are certain that a solution to the formula which we are trying to resolve exists, check out the following troubleshooting tips as well.

1. Double-check Goal Seek parameters.
   
   • First, 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 our Microsoft Excel, click File > Options > Formulas and change these options:
   • Maximum Iterations - increase this number if we want Excel to test more possible solutions.
   • Maximum Change - decrease this number if your formula requires more accuracy. For example: if we 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.
2. No circular references
   For Goal Seek (or any Excel formula) to work correctly, the involved formulas should not be co-dependent, i.e., no circular references.

That's how we perform What-If analysis in Microsoft Excel with the Goal Seek tool.