42  What-If Analysis and Scenario Modeling

42.1 Why What-If Analysis Matters

The single most useful question in business analytics is what would happen if…?

Static reports describe what did happen. Forecasts predict what will happen. What-if analysis answers a different question: what would happen if we changed an assumption? Tilt the price by 5 %, raise the marketing budget by 20 %, slow delivery by a day — what is the impact on revenue, margin, customer satisfaction?

The standard practitioner reference is Microsoft Excel Data Analysis and Business Modeling by Wayne L. Winston (2019), which catalogues every Excel what-if technique with worked examples. The deeper academic treatment is The Art of Modeling with Spreadsheets by Stephen G. Powell & Kenneth R. Baker (2011), which sets spreadsheet modelling in the broader management-science tradition.

For a visualisation-focused book, what-if analysis is what turns a static dashboard into a decision-support tool. The visual is the same; the audience can now steer it.

42.2 Excel’s What-If Toolkit

flowchart TD
    WI["Excel What-If<br>Analysis"]
    WI --> DT["Data Tables<br>One- and two-variable<br>sensitivity matrices"]
    WI --> GS["Goal Seek<br>Find the input that<br>produces a target output"]
    WI --> SM["Scenario Manager<br>Compare named<br>combinations of inputs"]
    WI --> So["Solver<br>Constrained optimisation<br>(linear, nonlinear, integer)"]
    style WI fill:#e3f2fd,stroke:#1976D2
    style DT fill:#fce4ec,stroke:#AD1457
    style GS fill:#fff3e0,stroke:#EF6C00
    style SM fill:#fff8e1,stroke:#F9A825
    style So fill:#e8f5e9,stroke:#388E3C

Excel ships with four built-in what-if tools, accessible from Data → What-If Analysis (Solver requires the Solver Add-in):

  • Data Tables — recompute a model across a range of input values; one-variable or two-variable.
  • Goal Seek — find the input value that produces a target output.
  • Scenario Manager — store named combinations of inputs and compare results in a single report.
  • Solver — constrained optimisation: maximise or minimise an objective subject to constraints.

The four tools cover most spreadsheet what-if needs without leaving Excel. For more sophisticated patterns — Monte Carlo simulation, decision-tree analysis — Excel can be extended via add-ins or VBA.

42.3 Data Tables

A Data Table computes a model across a range of input values, producing a sensitivity matrix in one step. The model lives in cells; the Data Table just substitutes inputs and reads the output.

42.3.1 One-Variable Data Table

Use a one-variable data table to see how the output changes as a single input varies.

Setup:

  1. Build the model in cells (e.g., B10 = Sales = Volume * Price - Cost, where Volume is in B2).
  2. In a column, list the input values to test (e.g., volumes 100 to 1000 in steps of 100).
  3. In the cell at the top right of the table, reference the model output: =B10.
  4. Highlight the table including the header row → Data → What-If Analysis → Data Table.
  5. Column input cell: B2 (the input that the column values should substitute for).
  6. OK.

Excel populates the table with the model output for each input value. Plot the column as a line chart for a sensitivity-curve visual.

42.3.2 Two-Variable Data Table

A two-variable data table varies two inputs simultaneously, producing a matrix of outputs.

Setup:

  1. List the first input range across the top row, the second input range down the left column.
  2. In the corner cell, reference the model output (=B10).
  3. Highlight the entire table → Data → What-If Analysis → Data Table.
  4. Row input cell: the cell substituted by the row values.
  5. Column input cell: the cell substituted by the column values.
  6. OK.

The matrix shows the output across every combination of the two inputs. With Conditional Formatting (Color Scales), the matrix becomes a sensitivity heatmap — the canonical visual for two-variable what-if.

42.4 Goal Seek

Goal Seek answers: what input value makes the output equal to a target?

Use case: a business case where the analyst wants to know the price that produces a specific profit, the volume that breaks even, the discount that achieves a target revenue.

Setup:

  1. Build the model in cells.
  2. Data → What-If Analysis → Goal Seek.
  3. Set cell: the output cell (e.g., B10 for profit).
  4. To value: the target (e.g., 100000).
  5. By changing cell: the input cell (e.g., B5 for price).
  6. OK.

