Spreadsheet skills: Being Sensitive with data tables

Liam Bastick regularly writes for CIMA. His article  “Spreadsheet skills: Being sensitive with data tables” with an accompanying workbook was recently published on the CIMA website and illustrate how to effectively use 1-D and 2-D Data Tables to conduct sensitivity analysis for NPV.

What is sensitivity analysis

Sensitivity analysis is an important technique in financial modelling, it enables us to analyse how changing certain input variable(s) affects the output variable. This enables us to create more realistic and useful models, as we are not constrained to single-point estimates but instead take into account potential variability in our estimates of input variables.

For example, in executive summaries, we might want to show how changes in a particular input affect outputs.

Sensitivity analysis in Excel

Excel contains various inbuilt features which allow us to do this, including Data Table, Scenario Manager and Solver. However, it is important to keep models as simple as possible, which means that if the same functionality can be achieved in a simple, straightforward fashion, then it should be done in that way.

1-D Data Table to calculate NPV value

In a typical NPV setup, we would create a model where we have a single discount rate and multiple future cash flows, so that we can calculate Net Present Value (NPV). In this simple example, we use a single discount rate of 8%. But, is it reasonable to base our model on just a single-point discount rate estimate? Perhaps a better setup would be to take into account multiple possible discount rates and, in each case, calculate the resulting NPV value.

This can be achieved with Data Tables, which allows us to see how small changes in discount rate impacts the NPV value. The Excel workbook which accompanies Liam’s article includes this example, as well as a more detailed example where we illustrate not only the effect of discount rate on overall NPV, but also the effect on the PV of individual future cash flow, which is useful when we require more detail.

Data Tables - Sensitivity Analysis - 1 Dimension

2-D Data Table to calculate NPV value by changing two variables

In the above example, we examined the effect of a single variable (discount rate). But what happens if we want to examine the effect of two variables at the same time, for example, the effect of discount rate and the effect of number of periods. We want to be able to answer the following question: “What is the NPV of our project over x periods with a discount rate of y%?”

This warrants the use of 2-D Data Tables as we require two-variable sensitivity analysis. Again, Liam’s workbook shows how this can be done.

Data Tables - Sensitivity Analysis - 2 Dimensions

The drawbacks of using Data Tables

Data Tables are highly useful as they save us time compared to manual approaches. However, Data Tables do have limitations that should be considered:

• We must hardcode the variable inputs that we wish to flex. This limits the flexibility of the Data Tables which we construct, and can be a significant limitation if we intend to change Data Tables on a regular basis.
• We should generally put inputs and outputs on the same worksheet as the Data Table. This imposes constraints on the way that we structure our workbooks.
• Calculation can slow down dramatically when using Data Tables. This can be frustrating, especially when we are dealing with large data sets and create multiple Data Tables. Liam’s sample worksheet, however, illustrates how to work around this using ‘On / Off’ switches next to the Data Tables themselves to emulate the effect of setting Excel’s recalculation option.
• We are limited to flexing only one or two variables with Data Tables. If more variations are required, you should consider Excel’s Scenario manager or the Solver add-in.

Work effectively with 1-D and 2-D Data Tables– Download worksheet

Reread the full article, “Spreadsheet skills: Being sensitive with data tables” on the CIMA website.

The article includes screenshots to illustrate 1-D and 2-D data tables in sensitivity analysis for NPV, and shows you how to use the Data Table dialog box. We’ve also included some tricks with custom number formats when working with Data Tables.

Here is the link to the accompanying spreadsheet which illustrates the use of 1-D and 2-D Data Table for sensitivity analysis involved in NPV calculations.

COMMENTS