This article discusses some common issues that might cause discrepancies between the CM and AR sub-ledgers and the corresponding general ledger control accounts in Sage 300 Construction and Real Estate (CRE). It assumes that all transactions are posted and there are no transactions that remain in the new transaction files, which will also cause discrepancies between the sub-ledger and GL. After posting the new file in all modules, review the journal report, correct any rejected transactions, and repeat these steps until there are no transactions remaining in the new file.
When the bank reconciliation is prepared monthly, void checks must be cleared properly or they could cause the bank reconciliation to not balance to the general ledger cash account.
All checks that have been voided with a void date prior to the bank reconciliation date must be cleared, but those that have been voided with a date in a future period must remain un-reconciled during the current month. These transactions will be cleared during the future period reconciliation.
During the reconciliation process, select conditions and add the following conditions:
Void Check EQ X
Void Date LE xx/xx/xx (reconciliation date)
Clear all the void checks that come up.
When the reconciliation report is printed, any checks that are voided with a future period will remain un-reconciled; see report sample below. The Adjusted Bank Balance and the Register Balance should be equal to each other and will be different from the Register Balance Adjusted for Future Period Voids, which should tie to the GL cash account.
If there are no checks voided in a future period, the Register Balance Adjusted for Future Period Voids will not appear and the GL cash account should tie to the Adjusted Bank Balance and Register Balance.
If future period voids are inadvertently cleared in an earlier period, it will cause the Adjusted Bank Balance and the Register Balance to be different. These transactions should be uncleared for the earlier period reconciliation and that will correct the problem.
The totals on the AR aging reports should tie to the totals in the GL accounts that are mapped as accounts receivable, retainage receivable, and customer cash receipts in the AR Settings. In the example below, accounts 1201 plus 2006 should equal the net accounts receivable on the AR aging. Account 1204 should tie to the retainage column on the AR aging report.
If the sub-ledger balances do not tie to the general ledger control accounts, it is most commonly a result of transactions that were posted to the incorrect AR control account. The AR>Reports>Reconciliation>Batch Reconciliation to GL report can help to find the mis-posted transactions. Enter the beginning and ending dates of the period being reconciled. Keep in mind that this report shows the detailed transactions, therefore, the reconciliation should be performed each month to minimize the size of the report. Additionally, these steps assume that the prior month’s reconciliation is correct and has no discrepancy.
Add conditions to filter the transactions to look at only invoice transactions as follows:
Transaction type NE Cash receipt
Transaction type NE Cash receipt adjustment
This will result in the report showing only invoice transactions, which should all have 1201 as the AR debit account and 1204 as the retainage debit account based on the example below.
If the detail is too large to review visually, then you can use conditions to filter the report further to look for transactions where the debit account is not 1201 or the retainage account is not 1204. Run the report with each of these conditions separately; see examples below.
Each version of the report with these conditions will result in invoice transactions that are reflected on the AR aging but were not posted to the proper general ledger account and are likely to be the cause of the reconciliation discrepancy.
To check the report for the proper posting of cash receipt transactions, condition the transaction type to not equal the invoice transactions as follows.
This will result in the report only showing cash receipt transactions, which should have a cash account as the debit and the AR account, 1201 in our example, as the credit account. Add additional conditions, as noted above, to filter the report for exceptions to this rule.
Things to Note
Please keep in mind that there can be many reasons why the sub-ledgers and the general ledger do not tie, but these are the most common and easiest to find using existing tools and reports. In some cases, custom reports may need to be created to compare transactions between the sub-ledger and the GL. Also, It is important to remember to always check the GL new file on a regular basis, but especially before reconciliation, and be certain that all transactions are posted.
Additionally, a good rule of thumb to remember with all modules is that any GL control account, such as CM cash accounts, AR/RR accounts, AP/RP accounts, and JC WIP or cost accounts, should not be used when entering transactions. These accounts are generally setup to automatically post from the corresponding module so entering them is not necessary (your setup may be different, but this is rare). If you find yourself entering a GL control account as part of any transaction, including GL or JC journal entries, think twice and double check the account that should be used for your particular transaction.
Need Help with Your General Ledger?
Click below to get in touch. One of our Sage Construction software consultants will follow up shortly to offer assistance.