Goal Seek is a powerful feature in Excel that is part of the What-if Analysis tools. It allows users to find the necessary input values to achieve a desired goal by adjusting a single input value automatically. This tool is particularly useful for solving problems involving financial, engineering, and logistical scenarios.


How Goal Seek Works

Goal Seek works by varying one specific input until a formula dependent on that input produces a desired result. It essentially performs a reverse calculation to determine the necessary input value that results in a particular output.


Steps to Use Goal Seek

Here's a detailed guide on how to use Goal Seek in Excel:

Step 1: Prepare Your Spreadsheet

First, set up your Excel worksheet with all necessary formulas and values. For instance, let's consider a scenario where you need to determine the selling price of a product to achieve a specific profit margin.

  • Cell A1: Cost Price ($100)
  • Cell A2: Selling Price (this is the cell we will change)
  • Cell A3: Desired Profit ($50)
  • Cell A4 (formula cell): =A2 - A1 (calculates profit)
Step 2: Access Goal Seek
  1. Click on the Data tab on the Ribbon.
  2. In the Forecast group, click on What-If Analysis.
  3. Choose Goal Seek from the list.
Step 3: Configure Goal Seek

The Goal Seek dialog box will ask for three pieces of information:

  • Set cell: This should be the formula cell where the result is calculated (A4).
  • To value: Enter the target value for the formula, which is the desired profit (50).
  • By changing cell: Specify the cell that Excel can change to achieve the goal (A2).
Step 4: Execute Goal Seek
  1. After filling in the necessary fields in the Goal Seek dialog box, click OK.
  2. Excel will automatically adjust the Selling Price in cell A2 until the profit in cell A4 reaches the desired $50.
  3. If successful, a dialog box will confirm that the goal has been achieved, showing the required selling price. Click OK to accept the solution.
Example in Detail

Let's say you manufacture an item for $100 and want to know at what price you need to sell it to make a $50 profit on each sale. By using Goal Seek, you input:

  • Set cell: A4 (which contains the formula =A2 - A1)
  • To value: 50 (the desired profit)
  • By changing cell: A2 (the selling price you need to determine)

Goal Seek will then calculate and adjust the selling price so that the profit is exactly $50, considering the cost price.


Key Points to Consider
  • Single Variable: Goal Seek can only manipulate one input value at a time. If your scenario involves multiple variables affecting the result, consider using the Solver feature, which can handle more complex scenarios.
  • Direct Dependency: The target cell must directly depend on the cell you want to adjust. If there's no direct dependency, Goal Seek won't work.
  • Feasibility: Ensure that the goal you set is achievable based on your formulas and input ranges. Goal Seek cannot find a solution if it's mathematically impossible.

Conclusion

Goal Seek is an excellent tool for anyone who needs to find specific values that meet a predefined outcome in their financial models or other calculations. It is intuitive and can significantly aid in decision-making processes by providing quick solutions to otherwise complex calculations.