CRITICAL Software's Blog

Crunched by Numbers: the Trouble With Spreadsheets

Posted by Yiannaki Loizou on 31/10/13 16:41

The business world is increasingly diverse and yet a number of common practices are still embedded within all kinds of organisations. One of these is a widespread reliance on spreadsheets, which lie at the heart of many businesses processes. And what’s not to like about the good old spreadsheet? On the surface, it’s a powerful, convenient and easy-to-use tool. We simply fire it up, type in our data and formulae and let the spreadsheet work its magic. No wonder it enjoys such a privileged status in our working lives.

But when it comes to complex data tasks, is our trust perhaps misplaced? Before long, a simple spreadsheet is loaded with more data, weighed down with more formulae, dressed up with pretty charts and, if we feel really ambitious, might even have a little code added behind the scenes. In no time, a once simple and humble spreadsheet can become a deformed and cumbersome mammoth, with each data interaction increasing the risk that our trusted electronic friend is a little less reliable and accurate. Sound familiar?

Many spreadsheets have excellent in-built tools that are ideal for supporting simple and relatively speedy processes such as calculating budgets and dealing with end-of-year accounts. For these kinds of tasks, spreadsheets are great for the flexibility they offer and their ease-of-use. However, many sizeable organisations also use spreadsheets to capture and process the data that support decisions about a range of more complex issues, from tracking customer relationships to analysing asset performance. The trouble is that spreadsheets are prone to a significant problem when it comes to bigger data challenges – we’re human and we all make mistakes. While the cost of human error can be a mere nuisance in less important tasks, for business-critical processes and decisions, it can be much greater. The Harvard University economists Carmen Reinhart and Kenneth Rogoff recently published a study on national debt that predicted incorrect and unflattering growth rates for countries where their spreadsheet indicated episodes of gross government debt equal to 90% or more of GDP. When a student tried to replicate the study, and found inaccuracies, the authors were forced to re-evaluate their work. They soon realised they had accidentally excluded data that would have shown that the countries in the study had registered much more positive growth rates. Although this error was spotted, significant political and economic damage had occurred and global fiscal policy had been affected. Reinhart and Rogoff can take consolation from the knowledge that they are not alone in committing spreadsheet mistakes. Ray Panko, Professor of IT Management at the University of Hawaii and an authority on bad spreadsheet practices, found that almost 90% of spreadsheet documents contain errors. The issue is now such a global pandemic that the European Spreadsheet Risks Interest Group has been established to raise awareness of spreadsheet errors and encourage practical measures to reduce their effects.

Ultimately, despite the trust and confidence we place in them, spreadsheets cannot automatically be regarded as enterprise-standard tools. Ironically, our trust and confidence can increase their potential to cause damage; we often take the accuracy of their determinations for granted. Once an error has crept into a spreadsheet, all the assumptions and data built on those foundations are affected. While a person running a small business from home might be able to afford the occasional spreadsheet error, larger businesses with complex data processes need more sophisticated, tested and controlled data solutions to avoid the risk of financial underperformance and reputational damage.

The Lonely Existence of the Spreadsheet
Beyond their vulnerability to human error, a more fundamental problem is that spreadsheets are often used in isolation. Staff in large and complex organisations typically make use of a vast number of spreadsheets, containing huge amounts of data and information. Every one of these spreadsheets is a live animal, containing ever-changing information that grows with the organisation. The problem is, when every spreadsheet sits on its own, the data and insights they contain are also isolated, growing further and further apart from the data held in other spreadsheets. Spreadsheets are simply not designed to share the information they contain across expansive, integrated systems, resulting in information ‘silos’ that limit the value of the data.

Breathing Life into Data
What’s the alternative? What if we were able to integrate the data in our spreadsheets, so that they are no longer isolated and used for only a single purpose? What if data used for one purpose could be recycled, given a new lease of life and allowed to form the patterns and trends that reveal broader insights? Merging data allows new secrets to be revealed and offers business leaders the intelligence to support more accurate business-critical decisions.

As an example of the power of collaborative data, Google Flu Trends has accurately predicted outbreaks of flu by collating data from the search terms used by millions of Google users world-wide. When suffering flu-like symptoms, users typically search for various key terms, which Google matches to IP addresses to predict flu trends in different regions of the world. Google is now able to predict regional outbreaks of flu up to ten days before they are reported by the Centers for Disease Control and Prevention. Critical Software Technologies’ advanced web-based tools offer exactly this kind of powerful collective data intelligence and industries and organisations from sectors as diverse as energy, transport and defence have already adopted tailored solutions.

For organisations that manage complex high-value, high-availability assets, Critical can devise a strategic roadmap to shift to a more powerful data-centric solution. To help achieve this, Critical has developed software tools that can be tailored to an organisation’s specific requirements. While spreadsheets can still form a useful part of data management processes, these software tools create the potential for central, validated data sets, capable of being transformed into easy-to-understand, actionable business insights. Bespoke solutions also offer the opportunity to build in quality and control checks to reduce the likelihood of human error where these are known to present issues at particular stages of complex data-handling processes.

In a fast-paced world, where we face rapidly lengthening to-do lists, the temptation of habit is always strong. However, with significant advantages on offer to organisations that evolve how they use their data, the spreadsheet habit is something many organisations can no longer afford to rely on.