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.

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.

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.

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 price*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 “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.