Unlocking Full Potential Of Excel Data Tables

This publication explains advanced methods of using data tables for sensitivity analysis in financial models

, ,
, , , , , , , , , , , , , , , ,

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 show 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.

You must log in to submit a review.