January 2006

The Role of Spreadsheets in Today's Corporate Climate

Understanding how spreadsheets are used in an organization may help internal auditors recommend strategies to enhance compliance with government regulations.

Ralph Baxter
Director of Marketing and Strategy
ClusterSeven

Recent government regulations such as the U.S. Sarbanes-Oxley Act of 2002 have focused on the integrity of end-to-end finance operations. Although IT systems are fundamental to this integrity, it is rare for one software application to support an organization's entire gamut of finance-related activities and needs. In addition, because organizations change over time, gaps exist between a company's business needs and a system's capacity to fulfill them. Consequently, many companies rely on spreadsheets to help fill these gaps.

Spreadsheets enable organizations to create and store financial transactions and forecasts, helping businesses accomplish their goals. In this capacity, spreadsheets have left behind their original role as calculation models, becoming a core business and compliance tool instead.

Internal auditors are in a unique position to help organizations understand the way spreadsheets may hinder or enhance the compliance process. To this end, auditors need to understand how organizations use spreadsheets and the role they play within the corporate infrastructure. This will help auditors recommend effective strategies to enhance compliance with government regulations.

SPREADSHEET USE

Many companies use spreadsheets to support the information management of critical business processes as a way to comply with never-ending regulations. The introduction of International Financial Reporting Standards, for instance, has called for modifications to existing accounting systems. While organizations wait for these changes to be incorporated into accounting applications, spreadsheets become a practical interim option to support financial activities.

Once spreadsheets are used, many executives assume they will be replaced shortly after an investment is made in a fully architected system. In reality, these "temporary" spreadsheets may be used for years before they are replaced, becoming difficult to manage as their numbers grow. Furthermore, some spreadsheets prove harder to transfer than others once the new accounting system is in place, because they are used frequently or are too complex to be replaced cost effectively.

In companies where spreadsheets are used to transfer data between IT systems, internal auditors usually find information gaps in areas where spreadsheet data is retrieved and manipulated. Because the integrity of the entire information chain may depend on an organization's effective use of spreadsheets (see Figure 1), they have become a key focus of recent federal regulations. As a result, auditors spend considerable time reviewing the accuracy and timeliness of spreadsheet information. To ease the review process, auditors should start by understanding the role played by spreadsheets in the organization. This will enable auditors to identify any risks and provide strategies that enhance present and future compliance efforts.

Relationship between business processes

Figure 1: Relationship between business processes, spreadsheet
use, and IT systems

In the corporate world, spreadsheet use is divided in two categories: modeling and operational. Spreadsheets created for modeling purposes act as complex calculators and address a particular activity, such as mergers and acquisitions. On the other hand, spreadsheets created for operational use support transactional processes, thus becoming a core business program containing static and changing data, as well as all the calculations applied to the data. One common application of operational spreadsheets is maintaining business lists, such as inventories and confidential customer information. Table 1 describes the primary differences between modeling and operational spreadsheets in more detail.

 

Modeling Spreadsheets

Operational Spreadsheets

User(s) Single. Built and used by the same individual most of the time. Multiple. Built by an IT developer for corporatewide use.
Duration of Use Short term. Spreadsheet models, often complex, are built for days or weeks only to be redundant after the relevant business decision is made. Long term. Spreadsheet models, both simple and complex, become part of the business' information flow and persist for months or years.
Structural and Functional Volatility High. Substantial structural revisions are likely to occur from one day to the next as major elements are added or replaced. Low to medium. All key structural elements are likely to be in place. Further evolution of the business process requires spreadsheet maintenance, with rare structural overhauls.
Data Volatility Medium. Data are related primarily to the exploration of alternative scenarios. High. As the application matures, transactional data become key spreadsheet variables.
Use Intensity Intensive. Use is likely to be intensive for a short period of time, and the knowledge of the spreadsheet's structure among its users is retained throughout its life. Sporadic. Use depends on the nature of the business process being supported. For example, many spreadsheets in financial reports are used at the end of the month. This means employees may forget how to use key spreadsheet functions.

 

As shown in Table 1, operational spreadsheets have more users over a longer period of time, who have less knowledge of the spreadsheet's underlying structure. Because of this, operational spreadsheets are a greater regulatory concern during compliance reviews, and internal auditors should pay close attention to their use and the accuracy of their data.

REGULATORY REQUIREMENTS, RISKS, AND INTEGRITY

The risks posed by operational and modeling spreadsheets are not new to executives. In the past, many executives accepted these risks. However, this acceptance is no longer rewarded in today's climate of increased regulatory compliance. Spreadsheets also have other significant failings:

  • They are vulnerable to error and, occasionally, fraud.
  • The information they contain, and any user interactions with them, are not always transparent to the rest of the organization.
  • It takes time and effort to understand unexpected changes in a spreadsheet’s output numbers and to communicate them appropriately.

Regulatory legislation asks executives to implement and document the internal controls that are in place to ensure the integrity of financial data and holds the business accountable if employees do not adhere to any regulatory policies and procedures. For these reasons, auditors should monitor whether organizations are implementing internal controls to determine if spreadsheet data are accurate and up-to-date; spreadsheets have been accessed by authorized personnel only; and new or changed spreadsheet calculations have been tested properly and signed-off by the required personnel.

