Eliminating Risks in Spreadsheets

A Guide for Internal Auditors to Regain Control and Limit Exposure

By: Richard Blaustein

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 utilized across every functional area of an organization for almost any type of reporting or analytical need – an unparalleled breadth of utility, often only limited by one’s own creativity. Unfortunately, however, the vast majority of these spreadsheet tools are created without using proper controls, testing procedures, and design standards. In such cases, the results can be disastrous. But, it doesn’t have to be that way.

Given the sheer breadth of use, the role in key decision-making processes, and the lack of adequate controls in development practice, the risk exposure is enormous – perhaps one of the greatest audit category 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 so greatly depend upon 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 potentially crippling an organization.

The good news is that, contrary to popular belief, spreadsheets are, in fact, eminently controllable. Internal auditors have the power to put in place specific procedures which can all but eliminate spreadsheet risk. They can control the previously uncontrollable, and do so without stifling in any way the analytical horsepower that drives an organization to excellence.

Categories of Risk

Where do things typically go wrong? There are 3 general categories of spreadsheet integrity risk:

· Structural Design

· Calculation Formulas

· 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. Not only will a well-structured spreadsheet make it easier for the author to develop it and make future changes and enhancements, but even more importantly it will make it easier, nay possible, for future users to work with. 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 properly. In today’s business world, job responsibilities change with the wind, and people come and go. It might otherwise be the best spreadsheet model in the world, but if it is a black box and the author leaves the company for greener pastures – its only value will be as a screen saver. 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 you can bet that something is being overlooked. What you have in this case is a hidden minefield. 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 VBA code. Secretly millions of dollars are being misallocated via incorrect decisions – directly the result of modeling errors which would have otherwise been preventable by proper structural design.

How does one achieve proper structural design? Interestingly enough, it starts with a mindset. The mindset is simple – don’t build the spreadsheet as if you’re building it for yourself – build it as if you were building it for someone else. Make it 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 – than the spreadsheet model will ultimately fail.

The key ingredient in following through on this mindset is to design the spreadsheet with a user-friendly Graphical User Interface (GUI). Essentially, this is a menu page (worksheet) which 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 many more individual worksheets – and thus worksheet tabs. It can be incredibly difficult for a user to figure out which one(s) 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 very clear to the user what to click on to get to the capital expenditure input section, for example. At the same time, the user will know every data input section that needs to be updated, because each input section will be right there 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 which calculate the end results. There are 4 primary deficiencies that the vast majority of spreadsheet model authors fall victim to:

· Fixed-value Formulas

· Lack of Columnar or Row Consistency

· No Protection

· No Intrinsic Error Checks

Fixed-value Formulas

Never, never, never should a formula contain a fixed (“hard-coded”) value within the formula itself! Yet, if you take a deep look into almost any spreadsheet model or report – even those built by internal “Excel gurus” – you will invariably find these types of ticking time bombs, whether it’s something as egregious as calculating labor costs using a fixed rate per hour within the body of the formula (e.g.: “=B1*15.50”) or something slightly more innocuous such as calculating pounds of product sold using a fixed product weight (e.g.: “=B10*33.6”). In these examples labor costs would mostly likely change far more frequently than product weights, but in either case, things will at some point change – and when they do, 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 the business’ operations. For example, multiplying a daily production number cell reference by a fixed 5 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 7 day production schedule? Then the calculated results are incorrect once again.

Prevention of these types of mistakes is simple – separate the input components from the formulas. When the hourly labor rate, product weight, or production days per week data 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 easily do so. Otherwise, they become lost in the great abyss of tens of thousands of individual cell formulas.

Bottom line – things change and thus, formulas need to be flexible to isolate the components of potential change. Don’t ever assume 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 he/she does remember, will he miss a formula - or a block of formulas? Of course, this cumbersome review would take an enormous amount of time…even if it were done without any mistakes!

Lack of Columnar or Row Consistency

