Goal Seek in Excel

What is Goal Seek in Excel?

The Goal Seek in excel is a “what-if-analysis” tool that calculates the value of the input cell (variable) with respect to the desired outcome. In other words, the tool helps answer the question, “what should be the value of the input in order to attain the given output?”

For example, the cost price of a product is $15 and the business wants to earn revenue of $50,000. The Goal Seek function can determine the number of units of the product to be sold to achieve the given target of $50,000.

The Goal Seek changes the variable (cause) to study the variations in the result (effect). It shows the impact of change in one value on the other value. This is why it is helpful in cause and effect analysis.

Goal seek function in excel is used in financial modeling and forecasting scenarios.

Goal Seek Parameters

The parameters to be specified in the “Goal Seek” dialog box are stated as follows:

Set cell–In this box, enter the cell reference of the formula to be resolved. This is the target cell or the formula cell.
To value–In this box, enter the desired output to be achieved. This is the value of the “set cell” to be attained.
By changing cell–In this box, enter the reference of the input cell (variable) to be adjusted. This is the cell we want to change in order to impact the “set cell.”

Note: At a given time, Goal Seek works with only one variable input. For working with multiple input values, use the solver in Excel.

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Goal Seek in Excel (wallstreetmojo.com)

How to use Goal Seek in Excel?

Example #1

You can download this Goal Seek Excel Template here – Goal Seek Excel Template

The following table shows the votes received by the two candidates, A and B, who contested in an election. In the present situation, candidate B is leading by 10,000 votes. It is given that to win the election, 50.01% votes are required.

We want to change the voting percentage (column C) of candidate A in such a way that he wins the election. Calculate the total number of votes required by candidate A to win.

Let us calculate the present voting percentage for both the candidates. This is done by dividing the individual votes received by the total votes.

Candidate A has won 46.43% of the total votes. Therefore, he needs only 3.67% votes to win the election.

The steps to use Goal Seek in excel are stated as follows:

In the Data tab, click “Goal Seek” from the “what-if-analysis” drop-down.
The “Goal Seek” excel window appears as shown in the following image.
Enter $C$2 in the “set cell” box. This is because C2 is the target cell.
Enter 50.01% in the “to value” box. This is the value of the “set cell” that we want to obtain. In other words, the “set cell” should change to the target value of 50.01%.
Enter $B$2 in the “by changing cell” box. This is the cell we want to change to impact the “set cell” C2. Since all the parameters are set, click “Ok” to get the result.
The output is shown in the following image. Candidate A should secure 70,014 votes to win the election. With 70,014 votes, the voting percentage of candidate A will become 50.01%. Click “Ok” to apply the changes.

The total number of votes and the voting percentage of candidate A has changed. Consequently, the votes and the voting percentage of candidate B also changes.

The votes of candidate B are calculated by subtracting 70,014 from 140,000. The final output is displayed in the following image. Hence, candidate A wins the election.

Example #2

Mr. A opens a retail shop and sells goods at an average selling price of $25 per unit. To meet his operating expenses, he wants to generate monthly revenue of $250,000.

We want to calculate the quantity of the goods Mr. A should sell to earn the given revenue.

Step 1: In cell B3, apply the formula unit priceUnit Price Unit Price is a measurement used for indicating the price of particular goods or services to be exchanged with customers or consumers for money. It includes fixed costs, variable costs, overheads, direct labour, and a profit margin for the organization.read more*units (B1*B2).

Step 2: In the Data tab, click “Goal Seek” from the “what-if-analysis” drop-down. The “Goal Seek” window appears as shown in the following image.

Step 3: Enter $B$3 in the “set cell” box. The target cell is the revenue cell B3.

Step 4: Enter 250000 in the “to value” box. This is the value of the “set cell” to be achieved.

Step 5: Enter $B$2 in the “by changing cell.” The quantity has to be calculated in cell B2. So, this is the cell that has to be adjusted to achieve the given revenue. Click “Ok.”

Step 6: The output is displayed in the following image. Hence, Mr. A needs to sell 10,000 units of goods to generate revenue of $250,000. Click “Ok” to apply the changes.

Example #3

The following table shows the marks obtained (in percentage) by a student in five subjects. Her present average grade is 88%. Since there are six subjects in the class, she is yet to appear for the last examination.

The student is targeting the overall average of 90% from all the six subjects. We want to calculate the marks she should score in the remaining subject to secure the given average.

Step 1: Open the “Goal Seek” window from the Data tab.

Step 2: Enter $B$7 as the “set cell.” The target cell is the average cell B7.

Step 3: Enter “90” in the “to value” box. This is the value we want to attain.

Step 4: Enter $B$6 in the “by changing cell” box. This is the value that has to be adjusted. Click “Ok.”

Step 5: The output is displayed in the following image. Hence, the student should score 98% in “subject 6” to get an overall average of 90%. Click “Ok” to apply the changes.

Likewise, the Goal Seek helps identify the exact input that should be adjusted to reach the desired result.

The Rules Governing the Parameters

The “set cell” should always contain a formula.
The formula of the “set cell” should depend, whether directly or indirectly, on the “by changing cell.” This is necessary to study the impact on the “set cell.”
The “by changing cell” should not contain any special characters.

Frequently Asked Questions

Define the Goal Seek in Excel.

The Goal Seek in excel calculates the input value (variable) based on a given output. Being a what-if-analysis tool, the Goal Seek is used when there is a need to study the impact of a change in one value on the other.

The Goal Seek is used in scenario analysis, cause and effect situations, break-even model, sensitivity analysis, and so on. The parameters used in the Goal Seek excel tool are listed as follows:
• “Set cell”–This is the reference of the formula cell.
• “To value” –This is the desired or targeted value to be achieved.
• “By changing cell” –This is the cell reference of the variable to be changed for attaining the desired value.
The Goal Seek works with one input at a given time.

What is Goal Seek used for in Excel?

The Goal Seek in excel is used in the following situations:

• To calculate the input (unknown) for a given output (known)
• To analyze the impact of a change in the input (variable) on the output (set cell)
• To perform what-if-analysis, i.e., explore the various outcomes if different values are used for the input
• To return the closest value (approximate) in case a solution to the problem is not found

Note: The formula used in the Goal Seek analysis remains the same throughout. The input values entered in the “by changing cell” box are changed to study the effect on the output.

How does the Goal Seek work in Excel?

The Goal Seek does backward calculations. It begins from the output that is known and returns the exact input. This input is a requisite for reaching the given output.

The steps showing the working of Goal Seek are listed as follows:

1. Enter data in Excel on which calculations using Goal Seek are to be performed.
2. Click “Goal Seek” from the “what-if-analysis” drop-down in the Data tab.
3. Enter the parameters in the tool.
a. Type the cell reference for the input and the output.
b. Type the target value to be achieved.
4. Click “Ok” to proceed.
5. In the “Goal Seek status” box, click “Ok” to apply the changes to the worksheet.

Note: The initial values can be restored by pressing the “undo” shortcut (Ctrl+Z).

19th/20th September 2020	Saturday - Sunday	9 am IST to 5 pm IST
26th/27th September 2020	Saturday - Sunday	9 am IST to 5 pm IST

Goal Seek in Excel