Reconciliation in Sage 100 Modules

In Sage 100, within the Accounts Receivable, Accounts Payable and Inventory modules, users sometimes find their month-end Trial Balance reports do not tie to the General Ledger account balance. The support desk receives frequent inquiries about the best way to find the difference in these numbers.

Here’s a tried and true method to narrow down the problem, given that all set-up options are correct.

Start at the General Ledger Menu

The golden rule before starting any reconciliation is to print the Daily Transaction Register from the General Ledger menu. This will pick up all unposted entries from any module. After this action, if the trial balance reports are still out of balance, you may begin the reconciliation process.

Choose a Date to Reconcile

The reconciliation can begin at any starting point but for discussion purposes we will start with the month prior to the one we intend to reconcile. First, print the trial balance for the previous month and make certain the invoice balance column still matches the general ledger ending balance.

In this example, we are trying to reconcile the 5/31/2020 trial balance. That is why we will start with the Accounts Payable trial balance and General Ledger account balance as of 4/30/2020.

First print the Accounts Payable Trial Balance by selecting Modules | Accounts Payable | Reports | Accounts Payable Trial Balance (see Image 1). Select the date desired and print.

Image 1

Note the Ending Balance

As highlighted in Image 1, the General Ledger ending balance for 4/30/2020 is also $121,214.02 (CR). In image 2, we see the balances by month, the allocated time period. To view this screen go to General Ledger | Main | Account Inquiry.

Image 2

Do the Balances Match?

Image 3 shows the Accounts Payable Trial Balance. AP trial balance as of 5/31/2020 does not match the GL ending balance as of 5/31/2020.

Image 3

Identify the Discrepancy Details

The first step is to narrow down the date when the trial balance first had a discrepancy with the General Ledger. Since the account inquiry screen will only show the balances as of the end of a given month, it is necessary to use a different approach to get the ending balances “by day” from the General Ledger versus the Trial Balance report.

To do this, we will run a General Ledger Detail Report. As highlighted in image 4, we will change the selection criteria to “by date,” and input the date we want to balance and the individual account number.

Image 4

Image 5

Image 6

Image 7

If that balances, move onto the next sequential day, and the next, and so on until the day the Trial Balance is no longer in balance with the GL account. In this example, the trial balance and the GL remain in balance until 5/31/2020. Once we narrow down the date, a closer look at the entries on that day may immediately reveal the cause.

Image 8

From this screen we can see the cause of the imbalance. The highlighted data indicates the error was made in a journal entry on 5/31/2020. Sometimes the cause is not quite this clear, though. In that case, an analysis of the individual registers is necessary to determine the difference between the detail and the posting.

This method is not only applicable to Accounts Payable, but to Accounts Receivable and Inventory as well. All three modules have Trial Balance reports that can be run by date and compared to the GL postings by date. By narrowing down the date the actual ledgers became out of balance, the reconciliation process becomes much more methodical and easier to identify.