Financial Modeling Context
Table Based Financial Modeling
Below are two income statements that produce identical results but are created using drastically different standards with vastly different capabilities.
The top income statement is from CFI's website. It is a typical, traditional financial model from a well respected organization. Like all traditional financial models, it is comprised of many formulas and hard coded actuals. The one on the bottom is a TBM output, not calculations, and single object, a PivotTable.
Those familiar with PivotTables know their advantages. They can be sliced, charted, drilled down into, and reordered with ease. They effortlessly extend interactive analytical tools to financial models.
Why TBM (Table Based Modeling)?
TBM pre-exists Excel and is still used by big systems handling problems too complex and too large for Excel. But it wasn't until Excel added tables and structured references (stable in 2013) that Excel was able to mimic those big systems. One advantage big systems have always had is access to data. That advantage was extended to Excel with Power Query back in 2010. That gave non-technical professionals access to financial systems' data sets (actuals from ERPs) and model inputs were no longer limited to what could be entered by hand. Add to that Excel's increased capacities and PC's evolving power, and Excel is now suitable for larger, more complex models.
Tables help make complex models simpler by:
Structured References - Make formulas self-documenting and, thus, far easier to understand. Cell references are cryptic. To understand what a cell reference means, we must first find it, then hope it is labeled. Good labels aid understanding. Structured Reference ARE the labels; thus, formulas using structured references have the label in them, so there is no need to go find them. This saves time and effort.
Automatic Dynamic Named Ranges - Make extending models to accommodate more items automatic. No need to create names in Name Manger with exotic formulas to determine range sizes.
New Structural Elements - Helps add granularity to our model structure. Group and name input sections and calculation blocks in tables.
Auto-Extending Formulas - Just enter as a new column and let Excel extend it to all table rows. No need to copy/paste or drag/fill formulas for repeated calculations
Formula Consistency Enforcement - Alerts us when formulas are inconsistent within columns.
Intellisense - Facilitate formula entry and eliminate reference entry errors common with cell references.
Advanced Integration - Work with data from remote systems imported via Power Query, or export to Power BI. Works seamlessly with Power Pivots.
Total Row - Eliminate entering formulas to total columns.
Filters and Slicers - Add auto-filters or slicers effortlessly.
Formatting - Automatically format headers and add (lightly) banded rows for easier following of calculation sequences.
In addition to table's features, TBM methodology adds analytics to models. The typical, very simple financial model structure, as exemplified in the CFI example, mixes inputs, processes and outputs. They all coexist, intermingled on the same spreadsheet. More advanced financial models segregate inputs from process but they still use processes as outputs.
TBM's structure separates all three major components. This allows us to optimize our processes section without regard to how it will look and without the burden of reporting functions like aggregations. The typical TBM processes section forms the one version of the truth data set over which all outputs are built. This provides these advantages:
Ensures all outputs remain in sync with each other.
Offloads the burden of reporting functions to outputs
Opens the door to interactive analytics both in Excel or in Power BI.
Adding analytics to TBM is second nature (easy). This is because our final calculations are already in a form that PivotTables love and PivotTables can be highly interactive. Users can sort them and filter them with slicers without any impact on the models calculations. PivotTables also offer drill down with, and without the Data Model's hierarchies. PivotTables also seamlessly support Pivot Charts enabling at-a-glance insights into our model's data.
Conclusion
Traditional financial modeling is designed for simplicity which is great as long as models are relatively simple. TBM is designed for simplifying complexity. As models become more complex, and demand for analytical tools over our models increases, TBM shines. In follow-on articles I will be sharing more complex TBM examples and explaining their application, implementation, and benefits.
Until then, let me share the model these screenshots came from (link at bottom). I hope you will provide your thoughts, positive, and especially negative, because it is only from disagreement that the potential for learning comes.
Click to download: BXL 3 Way Model for Comparison
