October 30, 2017

Electronic spreadsheets are versatile and vital for all types of organizations. However, the flexibility that makes spreadsheets such as Microsoft Excel so popular is also the primary reason that they can end up concealing countless critical errors.

Spreadsheet “Horror Stories”

An organization called European Spreadsheet Risks Interest Group keeps a list of “horror stories” caused by software errors. Here’s one example:

The Nevada City 2006 budget was created on an Excel spreadsheet. In one version released to some politicians and the public, it showed a $5 million deficit in the water and sewer fund. But in another version given to staff members, there was no deficit.

City officials announced one version was “corrupted and wrong.” While trying to detect what went wrong, they found other spreadsheet errors that also needed to be corrected.

One More Tip: Make an Excel spreadsheet expire on a specific date if the information is no longer accurate after a certain point. This will prevent users from viewing or editing the spreadsheet.

What’s at stake? Spreadsheet mistakes can result in flawed investment decisions, financial losses, inaccurate reporting, embarrassment, loss of investor confidence and various types of fraud.

The problems stem from the fact that Excel spreadsheets are often created informally. They may start out small and grow to be very complex (one spreadsheet can contain hundreds of thousands of lines). If one of the figures is incorrectly calculated, the mistake can be carried through to the bottom line. In some cases, the discrepancies can be inadvertent “human error,” such as a typo or a number placed in the wrong cell. But in other cases, they can involve attempts to commit and hide outright fraud. Without the proper oversight, training and auditing, spreadsheet inaccuracies might not be discovered for years.

Consider the following eight warning signs that your company’s spreadsheets may have taken on a life of their own:

1. Your company does not have a defined protocol regarding how and when your employees can create and use spreadsheets.

2. Employees who create complex spreadsheets using software do not typically prepare a manual detailing how the spreadsheet was constructed or how it is to be used and maintained by the end users.

3. Spreadsheets are not subject to a periodic audit to ensure their accuracy.

4. Numerous spreadsheets have been created by employees at your company who have been promoted, terminated, or left the company for other jobs. The spreadsheets continue to be relied upon by people who have no idea about their history.

5. In order for a spreadsheet to produce analysis, employees cut and paste data from multiple sources, or link to other spreadsheets within the company.

6. Multiple employees rely on, and manipulate, the same spreadsheet at different times throughout the year. In addition, multiple versions of the spreadsheet are circulated via e-mail and stored on employees’ personal hard drives.

7. The formulas within the spreadsheet can only be understood and manipulated by those with exceptionally advanced Excel skills.

8. Your company has spreadsheets with formulas and overall structure are not protected from further editing by the end users.

9. Spreadsheets contain VBA coding (visual basic for applications) that few employees have the ability to edit or review.

Your company can implement steps to dramatically reduce the frequency and resulting impact of spreadsheet errors. For example:

  • Conduct an inventory of all Excel spreadsheets currently being used by employees throughout your organization. Don’t limit the exercise to just the accounting and finance departments, since other departments, including marketing and supply chain, regularly use spreadsheets for a multitude of reasons.
  • Create a policy that details when Excel spreadsheets are to be created and used. Depending on the size of the organization, you may consider maintaining a centralized inventory of all spreadsheets in use by each department. This central list can prove to be helpful during compliance and year-end audits.
  • Require original authors of each active spreadsheet to prepare a detailed manual using a standard reporting format.
  • Limit the use of VBA coding and ensure that all coding is periodically reviewed for accuracy.
  • Require that business critical spreadsheets are audited or reviewed at least quarterly to ensure that the source data, formulas and other information are up-to-date and accurate.
  • Mandate that all fields that are subject to manual input are password protected.

If the appropriate checks and balances are put in place, the frequency and magnitude of errors in your company’s Excel spreadsheets can be dramatically reduced. Consult with your accountant to discuss additional ways to mitigate spreadsheet risk throughout your organization.

© 2017