No company is eager to hand out its spreadsheets for public analysis. However, a treasure trove of spreadsheets was made available that allows a new analysis of spreadsheet use by corporations.
It's not very often that all of a company's spreadsheets are made available for public scrutiny. But thanks to the efforts made by Felienne Hermans of the Delft University of Technology, all of the email attachments containing Excel files from Enron were made publicly available. (Her research is available here.)
Taking advantage of this rare opportunity, we ran the 15,922 spreadsheets through CIMCON's XLAudit tool and found some interesting results!
Of the 15,922 spreadsheets, 6,678 do not have any formulas. We left these out of the results presented below as these files were probably for record-keeping purposes rather than modeling or calculations. However, the fact that over 40% of spreadsheets do not calculate anything is potentially a problem unto itself. It is often postulated that companies should do everything possible to rid themselves of unnecessary spreadsheets. Record-keeping spreadsheets would seem the simplest of targets to move to a different system in order to improve information security. But given the huge numbers, identifying such files can seem insurmountable. And, in addition, given the many stories around sensitive data contained in spreadsheets being lost, even those spreadsheets need a minimum set of controls.
As measures of complexity, here is a look at the average spreadsheet that does contain formulas:
We also took a look at errors and issues within formulas. Note that previous studies have shown that over 90% of spreadsheets have errors:
We looked at formulas resulting in errors (#REF!, #VALUE!, etc.) and formulas that may have some logical issue (Formulas with Constants, Formulas Referring to Hidden Cells, Formulas Formatted as Text, etc.) and found that 86% of the spreadsheets had some sort of formula problem.
Additionally, there was an abundance of hidden information in the spreadsheets. While this does not necessarily indicate an error, hiding information can make the spreadsheet harder to understand or use by the next user and can also indicate attempts at fraud.
While we can’t reveal spreadsheet analysis data for other large corporations, we can say that the results we found at Enron are quite typical.
Chances are that the spreadsheets used within your company are fraught with issues that could put your company at unnecessary risk. You can hope for the best & wait until a problem happens… or take proactive measures to implement more effective controls of your spreadsheets today!
Because who wants to end up like Enron?
To learn more about how to mitigate spreadsheet and other EUC risks download the white paper "Taming the Spreadsheet Menance."