View More Content

Excel data tables

Excel data tables

In Excel, you can create data tables to see how different input values affect the result of a formula without having to re-type or copy the formula for each input value. This adds a new dimension to Excel that is very powerful but takes practice to get used to.

Applications of Data Tables in financial modelling for project finance

In project finance, data tables are useful in doing scenario and sensitivity analysis

Data tables can store the results of many different scenarios in one table. This is so that you can compare the results of a number of permutations to our original data together in one table. The results are produced in a table form in your workbook in a location specified by you.

This tutorial will discuss on how to create the following data table:

  • Creating a 1-D data table
  • Creating a 2-D data table
  • Using a data table for scenario analysis
  • Working with data tables
  • Other interesting applications

An accompanied workbook was created to illustrate how to create the data tables.

Creating 1-D data table

You can create one-variable or two-variable data tables, depending on the number of variables and formulas that you want to test.

Create a 1-D data table/1-Variable data table if you want to see how different values of one variable in one or more formulas will change the results of certain output cells.

A 1-D data table can be set up in two different ways: row oriented or column oriented.

Column-oriented data tables

For example; you would like to know how debt service cover ratio (DSCR) can be affected by sensitising the interest rates variable.

To find out how to create the data table, without having to retype or copy the formula for each value of interest rate, refer to screenshot 1.

By flipping every aspect of this column orientated example to go across the page and to select the “row input cell” in the data table form you can quickly have a horizontally laid out table.

Excel data table - one dimension
Screenshot 1:  1-D data table in Excel

  • In the accompanying file create a template for 1-D Data Table as shown in screenshot 1
  • Select the range of cells that contains the formulas and values that you want to substitute
  • On the data tab in the menu click table to activate the dialogue box
  • As the table is column-oriented, enter the cell reference in the column input cell box. Note that the cell reference must be an input and should not contain any formula
  • Using the example shown, the input cell is G56
  • Press OK and F9 to calculate the table
  • For live example, please refer to the attached Excel workbook

Creating a 2-D data table in Excel

A two-variable data table uses a formula that contains two lists of input values. The formula must refer to two different input cells.

For example, you would like to know how the minimum DSCR will be affected by sensitizing two variables, i.e., the interest rates and CPI variables.

When creating a two-variable table, one series is entered into the first column of the table and the other into the first row of the table.

The bullet points below refer to steps to create the data table:

  • Create a template for 2-D data table as shown in screenshot 4
  • Select the range of cells that contains the formulas and values that you want to substitute
  • On the data tab in the menu click table to activate the dialogue box
  • In the Row input cell box, enter cell G93 (CPI)
  • In the column input cell box, enter cell G92 (interest)
  • Press OK and F9 to calculate the table.

After you create your data table, you might want to change the format of the result cells.

Excel data table in two dimensions and conditional formatting
Screenshot 2: Creating 2-D data table with conditional formatting

Using data tables for scenarios

Firstly refer to our free tutorial entitled ‘How to build a scenario manager’. Using this structure, a data table will allow us to compare the results of scenarios all in one table. For example, you would like to know how the funding and DSCR would be affected by the various scenarios.

  • In the first row link in all the actual base case figures such as construction funding and DSCR from the model
  • In the first column input vertically the numbers of scenarios (one to eight in this example)
  • Select the entire table area including the numbers running vertically
  • On the data tab in the menu click table to activate the dialogue box
  • Input the ‘switch’ (cell D7) we created earlier in the scenario manager in the column input cell box

The way the data table works is to sequentially insert each “input value” into the “input cell”, as the input cell drives all the calculations throughout the model.

financial modelling of scenarios using data tables
Screenshot 3: Creating scenario data table suitable for financial modelling scenario analysis

Working with data tables

Each line in a 1D data table represents the model being calculated once with the relevant column reference as the applied input value. As a word of caution the entire table is recalculated whenever a worksheet is recalculated. For larger models this could be a problem.

There are two readily performed solutions that can be applied to accelerate the calculation of a worksheet that contains a data table:

  • Change the calculation option (tools – options – calculation) to automatically recalculate the worksheet but not the data tables (to manually recalculate your data table then press F9)
  • Construct a macro which deletes the active area of the data table and another one which ‘rebuilds’ the table -this is achieved by recording a macro or a more elegant version can be prepared in a similar way but by range naming the relevant areas

Other interesting applications

Although you cannot edit a section of a data table the results can be:

  • Graphed
  • Conditionally formatted
  • Used as the input to other arrays of formula, for example, to calculate geared / ungeared project returns

Rickard Wärnelid
by Rickard Wärnelid

Rickard's passion for financial modelling is built on specialist roles in the highly quantitative fields of derivatives and project finance, a career path complemented by an academic grounding in engineering physics. Born in Sweden and with global consulting and leadership experience, Rickard is an internationally recognised authority, speaker and thought-leader on the organisational benefits of best practice financial modelling.

Contact Rickard Wärnelid

view all