Excel iteratively adjusts the input until the output matches the target. The result is shown in the input cell.

Goal Seek is fast for single-input problems with a smooth response. For multi-input or constrained problems, Solver is the right tool.

42.5 Scenario Manager

Scenario Manager stores named combinations of input values — Best Case, Base Case, Worst Case — and compares them in a single summary report.

Setup:

  1. Identify the cells whose values change between scenarios (changing cells).
  2. Data → What-If Analysis → Scenario Manager → Add.
  3. Name the scenario, select changing cells, enter values.
  4. Repeat for each scenario.
  5. Summary generates a report comparing the scenarios on chosen output cells.

Scenario Manager is the right tool when the audience needs a clear three-column comparison (Pessimistic / Base / Optimistic) on a board pack. The summary report can be formatted with Conditional Formatting for traffic-light indication.

42.6 Solver

Solver is Excel’s constrained-optimisation engine. It finds the values of decision variables that maximise (or minimise) an objective, subject to constraints.

To enable: File → Options → Add-ins → Manage: Excel Add-ins → Solver Add-in → Tick → OK. Solver appears under Data → Analyze → Solver.

To use:

  1. Build the model in cells: decision variables (changing cells), objective (the cell to maximise/minimise), and constraint formulas.
  2. Data → Solver.
  3. Set Objective: the objective cell.
  4. To: Max, Min, or Value Of.
  5. By Changing Variable Cells: the decision variables.
  6. Subject to the Constraints: add each constraint (cell, operator, value).
  7. Select a Solving Method: Simplex LP for linear, GRG Nonlinear for smooth nonlinear, Evolutionary for non-smooth.
  8. Solve.

Common Solver problems:

  • Product mix: Maximise profit subject to capacity constraints.
  • Transportation: Minimise shipping cost subject to supply and demand.
  • Investment allocation: Maximise return subject to risk and budget.
  • Workforce scheduling: Minimise cost subject to coverage requirements.

42.7 Tornado Charts and Sensitivity Visualisation

A Tornado Chart is the standard visualisation of sensitivity analysis: a horizontal bar chart, one bar per input, showing how much the output changes when that input is flexed by a defined amount (typically ±10 % or ±20 %), sorted by sensitivity (largest at the top).

To build:

  1. List inputs in a column.
  2. For each input, compute the output at the low and high values (using one-variable Data Tables).
  3. Compute the spread: =High - Low.
  4. Sort the table descending by spread.
  5. Insert a stacked bar chart with low values negative and high values positive, both centred on the base output.
  6. Format the bars to look like one-bar-per-input.

The result is the iconic tornado shape — wide bars at the top for the most sensitive inputs, narrowing toward the bottom. The visual answers which assumptions matter most in a single chart.

For two-variable sensitivity, a heat-mapped Data Table (Step 4 above with Conditional Formatting) is the visual equivalent.

42.8 Monte Carlo Simulation in Excel

Monte Carlo simulation generalises sensitivity analysis: instead of testing fixed input values, it samples each input from a probability distribution thousands of times and reports the distribution of the output.

Excel-native Monte Carlo without an add-in:

  1. For each uncertain input, generate samples using RAND() and an inverse-CDF formula — for example, NORM.INV(RAND(), mean, sd) for a normal distribution.
  2. Compute the model output in each row.
  3. Drag the formulas down to a few thousand rows.
  4. Compute summary statistics (mean, median, percentiles) and plot the histogram of outputs.

For richer Monte Carlo with named distributions, correlation, and risk metrics, the (RISK?) add-in (Palisade), the Crystal Ball add-in (Oracle), or simple Python integration via xlwings give substantially more capability than the native Excel approach.

Wayne L. Winston (2019) dedicates a full section to Monte Carlo simulation patterns in Excel, including correlated inputs and tornado-style risk decomposition.

42.9 What-If Analysis in Power BI and Tableau

What-if is not exclusive to Excel; both major BI tools offer it.

  • Power BI What-If Parameters: Modeling → New Parameter → Numeric range. Power BI generates a small calculated table and a slicer. Reference the parameter’s value in DAX measures: Adjusted Sales = [Total Sales] * (1 + 'Discount %'[Discount % Value]). The dashboard slider drives the metric in real time.

  • Tableau Parameters: Covered in Chapter 31. A parameter Discount % referenced in calculated fields produces the same effect.

