THREE INFAMOUS SPREADSHEET CASES
In April 2014 Thomas Piketty published a 700-page book called Capital in the Twenty-First Century.
The New York Times said it ‘will be the most important economics book of the year—and maybe of the decade.’
Fortune called it ‘Groundbreaking…The usefulness of economics is determined by the quality of data at our disposal. Piketty’s new volume offers a fresh perspective and a wealth of newly compiled data that will go a long way in helping us understand how capitalism actually works.’
Some economists say he is up for a Nobel Prize for it. But not everyone agrees. He uses voluminous data, but based on spreadsheets. The FT later said ‘In his spreadsheets, however, there are transcription errors from the original sources and incorrect formulas.’ After the FT cleaned up the data, they came up with some conclusions that differed from the author’s.
Whether or not this negates the core of the argument is hotly debated. But the lesson is that even at the very highest level of international financial intelligence, spreadsheets can have glaring errors that go unnoticed. If you still have any doubts on this, here are two other examples.
In 2010 two Harvard professors published an economics paper in the prestigious American Economic Review. Called ‘Growth in a Time of Debt,’ it became hugely influential, and was turned into a best-selling book. Its arguments act as scientific justification for international austerity measures, adopted by many governments.
But a graduate student tried to reproduce the results, and failed. The professors shared their Excel spreadsheet with him. The student found flaws, including sums run over entirely wrong cells.
When JP Morgan’s Chief Investment Office wanted a new model for a credit portfolio they assigned an expert mathematician to build it. It ‘operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.’ In the aftermath of the financial trading melt-down, errors were found. ‘After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average as the modeller had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the value-at-risk.’ It is possible that the error cost the bank £5billion in revenue.
Before we go any further we should make it clear that we’re obviously not saying anything negative about spreadsheets themselves. Spreadsheets are clearly powerful, easy to use and flexible. These are great strengths. Everybody uses spreadsheets, and as we now know, financial wizards even use them to steer entire economies.
But ‘powerful’, ‘easy to use’ and ‘flexible’ are also great weaknesses. When doing something important on spreadsheets, like running your company, spreadsheet shortcomings quickly emerge. This is worrying, because if governments, world-leading mathematicians and top economists can make major errors that go unnoticed, what hope is there for mortals like us?
The rest of this paper focuses on the biggest drawbacks to using spreadsheets in a business context, in the hope that users will be better armed when wrestling with spreadsheet issues. The list is also helpful for readers seeking internal justification for moving on from spreadsheets to acquire a business management / project management system (such as Synergist).
A. Poor linking
Spreadsheets can be linked, but in practice we notice that few are. Why? Users tell us that as no one person manages all the spreadsheets, there’s a concern that a change in one will mess up the link to the other one. The result is silos of separate information. This makes it hard for individuals to know precisely what the latest information is. Is it on the spreadsheet you’re looking at, or in another one?
Consolidating spreadsheets isn’t a task for the uninitiated. Spreadsheets always grow in complexity as additional levels of data are required. New categories, new columns, new tabs, new subtotals. You quickly find you have dozens of clusters of information that need to be reassessed whenever someone wants to add yet more complexity to it, or when a new report is wanted. So links between spreadsheets and between applications (eg to the accounts system) are hard to make work.
B. Spreadsheet proliferation
The number of spreadsheets that companies use grows and grows as new angles are thought up by the team.
For example, Europe Economics is a project-based firm offering consultancy services on economic matters to companies and organisations such as trade and professional associations, regulators, competition authorities, governments and the European Commission.
“We previously used an array of spreadsheets, one for each major process, for example:
“We had all sorts of difficulties juggling all these spreadsheets.” They now use Synergist instead, unifying all these processes into one system.
Spreadsheets can be moved to a different folder. Hunting them down can require detective skills. And of course copies can be made, without it being clear which is which. And many spreadsheets are simply created by one individual and held on their separate system. So what happens if they are taken ill or leave the company? Important data, part of the company’s hard-won intelligence, so easily disappears.
The only solution to all these silo issues is to have one system accessed by all, subject to permission levels. Everybody knows where the data is and knows that they are seeing the real thing. No more working on old data or hunting for missing elements. Everything links together.
Maintaining and cleansing spreadsheets is inherently hard.
Let’s see why.
A. Multiple authors
Many of your spreadsheets are probably created by different people at different times, each using different approaches, definitions, descriptions and formulae. These inconsistencies make them hard to use and reconcile.
And if the original author of the spreadsheet is absent, it can be notoriously difficult to keep it functioning or amended.
B. Invisible errors
It’s hardly surprising that errors creep into spreadsheets, with complex formula manipulations, manual data entry and multiple copy-and-pastes going on. How many times have we all missed a negative sign or mistaken one column of numbers for another? Spreadsheets that contain errors don’t let you know. They simply display the wrong number. Luckily, some errors leap out at you because they are wildly wrong. But others don’t, and these are the dangerous ones that slip through the cracks and can then even be replicated in different spreadsheets.
In 2008, professor Raymond Panko of the University of Hawaii published 13 field audits that analysed spreadsheets that had been used in various real-world environments. His conclusion showed that 88% of the spreadsheets had errors.
C. Data on time
When running a business on multiple spreadsheets it is very common to find that the numbers don’t quite add up. It could be a simple formula error or a wrong cell targeted, or it could be that the source data was taken at different times from different sources and one was out of date. The number of potential permutations makes it notoriously time-consuming to resolve. By the time it is resolved, the information can make the data close to useless in terms of being able to solve underlying project, client and business issues.
D. Data veracity
Setting up a business spreadsheet takes a lot of thought. Is everyone going to get each one right first time?
Ask any programmer what percentage of their time is spent testing the software and fixing the bugs. It’s going to be considerably more than half of the time. Yet few spreadsheet creators would think of investing that much time in systematic maintenance work.
E. Data logic
As if all that wasn’t enough, spreadsheet data has no systems logic or business processing intelligence built in. So you can enter a number that from a business perspective conflicts with something else you have entered or simply can’t be possible, and it won’t see a problem.
A comprehensive business management / job management system, however, has comprehensive business process logic built in, with checks and tests to try to save you from such errors.
The co-founder of a fast-growing project-based company in London recently said:
“You need good processes today. And that’s why Synergist wins out. Synergist totally understands your processes.” So a system that thinks like a company running projects, rather than spreadsheets that think like calculators, are very different animals.
F. Poor auditing
Most people find it hard to thoroughly review spreadsheet formulas.
This is partly because not everyone has a software engineer’s mindset. And it’s also because each formula is located in a different cell. It’s a laborious job to audit a complex spreadsheet, not to mention a full set. And then the following day someone can tweak a formula and -- off you go again.
Multiple spreadsheets cannot provide live information because the data is so scattered. So some of the information quickly becomes out of date.
The Operations Director of a project-based business recently said:
“With Synergist we understand the metrics of every part of the company and track the processes and the finances. With it, we can spot issues early. We use the key reports a great deal. It’s essential in decision-making.”
“We use Synergist’s report alerts. For example, when estimated costs logged to a job reach 50% we see whether we are half way through completing the job. It flags up problems early. So when we get to 100% there aren’t any surprises.”
Up to date information is needed for three main reasons.
(1) To steer the business and inform decisions, daily.
(2) Periodically, such as month-end.
(3) At short notice, eg when something seems to have gone awry and the MD, the external accountants or the investor needs to see the big picture.
With only a series of spreadsheets to help, a huge amount of management time can go into trying to pull it all together. Whereas with a serious system, the numbers are up to date and available all the time.
Wasted time is not merely lost productivity. All that spent energy takes a heavy toll in other ways too.
Your team members didn’t join your company in order to spend their time hunting for information, re-entering information, reconciling data discrepancies or wrestling with spreadsheet formula errors.
It’s demotivating for everyone not to be able to focus properly on their real job. It’s demeaning when it happens month after month. What does it say about the company’s attitudes? Does it mean that wasted time doing petty repetitive tasks doesn’t matter? What sort of message is that?
Diminished client service
It gets worse. All that wasted time gets in the way of being able to deliver good client service.Wouldn’t your team come to the conclusion that your company culture doesn’t put client service high on the priority list?
The productivity question
And of course there are the costs of the wasted hours due to spreadsheet issues. For case study examples of productivity gains and ROI, see the Synergist website or contact us.
The vast majority of spreadsheet users across the world use Excel. Excel was not designed for collaborative work. Over time this picture will change with vendors (eg Google Docs) scrambling to produce collaborative applications for use via web browsers and the cloud, but the reality today is that Excel is what most people know and use every business day.
Forrester’s Q3 2013 Global Productivity Suite survey showed that Microsoft Office had a 95% share in the productivity marketplace, and the numbers are unlikely to change dramatically very soon. And many firms who are experimenting with Google Docs still use Excel anyway, according to Forrester, for many reasons such as user investment in Excel knowledge.
Using Excel collaboratively is problematical. To illustrate this, assume one user opens a spreadsheet and begins to make changes but hasn’t yet saved them. Minutes later, a second user opens the same file, unaware of the imminent changes, and begins to make other changes. Who saves first? Will the two sets of changes conflict? You get the picture.
The only solution is to have one single central source of all the data, removing the need for the vast majority of spreadsheets.
A system’s scalability is its ability to continue to function well when the number of users and the amount of data handled by each user grows and continues to grow.
There are several issues to consider here.
Avoiding a disruptive break point
Enforced system break points can bring enormous problems and some business risk. The timing is forced by the system break, not chosen by management. Breaks therefore tend to happen at a time of growth, when everyone’s focus is on the challenging job of managing complex business change, new clients, new projects, new staff and so on. Could there be a worse time to distract management from steering the ship?
Growth opportunities aren’t always planned
Unless you happen to have a crystal ball, in reality a lot of business growth tends to come from slightly unexpected quarters. A client’s success suddenly takes off. A new vertical market opens up. You hire a brilliant business development manager. When such turning points happen, you want to be empowered by your business system, not constrained.
How many is too many people?
Synergist can be used by 15 to 500 people.
Each spreadsheet you create is designed to focus on one area or deliver one sort of result. Later on, however, a separate focus is often needed for another type of analysis sharing some common elements with the first. So the person needing it has two choices: create a new one from scratch, or adapt the first one. Which to do?
Most people adapt the first spreadsheet. It’s surely going to save a lot of time, and might even reduce the risk of new errors being created. Yes? But the problem now is that the expanded spreadsheet is pulled in two different directions.
For example, various assumptions may have been made in the first spreadsheet that are simply inappropriate in the second one. Or some of the formulae may involve some historic data relevant to the first but not in the second. However once data is in spreadsheet form most people give it a credence that is beyond it’s worth. The numbers in a big impressive spreadsheet must have some validity, right? Whereas if the same information was merely spoken out loud the assumptions would immediately be challenged and tested.
Flexible? Spreadsheets are almost infinitely flexible at the level of each cell, but inherently inflexible as a complete spreadsheet. To change the emphasis of a complete spreadsheet often means rethinking much of the model -- no trivial task when someone’s in a hurry and may not even be completely aware of all the implications.
In 2013 Forbes published an article called:
As article headings go, it did its job -- it made its point and won attention. It may have been deliberately provocative, of course. But it went on to say that if the spreadsheet didn’t exist, then a lot of what the financial markets do couldn’t be done. ‘There would be no collateralised debt obligations, no credit default swaps, indeed much of the complexity of the financial markets would simply disappear in a puff of smoke.
‘Quite simply, without Excel we’d not have had the incredible financialisation of the economy over the past 30 odd years. And if we hadn’t had that then we also wouldn’t have had the financial crash of 2007. So there’s a dangerous piece of software for you.’
It’s a fanciful notion, but a sobering one. And in such a world, what would project-based businesses run their companies on? Systems designed for exactly that purpose. Coherent, intelligent, scalable, multi-user, real-time, everything-in-one-central-place systems. Like Synergist...
Our new App is available to download for Android, iPhone and iPad devices.
Providing on-site demonstrations, training, experience and consultation.
Synergist are sponsors of BMC The Business Marketing Club