When might you use the what if analysis and Goal Seek in Excel?

What-If Analysis: How to Use Goal Seek in ExcelKeyskillsetMay 20, 2020What is Goal Seek?Goal Seek is a built-in Excel tool that allows you to see how one data item in a formula imp

When might you use the what if analysis and Goal Seek in Excel?

What-If Analysis: How to Use Goal Seek in Excel

Keyskillset

Keyskillset

  • May 20, 2020
goal seek

What is Goal Seek?

Goal Seek is a built-in Excel tool that allows you to see how one data item in a formula impacts another. You might look at these as cause and effect scenarios. Its useful to answer what if type questions because you can adjust one cell entry to see the result.

The Goal Seek tool is especially useful for doing sensitivity analysis in financial modeling. Moreover, it is widely used in finance, sales, and forecasting scenarios. But there are other uses as well.

How does Goal Seek work?

Excel Goal Seek performs all calculations behind the scenes, and you are only asked to specify these three parameters:

  1. Formula cell
  2. Target/desired value
  3. The cell to change in order to achieve the target

This function can process onlyone input valueat a time. To find the best solution, use the Solver add-in. Especially, If you are working on an advanced financial model with multiple input values.

Example in Excel

Lets say we have a list of Sales Representatives with the number of sales to achieve in a given period of time. If everyone does the same amount of sales ($100K), then total sales would be $400K. We want to hit a goal of $500K for total sales instead of $400K. In addition, we know that Sarah is a more experienced sales representative. So, she can do more sales than others. Lets find how much should be Sarahs sales to achieve $500K as a goal for total sales.

goal seek

To open Goal Seek Excel function on the Ribbon, use shortcut Alt A W G.

goal seek

In the opened Goal Seek window , the set cell would be B6, and the desired value for the set cell would be 500.

goal seek

Next, we need to pick the cell wed like to change. In our case its B5 as we know Sarah can do more sales than other sales representatives.

goal seek

When the Goal Seek window is filled, click Enter for the result to appear on your spreadsheet.

goal seek

In our example, you can see that Sarah has to do $200K in sales, so we can hit $500K in sales total.

goal seek

Conclusion

Goal Seekis Excels built-in What-If Analysis tool that shows how one value in a formula impacts another. More precisely, it determines what value you should enter in an input cell to get the desired result in a formula cell.

Overall, whenever you want a formula to return a specificresult, stop guessing and use the Excel Goal Seek function. It is the best option, even when you are not sure what input value within the formula to adjust to get that result. Learn more Excel functions and speed up your Excel workflow and Financial Modeling skills with our educational games keySkillset.

Free Educational Games

MS Excel | PowerPoint | Financial Modeling
Email: Password:

Let's start

or

Start with FacebookForgot your password? Click here.Share on facebookShare on twitterShare on linkedin

More from our blog

Basic Python Keywords You Should Be Aware Of

Basic Python Keywords You Should Be Aware OfPosted by KeyskillsetFebruary 2, 2021

Top Interview Questions about Python Answered

Top Interview Questions about Python AnsweredPosted by KeyskillsetFebruary 2, 2021

Top 5 Cool Project Ideas for Python Developers

Top 5 Cool Project Ideas for Python DevelopersPosted by KeyskillsetFebruary 2, 2021

Video liên quan