Overview

Scenario & Sensitivity Modelling

In this guide, we build a simple break-even model to work out how many units of a particular item we need to sell in order to break-even (that is, units needed to cover all variable and fixed costs).

Along with the number of units, given that we also have an average production no. per month, we can also estimate what the payback period in years would be to reach this break-even amount.

Payback Period

With basic model in place, we then understand how the payback period varies. We do this by varying the following two variables in the model: (i) the rate of Tax at Point of Sale, and (ii) the rate of Tax on Pre-Tax Income

Sensitivity Analysis

We then do a sensitivity analysis, to see how the payback period varies, but by varying only one variable at a time - using a Gravity Table, and Tornado Chart - in order to see which input the model is most sensitive to.

Scenario Analysis

Finally, we do a scenario analysis, to see what the payback period looks like for different configurations of input variables - by varying a whole set of variables at a time, rather than just looking for the most sensitive input variable.

Template

Though we won’t run through every formula or explain every detail of how the workbook is constructed, the full template can be downloaded at the end of this guide.

Click ahead to find out more!