Spreadsheets: trusted business tool, or regulatory compliance threat?
Compliance experts estimate that 80 percent of enterprises use spreadsheets to support critical business functions. For example, in one Deloitte survey of 800 financial professionals, 88 percent said their firms "use spreadsheets of material importance in financial reporting." At the same time, however, research suggests the typical spreadsheet has a 2 to 5 percent error rate.
As a result, spreadsheets are one of the biggest compliance risks facing regulated companies. Indeed, despite their prevalent use, the life of the average spreadsheet is unstructured, untracked, insecure, and potentially just inaccurate. Learn how to preemptively control challenges that can run afoul of Sarbanes-Oxley (SOX), Basel II, or numerous other laws which regulate the integrity of financial processes.
Bet on auditors wanting to see all spreadsheets relating to your company’s financial reporting practices. Will your rows and columns pass compliance muster? To help mitigate the regulatory risks posed by spreadsheets, consider these 10 tips.
1: Acknowledge Spreadsheets’ Programming Power
One issue with spreadsheets is they’re simply so powerful. According to Gary Baker, who leads the IT governance practice at Deloitte & Touche LLP in Canada, "the spreadsheet problem is, we’ve given a programming language to a non-IT user," yet without any development environment-type oversight or safeguards. "You’re a programmer, but you’re also the tester and the user, so you’ve just lost all objectivity. Who’s going to detect the errors in that spreadsheet?"
2: Expect Errors
The average spreadsheet contains a substantial number of errors, says Ray Panko, an IT professor at the University of Hawaii. "Human error research indicates that for things about as complex as creating a spreadsheet formula, the error rate floor is about 2 percent to 5 percent." The reason: people tend to take shortcuts when doing math, and these shortcuts often produce errors. Regarding automation, please see tip number eight. On a related note, spreadsheet novices are three times as likely as experts to make mistakes.
Few companies, however, test for spreadsheet errors or outright fraud, preferring instead to eyeball results—often with predictable consequences. For example, Panko relates how one software developer used two 15,000-cell Excel spreadsheets to project the market for its products, with dollar figures rounded to whole numbers. Yet a user error inadvertently also rounded the modifier for inflation from 1.06 to 1, leading to a market undervaluation of $36 million. Under SOX, not to mention numerous accounting standards, such an error would obviously qualify as a material weakness.
3: Manage Spreadsheet Changes
One solution: don’t prohibit spreadsheet use, but rather identify which spreadsheets handle critical business functions, and then implement controls to ensure their integrity and accuracy, and especially to prevent fraud. For starters, apply change management controls to spreadsheets, including sign-offs, a record of all changes and the rationale for every change, plus rollback capabilities. Each spreadsheet’s business logic must also be thoroughly vetted, as with any application which handles complex business functions.
4: Beware the Orphans
When auditing spreadsheets, pay particular attention to the orphans: spreadsheets of unknown provenance which today still drive critical business processes. As Arthur C. Clarke wrote, "any sufficiently advanced technology is indistinguishable from magic," and as anyone who’s ever inherited a spreadsheet knows, some operate if not by magic, then at least through unintuitive logic that might take a lifetime to unravel.
Certainly, the average business user can’t be expected to accurately keep a 50-tab Excel workbook current. "To keep spreadsheets up to date and reflect the business as it’s evolving appears to be an impossible task, and it’s certainly not being done," notes Wolfgang Koester, CEO of FiREapps, which develops corporate foreign exchange management software.
5: Consider Versioning Software
The poster child of the spreadsheet world is Microsoft Excel. Until recently, however, software to manage Excel in regulated environments was scant. Beginning with Excel 2007, though, Microsoft itself began offering businesses a way to enforce change management, audit controls, and versioning for Excel spreadsheets. Together with SharePoint Server 2007, companies can even manage spreadsheets centrally and offer role-based access to HTML versions of spreadsheets.
6: Evaluate Granular Controls
According to Forrester analyst Boris Evelson, however, such content management approaches are giving way to more granular controls which audit spreadsheets at the cell level, can lock the underlying logic, and even roll back specific cell changes. "Many vendors are starting to move toward a fine-grained control approach, where everything that is done in a spreadsheet—data, formulas, and macros—at the cell level can be managed by centralized policies," he says. With the new approach, "the focus is on adhering to policies rather than relying on repository management and library services to limit access, track versions, and provide check-in/checkout." Such approaches can help transform spreadsheets into more full-featured and compliance-friendly enterprise applications.
7: Enforce Policies and Procedures
Any spreadsheet management product will require companies to specify policies and procedures for appropriate spreadsheet use. When determining what’s appropriate, again study critical business processes, and consider prohibiting spreadsheets from managing any complex or critical financial calculations. For example, using spreadsheets to test monthly cash flow projections could be acceptable, while calculating your company’s daily foreign exchange exposure might be prohibited, to avoid running afoul of Basel II or Financial Accounting Standard rules.
8: Automate Critical Business Processes
Evaluate the effectiveness of current spreadsheets. In particular, for any spreadsheet handling a critical business process, beyond assessing change management or auditing controls, "make sure there’s proper segmentation of data, logic, and presentation—otherwise automate and institutionalize it," says Andy Gage, vice president of marketing at FiREapps.
In general, using enterprise applications or add-on controls to automate financial business processes will lead to more cost-efficient and effective compliance. "At the end of the day, from a governance, risk, and compliance standpoint, statistics will show that the more you automate, the more reliability you’ll have from the data," says Gage.
9: Monitor Centralized Application Adoption
The presence of centralized ERP or budgeting software which can track and audit corporate financials, however, is no guarantee that spreadsheets aren’t still being inappropriately used to underpin critical decisions.
For example, one SOX auditor relays a story about a company that installed Applix TM1—server-based budgeting software—to automatically collate and formulate budget figures across the organization. Despite having a centralized tool to handle budget calculations, however, accountants in each business division still used Excel spreadsheets to perform their calculations, and then copied the information into TM1. Yet these spreadsheets offered no audit trail, accountability, or rationale for budget assumptions. Furthermore, accountants often manually reconciled multiple spreadsheets to create final budget figures, increasing the likelihood of errors.
Hence, simply building centralized tools for ensuring the accuracy of financial information isn’t enough. Companies must also ensure such tools are easy enough to use and full-featured enough that users will willingly give up their spreadsheets.
10: Balance Enterprise Applications and Spreadsheets
In many organizations, however, users simply aren’t going to surrender their spreadsheets. "One major reason why users are unwilling to eliminate spreadsheets and embed calculations into enterprise applications is that business methodologies—such as pricing, cost allocations, hierarchies, and others—change much too quickly for IT to respond with updates," says Forrester’s Evelson.
As a result, in many companies, the answer to the spreadsheet problem is simply better command and control: set spreadsheet polices and procedures, and then enforce them, by carefully managing —perhaps down to the column level—any spreadsheets entwined with critical business applications. In other words, a little oversight and tough love can help companies enforce the authenticity and reliability of their regulated financial information, while providing users with the spreadsheets they rely on to get their jobs done.
This article originally appeared at itcinstitute.com. Copyright 2008, 1105 Media Inc. Reprinted with permission.