Sensitivity Analysis Excel Model - using Data Tables (full version)

This is an extended version of my 'Analysing sensitivities with data tables' file with significant enhancements

Description
Data tables represent a powerful tool for sensitivity analysis in Excel. A conventional data table is a one- or two-dimensional table showing the results of a calculation (model outputs) at various model input values inserted into the headings of rows and/or columns of the data table. Excel Help provides a detailed description of how data tables are created and used (please check it out if you are new to data tables).

In this workbook I am going to share and explain a non-conventional way of using data tables which allows to extend a traditional sensitivity analysis. This method relies on the ability of data tables to evaluate a model many times at various input assumptions. The workbook consists of a simple demo cash-flow model and a dashboard showing its sensitivities to various inputs. The sensitivities are demonstrated through the following means:

a) a summary table showing the key metrics (IRR, money-on-money ratio, net return, etc.)
b) a high-level cash flow summary for each scenario (showing cash flows from investment, dividend income, debt service, exit proceeds)
c) a "tornado" chart which shows the effect of every input on the model's outcome
d) a "waterfall" chart demonstrating the portion of every input's effect in the total change between two scenarios

Data tables make the above visuals fully interactive - you can change one of the inputs in any scenario and the effect of this change gets reflected immediately in the tables and charts (all done without macros).

From a technical standpoint, I will explain the following tricks using data tables:

a) how to synchronize several data tables having different triggers of inputs with one data source
b) how to get around the need for having input cells on the same sheets with the data table
c) how to make an on/off switch for the data tables as an alternative to switching to the semi-automatic calculation mode in Excel

1 Sensitivity Excel Model file

Discuss

Further information

Objectives

The tool allows to see the outcomes of a financial models simultaneously under several scenarios

Use it if

Financial models with multiple scenarios

None

Reviews

• No review yet!

Any questions on Sensitivity Analysis Excel Model - Using Data Tables (full Version)?

The user community and author are here to help. Go ahead!