Data Tables is a powerful tool to help you analyze sensitivities in Excel financial models.
A conventional data table is a one- or two-dimensional table showing the results of a calculation (model outputs) at various model inputs in the headings of rows and/or columns of the data table. In the attached files we will study the essential principles of data tables.
But the abilities of data tables stretch far beyond that. In the attached files I have collected some advanced methods of using data tables in the sensitivity analysis.
Data Tables Version: Free Template Introduction
Explains the basics of data tables and shows how to create them. Then it demonstrates how to perform the following analyses using data tables:
– a summary table showing the key metrics by scenario
– a high-level cash flow summary for each scenario
– a “tornado” chart showing the effect of every input change on the model’s outcome
Data Tables Version: Full Excel Template
This is an extended version of the file which shows how to:
– link several data tables to the same model;
– locate data tables, their input cells and the model on different sheets
– add a switch (on/off) to speed up calculations;
– draw a waterfall chart.
The tables and charts are updated as you change any input. This is especially effective at a meeting or presentation.