Sensitivity analysis in spreadsheets

  • Tagged with:
  • Data tables,
  • Lookups,
  • Scenario Manager,

Also called scenario analysis and what-if analysis, perhaps the most useful feature of spreadsheets is their ability to run multiple versions of key assumptions through a model and present the effect on the key outputs.

Here’s the scenario (excuse the pun) — let’s say you’ve built a whiz-bang model to help you decide whether it is worth making a particular investment. Just like every good spreadsheet model, it’s at the mercy of its weakest assumption(s) — and you can think of at least 4 or 5 key assumptions that you know are important, but which are uncertain. So, you go about plugging in different values for each of these, and observing the impact on the 2 or 3 result cells that you care about. What you’d really like is a neat and easy way to set up, view, and play around with, various scenarios of your investment model.

In this post, we’ll explore a few ways to undertake this kind of sensitivity or scenario analysis in Excel, and discuss the pros and cons of each.

Scenarios tool

Excel has an in-built scenarios tool which lets you specify certain cells in your model as inputs (called “changing cells”) and other cells as outputs (called “result cells”). These scenarios are set up and maintained via the Scenario Manager. Depending on which version of Excel you have, this can be found in the menu/ribbon via Tools>Scenarios (XP or earlier) or Data>What-If Analysis>Scenario Manager (2007/2010).

Scenarios Summary in ExcelIt seems like the Scenario Manager has been part of Excel since the beginning of time. Unfortunately though, it has not changed or progressed at all in the past 3 or 4 iterations of the software. Scenarios are set up and maintained via a dialog, and once set up you can quickly produce a report summarising the key inputs and outputs for all of your scenarios.

Apart from the hideous default formatting (see right), this summary is not a bad way to view the results. However, it is a real shame that you cannot interact with your model through this table. For example, it would be really nice if you could change the values of the “changing cells” directly in this table, and have the results recalculate automatically.

Overall, we find Excel’s Scenario Manager to be a kludgy and highly unintuitive way to perform sensitivity analysis.

Data tables

Excel’s Data Tables allow you to see the effect on key output cells when various values of key input cells are run through the model. Data Tables are quite easy to set up, they are created and exist directly on your worksheet, and they recalculate automatically any time your model’s logic changes. For these reasons we really like data tables. But (and there’s always a but) they are also quite limited.

Data Tables come in 2 flavours. The first allows you to change only 1 input and see the effect on one or more output cells. The second allows you to change 2 inputs simultaneously but you can only see the effect on 1 output cell. Sure, it’s hard to visualise multiple inputs and multiple outputs in a simple way, but being restricted to these 2 types of tables for scenario analysis is too limiting. What’s more, Data Tables slow down the performance of your model. The bigger these tables get, and the more of them you cram into your spreadsheet, the longer the model takes to recalculate. (Fortunately, Excel recognises this and provides a way to turn off automatic recalculation for data tables.) Finally, the Data Table and the input cells it links to must be on the same worksheet. This is annoying as it often means you have to restructure your model to get the Data Tables to work at all.

2 kinds of Data Tables

Lookups

The final method we’ll look at for scenario analysis is the Lookup approach. This is where you set up your scenarios manually and use a single cell to tell your model which scenario to use. It is normal for this scenarios table to be placed on a separate worksheet to your core model. Using this approach, the key inputs to your model are now entered in this scenarios sheet, and the parts of the model that use these inputs link back to this sheet (see cells D4:D8 in the example below). The active scenario is determined by the value entered in a single cell (D2 below) — effectively telling the model which set of inputs to use.

The exact form of lookup function that you use to achieve this is matter of personal preference. We prefer using INDEX and MATCH for our exact lookups, but there are many choices, including: IF, SUMIF, VLOOKUP, LOOKUP, CASE, OFFSET, etc. (For more on this, you might want to check out our earlier post on lookups.)

Scenarios Using Lookups

The main challenge with this approach is being able to populate the key result cells for the scenarios automatically (see cells with ?). There are really only 2 good ways to do this. The first is a technique we have used for a long time, which is to write a macro to loop through all the inputs, one column at a time, inserting them into the model, and calculating and pasting the results into the appropriate key results cells. The advantage of this approach is that the scenarios page functions independently of the model itself, that is, the inputs can be changed either in the scenarios page, or in the model — they are not physically linked. This approach works well, but it takes a fair amount of VBA coding expertise. It also has the disadvantage of being somewhat opaque e.g. “I clicked this RUN MACRO button. What the hell just happened?”.

Data Tables for ScenariosThe second approach is to use a 1-input-multiple-outputs Data Table to recalculate the model for each scenario and populate the key results. (If you’d like to learn a bit more about this, check out this tutorial by NavigatorPF.)

The advantage of this approach is that the Data Table can recalculate automatically every time something is changed in your model. This can be a problem for big models (as discussed above), but automatic recalculation for Data Tables can be turned off. The bigger problem with this method though is that your key inputs now need to live in a separate worksheet which becomes the source for some (but not all) of your model inputs, whereas we prefer to have our inputs entered in context in the model itself.

Ideally the scenarios sheet would be a simple plug-in to the model. If we could maintain the independence of the model and the scenarios sheet, we could feasibly have one model, and many scenario sheets which can pass the model the desired inputs and retrieve the calculated results. This kind of functional decomposition or modularity is lacking from most modern spreadsheets, but something that would be enormously powerful and beneficial.

1 Comment so far Join the Discussion

  1. Thomas

    15th July 2012 at about 12am

    A similar Excel Add-in for Sensitivity Analysis for up to 20 variables can be found at:
    http://www.life-cycle-costing.de/sensitivity_analysis/
    and as the URL says, it had been developed for LCC assessment, but is applicable to all kinds of spreadsheet calculations.
    It has been tested on Excel 2010, but should work for 2007 as well.
    It is free to be used in private or commercial projects.

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

The top resource for online casino gambling and internet gaming strategies, rules, tips.