What-If Analysis in Excel: Scenario Manager, Goal Seek, and Data Tables for Business Planning

Published: July 30, 2026
by | Microsoft Excel

article poster

You have built a solid financial model. Your profit projection looks healthy, your costs are under control, and your revenue forecast seems realistic.

But here is the question that keeps every business analyst, finance manager, and small business owner up at night: What happens if your assumptions change?

What if sales volume drops by 10%? What if material costs rise faster than expected? What if you need to hit a specific profit target, how many units must you sell to get there?

Instead of rebuilding your spreadsheet every time a variable changes, you need a structured way to test uncertainty. That is exactly where what-if analysis in Excel becomes your most practical business planning tool.

Excel provides three powerful features for this: Scenario Manager, Goal Seek, and Data Tables. Each tool answers a different type of business question.

By the end of this guide, you will know exactly how to use them for budgeting, forecasting, pricing, and break-even analysis.

Let us start with the foundation.

What is What-If Analysis in Excel?

At its simplest, what-if analysis in Excel is the process of changing the input values in your spreadsheet to see how those changes affect the final outcome. Instead of guessing or sticking to a single forecast, you explore multiple possibilities.

Think of it like this: Your business plan is built on a set of assumptions, sales growth, unit price, operating costs, headcount, or interest rates.

What-if analysis allows you to tweak those assumptions and instantly see a new result, such as projected profit, cash flow, or break-even point.

For example, imagine a small retail business projecting monthly profit based on three drivers:

  • Sales volume (units sold)
  • Unit price
  • Cost per unit

If you change sales volume from 1,000 to 1,500 units, what happens to profit? If you raise price by $5 but volume drops slightly, is that still profitable?

What-if analysis lets you answer these questions without creating separate files for every guess.

Excel provides three dedicated tools to perform this analysis: Excel Scenario Manager, Goal Seek in Excel, and Excel Data Tables.

Each serves a different purpose, and knowing which one to use will save you hours of manual work.

Why What-If Analysis Matters for Business Planning

Business plans are not built on certainties; they are built on assumptions. And assumptions carry risk. The difference between a solid plan and a fragile one is whether you have tested how your numbers behave under different conditions.

This is why business planning in Excel without what-if analysis is incomplete. Whether you are preparing an annual budget, building a three-year forecast, setting next quarter’s sales target, or calculating a break-even point, you need to ask:

  • Which assumptions have the biggest impact on my results?
  • How much could profit change if costs increase by 5%?
  • What sales volume do I actually need to avoid a loss?
  • Is my “best case” scenario realistic or overly optimistic?

What-if analysis does not promise perfect predictions. Instead, it helps you understand the range of possible outcomes before you make a decision. That understanding is what separates reactive planning from proactive strategy.

Now, let us walk through each Excel tool so you can start applying them to your own models.

How to Use Scenario Manager in Excel?

Excel Scenario Manager is your tool for comparing different sets of assumptions side by side.

Instead of saving multiple versions of the same spreadsheet, “Budget_v1.xlsx”, “Budget_v2.xlsx”, “Budget_optimistic.xlsx”, you store all scenarios inside one workbook.

Scenario Manager is ideal for business cases like:

  • Best case, base case, and worst case financial forecasts
  • Different hiring or investment plans
  • Varying revenue growth and cost inflation assumptions

How It Works

You identify the input cells that will change (for example, sales growth percentage, cost per unit, or marketing spend).

Then you create named scenarios, enter the values for each scenario, and let Excel store them.

Finally, you generate a summary report that compares all scenarios side by side.

Practical Business Example

Assume you are building a sales forecast for the next 12 months. Your profit formula depends on three inputs:

  • Revenue growth (5%, 10%, or 15%)
  • Cost inflation (2%, 4%, or 6%)
  • New hiring costs ($10,000, $20,000, or $30,000)

Using Scenario Manager, you create three scenarios:

  • Worst Case: 5% growth, 6% cost inflation, $30,000 hiring costs
  • Base Case: 10% growth, 4% inflation, $20,000 hiring costs
  • Best Case: 15% growth, 2% inflation, $10,000 hiring costs

Excel saves all three. With one click, you can switch between them or generate a comparison table showing how each scenario affects net profit. This is Excel scenario planning at its most practical, no copy-pasting, no broken formulas, no version control headaches.

Scenario Manager works beautifully when you have a few discrete cases to compare. But what if you already know the result you want, and you need to find the input that gets you there? That is when you reach for Goal Seek.

How to Use Goal Seek in Excel

While Scenario Manager compares different assumptions, Goal Seek in Excel works backward.

You tell Excel: “This is the target result I want. Which input value do I need to change to hit that target?”

Goal Seek is perfect when you have one formula, one target, and one variable input. It is not built for multiple variables, which requires Solver, but for everyday business planning, Goal Seek is fast and precise.

When to Use Goal Seek

  • Finding the break-even sales volume (where profit = zero)
  • Calculating the price required to hit a target margin
  • Determining the cost reduction needed to reach a profit goal
  • Identifying the exact conversion rate needed to meet a revenue target

Practical Business Example

Your projected profit for the quarter is $80,000, but your manager has set a target of $100,000. All other inputs are fixed except one: sales volume. You want to know exactly how many units you must sell to reach a $100,000 profit.