The pattern is identical across Excel, Power BI, and Tableau: an input the user controls drives a calculation that flows into the visualisation. The tool differs; the modelling concept does not.

42.10 Best Practices

  • Separate inputs from formulas: Inputs in named cells at the top of the sheet; formulas reference the names. The model is then driven from a single location.
  • Use Excel Tables and named ranges: Data Tables and Solver work more reliably with structured references.
  • Document the assumptions: Each input cell carries a comment explaining its source and rationale.
  • Test the boundary cases: Run the model at the extremes of the plausible input range; verify it does not break.
  • Visualise sensitivity: Tornado charts and heatmapped Data Tables turn a wall of numbers into a single readable view.
  • Pair Goal Seek with a sketch: Before running Goal Seek, sketch the expected answer; if the result deviates wildly, the model has a bug.
  • For Solver, start with linear: Linear programming converges fast and produces interpretable shadow prices; use nonlinear or evolutionary only when the model genuinely demands them.
  • Document the chosen scenario: When using Scenario Manager, the summary report becomes part of the audit trail; export it.

42.11 Common Pitfalls

  • Inputs Embedded in Formulas: A discount rate hard-coded as 0.12 deep in a formula instead of referenced from a named input cell; impossible to flex without editing every formula.
  • Goal Seek Without Sanity Check: Trusting the result without sketching what the answer should be; a typo in the model produces a confidently wrong answer.
  • Solver Misconfigured: Wrong solving method (Simplex on a nonlinear model or vice versa) converges to a wrong answer or fails to converge at all.
  • Data Table Recalc Loop: Large Data Tables on volatile workbooks slow Excel because every recalc triggers the table. Set Calculation Options → Automatic Except for Data Tables while editing.
  • Scenario Drift: Scenario Manager scenarios become stale as the model evolves; the inputs no longer match the current model. Audit and refresh.
  • Tornado Without Sorting: Tornado chart unsorted; the visual loses its “tornado” shape and the sensitivity story is buried.
  • Monte Carlo Too Few Samples: A few hundred samples produce noisy histograms; aim for several thousand at minimum.
  • What-If on the Wrong Output: Sensitivity reported on a metric the audience does not actually care about; pick the output that drives the decision.
  • Decision Theatre: Running Scenario Manager to produce a slide that the team had already chosen; what-if exists to inform decisions, not to dress them up.

42.12 Illustrative Cases

A Pricing Decision Driven by a Two-Variable Data Table

A retail manager evaluates a price-and-volume tradeoff for a new product. A two-variable Data Table varies price (₹500 to ₹1500 in ₹100 steps) and assumed monthly volume (500 to 5000 in 500 steps). Conditional Formatting renders the resulting profit matrix as a heatmap. The manager and the marketing team can see at a glance the price-volume combinations that beat the target margin.

A Capital-Allocation Solver

A company has ₹50 crore to invest across six projects with known returns and risks. Solver maximises portfolio return subject to a budget constraint and a risk-cap constraint. The result allocates capital across the projects; shadow prices on the constraints inform whether relaxing them would be worth pursuing.

A Tornado Chart for the Business Case

The Yuvijen Telecom business case from Chapter 10 is extended with a tornado chart of sensitivity. Eight inputs are flexed ±20 %; the resulting tornado places retention rate, cost-per-customer, and retention offer cost at the top — telling the executive committee which assumptions matter most for the case’s robustness.

42.13 Hands-On Exercise: What-If and Scenario Modelling in Excel

Aim: Build a what-if-enabled business-case workbook for Yuvijen Stores that exercises Data Tables, Goal Seek, Scenario Manager, and Solver, plus a tornado chart of sensitivity.

Deliverable: An Excel workbook (yuvijen-what-if.xlsx) with separate sheets for Inputs, Model, Sensitivity, Scenarios, and Summary.

42.13.1 Step 1 — Build the Inputs Sheet

