Two commonly used reports to help you track and analyze your outstanding customer balances in Sage 100 are the AR Trial Balance and the AR Aging Report. There are often questions from users when their totals don’t match. Let’s clarify what each report shows, how they’re calculated, and why their balances may differ.
AR Trial Balance Report: What Is It?
The AR Trial Balance Report in Sage 100 provides a snapshot of each customer’s full receivable balance at a specific point in time. This report summarizes all posted transactions, such as invoices, payments, credits, and adjustments for each customer, giving a total balance due as reflected in the General Ledger.
key features
- Displays the total AR balance for each customer.
- Reflects posted transactions based on GL posting date.
- Represents the true balance as of the report date.
why use it?
This report is primarily used for financial reconciliation and to verify that the subsidiary ledger (Accounts Receivable module) matches the control account in the General Ledger.
AR Aging Report: What Is It?
The AR Aging Report in Sage 100 breaks down each customer’s outstanding invoices by age, typically into categories such as Current, 30 Days, 60 Days, 90 Days, and Over 90 Days past due. This report helps you track delinquent accounts and prioritize collections.
key features
- Categorizes open invoices based on their age (days outstanding).
- Focuses on open (unpaid) invoices as of a chosen cut-off date.
- Uses the Invoice Date and Due Date for reporting cut off.
why use it?
The AR Aging Report is essential for managing credit risk, scheduling collection efforts, and monitoring overdue receivables.
Why Don't the AR Trial Balance and AR Aging Reports Always Match?
It’s common for users to notice discrepancies between the total balances on these two reports. Several factors contribute to this:
- Timing Differences
- The Trial Balance includes all posting activity up to the report date. The Aging Report may exclude transactions not applied or posted to future dates (for example, recent payments or credits may appear on the Trial Balance but not yet reflected in the Aging buckets).
- Date Differences
- The Trial Balance Report utilizes the Transaction Date (GL Posting Date) for reporting while the Aging Report utilizes the Invoice Date. If the 2 dates differ, then the reports will differ, even if run as of the same date.
- Unapplied or Future-Dated Transactions
- If payments or adjustments are entered but not applied to open invoices or applied with a future date, the Trial Balance will show these changes, but the Aging Report may exclude them.
- Reporting Cut-Off Settings
- Differences in report parameters, such as cut-off dates or inclusion/exclusion of certain transactions, can create variances.
best practices for reconciling these reports
- Confirm Cut-Off Dates: Make sure both reports use the same reporting date for comparability.
- Post All Transactions: Before running reports, ensure all payments, adjustments, credits, and invoices are fully posted and applied.
- Review Report Options: Understand which filters and settings affect each report.
- Identify Discrepancies: Identify with differences in transaction date and invoice date, unapplied payments or adjustments, and future dates.
- Reconciling to GL: Use the Trial Balance Report to reconcile the AR subledger to the General Ledger. Since the Trial Balance uses the transaction/posting date, this is the report that should tie out to the GL.
The AR Trial Balance Report and AR Aging Report in Sage 100 each serve valuable but distinct purposes. Understanding how they are calculated, and why their totals may not always tie, helps users leverage these tools effectively for both financial reporting and collection management. If you encounter persistent discrepancies, consult with your accounting advisor or RKL support for guidance on best reconciliation practices.
Need help configuring or interpreting AR reports in Sage 100? Contact our team for expert support, report customization, and training.
Want More Sage 100 Tips & Tricks?
Make sure to subscribe to our blog to stay up-to-date on the latest insights and trends. We're dedicated to providing valuable resources for businesses looking to optimize their financial management with Sage 100.
Check out these blogs to help get you started!
How to Reverse a Cash Receipt
How to Reverse an Invoice Posted to the Wrong Month
Understanding the Purchases Clearing Report
How to Setup ACH Processing in Accounts Payable
