Scenario analysis is the process of evaluating multiple potential outcomes of a project, investment, or business based on varying assumptions and conditions. Conducting a scenario analysis is a common usecase for a spreadsheet financial model, here's how to think about doing scenario analysis in Foresight models and spreadsheet models generally.
Scenario analysis can be broadly grouped into (a) discrete scenario models and (b) probability-based simulations, both of which can be used to conduct sensitivity analysis.
Discrete scenario models
Discrete scenario models involve building model versions that vary key drivers and assumptions. Generally you can do this in two main ways:
- Copy the model to create multiple files or workbooks with the same structure, and alter the key assumptions you want to evalute; each model is a separate scenario.
- Create a scenario table to store the assumptions for each scenario in one model file or workbook, and use a selector to select which scenario is "live" or used in the model; all scenarios are stored in one model, only one scenario is displayed at a time.
Creating a scenario table is straightforward: just create a list of the assumptions to change for each scenario in rows going down, identify and label the scenarios in columns across the top, and input the relevant assumptions into the table, and then create another column which is used to display the "live" assumptions used in the model, and link these cells into existing inputs. For ease of use, I generally create a dropdown select using INDEX MATCH so that I can just select which scenario is displayed in the "live" column, and then create a key outputs section next to the scenario table so I can easily see how changing the drivers 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 generally refers to using software to use range-based inputs to create probability distributions of outputs. We can use Excel or Google Sheets to generate random numbers and trials to create basic Monte Carlo simulations (e.g. Introduction to Monte Carlo simulation in Excel and Creating a Monte Carlo Simulation Using Excel), but I like to leverage Excel and Google Sheets add-ins to built out scenarios and analyze outputs based on distributions of assumptions, as the construction of scenarios and the reporting of the results is far faster and easier.
Many tools exist - I've used RiskAmp, @Risk, and Crystal Ball in the past, and generally use Causal today, either directly in a Causal model or using their scneario modeling add-ins for Excel and Google Sheets, Causal Scenarios for Microsoft Excel and Causal Scenarios for Google Sheets.
Simulation modeling can be powerful because you can see easily see the ranges of outcomes that come from the interaction of multiple inputs, and the probability disribution outputs can help you understand how likely the outcomes are. They do tend to be harder to communicate and use, because if you change the structure of the model you will have to re-run the scenarios, but structured correctly they can be a great approach to use to broaden your use of scenario modeling.