Trial Balance not matching total on Aged Debtor/Creditor Report

This article will help you if you have found a difference in the figures when comparing the Trial Balance Report to Aged Debtor/Creditor report.  This can be caused by one of several different reasons, below is a guide you help you work through the steps on how to find the problem.

Please note, you will need to run the Trial Balance report with a tick in the box for “Include Open Balances” prior to following the steps below. In some cases, by ticking this box you may find that the reports will now match.

Step One – Incorrect nominal codes used on invoices/purchases

If the wrong nominal code has been used on a line item of an invoice or purchase then this can be a course of the problem.  The types of nominal codes that should not be used are:

• Bank Account
• VAT Control Account
• Debtor/Creditor Control Account

To check if any of your invoices have been entered with the wrong nominal code, you can follow these steps to find it:

1. Go to Reports> Business> Audit Trail.
2. Set the date range to include all historical data.
3. Ensure that you have only ticked in the box for “Include Invoices” (or “Include Purchases” if you are looking at the Aged Creditor report) and click “Run Report”.
4. At this point you have two options:
   1. Click on the control and F key on your keyboard to bring up the search browser option.  If you are searching in the browser, you will need to type in the nominal code names/numbers of any bank accounts that you may have, the VAT Control Account, or the Debtor/Creditor control accounts code.  In the below example, I have searched for “Debtor” and found the highlighted invoice.  This invoice will need to be edited and the correct sales or purchase code will need to use. 
   2. Click on “Download CSV” in the top right corner and open the file in Excel.  When opened in Excel, highlight the headers row and add a filter.  From here, you can then filter down on the nominal codes that shouldn’t be used to see if any invoices have been allowed to this code.  You will then need to go back to the software and find these invoices, open them and change the nominal code to the correct sales or purchase code.

Once you have done the above, you can then re-run the Trial Balance and Aged Debtor/Creditor reports to see if these now match.

If you have followed the above steps but still seeing a difference you will need to continue to Step 2 below.

Step Two – Future date entered on a payment

In some cases, you may have accidentally entered a future date on a payment for an invoice/purchase.  This would mean that the Trial Balance figure would be correct, as the invoice has been paid, but the Aged Debtor/Creditor report would be wrong due to that invoice not being paid as of “Today”.

To find if you have any future dates on a payment you will need to follow the below steps:

1. Run the Aged Debtor/Creditor report for today’s date.  Please Note: Set the “Customise” option to not include a detailed breakdown.  You will just want to see the total per customer.
2. Export the report to Excel by going to the top corner and click Export> Export to CSV.
3. Make a note of the total at the bottom of the screen.
4. Run the Aged Debtor/Creditor report again, but for a date way into the future.  As the wrong year can sometimes be entered on a payment, we would suggest to put in a date of over 12 months into the future.
5. Check the total at the bottom of the screen, if the figure is the same as today’s total then it is unlikely that you have any payments with a future payment date.  If the total is different, then more likely you do have a future date entered.
6. You will then need to compare the report from “today” to the future dated report by looking at the totals for each customer to ensure that they match.
7. Once you have found the customer with the discrepancy, you can then set the report “Customise” so that you can compare each individual invoice and find which one is causing the problem.
8. Open the invoice that you believe to be affected, and you will find the payment with a future date.  You will then need to change the date on this payment to correct the report.

Step Three – More detailed search

If you have followed the steps above and you are still not finding the problem then you will need to find the specific date from when the two reports become different.  Please follow the steps below on how to do this:

1. Open the Trial Balance and Aged Debtor report on two tabs in your browser.
2. Select a historic date, per example six months into the past, and set this date range on both reports.
3. If the reports still don’t match, select an older date and continue to work back until it balances.
4. When you come to a point where the figures do match, you may need to move the date forward slightly.  It may take several attempts moving forward and backward to find the exact date where it becomes unbalanced.
5. Once you have found the date where it becomes different, take a calculator and work out the difference between the two figures.
6. In another tab, go to Reports> Business> Audit Trial and put the same date in the “To” and “From” date range and tick the box to include invoices or purchases and run the report.
7. This will then filter all transactions for that take, and with the difference between the figures, you should be able to find the offending transaction and amend it as necessary.
8. Once you have fixed the transaction, re-run the two reports at the current date to see if they now match.  If they do not match, you will need to repeat steps 1 to 7 until all entries have been corrected.