Whitepaper: Six ways traditional spreadsheets are hindering your true potential
The world runs on numbers. But, have you considered what would happen if the numbers being used by your business were wrong?
Spreadsheets are used by most businesses around the world, but, while the world has moved forwards, spreadsheets have remained unchanged. This means that spreadsheets are often impractical, cumbersome, and perhaps most worryingly, contain data that is simply incorrect.
In this paper, we examine the six main issues inherent in traditional spreadsheet software and explore how these problems can adversely influence business planning, day-to-day workloads and even governmental decision making.
Errors
Take the Fidelity Magellan Fund, for example. The simple omission of a minus sign led to the over-statement of capital gains to the value of USD 2.6 billion (£1.6 billion), and ultimately, the announcement of a share distribution that could not be honored.
A review by a PhD student found basic spreadsheet errors in the analysis performed by leading Harvard economists, Reinhart & Rogoff, in an influential paper on government debt to GDP ratios.
Worse still, the paper was used to back up the decisions of a number of governments, including the UK’s, to undertake austerity programmes cutting levels of government spending. In the UK alone, these austerity cuts are worth £25 billion to date.
Although the economics debate is open for discussion, the episode was embarrassing for politicians worldwide. Having your soundbites debunked by a student’s homework is not a good look.
It is estimated that up to 90% of spreadsheets contain errors – and even small errors can mean big money.
In an experiment conducted by Panko and Featherman, developers were asked to estimate the likelihood that they had made spreadsheet errors during development.
Most confidently assumed they their work was correct. However, 86% were found to contain errors.
The findings of the study were clear: most large spreadsheets are likely to contain significant errors.
Scalability
For simple ad hoc business processes, spreadsheets work. Anyone can use them. So, when a new project begins, it’s likely to start as a spreadsheet.
But, what happens when time passes, and suddenly your spreadsheet model has become an application supporting an entire business process?
Fast forward four years. There are now 50 people, from multiple locations, collecting new product data from the field each week, aggregating it for monthly cash flow forecasting and comparing it with existing product data. New products are being released, and the spreadsheet continues to grow.
The process now consumes over 200 hours a week as unreturned spreadsheets are chased, reviewed for errors, and an increasing number of reports are created.
The issue of scalability is frequently overlooked – what was suitable for day one, may not be suitable now.
The analysts driving this process each cost the business $80k a year, plus benefits. The spreadsheet might be considered ‘free’, but managing this process is certainly not.
With nearly 800 million spreadsheet users in the world, businesses need to fully understand which processes the spreadsheet is adequate to support, and which processes and projects should be maintained using a more suitable, and more cost-effective, solution.
Flexibility
Data is intrinsically linked in traditional spreadsheets – but manual intervention increases workload and risk.
Traditional Spreadsheets are built on two dimensions. They can’t see anything beyond that. When you need more than two dimensions, as is often the case, you can approximate your data in Excel manually.
This process takes time, and increases the possibility of error. Likewise, once you have approximated your data, the spreadsheet is unlikely to understand the data in its native format. The solution, again, means more manual intervention, more time and more risk.
When understanding a spreadsheet’s ability to respond to changes in models, there are three core components you need to consider: presentation, logic and structure.
Within traditional spreadsheets, these three elements are intrinsically linked. You cannot change one without considering the effect on all three components.
Here’s an example. You want to turn a three-year cash flow forecast into a seven-year cash flow forecast. Using a traditional spreadsheet, the process would involve manually inputting the additional years to the right hand side of the model. But consider the following:
1: Presentation
Will this affect the presentation of any other the spreadsheet?
2: Logic
Even after copying existing formulas into the newly created columns, can you be sure your cell anchoring remains unaltered and working correctly?
3: Structure
What if this analysis was conducted across multiple product lines? Has this new structure been replicated across your entire worksheet?
Has this potentially compromised the integrity of your data?
The answer to all these questions is uncertain, without manually auditing every cell. This adds time and increases the chance of critical errors invalidating the spreadsheet.
Clarity
The calculations used in spreadsheets are often unclear – if a key stakeholder leaves, knowledge is lost.
This person created an original spreadsheet model four years ago and, since then, have been rigorously maintaining and updating the model.
The departure of key stakeholders is common and, given the scale of some models used, the handover of all knowledge is a huge task.
Even with the extensive documentation processes that are likely to be in place, it is hard to ensure that everything has been accounted for until an individual has already left.
Due to their complexity, it is often spreadsheets, and the formulae used within them, that get overlooked during this critical period. Most people can understand the logic used in a spreadsheet when it is shown to them in simple language, as shown below:
Current Assets = Cash + Accounts Receivable
Yet, when inspecting a cell in a traditional spreadsheet, the only information you are likely to see is:
=B6+B13
This isn’t particularly informative. A third party looking at the spreadsheet has no idea of what each of these elements refer to, or even what is being calculated.
In examples like this, modelers often have to rely on qualitative information to fully understand what is being displayed in a spreadsheet. The risk for businesses is that such information is not always available.
Efficiency
Spreadsheets regularly need a unique formula for each calculation – and this creates a host of problems.
The most common way to create a spreadsheet calculation is by clicking on a cell and hitting the ‘equals’ key. When that formula is needed in other cells, the original formula is copied and pasted into the new cells.
The process is straightforward enough – but the increased use of formulas in your spreadsheets opens you up to a range of potential issues.
1: The number of formulas to maintain
As a spreadsheet model grows, so does the number of unique formulas. The formulas take time to manage, maintain and update, and this comes at a cost.
2: Even more human error
The level of risk in a spreadsheet is directly proportional to the number of formulas used. In short, the bigger your spreadsheet, the more likely it is to contain errors, and the more incorrect formulas are used, the more compounded the issue becomes.
The human error website lists data from a number of studies of human cognitive errors.
These studies indicate that, even for simple cognitive tasks such as flipping switches, error rates are about one in 200.
For more complex cognitive tasks, such as writing lines of computer code, error rates are about one in 50 to one in 20.
Of course, everybody would like to think that their spreadsheets buck the trend, but in reality, the odds aren’t in your favor.
3. Performance
Traditional spread-sheets weren’t originally designed to handle very large datasets and complex calculations.
As the number of formulas you use increases, the calculation performance invariably slows. Eventually, your models may even grind to a halt. It is commonplace to find organizations who are heavily invested in traditional spreadsheets waiting, in the worst cases, multiple hours, often relying on ‘overnight runs’ to complete their calculation.
Collaboration
Teams regularly collaborate on projects – but this creates more versions and more confusion.
Spreadsheet models are often initially created as part of one-off exploratory projects. Frequently though, these models evolve into large team efforts involving a number of collaborators who maintain, develop and update the spreadsheet.
In an increasingly global world, groups of collaborators, often from many different departments and locations, will need to contribute – and this process needs to be managed carefully.
All too often, this ends with a number of different versions being distributed throughout your business simultaneously. This is called the ‘single source of truth’ problem.
The result is obvious.
The integrity of the spreadsheet is always in doubt, as a number of versions need to be compared, contrasted and edited to create a true ‘master file.’ If the issue remains unnoticed, incorrect versions of the spreadsheet will be added to, circulated, and then returned. The process can continue indefinitely.
The lack of controls make it is hard to determine when and where in the process any critical changes, or errors, have occurred.
Traditional spreadsheets have attempted to address the collaboration problem, but the solution of ‘cell protection’ makes models inflexible, hard to edit, and removes the capacity for ‘self-service modeling’, decreasing employee efficiency and productivity.
Conclusion
Spreadsheets haven’t evolved enough to keep up with the requirements of the modern workplace.
Survey respondents overwhelmingly plan to reduce their reliance upon spreadsheets for finance activities, with up to 61% in some sectors indicating their preference to move to a new solution.
It is a damning market indictment.
Despite this, traditional spreadsheets continue to be the most commonly used tool for financial planning and analysis across the world.
And the larger the company, the more they are used – 92% of companies with more than 5,000 employees report using spreadsheets for financial planning and analysis, while they are used by 79% of companies with less than 50 employees. That’s a lot of spreadsheet errors.
Too many businesses are over-reliant on outdated, inflexible software – this puts them, and their quality of work, at risk.
Quantrix Solves These Problems
1. Errors
Errors are a thing of the past. The multidimensional modeling solution from Quantrix produces the most reliable and robust solution for any business which requires accurate, accessible and actionable (AAA) data in realtime.
2. Scalability
In built scalability. When you build a Quantrix model, it grows with your needs. Common scenarios such as adding a new product, fund, investor or asset to analyze takes seconds and is replicated throughout the model wherever relevant. Turning a three year forecast to a four year forecast across multiple different analyses can be achieved in one click.
3. Flexibility
Quantrix understands multidimensional data. It will render it in two dimensions, but it’s aware of the complete representation. In a traditional spreadsheet you have to imagine how your data maps to 2D and then manually create the result with the software. You tell Quantrix how your data actually looks and it creates any layout you want, immediately. You can model without the extra burden of being a data translator. You can think clearly about your data, phrase it naturally, and write intuitive and efficient formulas to find the answers you need.
4. Clarity
Quantrix creates self-documenting models, with use of clear natural English formulas and the ability to easily trace dependents/precedents with the visual dependency inspector. This transparency creates confidence in the business logic used to build up complex models which leads to faster data-driven decision making.
5. Efficiency
In Quantrix, formulas are not written in cells. Instead, they are written in a separate formula section. They are also written to refer to the item or dimension for which the formula should calculate. 95% less formula to write and audit means less errors and faster performance. Customers have reported calculations which took 10 hours to recalculate in traditional spreadsheets, taking only 10 minutes when modeling in Quantrix.
6. Collaboration
A true real-time ‘self-service’ modeling platform. User roles and permissions allow for the creation of a ‘single source of truth’. Users can create and save versions of models and Quantrix takes care of the storage and retrieval using a lightweight web interface.
About Quantrix
Thanks to the flexibility and adaptability of our solutions, Quantrix helps drive data insight to more than 1,100 customers in 50 countries, ranging from the largest multinationals to individual business professionals.
We help finance, sales, risk, engineering and scientific professionals as well as policy researchers, entrepreneurs and business consultants take their data management and analytics to the next level.
Quantrix delivers rapid return on investment by empowering business and financial professionals to break away from restrictive two-dimensional spreadsheets and complicated modeling and business intelligence tools.
Quantrix Modeler is available directly from the company and from its partners in the United Kingdom, Ireland, China, Australia, New Zealand, Germany, Mexico, Chile, South Africa, the Philippines, Italy, and Brazil.
Quantrix is an independent business unit of IDBS, a leading worldwide provider of data management and analytics solutions.
