What-If Analysis Tools in Excel

What-If Analysis tools help you explore different scenarios and outcomes. Excel offers three powerful tools: Scenario Manager, Goal Seek, and Data Tables for comprehensive scenario planning.

Scenario Manager

Creating Scenarios

Use Case: Budget planning with 3 scenarios

Setup:
Cell B1: Revenue
Cell B2: Costs
Cell B3: =B1-B2  // Profit

Create Scenarios:
1. Data → What-If Analysis → Scenario Manager
2. Add: "Best Case"
   Changing cells: B1, B2
   Values: Revenue=500000, Costs=300000
3. Add: "Likely Case"
   Values: Revenue=400000, Costs=320000
4. Add: "Worst Case"
   Values: Revenue=350000, Costs=340000

Switch scenarios with one click to see different outcomes!

Scenario Summary Report

Scenario Manager → Summary

Creates table comparing all scenarios:
                Best     Likely    Worst
Revenue         500000   400000   350000
Costs           300000   320000   340000
Profit          200000    80000    10000

Perfect for presentations!

Goal Seek

Reverse Engineering Results

Question: "What sales do I need for $100k profit?"

Setup:
Cell B1: Sales (unknown)
Cell B2: Cost Ratio = 60%
Cell B3: Fixed Costs = 20000
Cell B4: =B1*(1-B2)-B3  // Profit

Goal Seek:
Set cell: B4 (profit)
To value: 100000
By changing: B1 (sales)

Result: Need $300,000 in sales

Data Tables

One-Variable Analysis

Show profit at different price points:

Setup:
Base price: $50
Profit formula in B5

Column A: Test prices (40, 45, 50, 55, 60)
Cell B4: =B5 (reference profit formula)

Data Table:
Select A4:B9
Data → What-If Analysis → Data Table
Column input: B1 (price cell)

Shows profit for each price automatically

Two-Variable Analysis

Test price AND volume combinations:

Rows: Different volumes (1000, 1500, 2000)
Columns: Different prices ($40, $50, $60)

Shows profit matrix for all combinations

Practical Example: Investment Analysis

Using All Three Tools

Investment Decision Model:

Inputs:
- Initial Investment: $100,000
- Annual Return: 8%
- Years: 20
- Annual Contribution: $5,000

Scenario Manager:
- Aggressive: 10% return, $10k/year
- Moderate: 8% return, $5k/year
- Conservative: 5% return, $2.5k/year

Goal Seek:
"What annual contribution needed for $1M target?"

Data Table:
Matrix of returns (5-12%) vs years (10-30)
See future value for all combinations

Comparing the Tools

Tool	Best For	Variables
Scenario Manager	Named scenarios, multiple inputs	Many
Goal Seek	Reverse engineering, targets	1 input
Data Tables	Sensitivity analysis, ranges	1-2 inputs

Real-World Applications

Sales Forecasting

Scenarios:
- Optimistic: +20% growth
- Expected: +10% growth
- Pessimistic: +5% growth

Data Table: Growth rate vs marketing spend
Goal Seek: Marketing needed for specific target

Loan Analysis

Scenario Manager: Different down payments
Data Table: Interest rates vs loan terms
Goal Seek: Down payment for target monthly payment

Break-Even Analysis

Scenarios: Different cost structures
Data Table: Price vs volume to profitability
Goal Seek: Units needed to break even

Best Practices

• Use Scenario Manager for pre-defined scenarios you'll revisit
• Use Goal Seek for quick "what input gives this output?" questions
• Use Data Tables for comprehensive sensitivity analysis
• Document assumptions clearly for each scenario
• Create summary reports for stakeholder presentations

Accessing What-If Analysis

Data tab → Forecast group → What-If Analysis

Three options:
- Scenario Manager
- Goal Seek
- Data Table

Keyboard shortcut: Alt + A + W