Best Practices for Controlling Spreadsheets

Spreadsheet Controls

As companies design and implement financial reporting and operating controls – they often overlook one of the more ubiquitous areas, spreadsheets. Spreadsheets offer an easy, readily available and simple solution to financial analysis and reporting. However this seeming simplicity often masks the risks to data integrity from design deficiencies and user errors and explains why spreadsheets are often so challenging to control. Various studies estimate the potential for significant errors to be as much as (or more than) 80% of all spreadsheets in use.

Our recommendations below offer some simple design and operating changes for your use of spreadsheets to better control the risks of this often overlooked area.

SPREADSHEET TYPES

Spreadsheet use is pervasive and due to its wide usage it encompasses all sorts and types of spreadsheets serving many different functions. When identifying those spreadsheets important to your financial reporting process, consider the following:

Data Templates – Spreadsheets used to transfer data between systems and users, including uploads to ERP systems (such as journal entry templates, or interface/upload templates).

Financial Reporting Templates – Spreadsheets used for internal and external reporting such as Hyperion or XBRL templates.

Logs – Spreadsheets may be used to log certain activity, or even act as de facto sub-ledgers with details of transactions and assets.

Reports – Regular system reports and extracts are often distributed as spreadsheets to facilitate their review through sorting and filtering. Consider the data integrity of these reports used for key decision making or monitoring.

Accounting entries – Spreadsheets may be used to calculate or support journal entries including key estimates, allowances, accruals/deferrals and valuations (i.e. market valuations). Consider the wide mix of entries and supporting spreadsheets.

Controls – Spreadsheets also are used for performing control functions such as account reconciliations, account analysis, budgetary reviews and exception monitoring or even checklists.

CONTROLLING SPREADSHEETS

Spreadsheets are subject to the same operating, design and control objectives as much larger ERP and other formal systems. While the impact of spreadsheets and the volume of $ dollar activity reported by them often matches that of underlying ERP accounting systems; spreadsheets do not receive the same support and oversight in their design and use. Challenging their control is the fact that spreadsheets are designed to be easy to use and change; and their use is often spread across a broad, decentralized group of user developers who lack formal design training. To address the risks to the design and use of spreadsheets, we’ve identified 5 keys areas below with recommendations to strengthen controls.

1. Transparent Design

Spreadsheets often start out as one-off models that quickly become part of the regular reporting cycle without much formalization to what has developed into a daily or monthly tool. Design the flow of the spreadsheet so that it is clear and readily understood by an outside reviewer.

Organization – Ensure that the spreadsheet’s layout is organized moving left to right across columns, and down the page. Use new tabs/spreadsheets for different data sets, calculations or sections maintained by other users. Consider how it will be used month-to-month and plan for those flows. Consider how it would be printed to give it a logical structure that is easy to use and follow.

Labels – Label everything from the spreadsheet tabs, to the spreadsheet titles, column and row headings, intermediate calculations and steps (such as sub-totals), cross-references, data input areas, etc. – for clarity and to help with its understanding and review. Use clear descriptions avoiding cryptic abbreviations and internal terms.

Instructions/Overview – Consider adding a separate spreadsheet tab that summarizes the objective of the workbook/spreadsheets, data sources, data uses, key calculations and data flows to instruct on the use of the spreadsheet, its various components and organization. The act of summarizing this information will force the user/developer to think logically about how the spreadsheet is to be used and organized and also has the benefit of capturing this info to instruct other users and reviewers and to document its design for maintenance of the spreadsheet.

Hidden data and formulas – Limit hiding information in the spreadsheet; hiding a step of the process limits the ability for end-to-end understanding and a comprehensive review.

2. Managing Inputs

Consider the nature of data inputs and how they will be maintained or updated.

Separate inputs from formulas – Segregate any data inputs, especially for blocks of data, from their related calculations to allow for updates to data without the risk of inadvertent changes to underlying formulas.

Consider the method of data input – Such as how data will be entered (download, copy and paste vs. manual keying) to ensure it is appropriately treated and that any changes to inputs are clearly evident and/or blocked. For downloads, consider the opportunity for imbedded formatting errors (such as truncated data, hidden characters, inserted blank spaces, unrecognized character codes, and numbers vs. text formatting) that may impact calculations. Consider how formatting and input errors will be detected and resolved. Understand your data.

Define a dedicated data input area – Block out a dedicated, clearly marked area of the spreadsheet for data input. Consider lines or color shading to delimit the input area from other sections, and to clearly show when inputs exceed defined ranges where formulas will need to be updated for the new input ranges.

Separate “master data” – Segregate master data (such as multiple elements of a formula) separate from the calculations and source data. Having the master data parameters separate from formulas will allow for them to be independently updated, and also easily reviewed.