However, before implementing any internal controls and management procedures to spreadsheets, organizations need to identify and consider the possible causes leading to integrity losses in spreadsheet outputs. Prior to considering any solution, organizations need to understand all the areas that could jeopardize a spreadsheet's integrity. Full spreadsheet integrity (i.e., assurance that the output is the expected processing of the input), depends on five key elements:

  1. The programmer of the spreadsheet's logic must understand the business calculation being modeled.
  2. The programmer must create the required logic without errors.
  3. Subsequent data inputs — manual or automatic — must be valid.
  4. Subsequent user and maintenance activity must not corrupt the original spreadsheet logic.
  5. Multiple user tasks performed on a spreadsheet must be conducted in the correct order.

Understanding the causes leading to loss of spreadsheet integrity also will help executives determine the best course of action to ensure compliance and diminish risks.

HOW TECHNOLOGY CAN HELP

Once auditors understand how spreadsheets are used and identify risks to spreadsheet data integrity, they are ready to provide recommendations to ensure compliance with government regulations. Currently, many organizations are using technological solutions to diminish spreadsheet risks and facilitate compliance. These solutions range from manual to automated options.

Manual solutions — such as regular reviews of spreadsheet logic — are somewhat effective. However, they will always suffer from an inability to monitor all spreadsheet rows, columns, and sheets within a reasonable period of time. Moreover, even the most thorough manual tests and audits provide no assurance that spreadsheet integrity has been maintained in each subsequent user interaction. For these reasons, regulators recommend that manual controls be tested repeatedly, unlike automatic controls. This provides a strong incentive for organizations to consider the use of technological solutions, which can be categorized in two main groups.

The first group of tools allows organizations to identify potential flaws in a spreadsheet's logic. They include the smart-tips in Excel 2003 and add-ins available in programs such as Visual Basic. These tools use recognition patterns in cell formulas — such as coloring cells that have repeated formulae — to give a visual understanding of the logic's flow or identify error-prone logic elements, such as a formula that refers to a blank cell. Unfortunately, the wide variety of spreadsheet structures and logic requirements make it difficult for these tools to be 100 percent effective, so they can only suggest, rather than confirm, areas where the logic patterns look inconsistent.

The second group of tools reviews a spreadsheet's integrity after it enters operational use by imposing, for example, some form of lockdown on spreadsheet changes. This can be effective in well-resourced IT departments and organizations where developers are available to change, test, and re-issue a revised version of each spreadsheet after it is amended. Alternatively, these tools can be effective in situations where employees only change a narrow range of data input cells within the spreadsheet application. Nevertheless, these tools may prevent users from continuing to adapt the spreadsheet's functionality to changing business needs in the appropriate amount of time. Either people must stop and wait for the corrections to catch up or the employee uses an unprotected spreadsheet copy. When the latter happens, all control is lost.

Another option in this category is to incorporate logic tools as part of the spreadsheet. However, these tools can be inappropriate for a number of reasons. First, they need to be used after every user interaction, creating extra work and delaying business output. Second,the results generated require interpretation by someone who understands the spreadsheet's structure, which is unlikely when using an operational spreadsheet. Third, logic tools do not validate the logic of a spreadsheet, resulting in a false sense of security. Finally, logic tools do not report data additions or changes, which are just as important as the logic flow to ensure the spreadsheet's output integrity.

Other solutions are becoming available to monitor the integrity and logic flow of operational spreadsheets. These tools focus on change management and user interactions, allowing companies to expose any variant information contained within a spreadsheet at all times and for all changes without disruption to the business. Areas of operational risk are highlighted when activity does not conform to expected patterns and new business information is available, such as reporting data values for key performance indicators.

LOOKING AHEAD

Organizations must identify and monitor spreadsheets that have become an integral part of their business processes. In many companies, spreadsheets are no longer documents of transitory value; they have become full-fledged business programs, which process data to meet specific business needs. When spreadsheets are used for operational purposes, they need to receive the same care and maintenance granted to other business applications and associated databases. Although achieving this may seem an arduous task, technology can help by reducing risk and helping organizations with regulatory compliance. More important, technology can monitor changes to key data over time, while trends relating to key business indicators held in spreadsheets are reviewed quickly.

Ralph Baxter is co-founder of ClusterSeven, an enterprise spreadsheet management software company established in 2003. Baxter is a committee member of The Information Security Special Interests Group affiliated with The IIA. He is also an energy and utilities specialist with more than 20 years' experience in the industry with companies including BP and the Lattice Group, now part of National Grid Plc.


Promoting the answer
The spreadsheet is quite useful in doing some engineering analysis. Eg; they can be used to prepare bills of quantities, materials costing as well as other project management strategy such as forcasting and critical path analysis.
Posted By: N.A.F Prince
2012-11-06 6:59 AM
intelligent auditor
this was clearly shows your good experience
Posted By: R.IyyanRaj
2012-10-22 9:03 AM


COMMENT ON THIS ARTICLE

Internal Auditor is pleased to provide you an opportunity to share your thoughts about the articles posted on this site. Some comments may be reprinted elsewhere, online, or offline. We encourage lively, open discussion and only ask that you refrain from personal comments and remarks that are off topic. Internal Auditor reserves the right to edit/remove comments.

Name:

Email:

Subject:

Comment:


To make something bold:
<strong>Text to bold</strong>

To make something italic:
<em>Text to italicize</em>

To make a hyperlink:
<a href="URL">Text to link</a>

 

Subscribe_June 2014 

IIA_AllStar_July2014

 IIA_AllStar_July2014

IIA Academic_Nov 2013

IIA SmartBrief

 IIA Vision University

 

 Twitter

facebook IAO 

IA APP