Audit Data Analytics in Excel: Practical Tests on ERP and Payroll Data

Most audit teams still test a sample of 40 invoices from a population of 100,000. This course shows you how to test all of them, in Excel, with no paid audit software. You get a ready-made library of audit tests you can run on standard ERP and payroll extracts: duplicate payments, split purchases, vendor bank changes, vendors matching employees, ghost workers, overtime spikes and more. Every test is shown on a small worked data example, so you can see the data before and after.

It is part of the EunoiaData Professional Toolkits series and is written for internal auditors, accountants, finance and payroll managers, and risk and compliance staff. Techniques are taught in Microsoft 365 first (XLOOKUP, FILTER, GROUPBY, LET and Power Query), with short notes for Excel 2016. Data examples come from ERPs such as SAP, Sage, Pastel, Microsoft Dynamics and Oracle, and from local payroll systems. Each lesson closes with three views: as the auditor, as the manager and as the board.

Examples follow one fictional case company, Kuvanya Group, operating across Zimbabwe, Zambia, South Africa, Botswana, Kenya and Nigeria, with mixed-currency ledgers and local payroll systems. The company and all its people are fictional, created by EunoiaData for teaching.

Frameworks used

The course explains these in its own words: the IIA Global Internal Audit Standards (2024), COSO Internal Control – Integrated Framework (2013), ISA 520 Analytical Procedures and ISA 530 Audit Sampling (IAASB, extant versions, with revisions in progress), and Benford’s Law as a statistical method. Editions are shown as at 2026 and are expected to change. EunoiaData is not affiliated with or endorsed by any framework body or software vendor; product names are trademarks of their owners and are used for identification only. The course does not prepare you for any certification exam.

What you will learn

• How to turn risks into tests, request complete ERP and payroll extracts, and prove completeness before testing
• How to clean, match, join and summarise audit data with Power Query, XLOOKUP, PivotTables and dynamic arrays
• How to find duplicate payments, duplicate vendors, split purchases and vendor master anomalies
• How to find ghost workers, overtime and allowance anomalies, and when Benford’s Law helps or misleads
• How to triage exceptions, combine analytics with sampling, and present results to managers and boards

Your toolkit

You leave with five ready-to-adapt tools: a data request and completeness checklist, a documented test workbook template, a ten-test procure-to-pay library, a nine-test payroll library, and an exception log with a findings tracker. The course includes more than 20 worked data examples.

How the course works

There are five modules, each with four lessons and a short quiz. You need 70% on each quiz to complete the course and earn your certificate. You should be comfortable with everyday Excel. Audit basics are assumed; if you need them, start with our Internal Audit Fundamentals course. It pairs with our ERP Audit Toolkit course, which covers the controls behind the data.