Join Audit Leaders TeamMate

Data analytics - a not so exclusive club

Guest blog by Alwyn Li CIA | 4 January 2018


When data analytics comes to mind, it always seems somehow a daunting concept, and perhaps quite a challenging one - which is true to a certain extent. One might suggest that unless you are a fully matured organisation with immense support from senior management, you may be running out options here.

Is that true? Perhaps not.

The general principle of data analytics is to spot trends and expectations. Yes, software could do it faster and maybe better when dealing with massive amounts of raw data. But the same goal can be achieved through other methods as well – simply the all mighty spreadsheet. In fact, some typical examples for using data analytics can almost all be performed through a spreadsheet.

Examples of compliance related internal audit engagements might include:

  • Evaluating expense reports and purchase card usage for all transactions
  • Analysing the source data by calculating the percentage of purchase card usage and compare it to the tolerance level
  • Performing supplier audits by utilising line-item billing data to identify anomalies and trends to investigate
  • Using the spreadsheet filter function to select repeated transactions with the same amount and/or occurring on the same day of the month; use the data to create a graph for comparison with any known seasonal/business cycle
  • Identifying poor data quality and integrity around various data systems that are key drivers to non-compliance risks
  • Using the spreadsheet to filter out blank cells or abnormalities in data formats

Other examples of use in fraud, risk assessment, detection and investigation could include:

  • Identifying ghost employees, potential false suppliers, and related parties or employee-supplier relationships
  • Comparing approved supplier lists with supplier payment data (vlookup); the same method can be applied for employee lists/headcount and salary payment data
  • Highlighting data anomalies that pose the greatest financial and/or reputational risk to the organisation
  • Using the spreadsheet to filter loss events data to identify any events with financial damages that are higher than the predetermined threshold

Further examples in operational performance related review may include:

  • Isolating key metrics around spend analysis e.g. payment timing, forgone early-payment discounts and payment efficiency
  • Calculating the percentage of payment timing (on time, early-payment) and compare percentage data with payment discount etc. or perform a simple sensitive testing
  • Performing duplicate payment analysis and recovery
  • Performing slow-moving inventory analysis

These are just some of the examples where the simple spreadsheet can be utilised for data analytics. In addition, with a bit of spreadsheet training on ‘macros’ for example even more tasks can be performed by using a spreadsheet only. After all, data analytics may not be such an exclusive club.

Whilst it may be true to say that use of data analytics is an acquired skill it is certainly a skill worth acquiring in enabling internal audit to strengthen its assurance to the Board and Audit Committee. It helps identify trends, common themes and weakness and therefore supports management to improve internal control.

Perhaps a question we as internal audit should ask ourselves is whether data analytics a tool for first or second lines of defence rather than internal audit as the third line or is it a tool that all three lines of defence should be using to improve the internal control environment across the organisation?


Read our research report Data analytics: Is it time to take the first step?

Content reviewed: 8 May 2019