US Trends

what if analysis excel

What-if analysis in Excel lets you change input values in your sheet to see how the results (your formulas) would change, without rebuilding the model each time. It’s mainly used for budgeting, forecasting, pricing, and “what happens if…” business questions.

What is What-If Analysis in Excel?

In Excel, what‑if analysis means you plug different numbers into the cells that feed your formulas to explore possible outcomes before making a decision. Instead of guessing, you systematically test scenarios (like higher sales, lower costs, or different interest rates) and see the effect on profits, cash flow, or any key metric.

Excel has three main what‑if tools on the Data tab under “What‑If Analysis”:

  • Scenario Manager
  • Goal Seek
  • Data Tables

These tools sit on top of your existing formulas; you build a normal model first, then layer what‑if analysis to simulate outcomes.

The Three Main Tools

1. Scenario Manager – multiple named scenarios

Scenario Manager lets you store and switch between named sets of input values (called scenarios) such as Best Case , Base Case , and Worst Case. Each scenario can change several cells at once (up to 32 changing cells in classic Excel), for example revenue growth, cost inflation, and tax rate together.

Typical use cases:

  • Budget planning: optimistic vs conservative forecast.
  • Project planning: fast vs delayed schedule with different costs.
  • Pricing: high-price/low-volume vs low-price/high-volume.

Basic workflow:

  1. Build your model with formulas (e.g., profit = revenue − costs).
  2. Go to Data → What‑If Analysis → Scenario Manager → Add.
  3. Choose the changing cells (inputs), give the scenario a name, and enter the values.
  4. Add more scenarios.
  5. Use Show to flip between scenarios, or Summary to create a comparison report.

Scenario Manager can generate a Scenario Summary sheet that lists your scenarios side‑by‑side in a static table, so you can compare outcomes at a glance.

2. Goal Seek – work backwards from a target

Goal Seek answers: “What input do I need to hit this result?” Instead of changing values manually, you tell Excel what result you want in a formula cell, and which single input it’s allowed to adjust.

Examples:

  • What sales volume do I need to reach a profit of 50,000?
  • What interest rate makes my loan payment equal a specific amount?
  • What discount gets revenue to a target?

Basic workflow:

  1. Click the formula cell containing the result (e.g., profit).
  2. Go to Data → What‑If Analysis → Goal Seek.
  3. Set Set cell = result cell, To value = desired target, By changing cell = input cell Excel should vary (e.g., price, volume, interest rate).
  4. Run it and accept the solution Excel finds.

Goal Seek works with one changing cell only , but it’s fast and ideal for “reverse engineering” a single target.

3. Data Tables – one view of many outcomes

A Data Table shows how a formula’s result changes as you plug in many different values for one or two inputs, all in a single grid. It’s perfect for sensitivity analysis, like “What happens to profit if price varies from 10 to 30?” or “What if both price and volume change?”

There are two types:

  • One‑variable data table:
    • Varies one input (e.g., different interest rates) and shows the resulting output(s), such as monthly payment.
  • Two‑variable data table:
    • Varies two inputs at the same time (e.g., price and quantity) and shows the combined effect on one formula, like revenue or profit.

Basic one‑variable setup:

  1. Put the base formula (e.g., profit) in a cell.
  2. List test values (e.g., interest rates) in a column.
  3. Link the formula to the input cell as usual.
  4. Select the whole column including the formula, then use Data → What‑If Analysis → Data Table , and set the row or column input cell.
  5. Excel fills the table with the resulting outputs.

Data Tables recalculate automatically when data changes, so they’re great live dashboards for “what‑if” questions.

Where You Find It in Excel

  • The group is on the Data tab in the Forecast or Data Tools/What‑If Analysis section, depending on Excel version.
  • You’ll see a dropdown labeled What‑If Analysis with options: Scenario Manager , Goal Seek , and Data Table.

Practical Examples

A few common real‑world uses:

  • Personal finance:
    • Try different loan terms and rates to see payments.
    • Adjust savings rate to reach a target balance by a certain date.
  • Business planning:
    • Model best/base/worst‑case annual budgets.
    • See how profit changes with different prices and sales volumes.
  • Operations and projects:
    • Test staffing levels vs service levels or deadlines.
    • Assess how delays or cost overruns change total project cost.

Mini comparison table (tool vs purpose)

Tool Main purpose Inputs it can change Typical use
Scenario Manager Compare named scenarios Several cells (up to dozens) Best/base/worst case budgets
Goal Seek Hit a specific target One cell only Find needed price, volume, or rate
Data Table Sensitivity analysis grid One or two inputs Price × volume, rate × term analysis
All three work together: you build a formula‑based model, then use scenarios for big “stories,” Goal Seek for exact targets, and Data Tables for detailed sensitivity or “heatmap‑style” views of outcomes.

Meta description (SEO):
Learn what‑if analysis in Excel: how Scenario Manager, Goal Seek, and Data Tables help you test scenarios, hit targets, and run sensitivity analysis for smarter planning and forecasting.

Information gathered from public forums or data available on the internet and portrayed here.