What is Goal Seek in Excel?
In this blog, we discuss the following:
What is Goal Seek? Why Use It?
Steps to Use Goal Seek in Excel
What is Goal Seek? Why Use It?
Excel has an endless amount of built-in components that can be essential tools for planning and strategizing. The feature we are going to examine is called the Goal Seek. Goal Seek is a Microsoft Excel add-in program you can use for what-if analysis.
Goal Seek in Excel can be used for any situation. However, the situations for goal seek is that it can only change only one variable at a time. In another blog, we go over another what-if analysis called the Solver, that feature can handle more than two variables being changed in order to reach the goal value. Here are a few scenarios and questions about where we can find ourselves finding these tools useful. Notice that the one variable in each situation is bolded below.
How many cookies do I need to sell in order to breakeven?
How many more customers do I need in order to reach my goal?
How many hours do I need to work this week to make x dollars?
Here's a video where you can follow along and forecast net income using Goal Seek in Excel.
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 in Excel:
Put the cursor on the cell that contains the output you want to change (Revenue)
On the Data ribbon, select What-if-analysis, then select Goal Seek (keyboard shortcut is Alt, A, W, G)
When the dialog box appears (as shown below) make “Set cell” equal to the revenue cell (E10)
Set “To value” equal to the output you want to achieve (type the number in)
Set “By changing cell” equal to the assumption you want to solve for (# of units, or cell E4)
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).
