2009

Eliminating Spreadsheet Risks

A Guide for Internal Auditors to Regain Control and Limit Exposure

Richard Blaustein
President, Analytic Solutions Inc.

Spreadsheet models and reports can be powerful tools that provide tremendous analytical insight and help guide key decision-making processes throughout an organization. Their extraordinary versatility allows them to be used across every functional area of an organization for almost any type of reporting or analytical need. Unfortunately, however, the vast majority of these spreadsheet tools are created without using appropriate controls, testing procedures, and design standards. In such cases, the results can be disastrous. But it doesn't have to be that way.

The risk exposure associated with spreadsheets is enormous — perhaps one of the greatest risks that exists within an organization. Internal auditors struggle constantly with how to control this risk without removing these otherwise invaluable tools from the hands of those who depend on them. They are faced with the weighty dilemma of how to foster and not inhibit business value creation, while at the same time preventing latently destructive practices from crippling an organization.

The good news is that spreadsheets are, in fact, eminently controllable. Internal auditors have the power to put in place specific procedures that can all but eliminate spreadsheet risk. They can control the previously uncontrollable, and do so without stifling the analytical horsepower that drives an organization to excellence. There are three general categories of spreadsheet integrity risk: structural design, calculation formulas, and data inputs.

STRUCTURAL DESIGN

The design of the spreadsheet's structure is one of the most important mechanisms to ensure its success — both in the short term and well into the future. One of the biggest risks with spreadsheet models is the "Black Box Effect," when only the original author knows how it works, can make changes, or even run it correctly. In today's business world, job responsibilities change with the wind, and people come and go. Even if the analyst who inherits the model is an "Excel guru" and claims to have it figured out, if it was not well-structured in the first place then something has likely been overlooked. Everything seems fine, decisions are being made based upon the model's output, but no one realizes that the "Excel guru" didn't update a key data input or formula or piece of visual basic for applications code. Millions of dollars are secretly being misallocated via incorrect decisions — directly the result of modeling errors that would have otherwise been preventable by correct structural design.

How does one achieve correct structural design? Interestingly enough, it starts with a simple mind-set. Instead of building spreadsheet as if building it for oneself, users should build them as if they were building it for someone else. Spreadsheets should be intuitive. If it's not clear to anyone who uses it, exactly what data inputs are needed, and where they need to be entered without missing any single input requirement, then the spreadsheet model will ultimately fail.

The key ingredient in following through on this mind-set is to design the spreadsheet with a user-friendly graphical user interface (GUI). Essentially, this is a menu page worksheet that allows the user to click on icons and buttons to navigate to where they want to go — data input sections, results sections, or model administrator sections. Instead of relying on finding the right worksheet tab to click on at the bottom of the workbook, the user can easily click on the corresponding button on the menu page. Depending on the complexity of the spreadsheet model, there can be 10, 20, 30, or more individual worksheets, and thus worksheet tabs. It can be incredibly difficult for a user to figure out which ones they need to click on to get to the right place, and then to which section within the selected worksheet. With a GUI menu page, the worksheets and data ranges can be organized and categorized intuitively so that it is clear to the user what to click on. At the same time, users will know every data input section that needs to be updated, because each input section is right in front of their eyes on the menu page. As a result, it is far less likely that something will be missed.

CALCULATION FORMULAS

One of the biggest integrity risks in spreadsheet models and reports, and one that should cause many a restless night for internal auditors, is the lack of control in the core engine of a spreadsheet — the formulas that calculate the end results. There are four primary deficiencies that the vast majority of spreadsheet model authors fall victim to.

Fixed-value Formulas
A formula should never contain a fixed ("hard-coded") value. Yet, a deep look into almost every spreadsheet model or report — even those built by internal "Excel gurus" — invariably contains these types of ticking time bombs. Any built-in factor will at some point change, and when it does, the spreadsheet model will produce incorrect results, leading to bad decisions. Even seemingly "permanently" fixed components, such as time, can change within the context of business operations. For example, multiplying a daily production number cell reference by a fixed five days within a formula to calculate the week's production is flawed and incorporates unnecessary risk. What if the business expands and the work week is extended to a seven-day production schedule? Then the calculated results are incorrect.

Preventing these types of mistakes is simple: Separate the input components from the formulas. When all components are placed into a separate data input section, the spreadsheet model analyst can easily see what inputs and assumptions need to be entered or updated, and then can do so easily.

The bottom line is that things change and formulas need to be flexible to isolate the components of potential change. It cannot be assumed that the spreadsheet analyst will remember to go back and change all of the impacted formulas if a fixed value formula's data component changes. Even if the analyst does remember, he or she may miss a formula or a block of formulas. This cumbersome review would take an enormous amount of time, even if it were done without any mistakes.

Lack of Columnar or Row Consistency
Spreadsheet model authors often take shortcuts by creating formulas that are different across similar types of columns. For example, in a spreadsheet with months across the top of the sheet in columns the author might reference a different input for each year. As soon as a change is made or years are added at a later time, the analyst might forget that each year's formulas are different, and as a result make a change in the first column's formula and mistakenly copy the modified formula across all columns to the right, making all of the years reference the year-one input. If the model owner changes, the likelihood of this mistake happening increases exponentially. To prevent this, all the columns should contain the exact same formula. The formula itself should contain the internal logic to look up the corresponding year's input and apply it accordingly.

