Table Based Financial Modeling
10 September 2019 Written by Craig Hatmaker and Lance Rubin
Introduction to the co-author
Craig lives in the US which is why he says he “spells modeling more efficiently ;) ” and the rest of the article is not written in the Queen’s English.
Craig is an IT executive with strength in systems, but he is no stranger to business and finance. His finance experience started working for a mortgage banker when US interest rates were double digits and debt instruments using the rule of 78s created negative amortization.
That was a long time ago when he used to model in code (ex. Basic, C, Cobol, etc.) because spreadsheets didn’t exist back then.
Spreadsheets revolutionized business, finance and modeling and impowered SMEs (Subject Matter Experts) to model on their own much faster than the IT department, which typically lacked the business or finance context, but not Craig.
Why did Craig select this topic and why is he passionate about it?
Finance SMEs are sometimes ‘spreadsheeters’ because they build models for themselves. When modelling for ourselves we are free to neglect labeling because we know what each value is. We are free to neglect structure because we know where everything is. We are free to use the most complicated, convoluted formulas we wish because we know what they do. We are free to neglect data validation because we know what is and is not acceptable. And if our models have errors, we will fix them as we find them and move on.
It is not until we have to create solutions for someone else that we realize we need certain disciplines within our spreadsheets. Our Models must be understandable by others so labeling, structure, and simplicity are required. Our models must not have errors so data validation, error checks, and protection are required. And if we model for many different customers, our value can increase greatly if our models are flexible enough to grow and accommodate a variety of situations.
As someone who models for others, those disciplines are key to my career and one relatively new Excel feature helps me address several of those areas better than anything else: Tables.
Topic Introduction
To be innovative in any field one must think outside the box. Being innovative is not just about adopting a new piece of technology or helping to develop that new technology but to find different and often better ways to do the same thing we do today but with existing technology.
This can be far more valuable as we are asked to do more with less and keep a tight leash on all costs. Being innovative and adopting a new approach to the same technology costs nothing other than our time to learn this new way and perhaps suck up a bit of ego and admit there is indeed a better way to do things.
But of course, we have options and this new way won’t necessarily meet all the requirements achieved in the old way, but nonetheless its worth taking a growth mindset and looking at it closer.
Lance has seen a table-based modeling approach being used in operational 3-way financial modelling with a high degree of structural integrity and flexibility with rolling data flowing through the model and updating it for changes in logic and line items.
Lance also publish a 3-way financial model visualised in PowerBI, but his horizontal timeseries model had to be converted into a vertical table format to allow this to occur. PowerBI was only visualising the outputs which were made vertical rather than being able to model all the key drivers within it, which would be possible on a table-based approach.
If we are going to push the boundaries of our skills today, which is what the SS to FM series is all about, then we need to think of new possible and alternative approaches to explore.
So let’s get into it.
Tables can add a ‘physical’ structural element that facilitates organizing our model’s ‘logical’ structure with dynamic ranges that automate formula documentation and consistency.
How would you teach this to school kids? What key tips would you give them?
To make it simple think “Kind Vertical Lists”.
As a kid we quickly learn that we have a ‘list’ of all sorts of things in our life. The homework we need to complete, the list of new words we need to learn for spelling at school each week.
If something has repeating elements, such as a formula that repeats across or down, it probably belongs in a list. Tables are lists inside Excel.
Think ‘vertical’ (the hardest part for any ‘traditional horizontal’ financial modeller to get their heads around). Lists that go down a page can be converted to tables using CTRL-T. Financial Modelers generally prefer horizontal based models so making this adjustment is not easy.
Think ‘kind’. Table formulas are more kind to people. They use words instead of cell references. There are literally dozens of attempts in technology development to make formula construction more natural language based so we can reader better rather than having cell ranges with absolute and relative references. As an example, in a column labeled “Gross Profit” might be a formula that reads “=[@Revenue] - [@[Cost of Goods Sold]]” which is much more kind for humans than “=E$24-E$25”.
Now once you have those Kind Vertical Lists, there are a whole lot of other aspects that you gain from your list.
Think of the ‘future’ particularly for operational models. Lists typically grow, I know the list our wives give us can grow quickly.
Time based lists often need more periods, item based lists need more elements such as accounts, products, regions, customers, etc. Tables grow automatically and with ease with little changes required to the overall structure when you add a new item to the list.
How easily can you add a new item to that horizontal structure of yours with native Excel (without buying software to do it for you)?
Think of ‘reuse’. Tables are easier to port to other models. Reusing model components speeds model development and drives out errors as we learn from our mistakes and build on our successes.
Think ‘connections’. Tables can connect to other tables through Excel’s advanced features: Data Model, Power Query, and Power Pivot. Connecting reduces formula count which reduces potential failure points and suddenly sees the often talked about merging of the two disciplines of Financial Modeling and Data Analytics.
Think ‘integrated relationships’. Table are like a flexible glue. Tables help ensure that formulas and references remain connected, easily integrating new data. Writing one formula copies it down a whole column and reduces the need to go back and copy formulas down to include the new data.
The two sides of the same data coin but often poles apart in thinking. Can we bring them closer together?
What practical steps can modelers take to learn more?
Craig has kindly provided (for free) a practical comparison of the traditional horizontal 3-way financial model to the table based financial model.
Here Craig compares a traditional three statement financial model offered by the Corporate Finance Institute’s (CFI) for download to a functionally equivalent Table Based Financial Model built by Craig’s company Beyond Excel (BXL). The model that CFI offers for download follows their single worksheet layout. CFI is well aware of multi-worksheet layouts so I assume they offer the single worksheet version because it is easiest for beginners to understand. BXL standards require multi-worksheet layouts to keep inputs separate from processes and outputs so the BXL model is more complex for this reason alone and not due to it being table based.
However, on the plus side, the table version is fully dynamic, self-documenting and structured in reading order. You can find both models in the Links and Resources section below along with a comparison of the outputs.
This first step proves that a 3-way Financial Model can indeed be functionally converted to a table based financial model. The next step demonstrates how tables are better for addressing multidimensional scenarios. Examine Craig’s entry to Paul Mireault’s Multidimensional Spreadsheet Challenge presented at the last EuSpRIG conference. Craig’s model leverages tables, Power Query, Power Pivot, Pivot Charts and Slicers to create a dynamic model with BI like interactions. The user experience can be enhanced with an optional add-in: Appify.xlam. Find links to the challenge, the model, and add-in in the Links and Resources section
To learn more about Craig’s methodology see BXL’s (under construction) online Advanced Excel Modeling curriculum. See Links and Resources section.
I’ve heard this before from Craig and you guys don’t get it this is analytics not modeling?
Craig says modeling with analytics is far more valuable than models without. Consider the mult-dimensional model for Paul Mireault’s challenge. In the challenge’s requirements we could see which month lost money, but we could not see in which sector. Craig changed the model from using aggregated data to detail data by allocating costs down to each transaction. The results of this new model produced identical results AND added the ability to determine profitability by any dimension revealing which sector caused that loss (click Sectors’ slicer tab for results). Modeling without analytics shows gains and losses. Models with analytics reveals why.
This is a topic that has been investigated before at length and many a financial modeler has probably had robust discussions with Craig on this approach in social media or directly and nothing has changed. Lance too saw Craig’s approach many years ago, but he also failed to see its benefits until now. So what’s changed?
Let’s take a step back and consider for a moment any traditional industry that doesn’t cater for customer’s user interface (UI) and user experience (UX).
All well-constructed financial models of complex multi-product, currency and division can be reasonably difficult to navigate and understand, even with a hyperlinked table of contents.
Dashboards can of course give you further transparency but to distribute that knowledge widely in an operational sense for say monthly rolling forecasts then you have spreadsheets flying everywhere.
So if you wanted to distribute the outputs of a robust 3-way financial model on mass and enable the user to slice, dice and investigate it closer without opening Excel, then you have issues around version control etc if everyone were trying to use the same Excel file.
With the advances in Excel’s own data modeling capability and its integration with PowerBI means that this distributed operational dashboard reporting is indeed possible.
Does the user really care about how the formulas are constructed, or just the engineer who built it?
What if it gave them the exact same answer? Do they really care?
Does the user really want an easy to use, visually appealing presentation of the model with its outputs and key assumptions?
When you buy a smartphone, or an app, do you ask to look at the coding language or just whether it looks good and works?
What if more IT experts, like Craig, were to learn more about accounting and finance and apply this table based scalable approach, would you still need the traditional modeler of today?
If we are going to truly move the Financial Modeling industry forward we should be open to alternatives. This isn’t about killing the old horizontal approach of model building but being open to alternatives.
In fact, it probably doesn’t really make sense for deal modelling to use this approach as there isn’t a lot of new data rolling through the model on a regular basis and the items tend to be reasonable stable through the life of the model building process. Perhaps a few tweaks here and there.
Links and resources on tables and models
BXL’s Recreation of CFI’s Three Statement Model:
Multi-Dimensional Spreadsheet Challenge - Excel Model (xlsx):
Multi-Dimensional Spreadsheet Challenge - User Guide (PDF):
Using structured references with Excel tables:
Proposed Spreadsheet Transparency Definition and Measures:
BXL’s Advanced Excel Modeling Curriculum:
What makes Power BI the market leader according to Gartner:
Lets take a closer look at the 2 modeling approaches
So we can see from an output perspective the 2 models are exactly the same, but under the hood they are quite different.
Below is an even deeper look at the model and formula construction and flow along with identified key statistics like number of unique formulae.
CFI Model Analyzed
BXL Model Analyzed
Whilst the CFI model uses no tables and pivot charts, has less formulas (unique and total) and arguably a simpler model in flow and design it isn’t scalable for future changes to new line items unlike the table based approach which is dynamic around new data.
The detailed analysis seen above of both models can be downloaded here.
How important is this skill in the context of learning FM?
Tables greatly improve model flexibility, reusability, understandability and transparency which can drive out errors and can cut model building time. Tables work well with Power Query which we can leverage to download the most recent historical records/actuals instead of error-prone, time consuming, hard-to-keep-current hand keying.
Power Query can also replace a sea of LOOKUP formulas reducing potential failure points and improving reliability (but the recently released XLOOKUP could help too).
Tables work well with Power BI which we can leverage to present our models to anyone on any device, anywhere on the planet.
How does all this disruption, AI and automation talk impact this topic?
Microsoft is fighting, and appears to be winning, for dominance in the BI space.
This fight introduced into Excel: tables, the Data Model, Power Pivot, Power Query, and Power BI.
These are extremely powerful tools that are responsible for catapulting Microsoft to the top of the BI space. While aimed squarely at data analysis, we can leverage these features to add data analytics to our financial models giving our customers insights into what might be hindering meeting their financial objectives and what changes can optimize those results.
But adding these tools and the table based financial modelling methodology requires dramatic changes in the current modeling standards. Table based Financial Modeling is currently incompatible with the most popular industry standards such as FAST, Corality, BPM, Operis, etc.
But just as digital replaced film, disruptive technologies and innovative thinking typically push old standards aside. Craig believes we may very well be witnessing a “Kodak Moment” in Financial Modeling with old methods giving way to Financial Modeling merged with BI.
Time will tell.
Experfy: A Comparison of Tableau and Power BI, the two Top Leaders in the BI Market
If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App
If you want to find more information on financial modelling and content visit the Model Citizn website.
CONCLUSION
If you want to learn more in 2019 and also understand how you can upskill in FM be sure to reach out to us or stay tuned.
I encourage you to also take a peak at my past articles on financial modelling which is the foundation of business decision making, planning and forecasting.
We have also redesigned our website to help you along your journey of levelling up in this space.
We will continue to discuss this topic and you can click to follow me on Twitter or LinkedIn or subscribe to our short but sweet newsletter.
Be sure to check out our pods and video page.
Here are also some past blogs that might be of interest.
Forecasting skills doesn't guarantee good modelling skills
How Technology Will Change FP&A Forever
Why so many unemployed accountants are already struggling..and how I can help?
Father battles cancer, son battles career extinction
Why being a 1-man/women VCFO business is a really bad idea?
Why Vlookup (vs Index(match) shows your lack of embracing change, innovation and better Excel skills
Financial Modelling Innovation: Predictive analytics vs Financial Modelling
Financial Modelling: Diamonds in the rough
Why Monte Carlo will change the way you make financial decisions and think about scenarios?
Driver Based Planning & Forecasting in the context of FP&A
Inside the mind of a spreadsheeter vs a financial modeler
Virtual CFO the Good, Bad and the Ugly
Do You Model Off against the Masters of Financial Modelling?
NEED HELP searching for the best advisory tool in the market?
Part 2 of Finance Innovation - Data Analytics / Big Data is not Financial Modelling
Why I disrupted my own role and you should do the same?
Consulting, Advisory, Finance and the art of influencing is about to change forever.
Your Financial Model can save lives !
The Machines Are Not Coming… they are here!
The Demand for Financial Modelling Skills Reaches new Heights
Lance Rubin is the Founder of Model Citizn, partner of theOutperformer, approved training provider to the Financial Modelling Institute and Group CFO for SequelCFO.
I have more than 20 years of combined experience working in model audit, investment banking, corporate finance, finance business partner and Fintech CFO.
Organisations I have worked with include PwC, KPMG, National Australia Bank, Investec Bank and Banjo small business lender.
I have a YouTube channel dedicates to the Future of Financial Modelling and also provide access to Models via Eloquens with thousands of viewers and downloads.