A 30-years player surviving the young bright kid
AirTable and its many successors, which replaced the simplistic cell-based formats of spreadsheets with the reliability and programmability of relational databases, should have driven us all to ditch Excel and Google Sheets. Yet we are ten years later, still deep into spreadsheet projects, learning their tricks and hacks and connecting them to apps as an essential data source.
Even though there are a lot of blog posts and YouTube videos explaining the benefits of AirTable and similar tools, more seems needed to get most people to switch from spreadsheets, a seemingly old format.
In this post, I refer to one specific use case of the spreadsheet format, which, based on my experience, is impossible to replace with new tools.- The Profit & Loss document is necessary for any serious financial analysis.
PNLs are not only more convenient to construct in a spreadsheet. It’s impossible to get the layout flexibility needed in such a document. I am referring to the format expected by every CFO and management team member, which they have used for tens of years.
What’s Required in PNL Format
Anyone who has made one knows that the months, quarters, or years go at the top, followed by rows of related financial information and summary lines showing the net profit. Typically, the income section would look like this:
Since the database infrastructure in the AirTable family of tools also has a tabular format, building the PNL with such a tool should have been easy. But as soon as you try, you find it more complicated than it seems. The reason: databases support inter-field calculations but cannot apply inter-record ones. More specifically, a typical PNL will involve several multiple + summarization operations at end-rows of sections, impossible to do in an AirTable-like tool. Here it is in more detail:
In the next collection of cells, the revenue from three different types of products is calculated by multiplying each cell, counting the number of units sold by its price, and then summarizing the three results:
4,815 = 300*9 + 150*9 + 85*9
As mentioned, such inter-record calculation is impossible in AirTable or any SQL-based tool. CFOs constructing these tables are accustomed to creating even more complex combinations using Excel’s rich set of built-in high-performance functions.
A similar situation is found when calculating cash flow, as calculation involves addition across columns and so on.
While limited flexibility in calculations seems to be the main reason AirTable fails to replace Excel in the financial data manipulations domain, there are a few additional, less critical abilities that make Excel a favorite, at least for the time being:
Formatting flexibility: Excel allows for any color, width, and line-drawing styles to be applied to rows, cells, and arbitrarily selected area, even change in font type size and color. AirTable and similar tools offer only a tiny part of these features.
Built-in rich set of functions: Excel offers massive (about 500!) built-in functions capable of manipulating fast rows and columns data, saving the need for separate programming effort to achieve similar results.
Impressive scalability at low cost: Spreadsheets, both Excel and Google Sheets, support massive amounts of data (>1M rows) while offering free or almost free service. AirTable and its competitors limit their support to much smaller data sets — tens of thousands, even at these levels, charge a significant monthly fee.
AirTable and other tools providing a spreadsheet-like interface to the power of databases took over many applications that used to be popular on Excel and Google Sheets. Management of lists of data, project management, and various marketing activities are all migrating to this environment. Their ability to flexibly arrange tabular data in several powerful formats, such as grouping and Kanban, made them an ideal solution for these applications. Thus It seemed natural that the business industry would drop spreadsheets and move entirely to new tools.
But Excel survived the revolution, and using its unique design and extreme flexibility, it offers a better answer in several critical areas, especially those involving financial calculations. Their unique flexibility survived another attempt to take part of their market with tools dedicated to PNL models (such as bizplan.com), but this has failed, too, as the rigidity of such tools fails to meet the needs of the financial community. We will continue to see Excel and Google Sheets around for years, especially if they adopt many of the flashy features of the new tools (like Google Sheets’s new @ commands).