Multidimensional Table Based Modeling
Introduction to Multidimensional (MD)
Table Based Modeling (TBM)
Table Based Modeling (TBM)
This series will explore five different table based approaches for solving multidimensional models. They will include: using Excel formulas; adding the Data Model; Using Power Query formulas; Dynamic Arrays, and LAMBDA. Links to these pages are in the table labeled Links in this series at right.
Multidimensional (MD) modeling happens when we have several categories (dimensions) of things that relate to each other. Each dimension can have one or more items (instances). Each instance will have one or more values (attributes) related directly to that instance. In some cases, dimensions, when combined (associated) will have attributes relating to the combination such as where an account intersects a period there is a balance.
Any Excel modeler can handle two dimensional modeling. The typical dimensions are accounts and periods. Modelers typically run the account dimension down the left side of the model and the period dimension across the top. Where accounts and periods intersect are inputs or calculations. But how would you handle, three, four, or more dimensions?
This was the question researcher Paul Mireault had so he issued a multidimensional modeling challenge to financial modelers in order to research how we tackle the problem. In Paul's challenge the dimensions are: Products (P), Market Sectors (S), Distribution Regions (R), and Months (M). The intersection of these dimensions is MSPR unit sales (MSPR means Month, Sector, Product, Region).
Links in this series
Introduction to MD TBM
Paul provided detailed specifications for this project. Below is an overview of how he envisioned the various elements interacting with each other.
Below is a simpler version of the above with master table inputs (white boxes) combining to feed association inputs (copper boxes) and calculations (teal boxes).
Traditional Spreadsheet Approach
Traditional financial modeling is in two dimensions: accounts and periods. This fits nicely on a two dimensional worksheet. In looking at how traditional modelers approached this challenge (see below), it is evident their thinking is geometrical. This means placement and orientation of dimensions on the spreadsheet are important. Fitting four dimensions into a two dimensional worksheet is not easy. Seven participants stopped at three dimensions.
Handling Change
These approaches work, but the geometric nature of these approaches do not accommodate change. Adding dimensions (categories), or even dimension instances (items) would require nearly a total rip-out and replace. Handling change is an important consideration because adding new customers, products, regions, periods, etc. is the norm, not the exception, in MD.
Adding Analysis
Many modelers reject the notion that analysis is part of modeling. If all the client wants to know is, "Is this a good investment?", in other words, if all they need is a yes or no answer, analytics is not important. In my world, clients ask for optimal solutions and that requires analytics. In my four decades of modeling, most of my clients found analytics indispensable. With analytics we can look into our model and find problems and opportunities. We can tweak inputs and find scenarios that are most likely, as well as most favorable. Paul's challenge did not ask for analytics and as a result, no one found a problem buried in the data. I added analytics and exposed that one of the market sectors in Paul's model should be dropped by our fictional company, or pricing dramatically increased. Adding analytics to traditional models is not trivial; but, In table based modeling (TBM), adding analytics is second nature (easy).
TBM (Table Based Modeling) Approach
TBM ignores the two dimensionality of the spreadsheet grid. Instead, it applies the same proven principals used for a half century by businesses large and small, in all kinds of industries, all over the planet: a database approach.
While a database approach is quite different from traditional modeling approaches, the overall model structure is similar to most standards.
Structure
At right is the standard BXL TBM structure which has the following sections:
Cover - This worksheet provides a brief description of the project's purpose. Excel is a lousy word processor so if I need to provide detail documentation, I use MS Word and link it to this workbook.
Table of Contents - This worksheet provides links to all worksheets, named ranges, tables, PivotTables, and Charts as-well-as brief descriptions of what those components are.
Modeler Inputs - Static information that the user should not change goes here. This can include: named constants used in formulas, configuration settings, data validation tables, and imported data (actuals).
User Inputs - This contains values/assumptions that the user may change. This may span more than one worksheet.
Process - This is where all calculation go. In general, all formulas go here. This may span more than one worksheet.
Formal Statements - This section aggregates process results in pivotTables formatted to match standard financial statements (when needed).
Summary Tables/PivotTables - This section aggregates process results in pivotTables. This provides tabular summaries to users who prefer that, and feeds the interactive analytical displays (charts).
Interactive Analytics - This section looks like a standard executive dashboard with charts that support drill down and filtering via slicers.
Integrity Checks - This section examines all we can think of for model problems or exceeded thresholds for financial ratios or KPIs
Why Tables
MD modeling is common on other platforms and has been for decades. Those platforms use relational databases designed to connect (relate) and aggregate (summarize) various dimensions. Those relational databases have tables. We can mimic those tables in Excel. Excel's tables were introduced back in 2003 (as lists) and enhanced with structured references in 2007 which were refined in 2013 to what we have today. Excel's tables have compelling advantages over traditional modeling methodologies including:
Automatic Dynamic Named Ranges - No need to create names in Name Manger with exotic formulas to determine range sizes
Structured References - Make formulas self-documenting and, thus, far easier to understand than cryptic cell references.
New Structural Elements - Old Excel's structural elements included workbooks, worksheets, and cells. We now have tables enhancing model structure.
Auto-Extending Formulas - No need to copy/paste or drag/fill formulas for repeated calculations
Formula Consistency Enforcement - Auto-extending formulas helps, but add to that alerts when formulas are inconsistent within columns.
Intellisense - Let Excel help us enter references 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 too.
Total Row - Eliminate entering formulas to total columns.
Filters and Slicers - Tables have auto-filters and work with slicers.
Formatting - Automatically format headers and add (lightly) banded rows for easier following of calculation sequences.
Handling Change
TBM is designed to handle change. Its foundation is built on the same concepts that enable ERPs (Enterprise Resource Planning) to adapt to varying industries, varying accounting standards, differing account structures, differences in scale, etc. Some changes can be accommodated with no formula changes and no modeler intervention what-so-ever.
Adding Analytics
TBM is also designed to layer analytic components over our single version of the truth. These components include PivotTables, Pivot Charts, and Slicers (see figures below). With analytics we can easily expose insights buried in the data.
Conclusion
TBM looks different largely because tables are structured to expand down the page instead of across the page. The visual difference is stark but the logical difference can be trivial as most Excel formulas work equally well vertically or horizontally. Beyond that, TBM has extremely compelling advantages.
TBM is a natural solution to multidimensional modeling and has been for decades in systems that handle models too large and too complex for Excel. TBM easily accommodates adding more products, sectors, regions, months or other dimensions. TBM formulas have the advantage of being self-documenting and self-replicating. TBM calculations can create a single version of the truth upon which we can layer as many pivots as we like to reveal key aspects hidden in the data. Pivots provide the foundation for Pivot Charts to which we can attach Slicers so we can interact with results to get the information we are most interested in.
Continue in this series to learn more about this extraordinarily powerful approach to modeling in Excel. The next section explores a version of this model that uses only Excel formulas for calculations.
