Microsoft Excel, the most well-known spreadsheet software, is very popular because it lets users easily visualize and organize data. This allows people who have no technical background to easily analyze data and even build applications. A novice Excel user can create a basic spreadsheet that analyzes data much more rapidly than they could with a statistical language, like R, or a programming language, like Java. While this has revolutionized the way organizations work with data, the fact that users can easily develop and deploy spreadsheets does not come without risks.
Raymond Panko, a professor of IT Management at the University of Hawaii, wrote that “90 percent of all spreadsheets with more than 150 rows … contained errors.” Over the years, there have been some spectacular cases of spreadsheet errors causing serious issues. A few examples of such cases were mentioned in a paper about spreadsheet risks by Philip Bewig:
- A missing minus sign once caused Fidelity’s Magellan Fund to overstate projected earnings by $2.6 billion.
- Falsely linked spreadsheets permitted $700 million in fraud at Allied Irish Bank.
- Voting officials reported instances of spreadsheet irregularities occurring in New Mexico and South Africa.
To understand the risks surrounding spreadsheets, a wide body of research has been established by the European Spreadsheet Risks Interest Group. Their website has a trove of practical advice that can be implemented to reduce the risks that come from using spreadsheets.
In July of last year, I attended their annual conference in Manchester, England and met professionals from academia, software development, government regulation, project finance, real estate and insurance. As someone with an actuarial background who works with spreadsheets to model the profitability of insurance contracts, I have taken an interest in learning how to minimize the risks inherent with these tools. In many insurance companies, actuaries are the resident experts in spreadsheets.
And at the conference, there was a lot of interest in how insurance companies use spreadsheets. The keynote presentation of the conference, “Spreadsheet Use in Solvency II Internal Models,” was given by a representative of the Financial Services Authority, which is the financial regulator in the UK. They audited how spreadsheets were used by insurance companies and, in almost every case they found issues. These included:
- A lack of appropriate policies and standards regarding the use of spreadsheets.
- An inadequate enforcement of existing policies and standards.
- An inadequate identification or understanding of the critical spreadsheets in an organization.
- Since the audits, the FSA has seen progress in these areas, such as insurance companies introducing end-user guidelines.
Written instructions that detail specifics, like how to update a spreadsheet and who is supposed to update it, are very helpful, but they seldom exist. In many companies, it’s not uncommon for someone to be given a spreadsheet and simply be told to “update it.” Often, the person who built the spreadsheet has since left the company, no documentation exists and the spreadsheet has a maze of confusing formulae.
Other signs of progress that the FSA noted were that scripts are replacing repetitive data manipulation, critical spreadsheets are being identified and reduced, and spreadsheet management software is being used.
An interesting part of the presentation came when the discussion turned toward the topic of “side calculations.” I’ll loosely define a side calculation as one that takes place in a spreadsheet other than the spreadsheet template that would normally be used to perform that calculation. For example, suppose an accountant had to calculate the profitability for a business division using a spreadsheet template provided by the manager. The template worked when the accountant enters various credits and debits, after which the profitability is calculated and displayed in the sheet. If the accountant disagreed with the way the spreadsheet calculated profitability, he or she might set up their own spreadsheet to calculate it and then link the results into the template their manager provided. In this case, the spreadsheet that the accountant set up is an example of a side calculation.
When completing a piece of work, side calculations are often made. Often these spreadsheets are made on the fly and are not thoroughly tested, although checks for reasonableness are occasionally made. One presenter at EuSpRIG made a statement that seems obvious: “If you build software, you should include tests with it. Tests should not be an extra.” Spreadsheets that are not thoroughly tested but are still used to feed information to critical spreadsheets should be an area of concern for managers. What good are controls on critical spreadsheets if the side calculations that sometimes feed into those sheets are not also controlled?
There are many other risks that exist when using spreadsheets, but it is important to realize that you do not have to be an Excel expert to understand some of the controls that should be implemented. Spreadsheet risks affect any organization that utilizes them, and it is important that management takes steps to minimize these risks. There are many examples of costly mistakes that arose solely from the improper use of spreadsheets. For technical and non-technical users alike, the EuSpRIG website contains practical information about controls that can be built around spreadsheets. A visit to this site is a good step toward learning about the policies that can be implemented in your organization to minimize spreadsheet risks.
Mark Chisholm is a London-based actuarial analyst working in general insurance, helping insurers use data to determine prices for their policies and hold enough reserves to pay out future claims. You can reach him at email@example.com.