United States

User-designed applications: How to control spreadsheets gone wild


In today's get-it-done-now business environment, managers welcome tools that speed data collection, analysis and reporting. The rise of desktop computing over the last two decades gives everybody access to data tools, such as spreadsheets, databases and business intelligence software, such as structured query language (SQL) and Hyperion. Like backyard mechanics with a hot rod coupe, managers can tinker with the workings under the tools' metaphorical hoods to get a customized experience. From additional rows and columns to complex macros and formulas, user-designed applications (UDAs) reflect users' specific needs more than networked ERP, accounting and financial reporting systems.

Users or designers can make Excel, to cite the most common tool, a souped-up performer, with flexibility and functionality for making business decisions, tracking trends and analyzing and reporting data, but they can create severe reporting and audit risks. Those risks can lead to unintentional errors in reporting, and even outright fraud. Some recent examples of spreadsheets gone wild:

  • Harvard economists Kenneth Rogoff and Carmen Reinhart used a spreadsheet in measuring typical economic growth rates across countries during episodes when public debt levels were low, medium, high or very high, in relation to gross domestic product (GDP). They concluded that median growth rates for countries with public debt over roughly 90 percent of GDP are about one percent lower than otherwise; average growth rates are several percent lower. The report was highly influential among economic policymakers. However, a graduate student found that a spreadsheet developed and used by the economists contained an error that averaged performance in only 15 countries, not the full sample of 20. The bottom line: instead of the negative 0.1 percent growth, as noted in the Rogoff-Reinhart study, the corrected spreadsheet formula equaled 2.2 percent growth.
  • The chief financial officer (CFO) of a technology company committed securities fraud by using hidden rows and white font or white cell-shading, so false account entries did not show up when a spreadsheet was printed in hard copy; he controlled the file and never distributed electronic copies, where users could have possibly detected the fraud. The information rolled up into financial statements. After the fraud was finally uncovered, the CFO paid $420,000 in civil charges, and the company lost $437 million in market capitalization.
  • A town in Wisconsin had an "operator error" in a spreadsheet that underestimated the cost of a 10-year bond. It was discovered that a cell across a line in a spreadsheet had not been added properly. As a result, the town will be paying about $400,000 more over the lifetime of its most recent 10-year borrowing plan than originally projected.

As the examples show, problems with UDAs cut across industries. Assessing and addressing risks are complicated because UDAs are so prevalent and so varied. They range from the low-risk, such as a spreadsheet to track an individual's CPE credits, to the high-risk spreadsheet with external links to other external data sources (e.g., bank exchange rates) and computational macros. Left uncontrolled, UDAs help expose fundamental flaws in corporate governance and internal control. The challenge for CFOs and their internal audit department is how to inventory the UDAs, and then determine the controls over them. Low-risk UDAs are harmless enough; but how does one identify and address the UDAs that raise financial, operational, regulatory and reputational risk?

Why use UDAs?

To start, CFOs and internal auditors should think about the reasons why managers create UDAs. In a word, shortcomings in networked programs drive them to do so. For all the value of ERP, accounting, procurement and financial reporting systems, they do have rigidities and faults. They may not fit a company's operations, or a company may not implement all of their parts, and vendors may not be willing to modify systems to fit a company's operational needs. These factors all add up to end-user frustrations. As a result, end users create all kinds of UDAs to meet identified needs. Working on their desktops, end users quickly learn how to create and modify programs outside the normal information technology (IT) software development life-cycle process and related controls environment.

By understanding motivations, CFOs can start to identify where in an organization the riskier UDAs may reside. The ultimate goal: to inventory the UDAs, rank and mitigate the risks and provide a defendable management mechanism, while ensuring the external auditors gain comfort on the appropriateness of risk response for each UDA or classes of UDAs. More broadly, management can think about what changes in process or technology may be needed to reduce the demand for UDAs, so that managers are using more control-compliant programs.

