Unlocking Full Potential of Excel Data Tables (Part 1)
  • Unlocking Full Potential of Excel Data Tables (Part 1)
  • Unlocking Full Potential of Excel Data Tables (Part 1)
Originally published: 01/03/2019 11:21
Last version published: 09/12/2019 23:09
Publication number: ELQ-46892-4
View all versions & Certificate

Unlocking Full Potential of Excel Data Tables (Part 1)

The model explains how to show models outputs under different scenarios simultaneously.

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

I have recently found out 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. I am going to share this approach through this workbook. Each list of it contains an illustration of a specific method of analysis with details and comments on each of them.

As an underlying example I have created a very simple demonstration model dealing with a real estate investment project. This example is entirely fictitious; yet, I have tried to make it close to reality . For the sake of simplicity, certain aspects of real estate business are ignored - therefore, you should not rely on it as a practical tool for evaluating investments and you are certainly welcome to contact me should you have any questions.

The first example shows key project parameters (IRR, net return etc) for each of the three scenarios. Any change of inputs in any scenarios is immediately reflected in the calculated results of the corresponding scenario.

The second example shows a brief executive summary for every scenario (an aggregated cash flow summary and key results). Again, this table is fully interactive and changing an input immediately reflected in the summary.

The third example shows how to build a tornado chart which is widely used in sensitivity analysis.

To access the advanced version of this file: https://www.eloquens.com/tool/QkbvsGeb/finance/sensitivity-analysis-excel-templates-and-methods/unlocking-full-potential-of-excel-data-tables-part-2

This Best Practice includes
1 Excel file, 1 PDF file

Andrei Okhlopkov offers you this Best Practice for free!

download for free

Add to bookmarks


Further information

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

Financial models with multiple scenarios


keyboard_arrow_downShow all


More Best Practices from Andrei Okhlopkov

See all

Any questions on Unlocking Full Potential Of Excel Data Tables (Part 1)?

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

4.8 / 5 (40 votes)

please wait...