AP vs GL Can Match… and Still Be Wrong | The Control Most Teams Miss (Power Query) скачать в хорошем качестве

AP vs GL Can Match… and Still Be Wrong | The Control Most Teams Miss (Power Query)

AP vs GL can match perfectly — and still be wrong. Most reconciliations focus on totals. But duplicate invoices, missing postings, and timing issues can cancel each other out and pass silently. Video Chapters: 00:00 Intro: Why reconciled totals can be wrong 01:21 Loading AP and GL reports into Power Query 02:22 Data Cleaning: Removing hidden characters (Trim & Clean) 03:06 Validating Data Types for Finance Reports 03:40 Grouping AP Data to identify Invoice-level totals 05:26 Grouping GL Data for sub-ledger comparison 06:24 Merging AP vs GL using Left Outer Join 07:09 Logic 1: Identifying Missing GL Postings (Completeness) 07:31 Logic 2: Detecting Duplicate Invoices 08:00 Logic 3: Calculating Timing Differences (Posting Dates) 09:44 Logic 4: Calculating Amount Variances 10:23 Building the "Control Status" Logic Engine 11:23 Final Audit Report: Filtering Exceptions & Actions 11:48 Summary: Moving from Manual to Automated Controls In this video, I show a different approach. Instead of matching balances, we validate invoice population integrity using Power Query (UI-only). The model checks every invoice and classifies it as: • OK – correctly recorded • REVIEW – timing or amount issue • BLOCK – duplicate invoice risk • ACTION – missing GL posting This is not just an Excel exercise. It’s how finance controls should be designed — focusing on system behavior, not manual checking. ________________________________________ What you’ll learn • Why AP vs GL totals can be misleading • How to detect duplicates and missing postings • How to test amount accuracy and posting timing • How to turn data into clear control decisions ________________________________________ This video builds the control logic (the brain). In the next video, we’ll move toward automation and exception tracking. If you work in Accounting, Controllership, FP&A, or Internal Audit, this series is for you