Why Are Spreadsheets Still A Common FP&A Tool?

“The status quo is not a neutral state, but a mindset to uphold the decisions of the past.”

In 2023, effective business planning, budgeting, and forecasting is a necessary capability to keep organizations running. Already this year, we have seen unexpected banking failures, unpredictable labor markets, and continued supply chain and logistics challenges based on geopolitical challenges. In light of these challenges, Amalgam Insights believes that businesses must have a shared version of the truth that they use as they look at their budget and finances.

And, in this case, we specifically talk about a “shared” version of the truth rather than the “single version of the truth” typically associated with data warehouses and enterprise applications. This is because data changes quickly and every stakeholder can potentially make different decisions to define and augment their data, even basic changes such as language and currency translation that can lead to different versions of the truth. In this analytically enhanced and globally complicated world, it makes more sense to have a shared version of the truth that is augmented with personalized or localized data and assumptions. However, this consistently shared version of the truth can be hard to accomplish in organizations where planning is handled in a distributed and personalized manner through spreadsheets. In the enterprise world, finance professionals are inured to the basic realities of auditable data, processes, and results. And they are often asked to provide reports and memos that are used at the executive level or by external investors and public markets to ascertain the health of the market. Given the assumed importance of this formality, why would experienced professionals use spreadsheets in the first place?

Let’s face it; spreadsheets are easy to use. They are the lingua franca of data; a format that every experienced data user has been trained on. And with plug-ins and Visual Basic, spreadsheets can now handle relatively complex analytic use cases. Even if they aren’t quite data science tools, spreadsheets can provide structured analytic outputs. Also, spreadsheets are accessible on every computer through Excel, Google Sheets, or other common spreadsheet software. And with the emergence of cloud-based spreadsheets, it is now possible for two or more people to collaborate within a single spreadsheet.

Spreadsheets also provide users with the ability to customize their own analytic views with their own personalized views of data and the ability to hypothesize by building their own models. Who hasn’t looked at data and wondered “what if the data looked a bit differently?” or “what if we have a drastic scenario that suddenly increases or decreases a fundamental aspect of the business?” In light of COVID, rapid interest rate hikes, global shortages in commodities production, trained labor shortages, and the increasingly unstable banking environment we are in, it is important to be able to test potential extreme assumptions and support a wide variety of scenarios. Between the ease of use, availability, and personalization aspects of spreadsheets, it is not hard to figure out why spreadsheets are still a leading tool for financial planning and analysis. Even so, Amalgam Insights has found that once organizations pass Dunbar’s number (approximately 150 employees), they start to struggle with collaborative tasks simply because it becomes difficult for any one employee to know all of the other employees who need to be involved in the business planning process and spreadsheets have been designed to maximize individual productivity, rather than collaborative work, for decades. From a practical perspective, people tend to work with the people they know best. This is fine for a small company with a dedicated office where everyone knows each other. According to US Census data, the typical 1,000-person company has 19 locations, making it highly unlikely that all of the key budget stakeholders will be in one office. In this regard, Amalgam Insights finds the following challenges in supporting spreadsheet-based planning at scale.

The distributed nature of work also makes spreadsheet governance a challenge, as it is easy for spreadsheets to suffer from version control issues, a structured workflow process, and for file owners to lose control of the inputs and outputs that they are responsible for supporting. The lack of version control, workflow, and activity tracking is especially challenging in industries and geographies that require tracking of any personal data either related to employees or customers.

Spreadsheets also struggle in large data environments, which are quickly becoming commonplace in the business planning world. Although a core enterprise database may only be a few gigabytes, accurate planning now often includes access to sales, operations, and potentially even IT transactional data sources that can quickly expand beyond the memory and data size constraints that spreadsheets are designed to use. From Amalgam Insights’ perspective, the size and variety of data are the biggest technical constraints that spreadsheets face as planning solutions.

Spreadsheets lack advanced analytic and machine learning capabilities. Although algorithmic, statistical, and machine learning tools are increasingly becoming part of the FP&A world, especially in forecasting, Amalgam Insights finds in practice that most organizations have not yet embraced complex analytics as a core part of their FP&A approach. Based on current job site metrics, Amalgam Insights estimates that less than 2% of FP&A professionals currently have a machine learning or data science certification or degree, making this an early innovator capability that has still not crossed the chasm to become a standard job requirement for FP&A.

But perhaps the most significant challenge with spreadsheet models is that they are often fragile: created based on the logic of a single person rather than on defined business logic and with little to no documentation associated with the plans, forecasting algorithms, and multi-tabular complexity that inevitably occurs when a spreadsheet is the primary planning tool for a business, which can also lead to costly data accuracy issues. The model is only as adaptable as the spreadsheet creator’s knowledge of the industry and is dependent on that employee staying employed. Considering that it is unrealistic to expect an FP&A senior analyst to remain in that role for more than five years before either getting promoted or getting a better offer, this human risk is a significant challenge for business planning solutions.

As organizations grow in size to support more than a handful of locations and a set of workers that exceeds Dunbar’s number of 150 colleagues, Amalgam Insights believes that it becomes necessary to adopt a formalized planning solution that supports collaboration, scale, advanced analytics, continuous planning across many scenarios, and advanced forecasting analytics. Otherwise, it is difficult for businesses to maintain a consistent and shared version of the truth across financial planning and analysis personnel that can drive both departmental and executive planning efforts.

Ultimately, the use of spreadsheets as a formal system of record for business planning is a risky one for any organization with a formal corporate structure, governed industry or geography, or any organization that has a significantly distributed business. The ubiquity of the spreadsheet makes it an easy place to start modeling a budget, and the value of the spreadsheet in helping users to structure small datasets will exist for the foreseeable future. But the fragility of the data structure, lack of user and version control governance, inability to scale, and the difficulty of verifying data with other sources while avoiding human error all lead to the need of supporting a more formalized planning solution over time. As organizations face a future of keeping distributed groups focused on a shared version of the truth and collectively consider a variety of scenarios at any given time, the risk of spreadsheet fragility needs to be matched up against the value of using a formalized FP&A solution designed to analyze, govern, and protect all relevant business data, formulas, and outcomes.