Financial Training Courses
Self Referencing IFs: A Compelling Alternative to Excel's Data Table Feature - January 2012
- Categorized in: Technical Updates
A traditional approach for presenting scenarios in Excel is to use Excel’s Data Table feature. However, Data Tables present a number of issues in financial models:
- Speed: a model using multiple data tables will run slowly because the Data Table feature is memory intensive. For example, a data table sensitizing 5 row inputs and 5 column inputs will calculate 25 times per iteration in order to present its data.
- Location: a Data Table must be located on the same sheet as the placeholder cells (the assumptions specified as the row and column input cells of the data table command). These placeholder cells are often located on a separate sheet from the desired location of the Data Table.
A great solution to these issues is to use self referencing IF statements instead of the Data Table feature. The discounted cash flow valuation model extract below shows a sensitivity table using self referencing IF statements in cells C27:E29 (click here to download this file). This particular sensitivity table shows various Enterprise Values based on different growth rates and discount rates.
The self referencing IF formula in cell C27 returns the Enterprise Value located in cell C22 if the discount rate and growth rate sensitivity values (located in cells B27 and C26 respectively) are the same as the discount rate and growth rate assumptions in the main model (located in cells C3 and C4 respectively). If these values are not the same, the self referencing IF statement returns itself.
Self referencing IF statements create intentional circularity, so iterations need to be running for the table to function correctly (consider adding a circular switch to the self referencing IF). Assuming the absolute references are included in the formula correctly, the IF formula can be copied across and down to populate the entire sensitivity table.
Finally, each set of sensitivity inputs needs to be run in the model so that the sensitivity table is populated correctly. In this example you would have to enter 10% in cell C3, followed by entering: 1%, 2%, 3% in cell C4. You would then enter 9% in cell C3, followed by 1%, 2%, 3% in cell C4. You would then enter 8% in cell C3, followed by 1%, 2%, 3% in cell C4. This updating needs to happen whenever any of the inputs in the model change.
In summary, sensitivity tables using self referencing IF statements provide a solution to the speed and location issues outlined above. The downside of this approach is the circularity issue and also having to run the sensitivity inputs in the table.