Excel data tables are pretty sweet, right? They enable you to perform sensitivity analysis with minimal effort. (For those of you who haven’t dealt with Excel data tables before, here’s a quick primer). For those of you enamored with data tables, I’ve got bad news: you could be doing it better.
If you’re doing something quick ‘n’ dirty, Excel data tables are okay. For anything more complicated, or any analysis that will be a recurring piece of your project / presentation, you shouldn’t use them.
This article will show you a better way to perform sensitivity analysis - leading to clearer, more auditable models.
This article will build off the prior two articles, so if you haven’t worked through those, please go to square 1.
Below is a summary of the main problems with data tables.
Variables sensitized using Excel data tables must be hard-coded. Oftentimes, however, the variables that you want to sensitize in a data table are the same variables you would like to include in your cases (covered in the prior article - #2 in the list above).
In the short-run, this isn’t a dealbreaker. You can replace the linked variable with a hard-code, then run the data table, paste your outputs to PowerPoint, and then finally relink the variable. In the long-run, however, this becomes quite tedious, especially when sensitizing multiple variables. Furthermore - late at night, under time pressure - you are more likely to forget one of these steps and make a mistake. Perhaps a subset of the outputs you pasted to PowerPoint are wrong - hopefully, you catch the mistake and just have to repaste.
Worse case: you wake up to a nasty email from your MD.
Worst case: you send a wrong output to the client and get called out.
Switching back and forth between hard-codes and linked variables is not a good system.
When you’re working on a bigger model and add multiple data tables, you will notice a slowdown. Even if you set calculations to manual vs. automatic, data tables cause a big performance hit.
How do you audit a data table? If it’s a two-way data table (most common), you have to manually set the two variables (in your model) and then trace through all the logic to confirm that everything is working properly. This is tedious at best, and has the same drawbacks discussed above under Hard-codes.
That’s a mouthful, huh? Stay with me. Self-referencing if statements are a thing of beauty. Self-referencing if statements are one of those tells that separate a great financial modeler from a competent one.
A self-referencing if statement is an if statement that references itself. Here’s a simplified example:
cell = if(criteria, exterior value, this same cell)
What’s going on here?
Basically, if the given criteria is True, show the linked exterior value; if not True, then just show the value already displayed in this cell.
Another way to think about it is a snapshot. If the criteria is True, take a new snapshot. If not, just show the last snapshot.
Building off the last article (which showed how to construct cases), we can combine cases with self-referencing if statements to build better sensitivity tables.
Here’s a simplified example:
We’re creating a sensitivity table for a merger model. We’re showing how the % accretion changes as a function of the % offer premium and % stock consideration.
Step 1: We can create a case for each data point in the sensitivity table. Ex:
20% premium w/ 25% stock, 50% stock, 75% stock
25% premium w/ 25% stock, 50% stock, 75% stock
40% premium w/ 25% stock, 50% stock, 75% stock
Step 2: Create the sensitivity table output. Instead of a traditional data table, in each cell we’ll use a self-referencing if statement.
cell = if (RUNNING CASE = THIS CASE, model accretion, this cell)
How does this work?
Only the running case updates. The numbers for all other cases are frozen until you change the running case.
If this is still confusing, don’t worry. We’re going to work through a real example below.
Data tables built with self-referencing if statements solve the issues we have with traditional data tables.
Your data tables automatically update as you change cases. The only hard-codes are the referenced case numbers. You don’t have to switch back and forth between hard-coded and linked variables.
Self-referencing if statements don’t hurt performance. Intuitively, this makes sense. All cases, except for the running case, are frozen.
Each data table cell links to the same calculated number in your model. To audit a given case or cell, just update the running case.
Now, let’s work through an example. We’re starting with the completed Excel template from the last article. For reference, here is this article’s completed template, but we suggest working through the steps below.
Go to the MCASE tab. As you can see, we’ve already created the cases for you. We’ll be looking at % accretion as a function of % premium and % stock consideration.
Let’s name the Running Case cell (F10):
You can name a cell by highlighting the given cell and pressing CNTL + F3. Then select New, and enter the cell name.
Naming cells makes it easier to refer to them in different sheets. Now we’ll be able to refer to the running case as
NOTE: use this Excel feature sparingly. A little bit goes a long way.
Now let’s create the sensitivity output. Create a new tab called - you guessed it - Sensitivity. Then create a skeleton of the % accretion sensitivity table.
Here’s what ours looks like, but you can format it however you like.
NOTE: we like to include the financial item being sensitized, so that the data table cell formulas don’t have to reference another tab, but that’s just personal preference.
Next, let’s add the relevant case numbers in a box above the data table. If you’re not sure what I mean, see below. If you go back to the MCASE tab, you will notice that each case (4 - 23) corresponds to one of the data table cells.
Time to add the actual calculations. Remember the formula:
this cell = if(MCASE = case number, % accretion, this cell)
Try your best to apply the formula, but you can reference the completed sheet if you get stuck.
NOTE: If you get a circular reference warning, you need to enable iterative calculations. Google if you’re not sure what this means.
If you implemented the formula correctly, only one of the cells in the table should have updated. The rest should be 0. Why? Because we’ve only run one case so far (the current case). Go back to the MCASE tab and run cases 7 and 8. On the Sensitivity tab, you’ll see a couple more cells updated.
As you’re probably thinking, manually running every single case would be super tedious. Fortunately, we have a solution for that. Get ready for some Excel magic.
Go back to the MCASE tab. To the right of your case variables, leave a blank column for spacing and then add two new columns. Set the first column equal to the row’s case number. Leave the second column blank, but at the top (above all case numbers), set a cell equal to the current date.
cell = now()
Here’s what that looks like.
Now, we’re going to use an Excel data table to run all cases automatically. I know, I know. Data tables are bad, but this is one place where you need them. Typically, my workflow is:
i) Build model
ii) Design outputs
iii) Run cases (via data table)
iv) Delete data table (so model doesn’t lag)
v) Paste outputs
If you need to run the cases again (i.e., to repaste), it’s easy enough to reinsert the data table, and then delete it.
Ok, so let’s add that data table. Highlight the two columns you added (the case column and the empty column with the date on top). Then create a one-variable data table, in which the column variable = MCASE.
Shortcut for data table: ALT + A + W + T
NOTE: you can ignore the row input variable.
Here’s what that looks like.
Press F9 to recalculate the data table. All cases have been run.
Go back to the Sensitivity tab to confirm all cases updated. You should see the following:
Intuitively, this output makes sense. From the acquiror’s perspective, the most expensive offer package is 100% stock with the highest premium (40%).
Now you know the proper way to perform sensitivity analysis. This method is not just for accretion / dilution - it’s for everything. For example, using this approach, you could calculate a sponsor’s IRR under various growth and financing scenarios.
By the way, in our completed template, we’ve broken proper data table etiquette by not deleting the data table before saving down. Especially when working on a team, it is best practice to delete the data table before saving. This makes it faster to open and edit the file.