Excel data tables

Excel data tables can be used to output results of financial modelling, plotting one key variable against another, showing the impact on the main result. A common example might be plotting the impact of two key inputs (e.g discount rate and long term growth rate) on a key output such as discounted cash flow valuation.

Using data tables in financial modelling: a simple example

This Excel spreadsheet, shows you how to construct data tables.  You need to find the “Data” tab, then “Data Tools”, “What-If Analysis”, “Data Table”.

Instructions for constructing data tables in a financial model

If you download the Excel spreadsheet and follow the instructions,  you’ll see how to construct a data table.  Data tables can be a little tricky to set up and get working (they need to be set up exactly like the one in the example).  Once they’re set up though, they’re very easy to update.  In the spreadsheet example, cell B8 could be a complicated result in the Excel model, that many cells feed into.  Cells B6 and B7 are key input cells that are used elsewhere in the model.

Special case: the single variable data table

Do you remember when we were running scenarios through our imaginary model? We had three sets of assumptions which we could run through the model by changing scenario number in the drop-down box. This is great. All the assumptions are clearly visible and auditable. We can change scenarios at the flick of a switch. But the model only presents us with one set of outputs at a time. If we want the high, base and low case outputs displayed side by side we need some kind of solution, preferably one that doesn’t involve a lot of repeated copying and pasting of outputs.

Previously we looked at using Excel’s scenario manager as a potential solution to displaying all the outputs side by side. But the single variable data table could have been another, perhaps slightly more elegant, solution. Please don’t blame us that too often there’s a lot more than one way of solving a problem in Excel!

Please click on the image below to download the single variable data table. If you follow the instructions in the spreadsheet you should be able to recreate the single variable data table. The data table will output revenue side by side for all three scenarios.

Single variable Excel data table

Financial modelling course tips: data tables

Data tables are great for highlighting the impact of key variables on a major modelling result.  But a data table only varies two inputs at a time.  Data tables lose much of their power if you run many tables across many variables – a great huge batch of data tables is not going to help focus anyone’s mind as part of decision making.  Implicitly data tables require  judgement and skill on the part of the financial modeller, as it’s generally the modeller who decides which variables to use in the data table.

About this online financial modelling course material

You’re looking at a free online material covering Excel functions – extracted from our regular modelling course training.



...join the conversation...

Your email address will not be published. Required fields are marked *