Goal Seek in Excel

Goal Seek in Excel (Table of Contents)

• What is Goal Seek?
• Examples

What is Goal Seek Analysis in Excel?

Goal Seek is the tool of What-If-Analysis which performs the task of identifying the variable number required to achieve the targeted number. For example, if the cost price is 15 and you are expecting the revenue of 50,000, then using excel Goal Seek we can identify how many units we need to sell to achieve the target of 50,000.

So, the goal seeks to show how one data can impact another set of data. So goal seek determines what value needed to achieve the desired result.

Examples of Goal Seek in Excel

Let us discuss some practical examples of goal seek analysis in excel.

Example #1

In an election, two candidates contested, in a survey from one of the TV, channel results are as follows.

Now arrive % Votes by dividing the individual vote by total votes.

Now Candidate A knows he is close to winning the election but short by 3.67%. Now he wants to analyze how many voters he needs to attract to win the election with the vote percentage of 50.01%.

Now we will apply excel GOAL SEEK Analysis to help out Candidate A here. Follow the below steps to apply the excel goal seek to this problem of Candidate A.

Step 1: Go to DATA tab What-If-Analysis and Goal Seek.

Step 2: Now we can see the below Goal Seek window.

In this window, we can see three boxes.

Set cell, To Value, and By Changing Cell.

Set Cell: This is the targeted cell. Which cell did we need to change the value?

To Value: The Set Cell value should be what??

By Changing Cell: By changing which cell we need to impact the Set Cell.

Step 3: In this example, we need to set the cell as C2.

Step 4: Next is To Value, this is the Set Cell value should be what. In this case, it should change to 50.01%.

Step 5: Next by changing which cell we need to impact the cell C2. In this example, we need to know how many votes Candidate A needs to secure to get the vote % of 50.01%. So By Changing Cell is B2 cell.

Step 6: Now all the parameters are set, click on OK to get the result.

As we can in the above image Candidate A should secure 70014 votes to get the vote % share of 50.01%.

Click on Ok to apply the changes.

Example #2

Mr. A opened a retail shop recently. In order to meet his monthly break-even, he wants to generate a revenue of 2.5 Lakh per month. On an average selling price, he will sell goods at 25 per unit. Now he doesn’t know how many quantities he needs to sell to generate a revenue of 2.5 Lakh.

In the cell, B3 apply the formula as Unit Price * Units.

Now open Goal Seek window.

The set cell is the Revenue cell, so set the cell as B3.

Next To Value should be 2.5 Lakh.

By Changing Cell is Units cell, so select the cell B2.

Click on Ok to get the required units to get the revenue of 2.5 Lakh at 25 per unit price.

So, Mr. A required to sell 10000 units in the month to generate a revenue of 2.5 Lakhs.

Example #3

A student appeared in 5 subject examinations and left with 1 more subject. As an estimation, he has secured below marks in each subject and his average grade is 88.

Since he has to write one more exam he is targeting the overall average of 90 from all the 6 subjects. Now that the student wants to know how much he needs to score in the remaining 1 paper to secure an overall average of 90.

Open Goal Seek first.

Set Cell will be Average Cell, so select B7 cell.

Next Set cell value should be 90, so enter the value as 90 for To Value box.

The final box is by changing which cell we need to see the impact. In this example, we need to change the cell B6 i.e. last subject paper.

Click on Ok to see the impact.

So, in order to get an overall average of 90 students has to score 98 in the final subject.

Like this, we can use Goal Seek analysis in excel to identify what needs to be done to get the desired or targeted result.

Things to Remember

• The set cell must contain a formula.
• Changing cells should be part of the formula to see the impact.
• Changing the cell should be empty and should not contain any special characters.

Recommended Articles

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

• Evaluate Formula in Excel | Using EVALUATE Tool
• Make Dot Plots in Excel
• How to do Sensitivity Analysis in Excel?
• Using Data Table in Excel
• One Variable Excel Data Table
• Excel Scenario Manager