Unlocking Full Potential of Excel Data Tables (Part 2)

This is an enhanced version of my 'Unlocking Full Potential of Excel Data Tables (Part 1)' post

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 synchronise 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

This Best Practice includes
1 Excel file, 1 PDF file

Acquire business license for $15.00

Add to cart

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

None

Reviews

  • Be the first to review this Downloadable Tool

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Andrei Okhlopkov

See all
keyboard_arrow_leftkeyboard_arrow_right

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

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


0.0 / 5 (0 votes)

please wait...