IT - Information Technology
Spreadsheet Risks & Mitigation
Read in 6 min. If your company is using spreadsheet - and most probably is - you should know the risks and how to avoid those. We review the top 5 risks of using spreadsheet in business along-with their remedies.
A Business Trainer from
Nooruddin is available for projects both virtually and in-person.
When the first spreadsheet "VisiCalc" was conceived by the Harvard Professor Dan Bricklin in 1979 he might never thought that the product which he sold for 10,000 USD will make fortunes for others and will be a source of earning to the buyers for millions of dollars.
Spreadsheets are so popular due to its magical nature which really has the potential of doing things in minutes which would take hours or at times days otherwise. But everything comes with its toll and spreadsheet is no exception. According to a study done by Powell, Lawson, and Baker in 2007 the results shown that 11 out of 25 spreadsheets have errors with non-zero effects. 10 out of these 11 spreadsheets had an error that exceeded $100,000, 6 had impact of error more than $10 million, and 1 had an error not below $100 million. If your company is using spreadsheet and most probably is you should know the risks and how to avoid the same, we will review the top 5 risks of using spreadsheet in business along-with their remedies.
Low-skilled Knowledge Workers:
The foremost risk is related to un-skilled manpower, people with low knowledge of spreadsheet applications are creating spreadsheets which results in mediocre sheets with errors & omissions at times are pressurized to do more in less time. In doing so they tend to make errors and these errors have their in-built cost associated with them.
To minimize the risk proper & formal training of the staff to be done in regular intervals especially for the users whose mistakes can be a cause of erroneous decision making or whose mistakes can cause material loss. Well known "COSO framework" also suggest that the users of spreadsheet should be encouraged to be trained in appropriate level to use this software.
Poor standards for creating spreadsheet are also a very big cause of increasing spreadsheet errors' risk. It is also a main cause of poor maintainability as the spreadsheet didn't followed the standard upgrading the same is difficult. Apart from this; lack of consistency also complicates things for the compliance & internal controls department when they verify / audit spreadsheets.
As spreadsheets are very similar to software, they do have both data & logic with them; proper caution should be taken when important spreadsheets are created. Proper policies & procedures should be in place to mitigate spreadsheet risks. Styles, content & controls over spreadsheets should be documented in the policies & procedures or they should be documented in the spreadsheet itself.
Data Entry & Reuse
The first time the spreadsheet is created normally proper care is being taken that all the formulas & functions are intact and working as needed. Overtime as data grows and copy-paste takes place it is not the same spreadsheet as created and at times not works the way as it should due to damaged formulas, broken links and/or overwritten cells.
There are several ways to mitigate the risk of recycling; it is suggested that the input data should be separated from the business logic or calculations, control totals / batch totals to verify that the formula results are accurate, use of the spreadsheet data validation options which allows you to restrict data entries / types of data entries finally use of user password so that the data cannot be changed accidently. Another popular approach is instead of retyping the data which is already available with your ERP or CRM you can just connect the data to the spreadsheet app.
Loss of Data
Large enterprise systems like ERPs & top level database have their internal data recovery mechanism but unfortunately spreadsheet applications don't have these utilities in-built. Data gets corrupted due to power failures or system malfunctions because of its client side usage, consequently, data which was collected in months evaporates in seconds.
To reduce risk of data loss first you should always save the file to the network and not on your desktop machines as the servers normally have their own backup procedure which is followed and you should also make sure that your server drive in which you have saved the data; back-up on a regular interval.
There are two major divisions in spreadsheet errors first one is system generated which may contains in the spreadsheet software you are using. One of the most popular spreadsheet software contains an error which shows 1900 a leap year however it is not. The user generated errors are the errors which can be controlled & mitigated.
There are two types of user generated errors qualitative errors & quantitative errors. The qualitative errors refer to the errors which create hurdles in maintainability or there are semantic errors (logical errors) which are hard to find but may harm the results drastically. The quantitative errors refer to errors like omission, alteration, duplication etc.
There are several methods which can reduce the risk of these errors; the choice will depend on the importance of the spreadsheet & availability of resources. First and most popular method is manual auditing this can be done as individual audit or team audit. Second which is less common is to follow proper system development lifecycle; planning, analysis, design, implementation & maintenance. Finally, companies also follow spreadsheet testing standards which ensure once spreadsheet is developed it will be tested and password protected to it can be used without any risk of errors.
10 Hidden Costs of Manual Reporting in Excel & What you can do about them, Apesoft
Your thoughts matter - more than you can imagine.
More 'IT - Information Technology' Articles
2 more Articles that may interest you, too...
IT - Information Technology
Eight emerging trends are transforming many markets and businesses. Executives should learn to shape the outcome rather than just react to it.