Scenarios

Scenario analysis evaluates multiple potential outcomes of a project, investment, or business based on varying assumptions. Scenario analysis splits into two main approaches: (a) discrete scenario models and (b) probability-based simulations. Both can be used for sensitivity analysis.

Discrete scenario models

Two main ways to build discrete scenarios:

1. Copy the model to create multiple files or workbooks with the same structure and vary the key assumptions. Each model is a separate scenario.
2. Create a scenario table in one workbook, with a selector for which scenario is "live". All scenarios are stored in one model; only one displays at a time.

To build a scenario table:

• List the assumptions to change in rows down
• Label the scenarios in columns across the top
• Input the relevant assumptions
• Add a "live" column that displays the selected scenario's assumptions and links into the model's existing inputs
• Use a dropdown with INDEX MATCH to select which scenario is live
• Build a key outputs section next to the table to see how changing the driver set impacts the model

One downside to this approach is that you can only see the outputs of one scenario at a time. Data tables in Excel can be useful ways to structure a model to report the results of multiple scenarios at one time, but they can be challenging to apply in more advanced scenarios, and they can slow down a model to a crawl in bigger Excel files.

A model that displays discrete scenarios can be used for sensitivity analysis - basically, which drivers have the biggest impact on your outputs, and how much - although it can be difficult to store the outputs of all the scenarios at one time. Iteration, copy and paste are common techniques for doing sensitivity analysis using scenarios in spreadsheets. Manual, yes, but sometimes manual work brings out the best analysis.

Web-based modeling tools like Causal and Equals are built to make building scenarios easy. Here's Causal's docs on scenarios.

Simulation modeling

Simulation modeling uses range-based inputs to create probability distributions of outputs. Excel or Google Sheets can generate random numbers and trials for basic Monte Carlo simulations (see Introduction to Monte Carlo simulation in Excel and Creating a Monte Carlo Simulation Using Excel). Add-ins make scenario construction and result reporting much faster.

Tools to consider: RiskAmp, @Risk, Crystal Ball, and Causal - either directly in Causal or via their scenario add-ins for Excel and Google Sheets.

Simulation modeling is powerful because you can see the range of outcomes from interactions between multiple inputs, and the probability distributions help you understand how likely outcomes are. Harder to communicate and reuse - if you change the model structure, you re-run the scenarios - but structured right, a great approach for broadening scenario work.