Looking into Excel Modeling's Future

Carl Stiller posted a simple test in LinkedIn's Financial Modelling’s forum (at right). Reader comments revealed some modelers struggle with the answer. Knowing the answer is critical to current financial modeling (FM) standards and it highlights a major problem with current standards: cryptic cell references (CRs).CRs were the only way early spreadsheets could work with cells. As spreadsheets evolved, named references (NRs) were introduced to greatly improve readability. A few pioneering modelers used them despite being banned by popular FM standards[i]. The standards group reasoned that implementing names introduced error potential. Even with potential errors FM standards grudgingly accept NRs because, as the pioneers proved, the upside outweighed the downside. While NRs gained acceptance in the FM community, the next evolution of names, structured references, has not.

Structured references (SRs) are fully automatic (no maintenance required) dynamic named ranges. They matured In Excel 2010 and have numerous advantages over CRs. The most notable being natural language, self-documenting formulas that look like:

=[@Revenue]-[@Expenses]

SRs are being adopted everywhere… except in FM. I believe this is due to the origins of FM which modified financial statements (P&L, CF, BS) with formulas to forecast changes. That paradigm comingled inputs (actuals and assumptions), computations (formulas) and outputs (financial statements). The results placed accounts down the side and dates across the top. This is known in IT circles as a crosstab table. SRs are not available in crosstabs, only vertical tables.

We can easily convert crosstabs to tables by copying and pasting with the transpose option.

Now I am not advocating anyone model horizontally then convert to tables. I am only demonstrating that geometry, horizontal verses vertical, forces no functional changes. But if we model in tables we gain advantages like adding periods simply by adding rows or using SRs to make formulas self-documenting. The problem, though, is tables are vertical and financial statements are not.

Financial statements have standard formats which FM must respect and, as stated earlier, those formats are crosstabs. Fortunately, converting tables to crosstabs is easy using another Excel feature, PivotTables.

PivotTables are shunned in FM circles just as names were shunned. PivotTables have been labeled as "the axis of spreadsheet evil" by some of FM's most revered experts. My view is PivotTables are wonderful. They reduce complexity by replacing a sea of formulas with a single configurable object thereby reducing risk. They are completely transparent with drilldown that effortlessly exposes source data. The only downside that I can think of is they require more than rudimentary Excel skills.

With improved skills we can create models that are better structured, less complex, more understandable, more transparent, more flexible and more secure with less effort while respecting required output formats. And since tables are created by Power Query and used by Power Pivot and Power Bi, we can integrate our models with these amazing tools to:

• 1. Import or download actuals via Power Query to tables

  2. Add formulas using structured references to forecast changes and then

  3. Present results in required formats and executive-friendly graphs.

NOTE: Excel’s power tools are now listed as skill requirements in many FP&A job postings.

I am sure this feels totally alien to most Excel modelers, but this is how modeling at scales exceeding Excel’s limits have been done for generations. All financial systems and ERPs store actuals in tables (aka relational databases). Forecasting programs read those tables, perform calculations and place results in tables known as data sets. Business intelligence (BI) programs aggregate data set tables and present them in required formats. Only in spreadsheets is this paradigm not used.

Spreadsheet modeling created its own paradigm based on what spreadsheets offered. That paradigm (for the most part) locks modelers into using cryptic cell references and out of Excel’s power tools. We need not cling to that paradigm. Excel has evolved offering us the opportunity to embrace a new paradigm leveraging all of Excel’s features. And by leveraging all that Excel offers, produce superior models and expand our own market offering.

It is time for FM to evolve once again.

[i] The FAST Standard, FAST02a/ 16.11.12 FAST 4.03-01 “Do not use Excel Names”