Kodak restates, adds US$9 million to loss – the hefty $11 million severance error was traced to a faulty spreadsheet. Kodak spokesman Gerard Meuchner said, “There were too many zeros added to the employee’s accrued severance.” (Source: MarketWatch, San Francisco, 9 November 2005) This is just one of the many examples of spreadsheet errors.
For more examples, you can even visit sites like this Web site which is published by the European Spreadsheet Risk Interest Group, whose goal is to illustrate common problems and errors that occur with the uncontrolled use of spreadsheets.
Obviously, the more complex the spreadsheet the more prone it is to error. According to studies performed by The Journal of Property Management in 2002, if your spreadsheet has in excess of 200 lines, the probability of error approaches 100 percent.
Ok, now you are starting to remember all of the budgeting spreadsheets used in the company, the forecasts, mergers and acquisitions analyses, support for top side entries, and if you remember correctly, they weren’t just simple calculations either. Very often, the IT department is called in to value-add and implement the right level of security controls and processes are in place to ensure that the data quality is maintained.
What can you do? How can you control spreadsheets from seemingly having a life of its own?
The Answer
You have to face it, you will never be able to replace all of the spreadsheets that exist in your company, they are just too easy to use, too nimble of an analytical tool and you will be hard pressed to keep them away from the teams of people in your organization. Here are the five steps you can take to gauge the level of risk these spreadsheets present, and how to create a controlled environment around the critical and most risky spreadsheets.
Step 1: How are your spreadsheets being used?
The first step is to determine what the uses of the spreadsheets are. Group them into the following categories:
– Operational
– Analytical/Management Information
– Financial
Next you must determine the complexity of the spreadsheets.
As the usage increases from Operational to Financial, and the complexity progresses from Low to High, so too does the importance of the integrity and reliability of the information generated by the spreadsheets. This dictates the level of controls that will be necessary.
Step 2: Determine the risks and issues with your spreadsheets
The next step is to evaluate the risks and issues with your spreadsheets. There are a number of things to keep in mind when performing this evaluation, chief among them should be the following:
– Complexity of the spreadsheet and calculations
– Purpose and use of the spreadsheet
– Number of spreadsheet users
– Type of potential input, logic, and interface errors
– Size of the spreadsheet
– Degree of understanding and documentation of the spreadsheet requirements by the developer
– Uses of the spreadsheet’s output
– Frequency and extent of changes and modifications to the spreadsheet
– Development, developer (and training) and testing of the spreadsheet before it is utilized
Step 3: Determine the necessary level of controls for your spreadsheets
We have isolated twelve primary controls that need to be considered in mitigating the spreadsheet environment:
1) Change Control
2) Version Control
3) Access Control
4) Input Control
5) Security and Integrity of Data
6) Documentation
7) Development Lifecycle
8) Back-ups
9) Archiving
10) Logic Inspection
11) Segregation of Duties/Roles and Procedures
12) Overall Analytics
The level of controls implemented should be considered relative to the spreadsheet’s use, complexity and required reliability of the information. Even for spreadsheets categorized as low in complexity and importance, control types (1) through (5) above should generally be in place.
Step 4: Evaluate ‘as is’ controls for each spreadsheet
This evaluation is performed by comparing the existing controls against the checklist of “necessary” control, such as those listed above. In addition, a test plan should be developed and executed to ensure that the controls operate effectively.
Step 5: Develop action plans for remediating control deficiencies
Finally, an action plan should be developed for each control gap that is identified. The focus of the action plans will be to increase the controls over the spreadsheet to the necessary level.
Parting words
More and more companies are relying on spreadsheets to assist them in an increasingly complex environment. However, it is clear that the flexibility, which is the tremendous benefit that spreadsheets provide, comes at a cost. Understanding how spreadsheets are used and the controls around them is a vital step for management to understand.
A spreadsheet management and remediation framework can be used to identify and address productivity, risk and control issues arising from the over-reliance on spreadsheets in the reporting environment.
Using this framework, we have seen the following benefits for our clients:
– Up to 90 percent reduction in manual labor and risk
– Up to 40 percent increase in staff productivity
– Realizing tactical “quick-wins” within a strategic roadmap
– Better understanding of the spreadsheet “problem”
– Foundations for a sustainable spreadsheet environment
Don’t let your company be the next victim to the spreadsheet blues!
–This article is contributed by Tan Shong Ye, Partner and Head of Security & Technology practice, and James Heiberg, Manager, both of PricewaterhouseCoopers.
Don’t miss related articles, white papers, product reviews, useful links and more! Visit our Spotlight on Data Management.
