Spreadsheets: Auditing & Validating (Part 2)

00_Spreadsheet AuditIn the previous post I mentioned few of the risks associated with spreadsheets. In this post I’ll try to show some excel tools which can help  in detecting errors and frauds in Excel spreadsheets.

In the late 1990’s “Poor control over spreadsheets at Jamaican indigenous banks contributed to management information and external reporting problems (i.e., P&L distortions) that contributed to the banks’ management and external regulators losing sight of the banks’ true positions and exposures. Which led to collapse of entire Jamaican Banking System.

Go to Special

Go To Special is a tool built-in Excel to select cells based on a specified type within a worksheet.
How to access Go To Special function?
Home > Find & Select > Go To Special
Shortcut – F5 or Ctrl + G

Options available in Go To Special

01_go to special

Constants
Selects all the cell with constant values. You have further option of selecting Numbers, Texts, Logicals and Errors.

In the picture below you can see the result of go to special constant. If you notice, it has also selected the cell N1. The cell looks blank but if you look at the formula bar it contains value “438”. It’s because the font colour is same as background colour. It’s one of the ways fraudsters manipulate spreadsheets.

02_go to special - constants-result

Formulas
Selects all the cell containing formulas. You have further option of selecting formulas which return values as Numbers, Texts, Logicals and Errors.

In the picture below you can see all cells are selected except G12 & G 13, even though they contain the formula. It’s because an embedded image is used on those cells. Another technique used by fraudsters to manipulate spreadsheets.

03 go to special - formula-result

You can use the objects options to select cells containing embedded objects.

The other options in Go To special are:

  • Commentsselects the cells with comments
  • Blanks – selects all the blank cells
  • Current region – selects the current region
  • Current array – selects the entire array, if the active cell is within the array
  • Row differences – Selects the cells that are different from the active cell within the selected rows. The cell used for comparison for each row is the cell in the same column as the active cell. This is very useful in highlighting inconsistent formulas in a row.
  • Column differences – Selects the cells that are different from the active cell within the selected column. The cell used for comparison for each column is the cell in the same row as the active cell. This is a very useful auditing tool for highlighting inconsistent formulas in a column. It also offers a quick and easy way to spot differences across multiple columns.
  • Precedents – Selects the precedent cells for the selected cells
  • Dependents – select the dependent cells for the selected cells
  • Last cell – selects the last cell containing data or formatting in the worksheet
  • Visible cells only – selects the cells which are not hidden (that is visible)
  • Conditional formats – selects all the cells with conditional formatting applied
  • Data validation – selects all the cells with data validation applied

Formula Auditing

The Formula Auditing tools are available under the Formula tab in the ribbon.
In the formula auditing tools you have trace precedent, dependent, show formula, error checking and evaluate formula.

Also note trace precedent and dependent are also available in Go To Special. The Go To Special trace precedents & dependents doesn’t show the arrows. By using trace precedent and dependent you can see how data is flowing in the worksheet. In the picture below you can see the pattern how data in cells D3 to D6 are linked to various other cells. You also notice that cell D5 doesn’t follow the same pattern and the same needs to be investigated.

04 Trace Dependent

The other tools available in auditing formula are error checking and evaluate formula. By using error checking > trace error, you can trace error by moving backwards in the worksheet. And evaluate formula helps in the understanding complex formulas.

Spreadsheet Inquire

Spreadsheet Inquire is an excel add-in available in MS office 2013 professional plus and Office 365 professional plus.

You can enable Inquire add-in by going to Options > add-ins > com add-ins.

With Inquire add-in you have the following main options; Workbook Analysis, Workbook Relationship, Worksheet Relationship, Cell Relationship, and Compare Files.

Workbook Analysis
It creates a detailed information on the workbook structure, formulas, cells, ranges and warnings.

05_Spreadsheet Inquire

  • In the Workbook tab hidden sheet & Very hidden sheet should be investigated.
  • In the Formulas tab investigate formulas with errors, formulas referencing hidden cells, negatives, duplicates, inconsistent and formulas with numeric constant.
  • In the Cells tab investigate invisible (font colour same as background), with conditional formatting as cells can be formatted with to show negative number as positive. However note the negative cells formatted to be shown as positive using format cell (ctrl +1) option doesn’t appear here.
  • In the Ranges tab you can find hidden rows & columns, named items and named items with errors.
  • The final tab is Warnings. All warnings should be investigated.

You can also export the workbook analysis report to an excel spreadsheet. The report is very detailed and each item on the left of analysis report has its own worksheet.

The other options on Inquire tab workbook relationship, worksheet relationship & cell relationship, gives a detailed diagrammatic view of how workbook is linked with different workbooks, worksheet with different worksheets in the workbook and cells with different cells.

Compare Files
It is similar to the word feature of compare documents. You can get a detailed listing of what have been changed in the new file when compared to old. The comparison looks something like this.

06_Spreadsheet Inquire compare

Very Hidden Sheets

Very Hidden sheets can’t be unhide through unhide sheet option in Excel. To view Very Hidden sheets you have to first go to Visual Basic Editor (Alt +F11). The Upper left corner lists down all the sheets in the workbook. Click the sheet which is not showing up in workbook and change it visible settings to “-1-xlSheetVisible” in the lower left corner.

07_Very Hidden Sheets

Microsoft Discovery & Risk Assessment Server 2013

Microsoft Discovery and Risk Assessment Server 2013 gives a consistent, automatic way to identify, analyze, and track files that are used in an organization. It can be used to create a master inventory of Excel workbooks Access databases, and automatically keep it up to date. It can also be used to categorize files according to predefined risk levels based on criteria, such as materiality or complexity.

So this tool will find all your Excel or Access files and give them a risk level based on criteria that you define and set.

Some additional points to consider:

  • Unhide all the hidden rows & columns in the worksheet
  • Format all the numbers as “Numbers” instead of keeping them custom or general, as it might be possible they are formatted to show negative as a positive value.

Since errors and frauds in spreadsheets can have major consequences. Adequate time should be spent on auditing and validating them. There is no precise way to audit Excel Spreadsheet. There are also a lot of paid tools available in the market to audit excel spreadsheets.

You can download the Excel file I used for this post here, spreadsheet audit template.
The actual file is created by Paula Guilfoyle, of the Excel club.

Related Articles:
Spreadsheets: Risks (Part 1)
Spreadsheets: Best Practices (Part 3)

 

Advertisements

3 thoughts on “Spreadsheets: Auditing & Validating (Part 2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s