WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Goal Seek in Excel

Goal Seek in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 is needed to achieve the desired result.

Goal Seek in Excel

Examples of Goal Seek in Excel

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

Example #1

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

Goal Seek Excel Example 1

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

Goal Seek Excel Example 1-1

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

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.

Goal Seek Excel Example 1-2

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

Goal Seek Excel Example 1-3

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.

Goal Seek Excel Example 1-4

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Goal Seek Excel Example 1-5

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.

Goal Seek Excel Example 1-6

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

Goal Seek Excel Example 1-7

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.

Goal Seek Excel Example 2

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

Goal Seek Excel Example 2-1

Step 2: Now open the Goal Seek window.

Goal Seek Excel Example 2-2

Step 3: The set cell is the Revenue cell, so set the cell as B3.

Goal Seek Excel Example 2-3

Step 4: Next To Value should be 2.5 Lakh.

Goal Seek Excel Example 2-4

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

Goal Seek Excel Example 2-5

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

Goal Seek Excel Example 2-6

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.

Example 3

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.

Step 1: Open Goal Seek first.

Example 3-1

Step 2: Set Cell will be Average Cell, so select B7 cell.

Example 3-2

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

Example 3-3

Step 4: 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.

Example 3-4

Step 5: Click on Ok to see the impact.

Example 3-5

So, in order to get an overall average of 90, students have 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 a downloadable template. You may learn more about excel from the following articles –

  • Evaluate Formula in Excel
  • Create a Data Table in Excel
  • One Variable Excel Data Table
  • Excel Scenario Manager
  • Excel Shortcut Paste Values
7 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Goal Seek Excel Template

Coursera IPO Financial Model & Valuation Free Download