Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

Goal Seek

Back-solve a solution with this Excel function

What is the Goal Seek Excel function?

The Goal Seek 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.

 

Goal Seek Excel Function Cover Image

 

Simple Goal Seek Example

Let’s look at a simple exercise first, to see how it 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.

 

Goal Seek Excel example and steps

 

Steps to Use Goal Seek:

  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 the 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).

 

Goal Seek Excel - Solution from Example

 

A Video Example of the Goal Seek Excel Function

Here is a more advanced video demonstration of how to use the Goal Seek Excel function. This video clip is an excerpt from CFI’s Free Excel Crash Course and recorded by Tim Vipond, CEO at the Corporate Finance Institute®.

 

 

Video Transcript

Suppose you want to find the NPV equal to 500 million based on a certain discount rate. In order to do that, what I’m going to do is use the GOAL SEEK function. I’m going to press Alt, A, W, and G for GOAL SEEK. What I want to do is set this value, so the value of the NPV equal to 500 million by changing the discount rate and press OK. I can see here that the discount rate is 25 percent to get to a 500-million NPV. I’m going to press Cancel and type that answer in this cell here. Now what I’ve done is I’ve just essentially taken a note of what the discount rate has to be to get a $500 million NPV.

All right, let’s do another example. suppose that I want 2020 net income to be $10 million, and I want to get there by changing the 2020 revenue. How much revenue do we need in 2020 to get $10 million of net income? I’m going to press Alt, A, W, and G for GOAL SEEK, so what I want to do is change this number here, this net income in 2020 to be $10 million by changing this target case revenue as the one that’s currently running in our model, so I need to change that cell, and I see that the target revenue is $122.7 million. Press Cancel and type here 122700, so that’s the revenue that’s required to get there.

This is how I can summarize an important information down here using GOAL SEEK.

Just a little bit of housekeeping here, I’m going to put a border Alt, H, B, P to put a border across the top there, and here I’m going to say goal seek analysis, in brackets What-if, and I’m just going to put a border over this section here, Alt HBP, and I can select these cells here, press Shift + Spacebar and Alt, D,G, G to group those. Now everything rolls back up to a nicely organized spreadsheet.

 

Additional Resources

CFI is the official global provider of the Financial Modeling and Valuation Analyst (FMVA)™ designation, a leading financial analyst certification program. To continue learning and advancing your career, these additional resources will be helpful:

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

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!