A Love Letter to Spreadsheets 💕
I love spreadsheets! I genuinely do. It may possibly be the nerdiest thing I’ve ever admitted to. Maybe it’s the IKEA effect, having invested 20 years honing vlookup skills, formatting pivot tables, and choosing just the right graph to make my financial data mean something to someone, or maybe I just like the feeling of sinking into that comfortable spreadsheet memory foam. Don’t get me wrong, my spreadsheets are far from perfect, but I just can’t walk away.
Hear me out: Spreadsheets can do powerful calculations, they’re flexible, and they’re widely available! Sure, there are plenty of new platforms and apps around wanting to disrupt my spreadsheet habit (Hello, Airtable! I see you Coda 👋)— but I would need to start learning all over again. Spreadsheets are easy. They allow me to play around with numbers, presentation, formulae, graphs. They give me the freedom to prototype a solution without committing to the final output. There are few tools around in the world of finance that can grow with the business, enable finance to adapt to changing business requirements, and empower us to rapidly model business decisions into financial outcomes.
I’m not alone either! In 2017, research showed that 71% of organizations are using excel to collect data across business units. The demand for end user computing (EUC) applications has never been higher. Even with the proliferation of platforms across finance, many teams are still reconciling and aggregating data in spreadsheets despite unprecedented investment in IT solutions. The ex-auditor in me shudders when I think of the risks inherent in spreadsheet use across finance.
It’s reported that close to 90% of spreadsheets contain errors — given that Microsoft reports over 1 billion O365 users globally — we’re dealing with an erroneous spreadsheet pandemic!
Well, maybe love was a bit of a strong word — maybe I’m just hooked? A quick internet search for ‘spreadsheet errors’ returns 49M results — with some very high profile facepalm moments among them. Imagine, for a moment, you are a world renowned psychologist and author of a New York Times bestseller on Dishonesty. You wake up one day to find yourself in the eye of a Twitter tornado calling out blatant spreadsheet high-jinks that undermine your research? 🥴
Suddenly, I have error terror! So, what are the most common risks, and what can we do about them?
⚠ RISK 1 — WHERE ARE THE SPREADSHEETS?
Most of us have access to spreadsheet software of some description. I’m 100% guilty of saving down ‘versions’ of my spreadsheets. I don’t really have an agreed naming convention. My filing system is adhocratic — I trust I’ll find my way back to the right version with a little help from my ‘recent files’ menu .
It’s not uncommon for spreadsheets to proliferate across an organisation. with work becoming more decentralized and teams working remotely, I’m willing to bet there are multiple copies of every spreadsheet hanging over the metaverse. And yet, there is no inventory or repository kept of all spreadsheets in use.
Anyone with final responsibility for reporting has experienced the real sense of fear as you attach a spreadsheet to an email. While we can pay particular attention to the high priority or high-risk areas of the business that depend on spreadsheets such as external financial reporting, cash flow planning, forecasting, or spreadsheets that are used in strategic decision-making, we need a solution to inventory and control our prolific use of spreadsheets.
So, spreadsheets — while I 💓 you, there are some areas we need to work on. I went ahead and made a list (I hope you’ll forgive me 🤨!)
💡 WISHLIST #1
An inventory of pre-approved templates with documented approval and owners, categorized into levels of risk
💡 WISHLIST #2
The ability to create approved versions of a spreadsheets and have them available within the inventory for a rollback review, or at various stages of development
💡 WISHLIST #3
The ability to simply, and securely, link workbooks directly to a data source — eliminating the need to replicate data by copy/paste
⚠ RISK 2 — TOO MANY COOKS
In this age of collaboration where most of our data and software resides in the cloud, working together in spreadsheets with autosave turned on is uniquely terrifying. Each of us can recall a moment where we’ve mistakenly right-clicked ‘delete’ instead of ‘insert’, or copied and pasted over some really critical data. Praise indeed to the person who invented the ‘undo’ feature — a true life saver!
Collaborating in spreadsheets is one of the riskiest things we do in finance. With little control over new cells being added, changes made to calculations or formulae, or calculated cells being overwritten either intentionally or unintentionally, a covenant of trust is the only way forward! Even a small error in a formula can compound significant errors in judgements or decisions made based on the financial output of a spreadsheet. Without any traceability, who’s accountable when things go wrong? According to Fannie Mae — no one — it’s simply a $1bn ‘honest mistake’.
Fannie Mae’s $1bn ‘honest mistake’ — Fannie Mae corrects mistakes in results
Fannie Mae, the mortgage company, made an $1.1 billion mistake in a spreadsheet. They had to correct their financial results of the year 2003. Although the specific cause was not uncovered, the time pressure to adopt new accounting standards has led to the error. Jayne Shontell, Fannie Mae senior vice president for investor relations, said:
There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard.
💡 WISHLIST #4
An audit log of all changes made within a workbook, by user, with rollback functionality
💡 WISHLIST #5
Ability to lock sheets and blocks of cells to user groups — reducing the risk of accidental overwriting of source data or critical formulae
⚠ RISK 3 — ACCESS CONTROLS
Can you think of another system in finance that doesn’t have access controls built in? For good reason, the Sarbanes-Oxley Act of 2002 (SOX) requires that all organisations should behave ethically and limit access to financial data. Data breaches and leaks are not uncommon, and can lead to significant financial loss for a company — recently evidenced by the FinCEN leaks in the US.
IT systems typically engage the principle of least privilege (POLP) when determining the right level of access to assign to a user. The POLP is the practice of restricting access rights to only those needed to do the job. It’s a basic tenet in digital security, and one that finance has embraced as a key control. Whether it’s an ERP system, Business Intelligence software, or a Reporting platform, we implement stringent access controls for all users to ensure that the right people see the right level information. Yet when it comes to spreadsheets, anyone can access no matter how sensitive the data, or how critical the financial model is. With so many finance teams working remotely in recent months, I can’t help but wonder if we are carrying additional risk due to poor access controls for spreadsheets. We need some guardrails to protect the company and the employee.
💡 WISHLIST #6
Enable role profile access to be provisioned per spreadsheet. Access could be edit or read-only. Copy/paste functionality could be a privilege rather than a default permission.
💡 WISHLIST #7
Ability to limit access to data, or spreadsheets, within a workbook that contains sensitive information.
💡 WISHLIST #8
Built-in workflow approvals that ensure a level of due diligence is followed in the preparation of financial information
⚠ RISK 4 — UNDETECTED ERRORS
I love how spreadsheets hide all those complex calculations in the formula bar — all we see is the end result. It’s like a little bit of finance magic! Whether it’s a simple SUM function or a complicated nested IF statement, once we’ve automated those tricky calculations, we rarely revisit them again. Out of sight, out of mind. It’s really quite beautiful… until it gets ugly!
The Reinhart-Rogoff Facepalm Moment — Harvard’s Carmen Reinhart and Kenneth Rogoff are two of the most respected and influential academic economists active today. Or at least, they were.
On April 16, doctoral student Thomas Herndon and professors Michael Ash and Robert Pollin, at the University of Massachusetts Amherst, released the results of their analysis of two 2010 papers by Reinhard and Rogoff. Reinhart and Rogoff’s work showed average real economic growth slows (a 0.1% decline) when a country’s debt rises to more than 90% of gross domestic product (GDP) — and this 90% figure was employed repeatedly in political arguments over high-profile austerity measures.
However, when they obtained the actual spreadsheet used for the original calculations, they identified three errors.
The most serious was that the esteemed economists had not selected the entire row when averaging growth figures: they omitted data from Australia, Austria, Belgium, Canada and Denmark. In other words, they had accidentally only included 15 of the 20 countries under analysis in their key calculation. When that error was corrected, the “0.1% decline” data became a 2.2% average increase in economic growth.
Would you release a book, or an article online, without having it reviewed (or at least having it spell-checked)? Me neither! Yet our spreadsheets often pass straight into the ‘live’ environment without any due diligence.
💡 WISHLIST #9
A spreadsheet audit functionality to highlight the most commonly reported spreadsheet errors
💡 WISHLIST #10
An approval process that releases a financial model, or a spreadsheet of any kind, into ‘production’. This approval process should certify that the workbook has passed an audit and is available for general use.
⚠ RISK 5 — SPAGHETTI JUNCTION
As a finance leader, I’ve become accustomed to workbooks linking to other workbooks — it’s often the only way to enable multiple workstreams to work simultaneously to create a collated set of financial numbers. What transpires is an untraceable spaghetti junction of formulae and spreadsheets that no one has the time, nor the capacity, to investigate. Tentatively, I put my trust in original preparer in the absence of easy traceability of inputs and formulae.
CFOs must have confidence in their data. They must know the exact data sources that feed into their financial reports — and their providence. The reality is that many finance departments don’t have visibility into data flows across their spreadsheet landscape. The data links between the different spreadsheets across departments, regions, and models are routinely undocumented and so impossible to view and accurately decipher. Dear spreadsheet — if it’s not too much to ask, can you make it easy for me to see where the data is coming from?
And while we’re on the subject of sharing — too often I need to pop out of my spreadsheet to send an instant message to a colleague asking for their input, checking that they have completed their portion of the work, or seeking an explanation for a variance. Imagine a world where we can seamlessly comment on a cell in real-time, without leaving the spreadsheet — or better still, assign a task to a team member and have the spreadsheet chase it up!
One study actually found that errors decrease by 78% when more than two people collaborated on a spreadsheet — maybe we feel more accountable when we work together. Either way, if two heads are better than one, let’s work together to eliminate risk.
JP Morgan and the ‘London Whale’ — Basic Excel flaws and incorrect testing led to JP Morgan Chase losing more than $6 billion in their London Whale disaster.
The banking and financial services holding company suffered a $6bn loss due to a copy and paste error in a new ‘Value at Risk’ (VaR) model. JPMorgan’s Chief Investment Office needed a new VaR model for a synthetic credit portfolio, and assigned the task to a spreadsheet wizard (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models). For the new model to work, data was copied and pasted from one spreadsheet to another. The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated. After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly, “After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”
Even spreadsheet wizards can make mistakes!
💡 WISHLIST #11
A graphical representation of the interrelationships within a workbook would be really useful. I know I can trace precedents — but it’s a horrible process 🥱
💡 WISHLIST #12
Ability to have real-time dialogue between team members within a workbook with mention functionality. I want to stay in flow when I’m in the spreadsheet zone 🙏
💡 WISHLIST #13
Ability to assign tasks to team members within a collaboration workbook, and have the spreadsheet do the chasing up!
The proliferation of spreadsheets across the world of finance is largely due to its flexibility and ease of use. For exactly those reasons, anyone can create a poorly executed, undocumented and visually impressive spreadsheet. Whenever finance think ‘how can I do this without IT or data engineering’, spreadsheets are the go-to tool of choice.
But what if we could do better? What if spreadsheets could embrace the rigor and governance of IT systems, the collaborative functionality of SAAS platforms, while retaining the flexibility and ease of use that have made them central to finance teams for decades? If you, or your team, are on a mission to Make Accounting Great Again — I would love to hear from you!
