Before we can discuss the future of financial modeling, we might want to have a bit of historical context for electronic spreadsheets. After all, it wasn’t that long ago that paper ledgers were the standard for budgeting, accounting, and operational planning.
The origin of electronic spreadsheets dates to June of 1979 with VisiCalc. Amazingly, many of the initial concepts used in the preliminary versions remain intact today. To learn more, watch the TedTalk: Meet the Inventor of the Electronic Spreadsheet.
While the user interface has seen some updating and some of the use cases and calculations may seem rudimentary by today’s standards, early programs laid the foundation for the future. Over time, as the notion of these types of spreadsheets grew in popularity, so did certain terminology: tables, rows, columns, cells.
In fact, today many personal and business computers come with spreadsheet software pre-installed. Spreadsheets are so popular that nearly every student and every business is likely to be familiar with spreadsheet basics. Unfortunately, the fast growth and ubiquity of spreadsheets has come despite persistent flaws in their architecture, and spreadsheet popularity tends to overshadow more robust approaches to financial modeling which we will describe below.
Countless stories have been told about the risk of spreadsheet errors and there is clear evidence that ignoring best practices could lead to potential issues. As the use of these tools became ubiquitous, a series of best practices emerged to mitigate the flaws of spreadsheets, especially for financial modeling, such as keeping actuals separate from calculations and input cells (see the table below for a full list).
That said, if modelers keep all the best practices in mind, spreadsheets can hold a great deal of value for a variety of needs. They are essentially a blank canvas, and as a result, they are incredibly agile.
Spreadsheets aren’t perfect and as requirements scale, new solutions might be required.
A defining concept of spreadsheet software is that all model information is contained in the cells. That is to say, the data, the formulas, the logic, the structure, the checks etc. etc. are overloaded and every cell that participates in the structure and logic will have its own unique formula. There is no structure, no logic, and no relationships that are not designed and implemented by the modeler through abstract formulas and references like “=sum(VLOOKUP(L34,MySheet!A17..C29,2,FALSE)” where the business meaning of the value to be looked up and the search range is unknown, and the proper functioning of the formula requires a certain configuration of data that is not validated or supported by the spreadsheet at all, yet it must be absolutely correct to return the proper answer.
Such a system might seem flexible at first, but as soon as structure and logic are built, models start to become rigid and fragile.
If a cell value is deleted, so too is its formula, which may cause the entire logic of the spreadsheet to fail and provide incorrect answers – if those answers were correct in the first place.
Furthermore, carefully built structure is resistant to change – if ever the model needs to scale out, adding new scenarios, products, regions, departments, channels, accounts etc. Customers describe the horrors of “open sheet surgery” wherein worksheets are duplicated, thousands of formulas changed or added, and the whole thing stitched back together.
Whole industries exist to reinforce best practices and mitigate the risks of spreadsheets, to audit spreadsheets, and to patch the functionality of spreadsheets with add-ons of various kinds.
If your needs extend beyond simple budgets and forecasts, to complex operational models in sales operations, supply chain, manufacturing, real estate or strategic finance you may run into the limitations of spreadsheets. For that reason, two other software categories have emerged: Enterprise Planning Tools and Financial Modeling Software.
Can these alternatives replace spreadsheets but address the more intense demands of real business planning and operations? Let’s explore.
Enterprise Planning Tools
Enterprise Planning Tools, as the name implies, are designed explicitly for large enterprises. While they provide the scalability and governance enterprises expect, such platforms also require enterprise grade resources, of both the financial and people varieties, to deploy and manage. Often they require database admins, IT admins, plus configuration and developer resources to be customized to the specific needs of the business.
Such planning tools provide a robust platform to develop important, standardized models that will be used by multiple stakeholders in the business. Those models will be managed, maintained and run as any large scale project would be. Adopting these systems can help to standardize certain business processes but buyers should expect some ‘sticker shock’ when it comes to purchasing this category of solution.
Beyond the costs and resources required, the limitation of enterprise planning tools is agility. Models that used to take a few minutes to create in a spreadsheet now might take multiple people, multiple design / build cycles and become similarly rigid. Most customers of enterprise planning tools actually never get rid of their spreadsheets, and end up modeling in a spreadsheet anyway, and later attempt to push back into the planning system as a repository. Many enterprise planning tools use spreadsheets as the “front end,” in many cases defeating the purpose of more rigorous model development but maintaining the cost.
A New Generation of Financial Modeling
Now that we understand where we’ve come from, you’re probably wondering: what is the future of financial modeling? Is there a software option that addresses the major pitfalls of spreadsheets at a reasonable price and maintains agility throughout the model lifecycle?
The answer: Professional financial modeling software from Quantrix.
Quantrix provides the agility of spreadsheets and the scalability and governance of an enterprise planning application. Moreover, Quantrix Qloud includes low-code design of model-based applications, so that the power of your models can be distributed in easy-to-use applications across your organization.
How is this possible?
Matrices: Unlike spreadsheets, every familiar table you see in Quantrix is actually a multi-dimensional matrix under the hood. This means that adding a new scenario, region or product is a single click, no more “open sheet surgery” required. Need an entire new forecast for next year? Again, a single click, no more saving a copy, dragging formulas, or duplicating sheets. Customers have reported up to 75% reduction in time to prepare operational reviews compared to their old spreadsheets.
Named Objects & Real-language formulas: Categories and metrics in your model are all referenced by their names, you can use the same familiar spreadsheet formula syntax, but formulas now read like a sentence, like “Profit = Revenue – Expenses”. No alphabet soup of abstract references. Human readable formulas are easier to write, read and troubleshoot so having a new team member (or auditor) understand how your model works is straightforward. Collaboration, confidence, and shared understanding of your decisions will improve.
Inherited Relationships: Relationships between tables are created with drag and drop, and formulas inherit those relationships automatically. This fortifies models with simple, logical structure without any special work or spreadsheet best practices. Because table relationships are inherited, there is no longer any requirement for that VLOOKUP/HLOOKUP/XLOOKUP/INDEX/MATCH/FILTER syntax that you have to research every time. The Profit formula above is exactly the same no matter if you are calculating it across 100 products with individual growth rates, 17 regions with distinct currencies, or time periods of 3, 5 or any number of years. One Quantrix formula in simple, real language, takes the place of hundreds or thousands of spreadsheet formulas, dramatically reducing the possibility for error and increasing efficiency.
Enterprise Capabilities: Quantrix also has data connectivity, role-based security, an audit trail, low code cloud apps, simultaneous collaborative editing and support for large models that the enterprise platforms have, but it costs a fraction of the enterprise tools in terms of both resources and budget.
Look how easy it is to understand the formulas in Quantrix:
Focus on modeling not maintaining.
To summarize, consider that every user must learn a set of spreadsheet best practices when creating a model to make it more secure, and less error prone. But these practices also make models less performant, and more rigid – Wouldn’t it be better if these best practices were built into the product?
The table below shows why Quantrix is the Future of Financial Modeling:
Spreadsheet Best Practices | Quantrix Built-In Functionality |
| Keep actuals from calculations and input cells | Formulas and cells are always separated |
| Create named ranges to simply formulas | Automatic ranges with real language formulas |
| Keep pivot tables on separate sheets (or well-spaced) | Always on pivoting |
| Enforce consistent naming across worksheets for lookups | Linked structure automates names updates and lookups |
| Keep data in rows and formulas consistent left to right | Orient data in any direction without changing formulas |
| Avoid non-tabular layouts and merged cells | Model multiple dimensions easily and instantly |
| Build in calculation checks to identify errors | Dependency inspector, audit trail, problems log |
To learn more about these best practices, watch our recent webinar: How to Ignore Spreadsheet Structure and Come Out Ahead.
Quantrix delivers the agility of spreadsheets and the scalability of enterprise planning software.
We would love to show you the power of our agile modeling platform so that you can see firsthand how it might deliver value in your business. Over 50k Quantrix users have saved time, reduced errors, considered new scenarios and made more confident business decisions with Quantrix across Sales, Finance, Marketing, Manufacturing, Supply Chain, HR, R&D in every industry.
There isn’t a modeling problem we haven’t solved using Quantrix. Why not challenge us?
You can book a demo and download our free 30-day trial!
