What-If Analysis and Scenario Planning Cheat Sheet

Updated 2026-05-26

🧠Study flashcards on this topic90 cards · spaced repetition→

What-if analysis and scenario planning empower decision-makers to explore multiple future states by systematically varying input assumptions and observing how outcomes change. These techniques transform static financial models into dynamic tools for evaluating risk, opportunity, and strategic alternatives. From Excel's built-in tools like Goal Seek and Data Tables to BI platform features like Power BI What-If Parameters and Tableau Set Controls, mastering these approaches enables you to move beyond single-point forecasts and build resilience into planning processes. A key insight: the value lies not in predicting the future accurately, but in understanding which variables matter most and how different combinations of assumptions affect your key metrics—allowing you to prepare for a range of plausible outcomes rather than betting on a single scenario.

What This Cheat Sheet Covers

This topic spans 13 focused tables and 90 indexed concepts, 90 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Core What-If Analysis Tools in ExcelTable 2: Scenario Planning FundamentalsTable 3: Sensitivity Analysis MethodsTable 4: Break-Even and Contribution AnalysisTable 5: Monte Carlo and Simulation TechniquesTable 6: Decision Tree AnalysisTable 7: Variance Analysis TechniquesTable 8: Solver Optimization and ConstraintsTable 9: Rolling Forecasts and Dynamic ScenariosTable 10: Advanced Modeling AssumptionsTable 11: Stress Testing and Risk AnalysisTable 12: Specialized Scenario TechniquesTable 13: BI Tool What-If Analysis

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Core What-If Analysis Tools in Excel

Excel's native what-if tools cover four distinct problem types: reverse-solving a single input (Goal Seek), testing ranges across one or two inputs (Data Tables), managing named sets of assumptions (Scenario Manager), and optimizing subject to constraints (Solver). Knowing which tool to reach for first saves hours of manual modeling.

Tool	Example	Description
Goal Seek	Set revenue cell to $500K, change price to find needed value	• Reverse-solves a single input to hit a target output • works only with one changing cell and one formula cell
One-Variable Data Table	Row of interest rates (3%–7%) shows monthly payment for each	• Tests a single input variable across multiple values simultaneously • can display results for multiple output formulas in the same grid.
Two-Variable Data Table	Rows: loan terms (15–30 yrs) Cols: rates (3%–6%)	• Tests two inputs simultaneously • creates a matrix showing one output for every combination of row and column values.
Scenario Manager	Save "Best Case", "Worst Case", "Base Case" with different inputs	• Stores named sets of assumptions (up to 32 changing cells per scenario) and allows instant switching • generates scenario summary reports.
Scenario Summary Report	Table comparing 3 scenarios side-by-side with result cells	• Generates a static snapshot of all scenarios with changing cells and result cells • available as regular table or PivotTable format.
Solver	Maximize profit subject to budget ≤ $100K, units ≥ 500	• Optimization engine that finds the best value for an objective cell by adjusting multiple variables while respecting constraints • supports linear, nonlinear, and integer problems

Table 2: Scenario Planning Fundamentals

Building sound scenarios starts with a clear base case, then systematically varies key assumptions to create a range of plausible futures. The discipline is in making scenarios internally consistent and genuinely divergent — not just optimistic/pessimistic copies of the same model.

Technique	Example	Description
Base Case Scenario	Revenue grows 5% annually, margins remain 20%	• Most likely outcome based on current trends and realistic assumptions • serves as the primary reference point for comparison against other scenarios.

The rest of this section is part of the full cheat sheet

Table 3: Sensitivity Analysis Methods

Sensitivity analysis answers "which inputs matter most?" by systematically varying one or more assumptions and measuring the effect on outputs. The choice of method — one-at-a-time vs. global — determines whether you capture interaction effects between variables or only individual impacts.

Method	Example	Description
Sensitivity Table (Matrix)	NPV values for discount rates 4%–12% and growth 0%–10%	• Two-dimensional grid showing how a single output varies with two inputs • built with Excel's two-variable data table.

The rest of this section is part of the full cheat sheet

Table 4: Break-Even and Contribution Analysis

Break-even analysis identifies the volume or revenue threshold where a business covers all costs; contribution margin analysis reveals how much each unit or product helps cover fixed costs. Together they form the foundation of pricing, cost structure, and profitability scenario modeling.

Technique	Example	Description
Break-Even Point (Units)	$BEP = \frac{FixedCosts}{Price - VarCost}$ $= \frac{100000}{50-30} = 5000$ units	• The sales volume where total costs equal total revenue • profit is exactly zero at this point.

The rest of this section is part of the full cheat sheet

Table 1: Core What-If Analysis Tools in Excel

Tool	Example	Description
Goal Seek	Set revenue cell to $500K, change price to find needed value	• Reverse-solves a single input to hit a target output • works only with one changing cell and one formula cell
One-Variable Data Table	Row of interest rates (3%–7%) shows monthly payment for each	• Tests a single input variable across multiple values simultaneously • can display results for multiple output formulas in the same grid.
Two-Variable Data Table	Rows: loan terms (15–30 yrs) Cols: rates (3%–6%)	• Tests two inputs simultaneously • creates a matrix showing one output for every combination of row and column values.
Scenario Manager	Save "Best Case", "Worst Case", "Base Case" with different inputs	• Stores named sets of assumptions (up to 32 changing cells per scenario) and allows instant switching • generates scenario summary reports.
Scenario Summary Report	Table comparing 3 scenarios side-by-side with result cells	• Generates a static snapshot of all scenarios with changing cells and result cells • available as regular table or PivotTable format.
Solver	Maximize profit subject to budget ≤ $100K, units ≥ 500	• Optimization engine that finds the best value for an objective cell by adjusting multiple variables while respecting constraints • supports linear, nonlinear, and integer problems

Table 2: Scenario Planning Fundamentals

Technique	Example	Description
Base Case Scenario	Revenue grows 5% annually, margins remain 20%	• Most likely outcome based on current trends and realistic assumptions • serves as the primary reference point for comparison against other scenarios.

Table 3: Sensitivity Analysis Methods

Method	Example	Description
Sensitivity Table (Matrix)	NPV values for discount rates 4%–12% and growth 0%–10%	• Two-dimensional grid showing how a single output varies with two inputs • built with Excel's two-variable data table.

Table 4: Break-Even and Contribution Analysis

Technique	Example	Description
Break-Even Point (Units)	$BEP = \frac{FixedCosts}{Price - VarCost}$ $= \frac{100000}{50-30} = 5000$ units	• The sales volume where total costs equal total revenue • profit is exactly zero at this point.