In order to take shortcuts, often times a spreadsheet model author will create formulas that are different across similar types of columns. For example, in a spreadsheet with months across the top of the sheet in columns extended for a 5 year period (60 columns), the author might build in, let’s say, a monthly raw materials price increase that varies by year. Unfortunately, it is a common – and very dangerous – practice for the author to make slight changes in the formulas for each year, such that the twelve (Jan-Dec) year one formulas reference the year one monthly percent increase input in cell B4, the twelve year two formulas reference the year two monthly percent increase input in cell B5, and so forth.

The problem with this is that as soon as a change is made or years are added at some point down the road, 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 60 columns to the right. Now all of the years reference the year one input! Now imagine if the original author of the spreadsheet model is working in a different position or no longer with the company. The likelihood of this mistake happening increases exponentially.

To prevent this, all 60 columns should contain the exact same formula. The formula itself should contain the internal logic to lookup the corresponding year’s monthly price increase percentage and apply it accordingly. In this case, one never has to worry about adding years or rolling through modifications in the base formula. Just copy and paste the cell to the right and all is fine.

No Protection

One of the most dangerous risks to a spreadsheet model or report’s integrity – and perhaps the easiest to address – is worksheet protection. As a default, Excel locks all cells in a spreadsheet. However, in order 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), then each worksheet within the workbook should be password protected so that formulas can not be accidentally deleted or modified, and rows and columns can not be inserted, the effect of which 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. When initially testing a spreadsheet model, the author might have checked, for instance, that the sum of a percentage distribution of individual product volume equaled 100%. However, it is possible that one particular formula which ultimately impacts an individual product’s volume calculation contains an error which inadvertently adds to it a component that is only triggered under certain circumstances. In such a case, the dormant error occurs at some point(s) after the quality control checks have already been completed, and as a result, the model produces incorrect results that might not ever be discovered.

To control for this 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 which are supposed to add up to 100% do not, or if the sum of the parts does not equal the whole, or if a result is a negative number, or if it exceeds a certain unreasonable pre-defined 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 VBA code can be written to send pop-up alerts to the user when an error occurs, as well as lock the user out of viewing – or even printing - the results sections until the errors are corrected. Automated real-time e-mail notifications can also be sent to a systems administrator or the spreadsheet model owner so that the error occurrences are immediately transparent and not allowed to remain dormant anywhere in the organization.

Data Inputs

The old adage applies, “Garbage in, garbage out.” A model is only as good as the data that feeds it. Even if all other aspects of a spreadsheet model are robustly controlled for, an end-user still can make a typo and enter the data input incorrectly. Similarly, they can misunderstand the type or format of the data that is desired, thereby making an incorrect entry for which the model depends. These types of errors are certainly not limited to spreadsheets. Any type of software or software platform is susceptible to this.

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, if not all but eliminate, the likelihood of their occurrence. There are 3 primary categories of these types of end-user error control mechanisms:

· Data Validation Checks

· Data Entry Controls

· Explanatory Features

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 end-user is alerted to the problem so that he/she may retry under the pre-established constraints. For example, a data input cell can be limited to only allow positive numbers, or whole numbers (integers), or dates, or times, or percentages, or text, or text less than x number of characters, or numbers greater than 25 and less than 500, or selections from a pre-determined or calculated option list presented via a drop-down list…You get the idea.

If you are creating a spreadsheet and you set up a data input cell in which the end-user is asked to enter a percentage (e.g.- enter the % of investment which is financed), then it is far better to set a data validation check on that particular cell to only allow percentages between 0% and 100% than to leave it completely unconstrained. What if the end-user means to type 40%, but instead mistakenly types 400% or 4000% - big problem. With data validation, the problem is immediately prevented from occurring.

Data Entry Controls

There are many types of data inputs which nicely lend themselves towards utilizing data entry control objects. These objects include such examples as: option buttons, check boxes, list boxes, drop-down combo list boxes, spinners, sliders, and the list goes on and on. These types of tools are an excellent way to not only improve ease-of-use, but more importantly to further prevent end-user data entry error.

A simple example is when a data input cell within the spreadsheet calls for an “either-or” selection (e.g.- “select yes or no”). Rather than having the user type “Yes” or “No” – and risk them typing “Yws” or “Np” which would otherwise cause an error in the model, an option button control should be utilized, such that a user can click on either Yes or No, but not both and not anything other than Yes or No.

