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.
Formulas discussed: Charts, Offset, Ranges
The workbook used throughout this post is available here.
Charts can be a wonderful way of presenting data to an audience – just look at the popularity of infographics. Sometimes, particularly with line graphs, you might want to change the start or end point of the graph, either so you can zoom in on a subset of the data, or to remove some dodgy-looking bits. An example of this would be where you have a model that includes an upfront construction period, with certain things not being calculated until construction completion, and the completion time might not be fixed. In this circumstance, you don’t really want to display all those blank or zero cells (see workbook example 1).