The cost of spreadsheet errors ranges from embarrassing to catastrophic – from money lost to bruised reputations. Major financial services companies to top tier universities have suffered public damage because of spreadsheet errors. Something as simple as a missing minus sign, out-of-place parenthesis, or a cut-and-paste error can have a billion-dollar impact.
Spreadsheets are easy to access, use, modify, and share. The familiar rows, cells, and endless fonts and colors make just about anything look great while performing difficult calculations. Those good looks, however, can be deceiving. Behind colored frames and pivot tables lie a multitude of mistakes that can unwittingly get passed as fact. Using such information to support critical business processes that have major implications to the organization’s future can be dangerous.
Spreadsheets are a powerful productivity tool. But they do not scale effectively and can create minute yet crucial errors that get overlooked. They were not designed to handle the enormous amounts of data, intricate calculations, or numerous users required by today’s risk management processes. The decentralized nature of spreadsheets makes it difficult to manage risk across multiple business lines and impossible to see big-picture relationships or identify trends.
If you are hanging on to spreadsheets thinking they are doing a fine job, chances are they aren’t. Here are a few ways spreadsheets may be hurting, instead of helping, your company.
Each spreadsheet must be created from scratch, emailed to the right people, and collected back in a timely manner – often accompanied by follow-up calls to gather more information or clarify what was submitted. Companies can spend two to three months collecting, checking, and formatting values in preparation for an event such as an insurance renewal. The process is time- consuming – and is repeated for every single renewal.
Even the most conscientious employee can make an error when entering data or configuring formulas. A typo in a simple table of information or a wrong keystroke can generate an avalanche of misinformation in a network of spreadsheets. The sheer volume of data also means that it’s almost impossible to uncover where the mistake occurred. Invalid data or an incorrect value from one cycle can easily slip by undetected, leaving a lasting impact when calculating values for subsequent years.
With no systematic structure – and a wide range of user-design proficiency – anyone can create a spreadsheet according to their own requirements and personal habits.
Reformatting spreadsheets is time-consuming, and every piece of data being rekeyed or cut and pasted opens the door to more errors. Additionally, untangling information from other user-defined functions, lengthy macros, and formatting instantly increases the risk of misinterpreted information.
Lack of Spreadsheet Checks
Unlike structured applications controlled by IT departments, spreadsheets have few controls in place to verify the accuracy of the contents. The reality is that when users themselves are responsible for making sure everything is correct, testing for accuracy can fall through the cracks and double-checking work often gets overlooked.
Additionally, outdated assumptions, poor cutting and pasting, miscalculations, and corrupted files can easily creep into a spreadsheet chain, leading to disastrous results in the entire data system. Did you know that nine in 10 spreadsheets – and one in 10 formula cells – contain errors? That’s an alarming number.
Difficulties with Version Control
Thousands of spreadsheets might be passed around an organization at any given time. Keeping track of all those files, which party is updating them, and where they need to go can be challenging, especially with multiple users on a spreadsheet. Two users could be making changes simultaneously, potentially undoing each other’s work or accidentally using the wrong version.
Spreadsheets are designed to be easy to copy and share, with no real restrictions on who has access. With changes usually not being documented or tracked, users can manipulate information, making it tricky to know who changed what – or why. In addition, if placed into the wrong hands, a spreadsheet accessed by an unauthorized person could lead to exploitation of employees, markets, and competitors. Limiting access to a spreadsheet, which isn’t always practical, is the only way to eliminate the risk of compromised integrity.
With increasing compliance requirements, organizations are pressured to produce more reports, with greater accuracy, in less time. Extracting relevant information, by hand, from numerous spreadsheets is no easy task. When the right information is finally corralled into a meaningful report, chances are that inconsistencies will surface, requiring more effort before a final report can be delivered.
Because a spreadsheet program is already on your computer doesn’t necessarily mean it’s free. The cost of a solution has to be measured in terms of the total operational cost to the organization over the long term. Using spreadsheets creates real costs and lost opportunity that can significantly impact the business, such as the cost of labor and the time required to collect, input, validate, formulate, and consolidate data. This adds up to a significant expense and lost opportunity. Time spent inputting information is time that brokers and underwriters lose on strategic tasks and analysis that add long-term value to the organization.
Spreadsheets are a tool. Whether or not they are the right tool depends on the job. Spreadsheets are great at giving snapshots of information at a given time, but without real-time data it becomes difficult to react to changing conditions, make accurate future decisions, monitor ongoing performance, or analyze relationships over time. With data stored separately, spreadsheets make it impossible to visualize relationships between critical risks or the cumulative impact on the organization.
Companies with a limited number of locations, employees, and assets might find spreadsheets to be the perfect tool. But as a company grows, so do the risks. Eventually, the risk of using spreadsheets becomes the biggest risk of all.
Stratford Dick is senior vice president, product strategy for Marsh ClearSight, where he develops data, analytics, and collaborative workflow products that help risk managers achieve clear line of sight to their risk. He has helped leading companies like Baxter Healthcare, CNA Insurance, and Wheels develop new business-to-business solutions, use big-data models to predict and take action on risk, and drive greater customer experience.
Subscribe to Data Informed for the latest information and news on big data and analytics for the enterprise.