Why consolidating financial statements in a spreadsheet is a very bad idea
It seems that ever since there was a spreadsheet people have been relinquishing many tasks and responsibilities to it and for apparently good reasons. It was easy to set up and perform all but the most complex functions and it was available everywhere.
Admittedly, an application such as Microsoft Excel which took the business world by storm (have you seen a non Excel spreadsheet used lately?) is a lot more powerful than its earlier versions. While packing an impressive collection of features suitable to anything from making simple lists and flat file databases, to adding up columns of numbers, to more complex analysis tasks using functions and VB programming and incorporating its incredible graphing and display capabilities, it also inherently presents risks which become increasingly greater as the complexity of the model increases.
It is commonplace in business today that most of these spreadsheets (workbooks in Excel, containing from several to many individual worksheets, or tabs, often linked to other Excel files across entire computer networks) are authored by individuals, who while having the necessary skills to design and implement them, almost never think of reviewing them for accuracy and completeness of formulas, functions, links and any programming code.
The few spreadsheets that are looked at are usually reviewed by their own authors and are almost never subject to a peer review, or by someone completely independent. From an internal control perspective, this practice is a bad idea and causes so many spreadsheets to be flawed and without proper risk mitigating controls.
In an article by Tony Kontzer in Tech Target, titled “Even with spreadsheet management, using Excel for finance isn’t wise”, and quoting additional authors and researchers, the author makes a point of the typical “ungoverned” use of spreadsheets (Excel) and how ingrained it is in the daily work of accounting and finance professionals.
Even with the new MS Excel 2013 with it’s better than ever built-in audit tools, it is ultimately the end-users who must establish an effective control environment that can be practically maintained and audited. This rarely happens in the real world, leaving numerous critical financial statements at risk for material errors.
In a research paper published by Dr. Ray Panko, of the University of Hawai’i
College of Business Administration,
and a follow up paper (http://arxiv.org/ftp/arxiv/papers/0809/0809.3613.pdf),
it is made clear that a very large percentage of spreadsheets used in business today are flawed. This should become a major concern for those using spreadsheets in accounting and finance functions.
In my work in internal audit and financial reporting consulting I constantly run into organizations (many of which are fairly large and complex, some publically held) that put too much trust into results produced by an array of homegrown spreadsheets.
This is especially true for spreadsheets entrusted with producing consolidated financial statements and also with the preparation of annual corporate budgets and various forecasts, still very popular with spreadsheets.
I have advised managements on the need to disclose such material weaknesses, and I have witnessed several occasions when the external auditors insisted on making such disclosures.
When testing the design and effectiveness of an internal control performed in a spreadsheet, and especially consolidations of financial statements, unless there is clear evidence that the spreadsheet (or any end-user computing activity) is periodically reviewed for accuracy and completeness of its design and use, and is under change management control, you can’t help but conclude that there may be a material weakness in this process, one that must be disclosed by publically held companies in their annual reports.
What this implies is that (for any type of organization) financial statements produced by spreadsheets are likely to contain material errors. For that reason, purpose designed consolidation tools are much more preferable to using spreadsheets. Many of the small and medium size company ERP software applications offer consolidations within the application database without exporting data to a consolidation spreadsheet. Once internal control over the process is established within the database, it is much simpler to monitor and audit than with spreadsheets.
If your organization still relies on financial statements consolidated using a spreadsheet, I strongly suggest you take another close look at the process and either introduce a well documented and periodically tested set of internal controls over the design, use and change management of this process, or better still, perform it in a purpose built application or more practically for small and medium size organization, implement the process within your accounting or ERP software, in a more secure environment.
Not taking one of the above steps is leaving too much to chance, which invariably, sooner or later, will result in material misstatements in critical financial statements.