In Excel, you set up:

  • Set cell: The profit formula cell (currently $80,000)
  • To value: 100,000
  • By changing cell: The sales volume input cell

Click OK. Excel runs iterations behind the scenes and returns the required sales volume, for example, 12,500 units instead of 10,000. Now you have a specific, actionable target for your sales team.

That is the power of what-if analysis in Excel when you need to solve for a specific goal, not just explore possibilities.

Goal Seek answers “What input do I need to hit one target?” But business planning often requires a broader view.

What if you want to see how profit changes across 20 different price points or 50 different sales volumes? That is where Data Tables become essential.

How to Use Data Tables in Excel

Excel Data Tables allow you to test many input values at once and see how each one affects your output.

Instead of manually changing a cell 20 times and recording the results, Data Tables automate the entire process and display all outcomes in a single grid.

Data Tables are the go-to tool for Excel sensitivity analysis. You can build a one-variable table (changing one input) or a two-variable table (changing two inputs simultaneously).

One-Variable vs Two-Variable Data Tables

  • One-variable: Test how profit changes when price varies from $10 to $50 in increments of $5.
  • Two-variable: Test how profit changes across different price points (rows) and different sales volumes (columns).

Practical Business Example

You run a subscription service. Monthly profit depends heavily on unit price and the number of subscribers. You want to know which combinations of price and volume produce the best profit.

Build a two-variable Data Table:

  • Row input: Unit price ($10, $15, $20, $25, $30)
  • Column input: Monthly sales volume (500, 750, 1,000, 1,250, 1,500)
  • Output cell: Profit formula

In seconds, Excel generates a full grid of 25 profit outcomes. Scan the table, and you immediately see the optimal price-volume combination. No manual typing, no accidental formula errors, no wasted time.

Data Tables are especially powerful for Excel forecasting tools when you need to present a range of possible outcomes to stakeholders, rather than a single number.

Scenario Manager vs Goal Seek vs Data Tables

By now, you can see that each tool answers a different planning question.

Here is a simple comparison to help you choose the right tool for your next what-if analysis in an Excel project.

Tool Best Use Business Example
Scenario Manager Compare named sets of assumptions side by side Best case, base case, and worst case annual budget
Goal Seek Find the single input needed to hit a specific target Break-even sales volume or required price for target margin
Data Tables See how changing one or two inputs over many values affects the result Price sensitivity table across 20 different price points

Think of it this way:

  • Use Scenario Manager when you need to compare planning cases.
  • Use Goal Seek when you need to solve for a break-even point or specific target.
  • Use Data Tables when you need to test sensitivity across a range of values.

In many real-world planning exercises, you will combine them. For example, use Data Tables to test price sensitivity, then use Goal Seek to find the exact price that hits your profit target, and finally use Scenario Manager to present three planning cases to your leadership team.

Best Practices for What-If Analysis in Excel

Even the most sophisticated Excel sensitivity analysis will fail if your spreadsheet is poorly structured.

Follow these best practices to keep your models reliable, auditable, and easy to update.

1. Separate Inputs, Calculations, and Outputs

Never hardcode assumptions inside formulas. Keep all variable inputs, like sales growth, unit cost, tax rate, or headcount, in a dedicated, clearly labelled assumptions section or worksheet. Your calculations should refer only to those input cells.

Example of what NOT to do:

=B2*0.15 (where 0.15 is the tax rate hidden inside a formula)

Example of what TO do:

=B2*TaxRate where TaxRate is a named cell or clearly labelled input.

2. Label Everything Clearly

When you return to your what-if model three months later, or when a colleague opens it, every input, scenario, and output should be self-explanatory.

Use cell comments, a documentation sheet, or simple text labels.

3. ValidateYour Formulas Before Running Analysis

One broken formula can invalidate an entire Data Table.

Run basic checks: Does your profit formula still work if you set sales volume to zero? Does your break-even formula return a sensible number?

Test edge cases before you invest time in scenario planning.

4. Keep What-If Models Separate from Reporting Dashboards

Your operational dashboard should show actual results. Your what-if model should be a controlled environment for testing assumptions.

Mixing the two increases the risk of accidentally overwriting scenarios or misinterpreting outputs.

5. Start with a Clear Business Question

Do not open Excel and start building scenarios for the sake of it. Before you touch the keyboard, write down: What decision am I trying to support?

Your question determines which what-if tool you should use.

Wrapping Up: Put What-If Analysis to Work for Your Business

You now have a practical framework for using what-if analysis in Excel to make better business decisions.

Let us recap the key differences:

  • Excel Scenario Manager helps you compare distinct planning cases: best, base, and worst.
  • Goal Seek in Excel works backwards from a target to find the input you need.
  • Excel Data Tables let you test a full range of values for true sensitivity analysis.

Whether you are a finance professional building a three-year forecast, a small business owner testing pricing strategies, or an operations manager planning for cost changes, these tools will save you hours of manual work and reduce the risk of costly assumptions.

Start with a clear business question. Choose the right tool. Keep your model clean. And let Excel do the heavy lifting for your business planning in Excel.

Ready to Take Your Excel Skills Further?

At @ASK Training, we help business analysts, finance teams, and professionals master practical Excel techniques that drive real decisions.

Explore our full range of Microsoft Excel courses to move beyond basic formulas into confident, data-driven planning.