Going beyond the spreadsheet
Tens of thousands of rows of data, hundreds of worksheets, countless tables... Where do you begin? Do you find yourself in this all too common dilemma? You’ve received all the information you could possibly want but how do you best analyse it?
Although investigators and analysts are receiving and evaluating more data than ever before, insights within the data are often difficult to achieve using traditional analytic methods. While spreadsheets offer a quick way to understand simple, one (or possibly two) dimensional figures and facts, the ability to draw conclusions from the data diminishes as the size of your spreadsheet grows in volume and complexity.
Visualisation, or visual analytics, is the concept of using pictures, charts, diagrams and maps to reveal key relationships, communications, trends and patterns within large amounts of data. Many companies are now using the power of visualisation to detect fraud and abuse; from detecting fictitious employees and conflicts of interest, to detecting inappropriate corporate credit card expenditure.
Increasingly, there is a myriad of sophisticated visualisation tools and techniques for you to choose from – from heat maps to word clouds to geospatial analysis to tree maps. However, one of the pioneers and more common visualisation techniques used is ‘link analysis’. Link analysis explores direct and indirect relationships and associations by drawing ‘links’ between related entities or objects.
Here are a couple of simple link analysis tests you can run which may help you to detect fraud and abuse within your organisation:
1. Employee and Supplier Master File Relationships.
This analysis will identify relationships between employees and suppliers based on unique identifying factors such as addresses, phone numbers, bank accounts or IRD numbers. Unexpected relationships are quickly identified which may indicate process gaps or relationships, warranting further investigation. In figure 2, ‘supplier 567’ and ‘employee 123’ are linked by a common address and bank account number. This could be a relationship known by the company, or it could be an undisclosed conflict of interest between a purchasing officer and one of the company’s main suppliers.
Data required to conduct test – employee and supplier master files.
2. Electronic Payment Data Relationships
This analysis identifies relationships within your payment data, examining payee name and payee bank account details. This analysis can be used to identify instances where payments appear to have been made to a legitimate supplier but have instead been directed to one of your employee’s bank accounts. In figure 3, suppliers 151 and 250 are linked to two bank accounts each. Either the suppliers have changed their bank accounts over time or the bottom bank accounts are fraudulent and belong to one of your finance team employees.
We recommend this analysis is done in addition to a master file analysis. This is because we have found that bank account numbers held on master files are often not kept up to date and may be different to the actual bank account receiving payment.
Data required to conduct test – list of payments made for a reasonable period (i.e. 1 year) recording date, amount, payee name and payee bank account number.
For more information please contact Lisa Tai.
+64 (0) 9 303 0943
One of the main visualisation tools utilised by the Deloitte Forensics team is i2. In September, Lisa attended the i2 APAC User Group Conference in Canberra which was attended by hundreds of analysts and investigators from the defence, law enforcement and commercial sectors across the APAC region.