What-If Analysis and Scenario Planning Cheat Sheet

Updated 2026-05-26

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. 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

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.

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.