3 “What-If” Analysis Techniques to Forecast and Model Scenarios in Excel

 

What-if analysis is a powerful approach to exploring uncertainty and testing different scenarios before making important decisions. It helps achieve business or research outcomes, such as “What happens if prices go up?” or “How much profit will we make if costs drop?” Excel’s What-If Analysis tools help you explore different possibilities without rewriting your formulas.

In this tutorial, we show three What-If analysis techniques to forecast and model scenarios in Excel.

We’ll explore three essential What-If analysis techniques:

• Goal Seek: Work backward to find the input needed for a specific result
• Data Tables: Analyze how one or two variables impact your outcome
• Scenario Manager: Save and compare multiple sets of assumptions

1. Goal Seek: Find the Input for a Desired Result

Goal Seek is ideal for reverse-engineering models. It adjusts a single input value to achieve a specific output, helping answer questions like “What input value do I need to achieve my desired result?”

Goal Seek uses iteration to change one cell (the input) until a formula-dependent cell (the output) reaches your desired value. It’s great for simple optimizations but is limited to one variable.

When to Use Goal Seek:

• Determine the sales volume needed to reach a profit target
• Calculate the interest rate required to meet loan payment constraints
• Find the break-even point for a product or service
• Identify the score needed on a final exam to achieve a certain grade

Let’s find out: “How many units must we sell to earn $20,000 profit?”

Steps:

• Set up your model in Excel (for example, enter the product details and profit formula)
• Insert the following formula to calculate profit

=(A2*B2)-(C2+(D2*B2))

• Go to the Data tab >> select What-If Analysis >> select Goal Seek

• In the dialog box:
• Set cell: Select the output cell (for example, E2, the profit formula)
• To value: Enter the target output (for example, target profit 20000)
• By changing cell: Select the input cell to adjust (for example, B2, Units Sold)
• Click OK

• Excel iterates automatically to adjust the sales value until the target profit is met

Insight: Use Goal Seek when your formula works backward from a known target (like break-even or target sales).

• Pros: Quick and straightforward for single-variable problems
• Cons: Doesn’t handle multiple variables; may not converge if the model is complex

2. Data Table: See How One or Two Variables Affect a Result

Data Tables let you see how changing one or two input variables affects your results, displaying all possibilities in a single table. This creates a sensitivity analysis that shows the range of potential outcomes.

• One-variable table: Varies one input across a row or column; tests different values for a single input
• Two-variable table: Varies two inputs (one row, one column) to create a matrix; recalculates the model for each combination and helps visualize trends

One-Variable Data Table

Let’s see how different unit prices affect profit. How does profit change if the Unit Price increases from $40 to $70?

Steps:

• Set up your model
• Enter these prices vertically in one column
• In the cell adjacent to the first price (for example, B5), reference the main profit result cell (for example, E2)

=E2

• Select the full table range, including the header (for example, A5:B12)
• Go to the Data tab >> select What-If Analysis >> select Data Table

• Leave Row input cell blank
• In the Column input cell, select the original Unit Price cell (A2)
• Click OK

• Excel fills in the profit for each price automatically

Interpretation:

• The resulting column shows profit sensitivity to price
• Higher prices usually increase profit, up to the point demand drops (if modeled)

Two-Variable Data Table

Let’s see: “What if both Unit Price and Units Sold vary?”

Steps:

• Enter prices vertically (left column) and units sold horizontally (top row)
• In the top-left cell of the table (above 40 and left of 800), reference the main profit result cell (for example, enter =E2 in D5)

=E2

• Select the entire table, including headers (for example, D5:H9)
• Go to the Data tab >> select What-If Analysis >> select Data Table
• In the Row input cell, select Units Sold (B2)
• In the Column input cell, select Unit Price (A2)
• Click OK

• Excel creates a full matrix showing profit under every price–sales combination

Note: For a Data Table, the input cells must be on the same sheet as the Data Table. The formula used to fill the table can be on another sheet by reference.

Insight: Data Tables are best for quick sensitivity testing, like price elasticity or volume-profit analysis.

• Pros: Visualizes ranges; handles up to two variables efficiently
• Cons: Can be computationally intensive for large tables; results are static until refreshed

3. Scenario Manager: Compare Multiple “What-If” Models

Scenario Manager lets you save and compare different sets of input values (scenarios) without overwriting formulas. You can quickly switch between them or create summary reports comparing all scenarios side-by-side. It is suited for complex scenarios with several variables, like best/worst-case forecasts.

When to Use It:

• Compare best-case, worst-case, and most-likely business projections
• Evaluate different strategic options with multiple changing assumptions
• Present multiple scenarios to stakeholders in a clear, organized format
• Track various versions of a model over time

Let’s create a business projection with three scenarios: Best Case, Base Case, and Worst Case.

Steps:

• Go to the Data tab >> select What-If Analysis >> select Scenario Manager

• Click Add
• Scenario name: Name the first scenario (for example, Best Case)
• In Changing cells, select Unit Price (A2), Units Sold (B2), Fixed Cost (C2), and Variable Cost (D2)
• Click OK

• Enter the values for the Best Case scenario
• Unit Price: 65
• Units Sold: 1600
• Fixed Cost: 8500
• Variable Cost: 25
• Click OK

• Click Add again and repeat for “Base Case”

• Click Add again and repeat for “Worst Case”

• Select any scenario >> click Show to apply values instantly
• Click Summary to show all cases

• Select Scenario summary
• Choose the profit cell as the “Result cell” (for example, E2)
• Click OK

• Excel creates a new Scenario Summary sheet comparing all scenario outcomes

Insight: You can use Scenario Manager What-If analysis to forecast and model scenarios in Excel. It’s one of the best decision-making tools (for example, investment, budgeting, or risk assessment) where multiple assumptions must be saved and reviewed together.

• Pros: Manages multiple variables; easy comparison with reports
• Cons: Manual setup; limited to 32 changing cells per scenario

Download Practice Workbook

Conclusion

This tutorial shows how to use three What-If analysis techniques to forecast and model scenarios in Excel. What-If analysis turns uncertainty from a barrier into an opportunity by exploring different possibilities with Goal Seek, Data Tables, and Scenario Manager. Whether you’re planning a business budget, evaluating investment options, or modeling project outcomes, these Excel tools help you understand how changes in your assumptions affect your results.