How to use Goal Seek in Excel to do What-If analysis

This tutorial demonstrates how to use Excel's Goal Seek feature (available in Excel 365 and versions from 2010 onwards) to achieve a desired formula result by adjusting an input value. What-If Analysis, a powerful yet often underutilized Excel tool, allows testing various scenarios and predicting outcomes without altering the original data. Goal Seek is a key component of this analysis.

• Understanding Excel's Goal Seek
• Using Goal Seek in Excel
• Practical Goal Seek Examples
• Troubleshooting Goal Seek Issues

What is Excel Goal Seek?

Goal Seek is a built-in What-If Analysis tool that reveals how a formula's input value affects its output. It identifies the input cell value needed to produce a specific formula result. You simply specify three parameters: the formula cell, the target value, and the cell to be adjusted. Goal Seek handles the calculations automatically. It's particularly useful for sensitivity analysis in financial modeling and various other applications.

For example, Goal Seek can determine:

• Required sales to reach a profit target.
• Necessary exam score for a passing grade.
• Vote count needed to win an election.

Goal Seek processes only one input value at a time. For models with multiple inputs, consider using the Solver add-in.

How to Use Goal Seek in Excel

Let's illustrate Goal Seek with a simple example:

This table shows the profit from selling 100 items at $5 each, after a 10% commission. The goal is to determine how many items must be sold to reach a profit of $1000.

Follow these steps:

1. Prepare your data: Ensure you have a formula cell and a changeable input cell.
2. Access Goal Seek: Navigate to the Data tab > What-If Analysis group, and select Goal Seek.
3. Define parameters: In the Goal Seek dialog box:
   • Set cell: The cell containing the formula (e.g., B5).
   • To value: The desired result (e.g., 1000).
   • By changing cell: The input cell to adjust (e.g., B3).
4. Review results: The Goal Seek Status box indicates success or failure. If successful, the input cell will be updated. Click OK to keep the new value or Cancel to revert. In our example, approximately 223 items need to be sold.

You can repeat this process, changing the By changing cell to explore different scenarios (e.g., adjusting the item price instead of the quantity).

This shows that a $11 price point achieves $1000 profit with 100 items.

Key Points:

• Goal Seek modifies only the input value.
• If no exact solution is found, the closest approximation is provided.
• The original value can be restored using Undo.

Examples of Using Goal Seek

Here are further examples:

Example 1: Profit Goal Determine the required fourth-quarter revenue to reach a $100,000 annual net profit.

Example 2: Exam Passing Score Calculate the minimum score needed on the third exam to achieve a 70% overall grade.

Example 3: Election What-If Analysis Determine the number of votes needed to win an election requiring a two-thirds majority.

Troubleshooting Goal Seek

If Goal Seek fails to find a solution, it might not exist. However, check these points:

1. Verify parameters: Ensure correct cell references and dependencies.
2. Adjust iteration settings: Modify Maximum Iterations and Maximum Change under File > Options > Formulas.
3. Eliminate circular references: Ensure formulas are not co-dependent.

Goal Seek is a valuable tool for What-If analysis in Excel. Mastering it enhances your spreadsheet capabilities significantly.

The above is the detailed content of How to use Goal Seek in Excel to do What-If analysis. For more information, please follow other related articles on the PHP Chinese website!