A guest blog post by Martha Ryan. She offers a practical look at why spreadsheets still dominate finance, and the hidden risks they create. A must-read for CFOs, analysts, and anyone relying on data-driven decisions every day.
Why Spreadsheets Still Rule Finance and Why That’s a Problem
By Martha Ryan
In an informal poll of 20 former colleagues, now working at 20 different Silicon Valley firms, every finance department used spreadsheets to produce their monthly and quarterly reports. All their companies are at least $500M in sales and they have a host of expensive ERP and FP&A applications that keep track of their operations and finance activities. So how do they close the books? Analyze pricing? Explore merger opportunities? Plan for expansion? Collect budget inputs? Generate forecasts? Integrate newly acquired entities? Spreadsheets.
Survey results cited by The Saas CFO on LinkedIn cites that spreadsheets have a 73% market share for financial planning and analysis. Finance Director Arnaud Lemaire summed up the situation nicely in a comment: “[Spreadsheets] are winning because the data feeding the forecast is messy, the process changes every quarter, the person building the model needs to tweak things nobody anticipated when the platform was configured. FP&A tools work great when inputs are clean and stable. Most companies aren’t there yet.”
Spreadsheets are used and re-used for critical decision making and reporting. They are almost all extremely complex with 10s of thousands of formulas. In this post, I will detail the problems with spreadsheets, how they come to be so complex, why companies depend on them, and what might be done to address the multiple challenges they pose.
Why Spreadsheets Become So Complex
Complex spreadsheets are built by talented finance professionals who take pride in their ability to tap into their deep knowledge of obscure formula types and layer on a bit of code to produce amazing tables and charts that point their leaders to a decision. And then, in previous job markets, this talent moves on to another position, another company. In today’s job market, talent is laid off with executives expecting that AI will fill the gap. The smaller remaining team has two options: decipher the thousands of 10-line formulas throughout 30 worksheets, tapping into Claude or Chat GPT, and still spend dozens of hours figuring out how the workbook actually works. Or these teams use the analyses as is without really understanding them. They make changes and pray to not see the dreaded #error. Or worse, they get no #errors and assume they are correct, only to discover months later that a key column has been excluding key data because of a formula error.
The Fragility Problem
CFOs know that their teams depend too much on spreadsheets. They queue up for IT resources to add code to the ERP systems to meet their needs. It’s a never-ending queue because business is dynamic and the needs constantly evolve. Other crucial projects take up IT resources and CFOs continue to rely on spreadsheets. CFOs expect that AI will help. They are not exactly sure how, and still have a deep-seated confidence that AI is the solution that will ensure the integrity of the quarterly results, the new direction the forecast is pointing, or the proposed acquisition really is a slam dunk. In a survey two years ago cited in The Wall Street Journal, 40% of public company CFOs were not confident in reported results. AI has been evolving at a screaming rate since then. Will it help?
Have you thrown a complex formula into Chat GPT to understand what it is trying to do? It kicks out a line-by-line definition of how the parentheses are impacting the “if, then” aspects of the formula. Claude takes it a step further and reflects the column/row header names, so you don’t have to hunt these down yourself. If you are good at keeping track of 10 different moving pieces and how they interdependent, you can then move on to the next formula in a complex workbook of 50+ formula types. What neither solution will do is decode the institutional memory buried in the workbook.
Let’s say that AI plus “elbow-grease” helps you decode the spreadsheet function, its dependencies, and its weaknesses. The institutional memory aspects often address the “why is this so complicated?” and is assumed common knowledge by the creator. You back into this awareness when you do the decoding. Often, it takes a whole other level of questioning to understand the history, data weirdness, and rationale for why “it’s always been done that way.”
No matter the case, spreadsheet are still the norm for critical analyses. A team dependent on spreadsheets is dependent on tools out of its control, understood in detail by one or two experts. The team that relies on these analyses and experts for repeated, critical path analysis is teetering on a fragile base.
How Complexity Creeps In: A Familiar Story
Complex workbooks often start with a modest request. “Please provide an analysis of the current MSRPs and determine the characteristics of lost customers as a result of the price increase last month.” Assigned the project, an enterprising analyst collects the relevant raw data from company ERP sources, uploads them into a new spreadsheet, and then starts sum-if-ing, xlookup-ing, match-ing, and tallying the results in a summary sheet. They look funny. He notices there are some outliers skewing the results. He figures out how to identify and exclude the outliers. To keep the workbook ‘robust’ so he can re-import the same dataset from a different period, he excludes the outliers with a formula. To not overwhelm the workbook, he nests that formula inside an xlookup.
The summary reflects new totals and still something is off. Research reveals there are some duplicates – not sure how those got in there. Make a note to follow up with the IT team to fix the data. He nests another exclusionary formula. The summary starts to look reasonable, so he shares it with a manager. She points out that it would be great to see if the channel had any impact on the pricing. His data doesn’t include channels. He learns that channel data comes directly from the distributor via a PDF file. After scanning it, he realizes the distributor PDF uses the marketing part number instead of the manufacturing part number. The marketing number has a one-to-many relationship with the manufacturing part number so matching becomes tough. The analyst figures out a nifty way to pivot table the results. The pivot adds three more tabs to the workbook including a crucial step of copying values into the third sheet – he makes a mental note to flag the hard copy sheet.
The evolution continues with the manager’s manager adding a few suggestions. Within a few days, the analyst has created a 30-tab workbook with table functions, pivot tables, over 10,000 formulas, hard coded entries, all parsing raw data from multiple sources to provide the analysis. The next day, the analyst starts to cleanup, labeling the tabs, and column headers. He is interrupted to work on another critical deliverable and doesn’t pick up the analysis again for a few days. He spends 30 minutes refreshing his memory of what he did on Monday and proceeds to make the workbook more robust with those cool somewhat obscure formulas he learned online that are just right for this analysis.
The meeting with the CMO is next week and the analyst has time to put together some clever charts which makes the workbook even larger and requires a bit more manipulation – a second and third summary tab so the data will reflect in the chart in a way that can be best appreciated by the CMO. Unfortunately, the CMO was called out of town on an emergency and the meeting has been pushed out two weeks. The analyst moves on to other things and as the rescheduled meeting approaches, tries to remember where exactly he put that analysis. After a fifteen-minute search, he finds the most recent version in a weird folder on his laptop where he saved it because the WiFi was broken at his apartment and he wanted to do some additional work at home. He refreshes the data (since a whole month has now passed) and fortunately everything looks good.
At the meeting, the CMO is very impressed with the analysis. She asks if it can be the basis of a forecast template? Of course! The analyst returns to his cube and makes a bunch more changes. Pricing updates every six months, and the new proposed pricing won’t be available for another two months. He saves the analysis on the network, labeled with a naming convention that is sure to be understood.
A month later the analyst gets an incredible offer from another firm and leaves the company. Another month after the analyst leaves leave, his now-previous manager has an outstanding commitment to the CMO for a new forecast deliverable and no idea which version on the network is the right one. All the data needs to be refreshed, and the next analyst doesn’t realize that the downstream summaries depend on hard coding the results of that pivot table on tab 25 (who would do that?) The next meeting with the CMO doesn’t go very well.
The scenario where key decisions depend on spreadsheets affects more than the finance department. The planning department and marketing department and reliability team and product development folks all rely on manipulating data from multiple systems which don’t quite capture their current needs. The industries affected are similarly widespread – manufacturing, SAAS, services, consultants, and government. It affects large, medium, and small companies. The smaller and younger the company, the more likely they depend on spreadsheets. A small concern I know with $20M in sales, had a plan to transition its planning from spreadsheets to a demand planning tool until “the downturn”. They are still using those same complex spreadsheets to plan their now $10M company with only one person who understands the plan.
Why Companies Stay Dependent
To summarize: spreadsheets are relied upon because large systems are incapable of adapting to the rapidly changing environment. They are complex because the data are imperfect and analysts must create huge workbooks and nest data cleansing and merging with the analysis. The spreadsheet skills of analysts are self-taught and different analysts use different formulas and approaches that can be a mystery to fellow analysts. The logic and sequence are difficult to visualize because they are buried in cells and sheets without an overall map. Once created, spreadsheets are saved in a range of shared and individual folders. Finding them requires diligence in naming the file, naming the folder, and managing versions. Limited time and resources mean that critical documentation details are frequently an afterthought.
Spreadsheets certainly have their place. What else gives you the flexibility to build on the fly, manipulate data, and summarize in tables and charts? However, as soon as you start using them repeatedly for critical decision making or reporting, you encounter myriad pitfalls of their fragility. When Arnaud Lemaire says that “most companies are not there yet” with clean data and stable inputs, I wonder what company is so staid and methodical? What analyst works in a predictable environment with such long sightlines that IT can be called upon to re-configure the ERP and finance systems to meet unanticipated needs nine to twelve months in the future? Dynamic describes all markets and the companies that address them. Even the government is rapidly evolving. Layer on budget and resource constraints in IT and among the data analysis teams, and spreadsheets soon become the analysts’ go-to for close, forecasting, pricing analysis, budgeting, integration, etc. These uses are constantly evolving and underpin critical decision making. Hence, the inherent fragility becomes a significant corporate risk.
This begs the question, really? After all these years, that’s what we have? Yes, that is the current situation. How does a CFO or finance manager deal with this reality of fragile dependency?
What Can Be Done: Practical Steps Forward
Getting your company’s data in order is a critical, if unglamorous, first step. AI can help find data anomalies and be enabled on a continuous search provided it is well trained on the typical examples of errors. A key source of institutional memory can be replaced with systematic corrections and overhauls. By eliminating the formulas in a spreadsheet that address data corrections, their complexity could be reduced by maybe 20%.
Reengineer with Structure (SIPOC)
Reengineering the spreadsheets periodically is another important step. With your AI assisted clean data, arrange your worksheets leveraging the concept of SIPOC – Supplier, Input, Process, Output, Customer. Specifically identify the data sources and what fields you need from each. That takes care of the S and I.
The P can be a bear. Often first built with random improvements, on short timelines, workbook formulas get longer and longer. Take a step back and understand the overall objective. Figure out the simplest way most of the analysis can be accomplished. Keep a list of the corner cases and don’t imbed them in the general analysis. Rather, segregate them in well-labeled sheets with simple and, if necessary, numerous distinct formulas instead of long complex ones. Consider simplifying by using models to address the occasional situations. For example, buffer the results with fixed percent adjustment, or group small outliers together as a single entity. These techniques work well in future looking analyses where there are many unknowns anyway. For both general and corner case treatments, be sure to document the processes.
O are your results. The results go hand in hand with C. What is your team looking for in the results? What questions are they trying to answer? What insights do they seek? In defining your results, KISS. As the workbook creator, you are deeply invested in the analysis. You know all the corner cases and all the pitfalls. Chances are this knowledge will NOT clarify the results. Frame the output as primary objectives (no more than 3) and secondary objectives (your long list of details and exceptions). Restrict the graphs and tables to the primary objectives. If someone wants to deep dive, design the results of the secondary objectives for those who know spreadsheets well and can navigate the data leveraging filters and pivot tables.
Spreadsheets – The Strategic Risk and Opportunity
None of the ideas I have outlined here are new or rocket science. They reflect decades of experience and observation learned from the value of discipline. The fundamental concept is to think of workbooks as a process. By cutting out wasted steps and complexity from the spreadsheet process, the streamlined workbook will help leverage AI. It will also help transition to ERPs and analytical software. The most essential motivator, however, is the risk a team incurs by NOT being disciplined, by NOT simplifying, and by NOT harnessing the institutional memory. In the short term, the results can be wrong and/or lead to bad decisions. In the long term, it will guarantee missing the benefits of the AI train.
Related Blog Posts
- Scale by Understanding Your Value Stream: Slides and Video Recap Martha Ryan and Terry Frazier offered a briefing on how to “Scale by Understanding Your Value Stream” at the Lean Culture on Feb-25-2021.
- Consider What’s Changed And What You Bring To An Opportunity
- Experiments Vs. Commitments
- How to Drive Innovation and Meet Commitments
- Slides from “Limits of I’ll Know It When I See It” Talk at SFBay ACM
- The AI Gold Rush: It Still Takes Teams
- Intrapreneur Mindset and Key Skills