Goal Seek

What is the Goal Seek Excel Function?

The Goal Seek[1] Excel function (often referred to as What-if-Analysis) is a method of solving for a desired output by changing an assumption that drives it.  The function essentially uses a trial and error approach to back-solving the problem by plugging in guesses until it arrives at the answer.

For example, if the formula for revenue is equal to the number of units sold multiplied by the selling price, Goal Seek can determine how many units have to be sold to reach $1 million of revenue, if the selling price is known. The function is extremely useful for performing sensitivity analysis in financial modeling.

Simple Goal Seek Example

Let’s look at a simple exercise first, to see how Goal Seek works.  Suppose we have a very basic model that takes the number of units sold, the retail price, and a discount to calculate total net revenue.

The current  model contains the following information:

• # of units: 500
• Retail price: $25.00
• Selling discount: 10%
• Revenue: $11,250

Now suppose we want to find out how many units have to be sold to reach $20,000 of revenue.  See the screenshot below and follow the steps listed to use the Goal Seek Excel tool.

Steps to Use Goal Seek Excel:

1. Put the cursor on the cell that contains the output you want to change (revenue)
2. On the Data ribbon, select What-if-analysis, then select Goal Seek (keyboard shortcut is Alt, A, W, G)
3. When the dialog box appears (as shown below) make “Set cell” equal to the revenue cell (E10)
4. Set “To value” equal to the output you want to achieve (type the number in)
5. Set “By changing cell” equal to the assumption you want to solve for (# of units, or cell E4)
6. Press OK

Below is the output from the analysis.  We can see that to achieve $20,000 of revenue, 889 units need to be sold. Notice that the solution is displayed directly in cell (E4), not in the dialogue box.

Press OK to keep the solution running in the model (cell E4 will permanently change to 889 units), or press Cancel to return to the original assumption (500 units).

Additional Resources

Thank you for reading CFI’s explanation of the Goal Seek Excel function. To continue learning and advancing your career, these additional CFI resources will be helpful:

• List of 300+ Excel Functions
• Keyboard Shortcuts
• What is Financial Modeling?
• Financial Modeling Courses

See all Excel resources