No Protection
One of the most dangerous risks to a spreadsheet model or a report's integrity — and perhaps the easiest to address — is worksheet protection. As a default, Excel locks all cells in a spreadsheet. However, to make this take effect, the worksheet itself must be protected. Data input cells should be unlocked so that a user can enter and change data input assumptions. All other cells — especially formula cells — must be kept locked. When the model or report is completed and in use (i.e., development is finished), each worksheet within the workbook should be password protected so that formulas cannot be accidentally deleted or modified, and rows and columns cannot be inserted, which in effect could destroy the integrity of the spreadsheet model.

No Intrinsic Error Checks
Even with thoughtful and careful calculation formula writing and subsequent quality control testing, there will be times when errors occur that only manifest themselves in certain situations. An error in one particular formula may be triggered only in certain circumstances, yet cause the model to produce incorrect results. In such a case, the dormant error occurs at some point after the quality control checks have already been completed, and as a result, the model produces incorrect results that may never be discovered.

To control for this type of error and prevent its occurrence, a spreadsheet model or report should contain intrinsic error checks within the model itself as a way for the model to, in effect, internally audit itself. Essentially, these error checks are formulas that are written and placed at various key points throughout the spreadsheet to check that various results match the intended result. They can automatically check if numbers that are supposed to add up to 100 percent do not, if the sum of the parts does not equal the whole, if a result is a negative number, or if a result exceeds a certain unreasonable predefined threshold. If any of these errors occur, a flag is triggered. A related process consolidates the status of these error checks and one quick glance in a very visible part of the spreadsheet model tells the user if there is a problem.

These types of continuous and self-auditing devices can be — and depending on the model complexity and value to the organization, should be — expanded upon far beyond the preceding simplistic examples. Automated pop-up or e-mail alerts can be used to prevent the user from accessing the results until errors are corrected and can alert a systems administrator so that the error occurrences are immediately transparent throughout the organization.

DATA INPUTS

The old adage, "Garbage in, garbage out," applies to data inputs. A model is only as good as the data that feeds it. Even if all other aspects of a spreadsheet model are robustly controlled, an end user still can make a typo and enter the data input incorrectly. Similarly, users can misunderstand the type or format of the data that is desired, thereby making an incorrect entry for which the model depends.

Even though these types of end-user errors can occur, there are specific mechanisms that can be incorporated into a spreadsheet model to greatly reduce the likelihood of their occurrence. The three primary categories of these types of end-user error control mechanisms include:

  • Data Validation Checks. Each data input cell in a spreadsheet can be set such that only certain types of data or ranges of data are allowed to be entered. Otherwise a customizable warning message appears, and the user is alerted to the problem so that he or she may retry under the pre-established constraints. For example, a data input cell can be limited to only allow positive numbers, whole numbers (integers), dates, times, percentages, or inputs within a specified range.
  • Data Entry Controls. There are many types of data inputs that lend themselves to using data entry control objects such as option buttons, check boxes, list boxes, and drop-down combo list boxes. These types of tools are an excellent way to improve ease-of-use, and more importantly, to further prevent end-user data entry error. For example, the option button allows for specific answers such as "yes" or "no" without risk of typos that will lead to error messages. Also, slider controls allow entries within a specified range while forcing the user to visually focus on the relative position — thus 40 percent won't be confused with four percent, even when both answers would pass the data validation check.
  • Explanatory Features. Some data input labels are very clear about what specific information is required, such as "Sales Units (thousands)." However, many types of data inputs are less intuitive to a potential user. An example might be "Marginal Income Tax Rate (%)." The model author might have intended for the user to enter a consolidated rate of federal, state, and local taxes, but the user might have only assumed federal. The result is a data input error that might result in a bad investment decision.

There are several ways to avoid this type of user confusion. All operate under the same premise, which is to give the user plenty of explanation and information as to exactly what data is called for. An inserted comment or data validation can be used to add an explanatory note. In cases where an in-depth or overt explanation is required, it is better to add a help button next to the data input cell. A custom-created help button is an effective way to provide valuable information to the user, demonstrate examples, and direct the user to additional resources if further help is needed. It also makes the option of getting helpful information much more apparent, because the help button is prominently displayed.

All in all, these types of explanatory features are vital to correct spreadsheet model usage. If users clearly understand what data is needed, then there is a far greater chance that the correct data will be entered, and the model will produce the correct results.

IMPLEMENTING BEST PRACTICES

Spreadsheets provide unparalleled flexibility and power in analyzing, reporting, enhancing, and optimizing performance and decision-making processes throughout an organization. Their potential value is near limitless. However, if not designed and constructed correctly, spreadsheets can pose tremendous risks by generating incorrect information and leading to poor decisions. Fortunately, these risks can be virtually eliminated by following appropriate design and development practices.

Richard Blaustein is president of Analytic Solutions Inc. in Holland, Pa.

To comment on this article, e-mail the author at richard.blaustein@theiia.org.


Share This Article:    

 

April 2014IaCover 

 IPPF_Ap42014

IIA Academic_Nov 2013

IIA SmartBrief

 Write for FSA Times

 

 Twitter

facebook IAO 

IA APP