Excel and Power Query for Better and Faster Audit Testing

📋 Overview

Most audit testing still starts the same way: someone exports data from the client's accounting system, spends an afternoon cleaning it up in Excel, and repeats the whole process next quarter or next year. Power Query ends that cycle. It records every cleanup step so the same preparation runs again with a single refresh, no matter where the data came from. This session shows how to use Power Query to prepare audit data, run common audit tests, and document the work so it holds up in review. This is a practical, show-and-do session built around the tests auditors actually perform. You'll see how to turn a messy client export into a clean, testable dataset, test a journal entry population for gaps, duplicates, and unusual entries, run a three-way match across purchase orders, receipts, and invoices, and apply Benford's Law and stratification to focus your testing. Just as important, you'll learn how to document each step so a reviewer can follow and re-perform the work. You'll leave with a repeatable approach you can apply to your next engagement.

👥 Who Should Attend

Audit practitioners and data analysts who want to move beyond manual sampling and build repeatable, automated testing workflows within their existing spreadsheet software.

🎯 Learning Objectives

• Import and clean audit data into a refreshable Power Query table
• Apply Power Query steps to test a journal entry population for gaps, duplicates, and unusual entries
• Perform a three-way match by merging purchase order, receiving, and invoice data
• Apply Benford's Law and stratification to identify transactions for further testing
• Document query steps so a reviewer can follow and re-perform the test

🚀 What You'll Walk Away With

• You'll stop manually scrubbing client data and start using automated, refreshable workflows.
• You'll catch gaps and duplicate journal entries using built-in logic instead of manual inspection.
• You'll perform three-way matches between orders, receipts, and invoices without matching them by hand.
• You'll identify high-risk transactions for testing using Benford's Law and data stratification.
• You'll hand off workpapers that reviewers can actually follow and re-perform without your help.

📚 Major Topics

1. Working with audit data: importing and cleaning data for testing
2. Journal entry testing: gaps, duplicates, and unusual entries
3. Matching and analytics: the three-way match, Benford's Law, and stratification
4. Documentation and reuse: reviewable workpapers and refreshable templates

🏷️ Topics

❓ Questions

✍️ A Note from the Author

I spent years watching colleagues lose entire afternoons to the same repetitive data cleaning tasks every single audit cycle. It's a frustrating waste of professional talent. I designed this course to show you how to build a system that remembers your work so you never have to fix the same broken export twice.