Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
  • Sensitivity Analysis Excel Model - using Data Tables (short version)
Originally published: 01/03/2019 11:21
Last version published: 05/03/2019 17:02
Publication number: ELQ-46892-2
View all versions & Certificate
certified

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

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

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

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/sensitivity-analysis-excel-model-using-data-tables-full-version

This business tool includes
1 Sensitivity Excel Model file

Andrei Okhlopkov offers you this business tool for free!

download for free

Add to bookmarks

Discuss

Further information

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

Financial models with multiple scenarios

Reviews


keyboard_arrow_leftkeyboard_arrow_right

More tools from Andrei Okhlopkov

keyboard_arrow_leftkeyboard_arrow_right

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

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

please wait...