Data Table:

The tool Data Table is one of the 3 analytical tools from the Excel What If Analysis kit. Scenario Manager and Goal Seek are the other two tools available.

Data Tables are usually used when you need to sensitize 2 variables of a specific calculation you have in your spreadsheet. A very common use of Data Table is done in financial projections such in the following example.

Example (Financial Projection)

In this forecast we have 8 specific inputs which are feeding the Profit assessment below.

These initial set of inputs are known as Base Case assumptions, and they lead (at this stage) to a profit calculation which varies from $ 323 k in Year 0 up to $ 350 k in Year 4. This profit calculation then impacts the amount of dividends an investor can expect to receive. Investor then use that dividend amount to calculate the return of their investment (i.e. 20% in this Base Case scenario).

Data Table – Identifying the key parameters

Before applying the Data Table in your spreadsheet make sure you first define which pair of assumptions you want to stress in your assessment and the specific output you want to analyze. Also, make sure the Data Table is created in the same spreadsheet where your inputs are located, otherwise you may get inconsistent results.

In this example, we are interested in understanding how the Internal Rate of Return or IRR (cell C23) will change as a result of variations in different pair of inputs. Regarding the inputs themselves, let`s assume we want to stress variations of +5%/-5% over the Base Case assumptions in the following 3 pair of inputs:

• Investment x Payout
• Fixed Cost and Tax Rate
• Initial Price x Inflation

Data Table – How to build the templates

So, let`s start the works in first data table template (range I3:N8). Here you need to place the “Investment” in the row header of the table (range J3:N3) and the information “Pay-out” in the column header (range I4:I8). So, follow the steps below:

1. The Base Case assumption related to Investment (i.e. $ 300 k) should be inserted in the middle of the row header (cell L3) as that`s the mid-point of our range. An important thing to highlight here is that you cannot link this cell directly to the input on the top left part of your spreadsheet, otherwise you may get inconsistent results when you run your Data Table later on. So, for cell L3, just type the number 300,000.

2. Now you need to calculate variations of +5%/-5% over this Base Case assumption just so the entire row header (i.e. range J3:N3) is filled with the alternative inputs.

3. Let`s now take a similar approach to the Base Case assumption Payout and insert the number 25% in cell I6 as that`s the mid-point of the column header of your data table.

4. Now calculate the variations of +5%/-5% over this Base Case assumption just so the entire column header (I4:I8) is filled with the alternative inputs you need. Your table at this stage should look like the one below:

5. Now repeat the exact same process for the remaining data tables (Fixed Cost x Tax Rate (Range I10:N15) and Price x Inflation (Range I17:N22)). At the end you should have your tables structured as per image below:

Data Table – How to calculate it

After organizing your data, the calculation itself becomes straight forward. For the first data table (range I3:N8), follow the steps below:

1. The corner of that table (cell I3 in this case) needs to reflect the output you are trying to stress which in this case is the IRR initially calculated in cell C23. Therefore, link cell I3 to the cell C23 and press CTRL + ENTER.

2. Now press CTRL + A to select the entire template and in the ribbon go to What-if-Analysis > Data Table. The menu below will show up which requires 2 inputs: Row Input Cell and Column Input Cell.

3. We defined the variable “Investment” to be in the Row Header of the template. Therefore, the Row Input Cell must be the Base Case assumption related to the variable “Investment” which is in cell C3. So, select that cell for this field.

4. We defined the variable “Pay-out” to be in the Column Header of the template. Therefore, the Column Input Cell must be the Base Case assumption related to the variable “Pay-out”. This is in cell F6. So, select that cell for this field.

5. With both fields complete, just press ENTER and Excel will calculate alternative results of IRR for different combinations of the inputs “Investment” and “Pay-out”, everything else the same.

6. Now repeat the exact same process for the remaining data tables (Fixed Cost x Tax Rate (Range I10:N15) and Price x Inflation (Range I17:N22)).

Note: Be careful in steps 3 and 4! You need to make sure the fields “Row Input Cell” and “Column Input Cell” have the correct inputs. Your tables should look like these:

Testing and presenting your results

As a test your calculations are consistent, check what IRR you have in the intersection of the Base Case Assumptions. If the intersection number is equal to 20% (i.e. Base Case IRR in all of the 3 tables), then your calculations are correct!

As a final touch, you need to make sure your results are presentable nicely. For example, anyone not familiar with the concept of “Data Tables” in Excel might struggle to understand the number in the “corner” on those tables. We know this is just to support the overall calculation done in the table. Therefore, use a white font colour for that number before copying the results to any report. This way, that number will not be visible.

In addition to that, while reporting results it`s always a good idea to make them as transparent as possible. The tool conditional formatting can help you a lot with that.

Conditional Formatting – Complement to your calculations

Follow the steps below:

1. Select this numerical area of your first Data Table, then go to Home > Conditional Formatting > Highlight Cells Rules > “Greater Than”.

2. In the menu that pops up, choose a threshold of 20% for example. Select the standard green colour available and press ENTER.

3. While your selection is still active, go back to the same Conditional Formatting section. However, this time choose the option “Less Than”. And let`s define the threshold of 15% and keep the default red color chosen.

4. Finally, go back to the same conditional formatting section but this time select the option “between”. Here, select as thresholds the numbers 15% and 20% and choose the yellow background color this time and press ENTER.

5. Copy the formatting of the first data table and apply it to the other 2 Data Tables.

You should then have all your Data Tables organized as per image below: