How to Use Goal Seek in Excel to Reverse-Engineer Formula Results
Goal Seek is a feature in Microsoft Excel that allows you to find the input value that will produce the desired output value in a formula.
In this step-by-step Excel tutorial, you’ll learn how to use Goal Seek to solve for unknowns in financial models, budgets, and more.
Data preparation
Goal Seek requires you to have a formula in a cell and a desired output value. For example, you might have a formula that calculates the total cost of a purchase, and you want to know how many items you can buy with a budget.
For example, if each item costs $230 and your budget is $1,000,000, how many units can you afford? Select the cell containing the formula that calculates your desired outcome; this is the Set cell in Goal Seek. In our example, this would be the cell containing the total cost calculation, which should ultimately equal your budget.
What-if Analysis
To use Goal Seek, go to the Data tab and click the What-If Analysis button. Then, select Goal Seek from the list.
The Goal Seek dialog box will open. In the Set cell field, select the cell that contains the formula. In the To value field, enter the desired output value. In the By changing cell field, select the cell that contains the input value that you want to change.
Click OK and Excel will automatically adjust the input cell to reach the target result. Excel will adjust the input value in the By changing cell field until the output value in the Set cell field matches the desired value.
The Goal Seek process will stop when the output value in the Set cell field matches the desired value. The input value in the By changing cell field will be the value that you need to change to achieve the desired output value.
As you can see, Goal Seek in Excel offers a fast and intuitive way to calculate backwards from a result. Practice Goal Seek with more advanced examples like loan payments, breakeven analysis, or pricing scenarios.
Keep in mind that the input value that Goal Seek finds is only an estimate, so you may need to perform the process multiple times to find an accurate result.
Goal Seek is most effective when there is a single input value that directly affects the output formula. It may not provide accurate results for complex scenarios with multiple interdependent variables or when the relationship between the input and output is not linear. In such cases, other What-If Analysis tools like Solver might be more appropriate.
Leave a Reply