A slider control (similar to a scroll bar) is a great example of a control that can be used to only allow entries within a specified range, while at the same time forcing the end-user to visually focus on the relative position of the slider, which greatly reduces the possibility of the occurrence of a typo entry that might still fall within the acceptable range. For example, if a cell contains a Data Validation Check (as described in the previous section) which constrains the end-user to only enter percentages between 0% and 100%, then if the user intends to enter 40%, but types 4% instead, the entry would still be accepted and the user might not recognize the mistake. However, with a slider control, the user drags the slider box along a continuum (or clicks the up/down arrows or clicks before/after the slider box) to adjust the entry. In doing this the user is visually focused on the selection relative to its position on the range continuum. Thus, it is unlikely that the user would incorrectly effectuate an entry of 4% if she intended it to be 40%.

Explanatory Features

Some data input labels are very clear in what specific information is required, such as “Sales Units (thousands)”. The end-user should have no problem figuring out what type of data input is needed and in what format. However, many types of data inputs are, by nature, less intuitive to a potential end-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 end-user might have only assumed federal. Another example might be that the end-user is asked to enter “Corporate Discount Rate (%)”. The end-user might not have any idea what that means, and might leave it blank or enter an incorrect number – perhaps the corporate borrowing rate (incorrectly). In either case, the result is a data input error which might result in a bad investment decision.

There are several ways to avoid this type of end-user confusion. All operate under the same premise – give the end-user plenty of explanation and information as to exactly what data is called for. The simplest, although least visually attractive way, is to insert a comment into the input cell explaining what is needed. Excel’s default is to show a red triangle comment indicator in the upper-right corner of the cell. When the user rolls the mouse over the cell, the explanatory comment becomes visible.

Another similar, but more professional looking mechanism is to use Data Validation to set an input message, such that when someone clicks on the cell within the spreadsheet, the explanatory message appears.

In cases where an in-depth or overt explanation is required, it is better to add a Help button directly next to the data input cell. The Help button is custom-created and might typically take the shape of a rounded rectangle or a circle, adding the word “Help” inside of it. A short VBA program is written to make visible a larger text box which contains the explanation of what is required in the data input cell. The larger text box would typically contain a Close button to close it (via a second short VBA program) when finished reviewing the explanatory information. While more complex to create, this type of explanatory mechanism is a fantastic way to provide valuable information to the user, demonstrate some examples, and direct them to additional resources if further help is needed. It also makes the option of getting helpful information that much more apparent since the Help button is prominently displayed.

All in all, these types of explanatory features are vital to a spreadsheet model being used properly by an end-user. If they clearly understand what data is needed, without confusion, then there is a far greater chance that the correct data will be entered, and the model will thus produce the correct results.

Summary

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 properly, spreadsheets can pose tremendous risks by generating incorrect information and leading to mistaken decisions as a result of spreadsheet error.

Fortunately, these risks can be virtually eliminated by following proper design and development practices as highlighted in this article. It is unrealistic, however, to expect even the most sophisticated Excel user to be able to adequately follow and implement these tactics without the assistance of professional spreadsheet developers and experienced analysts. At Analytic Solutions, we live this every day – and have so for our entire educational and professional careers. Unlike any others in professional spreadsheet development and audit services, our analyst developers possess a powerful dual-expertise advantage. They are certified experts – certificated as such by Microsoft® – in professional Excel spreadsheet development, and at the same time they are experts in analytical sciences, including finance, accounting, and statistics. Analytic Solutions’ professional staff can assist internal audit and finance departments in helping to implement best practices in spreadsheet design and development throughout an organization by evaluating and correcting existing spreadsheet models and reports, as well as training internal resources on how to comprehensively implement world-class spreadsheet integrity controls.

For more information, contact Analytic Solutions, Inc. by e-mail at info@analyticsolutions.com or by phone at (215) 497-8394. Come find out why leading companies around the world such as GE and Starbucks depend on Analytic Solutions to provide them with the great peace of mind that comes with risk-free spreadsheet development.

© Copyright 2008 by Analytic Solutions, Inc. All rights reserved.