Managing inputs - Part 1: Cases, scenarios & sensitivities.
Most of the models we build have a similar input sheet structure. This structure has stood the test of time across thousands of model builds.
When designing the input sheet structure, we were aiming for the following outcomes:
- The ability to easily switch input scenarios;
- The ability to run alternative scenarios on base case data without changing the base case numbers themselves;
- The ability to have both constant and series based inputs;
- Clarity around which input scenario is active in the model;
- The ability to see quickly and accurately what has changed between different input sets;
The input sheet needs quite a lot of structure to fulfil the above objectives.
Over time, we have found it helpful to separate constant inputs from series (i.e. time-based) inputs. We will come back to series inputs in the next chapter.
This chapter will go through each element of the constants input sheet structure.
Although it seems like a lot of work to set up, we never build this from scratch. When starting a new model build assignment, we always start from a pre-existing "start file" with all the model infrastructure already set up. See Chapter X for more on the Start File.
Cases, base cases, scenarios and sensitivities.
Base case, sensitivities and scenarios are frequently used terms in financial modelling.
For this book, I'm defining them as follows:
- Case: A set of inputs representing a particular view of the business or project.
- Base case: A case that is the current "best view" of what will happen in the business or project. Note that it's possible to have multiple "base cases". For example, we might be looking at two different financing options and have a base case for each financing option.
- Scenario: A variation on a case based on changing a subset of the assumptions.
- Sensitivity: A variation on a case based on changing one assumption.
Applying these definitions in our model.
- Each of the columns from K to R in our example represents a case.
- Columns K, L, and M are base cases.
- Columns O through R are for scenarios based on a selected base case.
Spacer columns
We have set up this example input sheet to store seven cases: three base cases and four scenarios. There is nothing magic about these numbers. We need to be able to flexibly add base cases or additional scenario cases.
Columns J, N and S are spacer columns. They are narrow and formatted with grey shading. We should insert any new cases between those spacer columns; that way, the functions that make the case selection work will continue to operate.
Comments
Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.