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.

Data tables in 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, 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) 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.

Screenshot 1: Template for 1-D Data Table

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

Screenshot 2: Creating 1-D Data Table

Creating a 2-D data table

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 be affected by sensitizing 2 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.

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.

Screenshot 4: Creating 2-D Data Table

Using a data table for scenarios

Firstly refer to our free tutorial titled “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 e.g. you would like to know how the funding and DSCR 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 (1 to 8 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.

Screenshot 5: Creating Scenario Data Table

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

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...