Check your data – Build-in data checks, check-sum, record counts and other validation of acceptable data values, data ranges, date ranges and transaction types/codes to ensure that data input conforms to expectations, agrees to the source data (is complete and accurate), and is properly formatted for the design of the spreadsheet (text vs. numeric data, imbedded blank spaces, field widths, etc.).

3. Formulas and Functions

Simple errors in formulas, or in formula data ranges can have significant impacts to the resulting outputs and conclusions drawn. These errors may not be readily apparent or easily identified, especially under the time pressures of a financial closing.

Understand your formulas – Formulas may require a certain format of data (i.e. text, numeric, sorted, etc.) to properly operate. Errors may not always be self-evident, and may result in improper calculations and erroneous results unknown to the users. Selection of the wrong formula, or improper formula range may return incorrect/incomplete values.

Research and treatment of errors – Errors (i.e. “#N/A” or “NULL”, “#NUM”, “#VALUE”, “#REF”, etc.) may show problems with your formulas or may indicate that the data has changed (i.e. new or missing elements or improper formats). Some functions, such as lookups may be expected to regularly generate acceptable errors among the outputs. That is, the formula may return errors for certain transaction types or activities that do not regularly occur and that do not have a match in the current lookup. It is important then to distinguish between acceptable errors and those “true” errors requiring correction. Errors should be researched, and any correction or acceptable remaining errors should be identified and explained.

Filters – Data may be filtered to exclude out of range, inappropriate or unrelated activity for the analysis. Ensure that filter criteria is clearly identified, properly applied, and that filters used are clearly indicated for review and cannot be easily or inadvertently overridden and changed.

Test your formulas – Test your formulas for a range of values and different types and formats of inputs. The difference between on-screen copying of data from formal downloads/exports, and the choice of file format (i.e. “.txt”/“.rtf”, “.CSV”, “.xls”/“.xlsx”) may have different results when input or copied into the spreadsheet.

4. Change Control

The functioning of the spreadsheet may change over time, as well as data used changing period-to-period with regular reporting or for the latest/revised data available. Regardless of the nature of change – methods should be employed to highlight changes made and ensure they are appropriate and properly reviewed.

Versioning – The underlying logic of a spreadsheet can change over time, as well as the spreadsheet data being regularly updated with each reporting period. The file name and spreadsheet notes should indicate the version (i.e. v1, v2, v3…) of the spreadsheet logic, and should also clearly note the period being reported.

Changes to Design – Ideally changes to the design should be independently reviewed prior to use; especially for major changes. Changes to the design should be controlled, limited to proper users (such as thru password protection) and reviewed and tested. If the review of changes is not performed in advance, at a minimum, the reviewer/approver should be aware of the changes made and evaluate them in their regular review and approval of the spreadsheet. Instructions/overview should include changes made to the design logic to highlight them for review and maintenance.

Changes to Data – There are changes to data that occur when the spreadsheet is regularly updated for each period’s reporting. These updates should be included under the regular review of each period’s spreadsheet/reporting. Changes may also occur to the data for the latest data available and preliminary versus final analysis. These changes should be clearly identified to ensure they are properly treated and reviewed. To avoid any confusion between the data sets and spreadsheets, the file names should clearly indicate the changing phases of the data within the file name and notes showing the date/time the spreadsheet was last updated or prepared.

5. Securing Spreadsheets

There are numerous ways to secure spreadsheets to protect them from inadvertent or improper changes, and ensure the spreadsheet operates as intended and is available for use.

Network Location – Ensure key spreadsheets are saved to a defined network location. There are multiple benefits to centralized retention, including:

  • The spreadsheet is available to a group of users/reviewers through the network,
  • Access to the spreadsheet is controlled/restricted thru the network,
  • The defined location helps with monitoring and indicates the spreadsheet’s importance through its inclusion/membership in the directory, and
  • The centralized storage location helps with proper retention and backup of the spreadsheets

Password Protection – Ensure spreadsheets are password protected and that passwords are known/shared with only necessary users. Protect formulas from change through the use of passwords and password protect input data from inadvertent or improper changes.

Backup – Ensure that spreadsheets are regularly backed up and available for use when needed. Consider backup of both blank spreadsheet templates and well as spreadsheets complete with data. Large input data blocks and input files should also be retained and backed up in the eventuality that a spreadsheet needs to be corrected or restated.

How We Can Help

We’ve identified a number of areas where controls around spreadsheets can be strengthened. These recommendations apply to most spreadsheets and their uses, however the application and implementation will vary for your particular needs and data.

  • We can help you inventory high risk/high importance spreadsheets for your day to operations, or for SOX compliance.
  • We’re available to assist in any design considerations, or other ways to better control spreadsheet design and use.