Posted on 5 Comments

Are You Auditing Spreadsheets? You Should Be…And Here’s How?

End User Computing (EUC) applications (i.e. Excel, Access, etc) provide great benefits to organizations.  Savvy users can create applications that allow them to gather, sort, filter and analyze data.  One major benefit is the ability to make quickly make critical decisions based on the data.  This alone can increase income, increase market share or provide some other competitive advantage.

Of course these benefits are not without risk.  User developed and user controlled applications typically are not subject to the same development methodology as traditional computer applications. As a result, each end user developer can decide on the level of security, input validation, types of calculations, amount of and type of testing for each application.  The inconsistent development approaches by non-technical personnel along with the significant reliance often placed on EUC data is where the true risks reside.  Many organizations have experienced loss of income or data exposure (i.e. SSN #s exposed) because of poorly executive EUC strategies.  A recent study by Oracle and Accenture, more than 70% of the 1,100 global executives interviewed said they use spreadsheets to track and manage financial reporting daily (http://www3.cfo.com/article/2012/5/spreadsheets_financial-reporting-spreadsheets).

Organizational response to EUC risks range from non-existent to units fully dedicated to education and development to banning EUCs altogether (good luck with that one).  Regardless of the approach, organizations need to be aware of EUC risks.  Additionally, Internal Auditors must perform some test work surrounding EUC controls.  There are several approaches that can be taken, however, I suggest one of both of the following:

(a)    Inventory and risk rank EUC applications and test those posing the most risk

(b)    Embed EUC auditing into regular audit engagements.

Note: Options one assumes your organization has an EUC inventory and has risk ranked them accordingly.  This may not be a viable option for most.

So those are the two approaches, but exactly how do you conduct an EUC audit?  What follows is an 8 step high level summary:

1)     Inventory

Obtain or develop an inventory.  Ideally, management would have one available.  Most organizations required to comply with SOX should have this information.

Content Continues After Ad

2)     Risk Rank

Risk Rank EUCs using a standard rating methodology.  Rating factors may include usage (i.e. financial, decision making, informational, etc), dollar impact, or complexity of calculations.

Note:  You may skip steps 1 and 2 if the strategy is to embed EUC testing in scheduled engagements.

Now you want to test the following:

3)      Access and Security

  • Determine who has access to the actual file (i.e. Excel, Access, etc).  The file may be stored on a shared network drive, therefore, it is important to determine who has access to the file and is the access necessary.
  • Also review password protection for opening documents.  For example, who has the password, is it strong, etc.

4)      Input Controls

  • Gain an understanding of the type of data input and/or loaded into the EUC application.
  • Determine if the process for getting data into the system ensures data is accurate and complete.
  • Also review “input masks”.  For example, does the EUC application ensure only text is entered in text fields, dates in date fields, etc.

5)      Calculations and Formulas

  • Gain an understanding of the formulas used.
  • Recalculate independent of the application.

6)      Outputs

  • Review reports generated by the application.
  • Again check the calculations.
  • Review the content and distribution list to determine if the appropriate parties receive the information (or stated another to determine if inappropriate parties have access to the information).

7)      Change Control

  • Review the process for making changes to EUC applications.
  • Determine if changes are appropriately tracked, tested and approved.  There is nothing worse than multiple users making changes to an Excel worksheet that produces eventually produces untraceable inaccurate results.

8)      Version Control

  • Test processes designed to ensure that the latest approved version of the EUC is in use.

Performing EUC audits using these 8 steps will help strengthen EUC controls and may save your organization from losing money and/or data.

I offer a training session on EUC auditing (click here for more information).  If you are interested, please contact me.

If you like this post, tell a friend.

 

Robert Berry (96)

Robert (That Audit Guy) Berry is a risk, compliance and auditing advocate, educator and innovator. He helps good professionals become better by creating articles, web services and training that allow them to expand their knowledge network.

5 thoughts on “Are You Auditing Spreadsheets? You Should Be…And Here’s How?

  1. Great post! Have you ever used a tool for spreadsheet auditing? I’ve used Compassoft many times – it helps get the job done.

  2. I have not used Compassoft, but may need to check it out.

  3. Appreciate and agree.
    I was involved in audits where we found huge reliance on spreadsheets for monitoring parameters. These were so difficult to construct and maintain. But IT was in no way able to offer an alternative solution . The biggest risk was – no one knew the formulas and how the sheet was constructed, important underlying concepts etc.

  4. One issue I didn’t see mentioned is that of validation.

    Before relying on spreadsheets, some test should be run that the calculations were done correctly, and that it deals correctly with unusual or impossible inputs (e.g. zero, impossibly large numbers, negative numbers).

  5. Daniel – Interesting point. I kind of embedded that in the calculation portion. But it seems like it might make more sense explicitly stating it as you mention.

    Thanks for contributing to the discussion.

Leave a Reply