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

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. It’s 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 only one input value at 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

Let’s 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. Let’s find how much should be Sarah’s sales to achieve $500K as a goal for total sales.

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

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

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

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

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

Conclusion

Goal Seek is Excel’s 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 specific result, 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.

‍