FREE
TUTORIAL
EXCEL DATA TABLES
TABLES
Introduction 1D Data Table Variable (Input
cell)
In Excel, you can create data tables to see how different Interest Rate Change: 0.00%
input values affect the result of a formula without having to Interest Rate Interest DSCR
Change Rate Average Min
re-type or copy the formula for each input value. This adds a 6.25% 1.63 x 1.35 x Direct links to
new dimension to Excel that is very powerful but takes 0.00% values / output in
0.25% the Model
practice to get used to. 0.50%
0.75%
1.00%
In Project Finance, Data Tables are useful in doing Scenario and 1.25%
Sensitivity analysis. 1.50%
1.75%
2.00%
Data Tables can store the results of many different scenarios in 2.25%
2.50%
one table, so that you can compare the results of a number of 2.75%
permutations to our original data together in one table. The 3.00%
3.25%
results are produced in a table form in your workbook in a 3.50%
location specified by you. 3.75%
4.00%
4.25%
This Tutorial will discuss on how to create the following data 4.50%
4.75%
table: 5.00%
• Creating a 1-D data table Screenshot 1: Template for 1-D Data Table
• Creating a 2-D data table
• Using a data table for scenario analysis • In the accompanying file create a template for 1-D Data
• Working with data tables Table as shown in Screenshot 1
• Other interesting applications • Select the range of cells that contains the formulas and
values that you want to substitute
An accompanied workbook was created to illustrate how to • On the Data tab in the Menu click Table to activate the
create the Data Tables. dialogue box
• As the table is column-oriented, enter the cell reference in
Creating 1-D data table the column input cell box. Note that the cell reference must
be an input and should not contain any formula.
You can create one-variable or two-variable Data Tables, • Using the example shown, the input cell is G56.
depending on the number of variables and formulas that you • Press OK and F9 to calculate the table.
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
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 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.
www.navigatorPF.com
For example, you would like to know how the Minimum DSCR be The way the Data Table works is to sequentially insert each “Input
affected by sensitizing 2 variables, i.e. the Interest Rates and value” into the “Input cell”, as the Input cell drives all the calculations
CPI variables. throughout the model.
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 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
Screenshot 4: Creating 2-D Data Table then press F9.
• Construct a macro which deletes the active area of the data
Using a data table for scenarios table and another one which ‘rebuilds’ the table. This is
crudely achieved by recording a macro or a more elegant
Firstly refer to our free tutorial titled “How to Build a Scenario version can be prepared in a similar way but by range
Manager”. Using this structure a data table will allow us to naming the relevant areas.
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 Other interesting applications
• In the first row link in all the actual Base Case figures such Although you cannot edit a section of a data table the results can
as Construction funding and DSCR from the model be
• In the first column input vertically the numbers of scenarios • Graphed
(1 to 8 in this example) • Conditionally formatted
• Select the entire table area including the numbers running • Used as the input to other arrays of formula, for example to
vertically calculate geared / ungeared project returns
• On the Data tab in the Menu click Table to activate the
dialogue box
• Impute the “switch” (cell D7) we created earlier in the
Scenario Manager in the Column input cell box.
About Navigator Project Finance
Founded in 2004, Navigator Project Finance Pty Ltd (Navigator) is the project finance modelling expert. Headquartered
in Sydney, Australia, Navigator is raising the global benchmark in financial modelling services to the project finance
sector. Navigator designs and constructs financial models for complex project financings, offers training courses
throughout the Middle East, Asia and Europe, and conducts independent model reviews of project finance transaction
models. Navigator delivers fast, flexible and rigorously-tested project finance services that provide unparalleled
transparency and ease of use.
Customers include market leaders such as Deutsche Bank, ANZ Investment Bank, Bovis Lend Lease, Oxiana, Mirvac
Property, Westpac and the Commonwealth Bank of Australia, together with leaders from the finance, mining, property,
utilities, banking, chemical and infrastructure sectors.
Navigator Project Finance Pty Ltd P +61 2 9229 7400 E enquiry@navigatorPF.com
www.navigatorPF.com
