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.
Topics Covered
- Working with audit data: importing and cleaning data for testing
- Journal entry testing: gaps, duplicates, and unusual entries
- Matching and analytics: the three-way match, Benford's Law, and stratification
- Documentation and reuse: reviewable workpapers and refreshable templates
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
Level
Basic
Instructional Method
Group: Internet-based
NASBA Field of Study
Computer Software & Applications (2 hours)
Program Prerequisites
None. A basic working knowledge of Excel is recommended.
Advance Preparation
None
Instructor
Stephen M. Yoss
Stephen M. Yoss, Jr., CPA, MS, is a certified public accountant, the senior technology strategist and partner of
Devmatics, a continuing education instructor for financial professionals, and a licensed pyrotechnician. While his interests and skills are varied, they all share a common thread—his love for and skill in finding technology-based solutions.
Whether it’s teaching in the classroom, consulting clients in a boardroom, or shooting a fireworks display, Steve brings passion, hard work, value—and above all else—technological expertise to each of his clients. With an open, honest approach, he creates a unique strategy for each client specifically designed to benefit their needs and streamline their operations in order to create efficiency and maximize their financial potential. For more information about Steve, please
click here to visit his personal website.