Create a sheet Inputs with named cells:

  • B2 Price = 500
  • B3 Volume = 1000
  • B4 UnitCost = 300
  • B5 FixedCost = 200000
  • B6 GrowthRate = 0.05

Each cell carries a one-line comment explaining its rationale and source.

42.13.2 Step 2 — Build the Model Sheet

On a sheet Model, compute the outputs:

Revenue      = Price * Volume
Variable Cost= UnitCost * Volume
Margin       = Revenue - Variable Cost
Profit       = Margin - FixedCost

Format Profit prominently; this is the decision-relevant output.

42.13.3 Step 3 — One-Variable Sensitivity

On a sheet Sensitivity:

  1. Column A: list prices from 400 to 700 in steps of 25.
  2. Cell B1: =Model!B5 (the Profit cell).
  3. Highlight the table A1:B14 (or appropriate range) → Data → What-If Analysis → Data Table.
  4. Column input cell: Inputs!B2 (the Price cell).
  5. OK.

The table populates with profit at each price. Insert a Line chart of the result.

42.13.4 Step 4 — Two-Variable Heatmap

On the same sheet, build a two-variable table:

  1. Top row: prices 400, 450, 500, 550, 600, 650, 700.
  2. Left column: volumes 500, 1000, 1500, 2000, 2500, 3000.
  3. Top-left cell: =Model!B5.
  4. Highlight the table → Data → What-If Analysis → Data Table.
  5. Row input: Inputs!B2 (Price). Column input: Inputs!B3 (Volume).
  6. Apply Conditional Formatting → Color Scales → Red-Yellow-Green to the result matrix.

The heatmap shows profit across all 42 combinations.

42.13.5 Step 5 — Goal Seek for Break-Even

  1. Data → What-If Analysis → Goal Seek.
  2. Set cell: Model!B5 (Profit).
  3. To value: 0.
  4. By changing cell: Inputs!B3 (Volume).
  5. OK.

Excel finds the break-even volume. Note the result; reset the input afterwards.

42.13.6 Step 6 — Scenario Manager

  1. Data → What-If Analysis → Scenario Manager → Add.
  2. Scenario name: Base Case. Changing cells: Inputs!B2:B6. Values: current. OK.
  3. Add → Pessimistic Case: Price 450, Volume 800, UnitCost 320. OK.
  4. Add → Optimistic Case: Price 550, Volume 1300, UnitCost 280. OK.
  5. Summary → Result cells: Model!B5 (Profit), Model!B3 (Margin). OK.

Excel generates a side-by-side comparison report on a new sheet.

42.13.7 Step 7 — Solver for Optimal Pricing

  1. Enable Solver if not already (File → Options → Add-ins).
  2. Build a small auxiliary model: assume volume responds to price as Volume = MAX(0, 2000 - 2 * Price) (a simple linear demand).
  3. Data → Solver.
  4. Set Objective: Model!B5 (Profit). To: Max.
  5. By Changing: Inputs!B2 (Price).
  6. Constraints: Inputs!B2 >= 100, Inputs!B2 <= 1000.
  7. Solving Method: GRG Nonlinear (because the demand-price relationship is nonlinear in profit).
  8. Solve.

Solver finds the profit-maximising price.

42.13.8 Step 8 — Tornado Chart of Sensitivity

  1. On a sheet Tornado, list the five inputs in column A.
  2. For each input, compute Profit at -20 % and +20 % of the base value (using one-variable Data Tables, or by manually flexing each input one at a time).
  3. Compute spread (High − Low) for each input.
  4. Sort the table by spread descending.
  5. Insert a Stacked Bar chart with the high and low values centred on the base profit.
  6. Format to look like a tornado.

The chart shows which inputs the business case is most sensitive to.

42.13.9 Step 9 — Connect to the Visualisation Layer

The hands-on demonstrates that what-if analysis is a visualisation pattern in disguise:

  • Data Tables with Conditional Formatting are heatmaps.
  • Tornado charts are sorted-bar visuals of sensitivity.
  • Scenario Manager outputs are Conditional-Formatted comparison tables.
  • Power BI What-If Parameters and Tableau Parameters carry the same logic into BI dashboards.

