What is Goal Seek in Excel?

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:

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 you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Goal Seek in Excel (wallstreetmojo.com)

How to use Goal Seek in Excel?

Example #1

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.

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.

  • 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.

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.

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 PriceUnit 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 moreunits (B1B2).

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

This has been a guide to Goal Seek in Excel. Here we learn how to use Goal Seek function in Excel along with step by step examples and a downloadable template. You may learn more about Excel from the following articles –

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.

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).

  • Evaluate Formula in ExcelEvaluate Formula In ExcelThe Evaluate formula in Excel is used to analyze and understand any fundamental Excel formula such as SUM, COUNT, COUNTA, and AVERAGE. For this, you may use the F9 key to break down the formula and evaluate it step by step, or you may use the “Evaluate” tool.read moreCreate a Data Table in ExcelCreate A Data Table In ExcelA data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. It can be found under the data tab in the what-if analysis section.read moreOne Variable Excel Data TableOne Variable Excel Data TableOne variable data table in excel means changing one variable with multiple options and getting the results for multiple scenarios. The data inputs in one variable data table are either in a single column or across a row.read moreExcel Scenario ManagerExcel Scenario ManagerScenario Manager is a what-if analysis tool that works with many scenarios that are supplied to it. It uses a set of ranges that have an effect on a certain output and can be used to generate different scenarios such as bad and medium depending on the values.read moreExcel Shortcut Paste ValuesExcel Shortcut Paste ValuesPasting values is a common procedure that allows us to eliminate any formatting and formulas from the copied cell and paste them into the pasted cell. “Alt + E + S + V” is the shortcut key for pasting values.read more