Reconciling accounts payable & reciveable to the trial balance

Next steps

Starting with the first month where the aging report and the Trial Balance do not agree, run the AR or AP Ledger report for the period and base this on the GL posting date.

Run the General Ledger report for the same date/time period and filter for the relevant control account.

Reconcile both reports side by side, it might be easier to shorten the period you are reconciling to a week, or a day.

Once you have identified the specific transactions that are causing the imbalance should you require further assistance in determining the cause, or how to resolve please contact our support team.

Overview of Datel supplied reports

Datel have created some custom reports that can assist with identifying transactions that could cause differences between AP or AR and the Trial Balance.

Please ask Support to install these for you. These reports are under continual improvement so we would welcome any feedback.

AP/AR Adjustments posted to Control Account

These reports will detail any adjustments that have been posted to the Control account, they should be posted to an account that you would sweep through, the adjustment being offset by a subsequent receipt, payment, or credit note transaction.

An Additional prompt for the control account must be selected to isolate the relevant postings. This can be set as a filter to default the control account.

Change the Time Period to Inception to Current Month as at today’s date to capture any transactions up to today or change to the appropriate Time Period from the last point of balancing.

AP/AR Advances not posted to Control

These reports will display any Advances that have not been posted to the Control account. Because an exclusion is required for any GL account other than the control account a filter must be added to the report prior to running.

Change the Time Period to Inception to Current Month as at today’s date to capture any transactions up to today or change to the appropriate Time Period from the last point of balancing.

When Advances are applied a journal is posted between the account chosen on the Advance and the control account.

Please check Advances that do not have a Total Applied value or have a Total Due value first.

There will be a difference to the GL until this payment date is past, for example in the list below the 3rd advance dated 27/07/2023 would have created a difference until 16/04/2024.

This can either be noted, or corrected via a reversing journal or by voiding and starting again.

Please note - These reports will need to be duplicated and edited to change the filter for the Control Account if not 1200 for Debtors Control or 2100 for Creditors Control.

AP/AR Allocation Check

These reports will check for any payments or receipts that have been allocated before the invoice transaction date.

Change the Time Period to Inception to Current Month as at today’s date but enter the Invoice and Payment Date ranges to find the effected transactions.

To check for invoices with a GL Posting Date August 2023 but have been paid via payments dated up to the end of July 2023 enter the dates as follows:

In addition, you can check for Invoice dates in the same way.

To avoid this consider switching on the setting in AP and AR config (this must be done in each entity, not just at Top Level). Please note this only relates to the invoice date not the GL Posting Date.

Manual Journals posted to Control Account

This report will display any manual transactions that have been posted to the selected Control Account, which is an additional prompt that must be selected.

There is no need to select a particular journal type as system generated journals will be excluded (for example).

Change the Time Period to Inception to Current Month as at today’s date to capture any transactions up to today or change to the appropriate Time Period from the last point of balancing.

AP/AR Control Account Posting Check

This report will display any AR or AP Invoices or Credit Notes that have been posted to the selected Control Account, which is an additional prompt that must be selected.

Change the Time Period to Inception to Current Month as at today’s date to capture any transactions up to today or change to the appropriate Time Period from the last point of balancing.