Every what-if technique closes a loop the audience cares about: how robust is the recommendation, where would I want the analyst to look harder, what assumption could change the answer? The visual that surfaces these answers is the most useful one the analyst can produce.

TipFiles and Screen Recordings

Excel workbook (yuvijen-what-if.xlsx), the sample inputs, and screen recordings of each Excel what-if technique will be embedded here.

Summary

Concept Description
Foundations
Why What-If Analysis Matters The single most useful question in business analytics is what would happen if assumptions changed
Excel What-If Toolkit
Data Tables Recompute a model across a range of input values; one or two variables
Goal Seek Find the input value that produces a target output
Scenario Manager Store named combinations of inputs and compare in a single summary report
Solver Constrained optimisation: maximise or minimise an objective subject to constraints
Data Tables
One-Variable Data Table Sensitivity matrix for a single input
Two-Variable Data Table Two-variable matrix produced by varying both inputs simultaneously
Sensitivity Heatmap Two-variable Data Table with Conditional Formatting Color Scales applied
Goal Seek
Goal Seek Setup Set cell, To value, By changing cell; iterates to match target
Goal Seek Limitations Single input only; for multi-input or constrained problems Solver is right tool
Scenario Manager
Scenario Manager Setup Add scenarios with named changing cells; generate Summary report
Scenario Summary Report Side-by-side comparison of scenarios on chosen output cells
Solver
Solver Setup Set Objective, By Changing Variable Cells, Subject to Constraints, Solving Method
Simplex LP Method Linear programming; converges fast and produces shadow prices
GRG Nonlinear Method Smooth nonlinear; for models with curved response surfaces
Evolutionary Method Non-smooth or discrete; uses genetic algorithms; slowest convergence
Product Mix Problem Maximise profit subject to capacity constraints
Transportation Problem Minimise shipping cost subject to supply and demand
Investment Allocation Maximise return subject to risk and budget constraints
Workforce Scheduling Minimise cost subject to coverage requirements
Sensitivity Visualisation
Tornado Chart Standard sensitivity visualisation; horizontal bars sorted by spread
Sensitivity Visualisation Tornado for one-variable, heatmapped Data Table for two-variable
Monte Carlo Simulation
Monte Carlo Simulation Sample inputs from probability distributions thousands of times; report output distribution
Inverse-CDF Sampling Generate samples using RAND() and inverse-CDF formulas like NORM.INV
Crystal Ball and at Risk Add-Ins Add-ins for richer Monte Carlo with named distributions, correlation, and risk metrics
What-If in Power BI and Tableau
Power BI What-If Parameters Modeling New Parameter Numeric range; reference value in DAX measures
Tableau Parameters Same pattern in Tableau using parameters and calculated fields
Best Practices
Separate Inputs from Formulas Inputs in named cells at the top of the sheet; formulas reference the names
Use Tables and Named Ranges Tables and named ranges work more reliably with Data Tables and Solver
Document Assumptions Each input cell carries a comment explaining source and rationale
Test Boundary Cases Run the model at extremes of the plausible input range to verify it does not break
Visualise Sensitivity Tornado charts and heatmapped Data Tables turn numbers into readable views
Pair Goal Seek with Sketch Sketch the expected Goal Seek answer; if result deviates wildly the model has a bug
Start Solver Linear For Solver, start linear; nonlinear or evolutionary only when needed
Document Chosen Scenario Scenario Summary becomes part of the audit trail; export it
Common Pitfalls
Inputs Embedded in Formulas Pitfall of hard-coded constants buried in formulas instead of named input cells
Goal Seek Without Sanity Check Pitfall of trusting Goal Seek result without sanity check
Solver Misconfigured Pitfall of wrong solving method on the wrong problem type
Data Table Recalc Loop Pitfall of large Data Tables triggering recalc on every workbook change
Scenario Drift Pitfall of Scenario inputs becoming stale as model evolves
Tornado Without Sorting Pitfall of unsorted tornado losing its shape and sensitivity story
Monte Carlo Too Few Samples Pitfall of a few hundred samples producing noisy Monte Carlo histograms
What-If on Wrong Output Pitfall of sensitivity reported on a metric the audience does not care about
Decision Theatre Pitfall of running scenarios to dress up a decision already made