What-If Analysis

What-if analysis tests how changing inputs affects a model's results. It is also Excel's Goal Seek, Data Table and Scenario Manager tools. See examples of each.

5 min readGlossary
Contents
  1. Types of what-if analysis
  2. What-if analysis example
  3. What-If Analysis tools in Excel
  4. What-if analysis vs. sensitivity analysis vs. scenario planning
  5. How finance teams use what-if analysis
  6. Frequently asked questions

What-if analysis is the practice of changing the inputs in a model to see how the results change, for example how profit responds if prices rise 5% or volume falls 10%. It covers several techniques: sensitivity analysis, which changes one input at a time; scenario analysis, which changes several inputs together; and goal seeking, which works backward from a target result to the input needed to reach it.

In Microsoft Excel, What-If Analysis is also the name of a menu on the Data tab, in the Forecast group, that contains three tools: Scenario Manager, Goal Seek and Data Table. Finance teams rely on it for budgeting, pricing, break-even and financing decisions.

Types of what-if analysis

  • Sensitivity analysis: vary one input, hold the rest constant and measure the change in the output. Results are displayed in data tables or tornado charts.
  • Scenario analysis: define complete sets of assumptions, such as base, upside and downside cases, and compare the outcomes. It is covered in depth under scenario planning.
  • Goal seeking: fix the result you want, such as a target profit or an NPV of zero, and solve for the input that produces it.
  • Simulation: sample many inputs from probability ranges at once (Monte Carlo simulation) to see the spread of potential outcomes and how likely each is.

What-if analysis example

A product line sells 5,000 units a month at $40, with variable costs of $24 per unit and fixed costs of $60,000 a month. Its monthly operating income is 5,000 × ($40 − $24) − $60,000 = $20,000. Each what-if technique answers a distinct question about it:

  • Sensitivity: with everything else unchanged, operating income is $10,000 at a price of $38, $20,000 at $40, $30,000 at $42 and $40,000 at $44. Each $1 of price is worth $5,000 a month.
  • Goal seeking: to earn $30,000 a month at $40, the line must sell ($30,000 + $60,000) ÷ $16 = 5,625 units. At 5,000 units, it needs a price of $24 + ($90,000 ÷ 5,000) = $42.
  • Scenarios: a downturn with 4,500 units at $38 gives 4,500 × $14 − $60,000 = $3,000, while a growth case with 5,500 units at $42 gives 5,500 × $18 − $60,000 = $39,000.

Setting the target to zero is a form of break-even analysis: the line breaks even at $60,000 ÷ $16 = 3,750 units a month.

What-If Analysis tools in Excel

All three tools appear under Data > What-If Analysis, in the Forecast group of the ribbon. Each needs a model in which the result cell is a formula that depends, directly or through other cells, on the input cells.

Goal Seek

Goal Seek finds the single input value that makes a formula return a target. Choose Data > What-If Analysis > Goal Seek, then enter the Set cell (the formula, such as operating income), the To value (30000) and the By changing cell (units sold). Excel iterates until it finds 5,625. Goal Seek changes only one input; to change multiple inputs at once or apply constraints, use the Solver add-in.

Data Table

A data table recalculates a result for a list of input values. For a one-variable table, list the prices down a column, put a reference to the operating income cell (for example =B10) one row above and one column to the right of the first price, select the range and choose Data > What-If Analysis > Data Table, then enter the price cell as the Column input cell. A two-variable table puts one input across the top row and another down the first column, with the result reference in the top-left corner, and fills the grid. Excel fills the results with a {=TABLE()} array formula, so individual result cells cannot be edited.

Scenario Manager

Scenario Manager saves named sets of input values, such as Base, Downturn and Growth, and swaps them into the model on demand. Choose Data > What-If Analysis > Scenario Manager, click Add, name the scenario, select the changing cells (up to 32 per scenario) and enter their values. The Summary button builds a report on a new sheet that compares selected result cells across all scenarios.

Next to What-If Analysis in the same group is Forecast Sheet, which extends a historical time series into the future. It is a forecasting tool rather than a what-if tool.

What-if analysis vs. sensitivity analysis vs. scenario planning

The three terms overlap because what-if analysis is the general practice and the other two are particular forms of it.

  • What-if analysis: any structured change of inputs to see the effect, including goal seeking and simulation.
  • Sensitivity analysis: one input at a time, to rank which assumptions matter most.
  • Scenario planning: several inputs changed together into a few coherent futures, each with a response plan.

How finance teams use what-if analysis

  • Budgeting and forecasting: testing hiring plans, price increases or cost savings in a driver-based planning model before committing to them.
  • Pricing: finding the price or volume required to reach a margin target.
  • Investment decisions: checking how net present value (NPV) or payback changes with project assumptions.
  • Financing and cash: testing how changes in interest rates, payment terms or collection times affect cash and loan covenants.
  • Currency risk: estimating how a move in exchange rates changes the home-currency cost of foreign-currency purchases, the company's FX exposure.

The results are only as dependable as the model's logic and the input ranges chosen, and none of the Excel tools says how likely an outcome is.

Frequently asked questions

Where is What-If Analysis in Excel?

It is on the Data tab, in the Forecast group. Clicking What-If Analysis opens a menu with Scenario Manager, Goal Seek and Data Table.

What are the three what-if analysis tools in Excel?

The three tools are Scenario Manager, Goal Seek and Data Table. Scenario Manager and Data Table run inputs forward to show the results, while Goal Seek works backward from a target result to the input needed.

What is the difference between Goal Seek and Solver?

Goal Seek changes one input to hit one target value. Solver, an Excel add-in, can change many inputs at once, respect constraints and maximize or minimize a result.

Why is my Excel data table not updating?

The workbook is probably set to Automatic Except for Data Tables, an option under Formulas > Calculation Options that big models use to stay fast. Press F9 to recalculate, or switch the option back to Automatic.

Is what-if analysis the same as sensitivity analysis?

Not exactly. Sensitivity analysis is one type of what-if analysis, the type that changes a single input at a time; what-if analysis also covers scenarios, goal seeking and simulation.

Let’s make AP
work for you.