What-If Analysis in Excel: Goal Seek and Scenario Manager

What-If Analysis in Excel is a powerful feature that allows you to explore different outcomes by changing inputs in your formulas. The most commonly used tools within What-If Analysis are Goal Seek and Scenario Manager.

---

1. Goal Seek

Use Case: Find the required input to achieve a specific goal in your formula.

Example:

You are calculating profit with the formula: Profit = Revenue – Cost
Suppose your cost is $10,000, and you want a profit of $5,000. You need to find the revenue required to achieve this profit.

Steps:

1. Set up the formula in a cell:
   • For example, in C2, enter =B2 - A2 where:
     • B2 is Revenue
     • A2 is Cost
   • Enter 10,000 in A2 (Cost).
   • Leave B2 (Revenue) empty for now.
2. Go to Data > What-If Analysis > Goal Seek.
3. In the Goal Seek dialog box:
   • Set cell: Select the cell with your formula (e.g., C2).
   • To value: Enter the goal value (e.g., 5000 for profit).
   • By changing cell: Select the cell with the input to adjust (e.g., B2 for Revenue).
4. Click OK.

Result: Excel calculates the Revenue required to achieve a profit of $5,000 (e.g., $15,000).

---

2. Scenario Manager

Use Case: Analyze multiple scenarios by changing inputs and comparing outcomes.

Example:

You are projecting sales for three pricing strategies and want to see how revenue changes.

Price per Unit	Units Sold	Revenue
20	1000	=B2*A2

Steps:

1. Set up your formula in Excel:
   • For example, in C2, enter =A2 * B2 (Revenue = Price × Units Sold).
2. Go to Data > What-If Analysis > Scenario Manager.
3. In the Scenario Manager dialog box:
   • Click Add to create a new scenario.
   • Name the scenario (e.g., “Low Price”).
   • Define the Changing Cells (e.g., A2 and B2 for Price and Units Sold).
   • Enter the values for this scenario (e.g., Price = 20, Units Sold = 1000).
4. Add additional scenarios:
   • “Medium Price”: Price = 30, Units Sold = 800.
   • “High Price”: Price = 40, Units Sold = 600.
5. Click Summary to generate a comparison table for all scenarios.

Result: A summary table showing the Revenue for different pricing strategies.

---

Key Differences

Feature	Goal Seek	Scenario Manager
Purpose	Find a single input to achieve a goal.	Analyze multiple sets of inputs at once.
Output	Updates the input cell directly.	Creates a summary of different scenarios.
Use Case	Backward calculation.	Comparing “what-if” situations.

---