Sources of risk in UDAs

First, CFOs should understand the types of UDA risk. Risks include:

  • Access control
  • Change control
  • Version management
  • Lack of appropriate UDA developer experience and support
  • Lack of system and user documentation (i.e., when the user or developer leaves the company, nobody can understand the spreadsheet used for key analyses)
  • Lack of formal or sufficient testing
  • Hidden columns, rows or calculations (i.e., can be the basis for fraud)
  • Key spreadsheets stored on local drives and unprotected network share
  • Privacy and confidentiality

Leaders in UDA risk management are typically large corporations and public companies with Sarbanes-Oxley Act (SOX) requirements. They have the audit resources, probing external auditors and regulatory needs to assess the risks of UDAs. Private and mid-market companies, however, often struggle to acknowledge and respond to UDA risks. Given the pressure of their other responsibilities, CFOs and Internal Audit may either be confident in their controls or put UDAs at the bottom of the to-do list until corrupted numbers and reports enter the corporate data stream and have a higher and more direct impact, infecting financial reports and, in the worst cases, leading to investigations, declining market valuations, fraud charges and regulatory actions.

The good news is that the risks and the responses are well-established. Decades of controls experience come into play to handle UDAs. UDAs are simply part of the audit universe, and should be treated as such, with the most significant questions to be answered being:

  • What are the UDAs being used for
  • Which ones are significant
  • How does one control the most significant risks, that is, data inputs, data processing and data outputs
  • Who has ownership and ultimate control over the UDAs

Identifying UDA risks

Given limited budgets and staff, Internal Audit should start first by inventorying UDAs throughout their organizations. In our experience, spreadsheets related to general ledger (G/L) or external financial reporting generally account for 75 percent of high-risk UDAs. Key areas on which to focus include:

  • GL-related: complicated accruals; third-party data file uploads; deferred revenue calculations
  • External financial reporting: Consolidation; Management Discussion and Analysis (MD&A) and footnote support; segment reporting
  • Operational: Procurement; distributor rebates; budgets

Oftentimes, risks result from a lack of coordination. In one case, a client used spreadsheets to calculate sales commission accruals at different locations. Due to poorly governed UDAs, the formulas varied by location, with different degrees of adherence to corporate accrual standards. Corporate accounting did not know about the variations, although the standards had been communicated on multiple occasions. The impact was not material in financial reporting, but the corporate executives learned a lesson in the importance of locking down formulas, ensuring consistent communication to the field and establishing lines of accountability.

Internal Audit and UDA Control

The role of Internal Audit is clear. Internal auditors can provide a framework and methodology for proper UDA control, and provide consulting services to management to help with the establishment of policies and procedures to provide guidance over appropriate risk response, based on UDA complexity and usage. Their audit of high-risk or critical UDAs would consider issues of access, change control, design, documentation and testing (e.g., do different users get similar results). Companies complying with SOX have more impetus here, since their external auditors generally require that they inventory spreadsheets, identify them by departments and users and score them for risk, in order to ensure the appropriate controls related to financial reporting.

Internal Audit can use best practices in dealing with UDAs. They work in any situation. Best practices include peer review, quality assurance (QA), as applied to any other application development or change function, system and user documentation and the use of delivered (but limited) error prevention and formula audit tools in older versions of Excel. However, new functionality that is imbedded into Excel 2013 can help organizations to analyze UDA risk factors. CFOs and internal auditors eager to keep up with the leading practices can utilize the Institute of Internal Auditors' Global Technology Audit Guide (GTAG) "Auditing User-developed Applications." Another valuable resource is the European Spreadsheet Risks Interest Group at www.eusprig.org.

The bottom line: UDAs can be developed and properly controlled in a way that supports organizational objectives through use of traditional internal audit strategies and clear direction from management, or they will be developed "in the dark," and possibly wreak havoc on an organization.

For further information, please contact Steve Hunt, director, RSM LLP.