The AP/AR aging reports can present what appear to be inaccurate numbers. To analyze the financial picture that is painted it is important to understand how transactions are included in the report and with what date.
NOTE: Information and examples in this article are presented with reference to the Aged Payables report. The Aged Receivables Report works in the same manner.
There are several topics common to most problems to Aged Reports:
- The dates designated as the current period dates control what information does and does not show up on the report.
- The aging date (on the options tab) and method (document date or due date) will determine what aging classification an invoice has and what information is used to calculate that classification.
- The Aging report IN TOTAL should match the general ledger if run identically.
- If an invoice has multiple entries to it the system uses the information from the FIRST instance to establish aging criteria.
- Unusual or negative amounts are usually the result of a transaction having an incorrect date.
Topic 1 – Effective Dates
The effective date of a transaction is the main date that controls if it will or will not be on the aging report. Unlike some other reports, the Aging report does not really look to see if a year is open or closed. The numbers given on the aging report go back to the beginning of time until the date of the ending balance on the report. The beginning balance date really does not have a significant effect on the report, only the ending balances date. With this in mind it’s important to understand that:
- Information will be on the report based on the effective date. If the document date or due date is different or out of the date range of the report it will still show up on the report. Out-of-sync documents or due dates will control what aging period the information comes into but will not control if it is on the report or not.
- Information will not be excluded from the report just because its effective date is prior to the beginning balances date.
- Information will be excluded from the report if the effective date is after the Period End date, even if the due date and document dates are in the range of the report.
To better understand this let’s look at the following.
This is a detailed AP ledger that covers all the transactions posted to the AP account. We have items posted with an effective date between 6/23 and 9/22.
We can run the Aging report from 11/1 to 11/30. Even though only the AP200903 invoice had an effective date inside this range all invoices pull it. This is because it is really only looking at the last date (11/30) and the effective dates of the invoices.
If we change the date range to run till just the end of 9/30 we get the following report. Notice that AP200903 has dropped off the list. This is because the effective date on that invoice is 11/25/09 which is after 9/30/09.
Topics 2 – Understanding the Aging Date
The effective date controls what information does and does not appear on the Aged Report. The Aging date controls what aging period the information falls into. If we look at the options tab of the aging report we will see that we can specify the Aging date and have it age either by effective date or due date.
The Aging Date is the date that you are aging as off. Another way to think about it is to say “As of 11/1/2009 this invoice is X days due”.
The periods are controlled by the value typed in on this screen. These values are all or nothing for the entire report, you cannot have some invoices aged on one schedule and others on a different schedule. If you need that you can run separate reports.
The Age by date is the date it considers the invoice “due” for purpose of aging. The system will determine what aging period this goes into by comparing the Selected Age by date to the due date.
For example, you have an invoice that has a document date (which is the same as the invoice date) of 10/1/2009 and due date of 11/1/09. You set your aging date for 12/5/09.
If you age by the Due date the invoice is 12/5/09 – 11/1/09 = 35 days. It would show 31-60 Columns.
If you age by Invoice date it would be 10/1/09 – 12/5/09 – 65 days. It would show in the 61-90-day Column.
If you set your aging date to 10/1/09 in either case it would show in the Current Column. This is because in one case it is due today (0 days old) and in the other case it is not due for 30 more days, items not yet due show up in the current column.
Understanding the use of the aging date and selecting the correct aging basis is the key to getting your information in the correct aging report.
We can see this if we use the examples from the previous topic.
If we set our aging date to 11/1/09 and run by Due Date we get these results.
If you change the Invoice Date we get different results.
Topic 3 – The Aging report IN TOTAL should equal the General Ledger if the reports are set up identically.
It is very common to attempt to tie the Aging Report to the General Ledger. This is possible, but only if you set the reports upright and look at the total.
On the General Ledger report, you need to make sure that you are running the report with the proper Accounts Balances Begin Date. This should be the first day of your first open fiscal year. When setting the current period dates you need to make sure that the “Through” date is the same as the last day of your Aging report. Also, make sure that both the Aging and GL report have the same segments in the report body and filters on the filter tab. If the reports are set up correctly the report difference on both should match.
Example Aged AP Report
Only the Report Total is sure to match. The amounts in the different aging categories may appear differently.
If these two reports DO NOT match double-check to make sure they are set up correctly. If they are then you should probably run the data integrity checks, there is a good chance you will be failing either AP17 or AR26. This is because there is a transaction that got posted into a closed fiscal year. See KB 40712 (AP Issue) or KB 40714 (AR Issue) on how to deal with this problem.
Topic 4 – If an invoice has multiple entries to it the system uses the information from the FIRST instance to establish aging criteria.
Example
Let’s look at AP200903 in a detailed AP ledger.
We entered the invoice on 9/25/09, paid it on 11/25/90, and then entered another instance of it on 1/1/2010. The document date was 1/1/2010 and the due date was 2/2/2010.
When we run the Aged AP ledger with an effective date that includes 1/1/2010 we get the outstanding invoice.
Note however that this invoice has an invoice date of 9/25/09 and a due date of 10/25/09 even though the amount of $22 is associated with an invoice that had an effective, document and due dates in 2010.
Topic 5 – Unusual or negative amounts are usually the result of a transaction having an incorrect date.
If transactions get posted with the wrong effective dates they might show up on the aging report with some unexpected consequences. It is possible for an invoice to be complexly paid and yet show up as still outstanding on the aged report.
Example of Incorrect Dates
We have an invoice that has been entered and paid. The check has been cut and cashed. When you go into Select AP Invoices to Pay, that invoice is not outstanding.
Yet when you run the Aged AP report you see that this invoice still has an outstanding balance of -$33.00.
We run a Detail AP Ledger and see that we have an APS (check) with no API. It’s not possible to have that combination.
The reason for the confusion is that the effective date of the invoice was out of the range of the report. If we change our report dates to run from 1/1/1950 to 12/31/2050 we get a more complete picture.
We see the missing invoice and that it has an effective date of 9/9/2019 which is well outside the date range we normally run the report for. Because of this the report picked up the payment but not the charge and showed an outstanding negative amount in the aging.
How to Correct this Problem?
The aging report is going to continue to show the negative amounts in aging until the report includes the effective date of all the transactions. This means this number could be on the report for years.
The solution to this problem is that you have to reverse the transaction that is outside the date range and then re-enter it with the proper date.
General Tip of Troubleshooting
If you are running an aged report and you see something you don’t understand, it is a good idea to run a Detail AP ledger. This gives you more information including the dates that may explain the behavior.
The AP ledger should be run from 1/1/1950 to 12/31/2050. While this date range may seem excessive it will catch transactions that have been entered by mistake into the far future or distant past which is a very common source of problems.
The setup of the report on the content lab should look something like this.
On the filter tab, you should filter for the Vendor Id and Original Invoice Number. Make sure are filtering on the Original Invoice Number and NOT the document number. Filtering on just the document number may not pull in all transactions linked to the invoice.
With this information, you should get a better picture of the activity for this invoice and be able to determine why it is not displaying as expected.
Register for our MIP Fund Accounting newsletter today!
If you need assistance with your MIP Fund Accounting software or have additional questions, please contact our MIP support team at 260.423.2414 or at 800.232.8913.