"Our research with clients has shown that 94% of an organization's spreadsheets will contain errors." Seriously, ninety-four percent. If that doesn't get you off of Excel for anything other than ad hoc analysis, I don't know what will!
Title:Assistant Treasurer Company: Integrys Energy Group
| Feb 15, 2012
I question the 94% figure - this is research from a firm with a vested interest in selling an alternative. Perhaps they count having an extra tab in your spreadsheet as an "error". Or having a tab labeled "Sheet2".
There are ways to make Excel error-proof - password protecting, tracking changes, only opening certain cells for input...etc.
It is still the most versatile tool, and if you want a competitive advantage you need a one-of-a-kind process - and no software vendor will be able to supply that.
It's easy to blame excel, but automated ERP systems have errors in the data too...coming from human input errors, which happens in every organization. A good manager will have the right people and the right processes in place to catch these errors. The two examples provided in the article are not excel problems, they are people and/or procedural problems.
Since when did the term "Audit" fall out of vogue with CFO's. Whether it is your financials, your management reports or a spreadsheet done ad hoc, as an accountant/finance professional and a CFO, one should always audit the material given to us.
When I was teaching accounting at CUNY, one of the precepts to using a spreadsheet was to test the results to every calculation by using simple data; data that you could independently test and know what the result should be. Then give the draft spreadsheet to someone else to look it over; missing a negative sign is easier than it looks...
I also agree with Scott Graves; I have found errors in accounting systems because of bugs as well as GI/GO syndrome.
It's great to hear support for Excel by this community. Canned/cloud/big/expensive alternatives to Excel have errors as well. Excel is flexible, free (sunk cost), and can be structured to minimize errors. Excel alternatives (such as those for financial planning), often require Excel at the front or back ends anyways. Thus, time and expense can often be saved by carful design of Excel based solutions. These solutions can be trusted with the proper controls.
Bob, for more years than I want to remember, I've been an Excel junky singing its praises. However, at a previous employer, I installed and used a forecasting package that was exceedingly beneficial with respect to performing consolidations, multiple scenario analysis, and version control while allowing for online collarboration. It was definitely a step up from passing spreadsheets around via e-mail.
Generally, but I always verify with a designed process. I also typically limit tab and file size as part of my design. The larger and mire complex, the greater the error risk...somewhat exponentially unfortunately.
I agree with Scott. If the processes / operators are inefficient or error-prone, then the tool are also error-prone, doesn't matter if it is excel, ERP, cloud or even paper. What is more crucial is how companies establish checks and balances in place to catch these errors and rectify them on time
The 90+% number is in the link at the end of the article to EUSPRIG (http://www.eusprig.org/basic-research.htm), an independent group that studies spreadsheet risk. Spreadsheets, while a wonderful tool, are easy to goof up. Most of the accounting departments I've seen do not protect cells with formulas to prevent someone typing over the cell contents, thus destroying the integrity of the calculations. The larger the spreadsheet grows, the more complex it becomes - more links, multiple sheets, charts, etc. and thus more prone to errors - whether big or small. Add to that the difficulty in proofing a spreadsheet (do you use the formula auditing functionality to trace precedents and dependencies in the sheets you receive?) - It all adds up to a very reasonable 90% error rate.
On the good news side - you can do some simple sanity checks (refooting the balances just by highlighting the row or column and looking at the summary on the gray bar at the bottom for totals and counts - in excel 2010), and excel has added the "Green triangle" in cells when the formula is inconsistent with cells adjacent to it, which doesn't take a lot of time to give some comfort level in the data.
The EUSPRIG.org web site also has a best-practice page that is worth the effort. (I pass it on to all my accounting colleagues). A simple peer-review within your team can usually spot the big errors. Once you get it right - lock it down and use templates. It will save a great deal of pain during month end close.
That 94% number, though accurate and from as unbiased a source as you're going to find (Ray Panko from the Univeristy of Hawaii) is banded about a lot when the topic of spreadsheet risk comes up.
It is however, not a risk-adjusted number. Its a simple count of errors found, regardless of the error's potential impact.
The risk adjusted number, from the same body of research, showed that between 5% and 40% of spreadsheets have "significant errors" with potential financial impacts ranging from $100,000 to $100 million. Not as bad as 94% errror rate, but those potential $ impacts are just as eye-popping.
I second Gary's recommendation to use EuSpRIG's best practices page.
For those that want to dive deeper, there is the Spreadsheet Standards Review Board "an organization created to develop and promote general acceptance of best practice spreadsheet modelling standards" http://www.ssrb.org, and/or the FAST modelling standard http://www.fast-standard.org/
From the C-level perspective, you might be interested in a white paper I wrote on How to Manage Spreadsheet Risk - http://bit.ly/IaGgyh
There is a risk of error in any analysis! There is so much that can be done in Excel to limit the risk of undiscovered error. For example, use your formatting. Use a different color font to highlight dynamic numbers that are hard coded, and another font color for static numbers. Protect worksheets, create forms, hide "raw number" worksheets from end users. Compare and contrast results withe other data sources. Have multiple levels of reveiw by people who are well-versed in the subject matter.
A bit confused. Are we talking about mathematical errors of Excel, or are we talking about modeling and human errors? I define mathematical errors as Excel giving inconsistent answers in additional, IRR, etc. Modeling error is where someone does not check balance sheet footing, or sum of columns and sum of rows do no match.
While there maybe some mathematical precision issues (a quick search on the web brought up this interesting article from Microsoft: http://support.microsoft.com/kb/78113 about floating point math), I haven't been seen any formulas that are wrong. Not that I have used every function available in excel! -- but I would caution that reliance on a spreadsheet to do your calculations makes sense only if you actually understand the calculation you are trying to do. If you are using the built-in double-declining balance depreciation method, you should be able to dust off the old accounting text and verify that when calculated manually, you get the same answer.
I run across the human error, incorrect formula, or hard-coded number where a formula used-to-be in a cell types of problems far more often than a pure arithmetical or software error.
Errors in the software I must toss over the fence to the vendor; but the cell contents are where the usual errors show up, and I can something about that.
All great comments. I would simple add that in my 20+ years of developing excel applications, most of the errors occur simple because excel is used far to often to create ad hoc analysis templates that later become embedded periodic reports for some manager. This leads to someone, usually the least qualified, to create an on the fly workbook with very little though put into the design and structure. After all this is just another one off project, right!
Several years later when someone else inherits the workbook (hopefully someone with an understanding of data analysis and reporting) and usually after it has been modified and "enhanced" several time, they notice little flaws, then after further digging bigger flaws, both in logic and numerical calculations (both the self inflicted kind and those perpetrated by Redmond, WA).
At this point, they realize that the workbook is completely worthless for managerial decision making and begin to either re-engineer the process or purge the workbook.
My next statement is a bit self serving, so I will at least qualify myself.
I am more and more convinced that within any organization that relies on Excel for critical analysis and reporting needs (i.e. process simulations, EOQ modeling, budgeting, ect.) their need to be a designated "Expert" through which all significant Excel workbooks must pass prior to being placed into the "Regularly Relied Upon" category of spreadsheets. This means, if any member of senior management, the board of director, outside analyst, ect. will be relying on data from an excel spreadsheet, it must have a "seal of approval" from the designated expert.
Title:Director of Revenue Company: Calypso Technology
| May 2, 2012
Of course any purchased/implemented package will have particular defined reports. Once others are (inevitably) needed, it's often back to Excel. That doesn't mean don't get the package. But you can't escape Excel.
Comments
Company: Undisclosed
"Our research with clients has shown that 94% of an organization's spreadsheets will contain errors." Seriously, ninety-four percent. If that doesn't get you off of Excel for anything other than ad hoc analysis, I don't know what will!
Company: KDR Designer Showrooms
Michael - I agree with you completely!
Company: Integrys Energy Group
I question the 94% figure - this is research from a firm with a vested interest in selling an alternative. Perhaps they count having an extra tab in your spreadsheet as an "error". Or having a tab labeled "Sheet2".
There are ways to make Excel error-proof - password protecting, tracking changes, only opening certain cells for input...etc.
It is still the most versatile tool, and if you want a competitive advantage you need a one-of-a-kind process - and no software vendor will be able to supply that.
Company: Duke Manufacturing
It's easy to blame excel, but automated ERP systems have errors in the data too...coming from human input errors, which happens in every organization. A good manager will have the right people and the right processes in place to catch these errors. The two examples provided in the article are not excel problems, they are people and/or procedural problems.
Company: SBA * Consulting, LTD
Since when did the term "Audit" fall out of vogue with CFO's. Whether it is your financials, your management reports or a spreadsheet done ad hoc, as an accountant/finance professional and a CFO, one should always audit the material given to us.
When I was teaching accounting at CUNY, one of the precepts to using a spreadsheet was to test the results to every calculation by using simple data; data that you could independently test and know what the result should be. Then give the draft spreadsheet to someone else to look it over; missing a negative sign is easier than it looks...
I also agree with Scott Graves; I have found errors in accounting systems because of bugs as well as GI/GO syndrome.
Company: LogLogic
It's great to hear support for Excel by this community. Canned/cloud/big/expensive alternatives to Excel have errors as well. Excel is flexible, free (sunk cost), and can be structured to minimize errors. Excel alternatives (such as those for financial planning), often require Excel at the front or back ends anyways. Thus, time and expense can often be saved by carful design of Excel based solutions. These solutions can be trusted with the proper controls.
Company: Allscripts
Bob, for more years than I want to remember, I've been an Excel junky singing its praises. However, at a previous employer, I installed and used a forecasting package that was exceedingly beneficial with respect to performing consolidations, multiple scenario analysis, and version control while allowing for online collarboration. It was definitely a step up from passing spreadsheets around via e-mail.
Company: TTX
Generally, but I always verify with a designed process. I also typically limit tab and file size as part of my design. The larger and mire complex, the greater the error risk...somewhat exponentially unfortunately.
Company: Gorilla Expense
I agree with Scott. If the processes / operators are inefficient or error-prone, then the tool are also error-prone, doesn't matter if it is excel, ERP, cloud or even paper. What is more crucial is how companies establish checks and balances in place to catch these errors and rectify them on time
Company: Colorado PERA
The 90+% number is in the link at the end of the article to EUSPRIG (http://www.eusprig.org/basic-research.htm), an independent group that studies spreadsheet risk. Spreadsheets, while a wonderful tool, are easy to goof up. Most of the accounting departments I've seen do not protect cells with formulas to prevent someone typing over the cell contents, thus destroying the integrity of the calculations. The larger the spreadsheet grows, the more complex it becomes - more links, multiple sheets, charts, etc. and thus more prone to errors - whether big or small. Add to that the difficulty in proofing a spreadsheet (do you use the formula auditing functionality to trace precedents and dependencies in the sheets you receive?) - It all adds up to a very reasonable 90% error rate.
On the good news side - you can do some simple sanity checks (refooting the balances just by highlighting the row or column and looking at the summary on the gray bar at the bottom for totals and counts - in excel 2010), and excel has added the "Green triangle" in cells when the formula is inconsistent with cells adjacent to it, which doesn't take a lot of time to give some comfort level in the data.
The EUSPRIG.org web site also has a best-practice page that is worth the effort. (I pass it on to all my accounting colleagues). A simple peer-review within your team can usually spot the big errors. Once you get it right - lock it down and use templates. It will save a great deal of pain during month end close.
Company: Jerts Consulting & Services
That 94% number, though accurate and from as unbiased a source as you're going to find (Ray Panko from the Univeristy of Hawaii) is banded about a lot when the topic of spreadsheet risk comes up.
It is however, not a risk-adjusted number. Its a simple count of errors found, regardless of the error's potential impact.
The risk adjusted number, from the same body of research, showed that between 5% and 40% of spreadsheets have "significant errors" with potential financial impacts ranging from $100,000 to $100 million. Not as bad as 94% errror rate, but those potential $ impacts are just as eye-popping.
I second Gary's recommendation to use EuSpRIG's best practices page.
For those that want to dive deeper, there is the Spreadsheet Standards Review Board "an organization created to develop and promote general acceptance of best practice spreadsheet modelling standards" http://www.ssrb.org, and/or the FAST modelling standard http://www.fast-standard.org/
From the C-level perspective, you might be interested in a white paper I wrote on How to Manage Spreadsheet Risk - http://bit.ly/IaGgyh
Company:
There is a risk of error in any analysis! There is so much that can be done in Excel to limit the risk of undiscovered error. For example, use your formatting. Use a different color font to highlight dynamic numbers that are hard coded, and another font color for static numbers. Protect worksheets, create forms, hide "raw number" worksheets from end users. Compare and contrast results withe other data sources. Have multiple levels of reveiw by people who are well-versed in the subject matter.
Company:
A bit confused. Are we talking about mathematical errors of Excel, or are we talking about modeling and human errors? I define mathematical errors as Excel giving inconsistent answers in additional, IRR, etc. Modeling error is where someone does not check balance sheet footing, or sum of columns and sum of rows do no match.
Company: Colorado PERA
While there maybe some mathematical precision issues (a quick search on the web brought up this interesting article from Microsoft: http://support.microsoft.com/kb/78113 about floating point math), I haven't been seen any formulas that are wrong. Not that I have used every function available in excel! -- but I would caution that reliance on a spreadsheet to do your calculations makes sense only if you actually understand the calculation you are trying to do. If you are using the built-in double-declining balance depreciation method, you should be able to dust off the old accounting text and verify that when calculated manually, you get the same answer.
I run across the human error, incorrect formula, or hard-coded number where a formula used-to-be in a cell types of problems far more often than a pure arithmetical or software error.
Errors in the software I must toss over the fence to the vendor; but the cell contents are where the usual errors show up, and I can something about that.
Company: Peak Business Solutions
All great comments. I would simple add that in my 20+ years of developing excel applications, most of the errors occur simple because excel is used far to often to create ad hoc analysis templates that later become embedded periodic reports for some manager. This leads to someone, usually the least qualified, to create an on the fly workbook with very little though put into the design and structure. After all this is just another one off project, right!
Several years later when someone else inherits the workbook (hopefully someone with an understanding of data analysis and reporting) and usually after it has been modified and "enhanced" several time, they notice little flaws, then after further digging bigger flaws, both in logic and numerical calculations (both the self inflicted kind and those perpetrated by Redmond, WA).
At this point, they realize that the workbook is completely worthless for managerial decision making and begin to either re-engineer the process or purge the workbook.
My next statement is a bit self serving, so I will at least qualify myself.
I am more and more convinced that within any organization that relies on Excel for critical analysis and reporting needs (i.e. process simulations, EOQ modeling, budgeting, ect.) their need to be a designated "Expert" through which all significant Excel workbooks must pass prior to being placed into the "Regularly Relied Upon" category of spreadsheets. This means, if any member of senior management, the board of director, outside analyst, ect. will be relying on data from an excel spreadsheet, it must have a "seal of approval" from the designated expert.
Company: Calypso Technology
Of course any purchased/implemented package will have particular defined reports. Once others are (inevitably) needed, it's often back to Excel. That doesn't mean don't get the package. But you can't escape Excel.