Charts and Dashboards: Data Tables - Part 1

Welcome back to our Charts and Dashboards blog series.  This week, we’ll cover the uses and types of Data Tables.

Sometimes, you may want to flex one or two variables to see how these changes in input affect key outputs.  This may be performed using Excel’s built-in Data Tables.

Data Tables are ideal for executive summaries where you wish to show how changes in a particular input affect a key output.  However, you should use them sparingly.  If you can achieve the same functionality without using Data Tables, then you should do that:

In this illustration, the key output revenue has been given in cell D5.  We want to summarize what happens if we increase (“flex”) this figure by a given percentage, with the inputs specified in cells D11:D20.  This can be simply computed by using the formula  

in cell D11 and simply copying this calculation down.

Data Tables should really be used when such simple calculations are not possible, and you want to flex one variable (known as a “one-variable” or “one-dimensional (1-D)” Data Table) or two (known as a “two-variable” or “two-dimensional (2-D)” Data Table).

Let’s take a look at one-dimensional tables this week.

1-D Data Tables

This is best illustrated using another example:

It’s not vital you understand what this spreadsheet is doing.  It is essentially using inputs in cells F5 and F10:K10 to generate an output in cell K24, as it calculates what cash received in row 10 would be worth now if interest were 8.0% per period (known as the “Net Present Value” (NPV)).

Therefore, with a simple Net Present Value calculated for a total of six periods (0 to 5 inclusive), the output for a discount rate of 8.0% (cell F5) is +$9,482 (cell F24).  But how has this table been constructed?

We’ll cover this next week, when we go through the step-by-step process of constructing a one-dimensional Data Table.

That’s it for this week.  Come back next week for more Charts and Dashboards tips.