Introduction to the Tool/Technique
Computer-Assisted Audit Techniques (CAATs) and Microsoft Excel are powerful tools in the internal auditor’s toolbox, particularly during fieldwork and evidence collection. These tools help auditors analyze large datasets, detect anomalies, and validate business processes efficiently. Excel remains widely used due to its accessibility, flexibility, and compatibility with most systems. When combined with CAATs techniques such as filters, pivot tables, conditional formatting, and formulas, Excel becomes a low-cost yet high-impact audit tool.
Practical Template: Transaction Testing Worksheet (Excel-based)
A useful template for applying CAATs in Excel is a Transaction Testing Worksheet. This structured Excel workbook includes:
Sheet 1: Raw Data Import – Unaltered transactional data exported from the system (e.g., general ledger, payroll, procurement).
Sheet 2: Criteria & Filters – Key audit criteria (e.g., transactions above $10,000, weekend postings, duplicate payments).
Sheet 3: Analysis – Pivot tables, formulas (e.g., IF, VLOOKUP, SUMIFS), and flags for anomalies.
Sheet 4: Findings Log – Documented exceptions, comments, supporting evidence, and references.
Practical Application: Real-Life Scenario at ACME Corp.
At ACME Corp., the internal auditor Auren was assigned to review vendor payments as part of the Procurement to Payment (P2P) process audit. The CEO, Liora, emphasized the need for transparency, and Project Manager Aven wanted a fast turnaround to inform budget planning.
Auren exported 12 months of payment data into Excel from ACME’s ERP system. He then populated the Transaction Testing Worksheet:
In Sheet 2, he applied criteria such as:
Duplicate invoice numbers
Payments on non-working days
Payments to unapproved vendors
Transactions exceeding approval thresholds
In Sheet 3, Auren used Excel’s pivot tables to summarize payments by vendor and date. He created conditional formatting rules to highlight weekend payments and used the COUNTIFS formula to spot potential duplicate entries.
The analysis revealed that three vendors were paid twice for the same invoice within a five-day span. It also showed that a junior staff member approved payments above their authority limit.
In Sheet 4, Auren documented the findings, noted responsible parties, and added screenshots of flagged entries and approval trail extracts.
In the wrap-up meeting, Auren presented the template to Aven and Liora, who appreciated the clarity and audit trail. They used the findings to tighten controls and train procurement staff on the approval matrix.
____
Upon reading the Disclaimer, a reader may click HERE to download an example of a relevant tool