Formulas used: Offset, If, Conditional formatting
The workbook used throughout this article is available here.
You’re using a financial model and someone asks you to run a sensitivity on x or to run a scenario. Or someone might refer to setting up some scenarios for analysis. They sound almost the same, but can be a little different in practice.
A sensitivity analysis usually involves looking at one variable in isolation and assessing how it changes your results. Scenario analysis usually involves looking at a combination of variables, and usually a number of pre-set combinations.
Section 1 of the example workbook contains the static inputs for our example. In this case, we’re looking at simple revenue and costs for a business.
We’re then going to take those inputs and convert them into the start of a profit and loss statement for a number of years.
Before that, set up some sensitivity override input cells (contained in section 2 ).
For the outputs, we’ll first calculate the number of units sold, price, etc. The formulas here take the base inputs, adjust for the sensitivity inputs and extrapolate out over the time periods.
Then in the P&L, you don’t need to include the sensitivity cells in the formula, just the outputs you’ve calculated above. Essentially, you want to bring in the sensitivity overrides as early in your model flow as possible, right after the assumptions, which makes your model easier to read and audit, and cuts down on your work.
Using a scenario manager to control your sensitivities takes a little more work to set up, but can cut down on your ongoing workload significantly.
Section 3a contains a simple example of the type of scenario manager we use at OptimisedFM. The yellow highlighted cell fully controls which set of inputs flow through the model. By preloading the scenarios you want to run, which in some cases might just be running one sensitivity, you won’t lose track of what you did previously.
You can also combine the scenario manager with individual sensitivity overrides, as we’ve done in section 4.
Pro-tip: Use conditional formatting in your scenario manager so that you can easily see which inputs are different from your base case in any given scenario
Time-series Scenario Manager
Section 5 contains a scenario manager set up to control time-series based inputs. In the example, it is controlling part of a mine plan, but you can use a similar setup for anything that you expect to vary over time. Common inputs we use this setup for are interest rates, exchange rates or staff numbers.
When you create one of these, the easiest place to start is with the time series inputs themselves (in 5b), rather than the scenario manager. The bottom row of each of the inputs is the row that flows through the model, after it has chosen the active scenario. You can choose the active scenario in a number of different ways, such as offset, choose, lookup, vlookup, index (with or without match) – see our posts on Offset and choose and Index and match vs lookup if you need help with these. In 5b, we’ve used offset for the mined tonnes and lookup for the grade. Despite its weaknesses with auditability, I tend to use offset for scenario managers, as if you insert new rows for new input cases, offset will automatically incorporate these new rows, whereas choose definitely won’t, and the lookup/index functions might (depending on where you insert the rows/columns).
Extending sensitivity and scenario analysis
Depending on what the key answers you want to obtain from your model are, you can combine your sensitivity inputs and/or scenario manager with a 1- or 2-D data table. We’ll cover this in a future post, including when to use data tables and when to consider using a looping macro to control the manager.
Have any questions? Post them in the comments or email me at [email protected]. We’ll also take suggestions for any future topics you’d like this blog to cover.