Retrospective Aged Debtors

The Process

Aged Debt Reporting from Advanced Financials

Each AR transaction created will be assigned (or calculated using the customer’s payment terms) a due date of when the transaction is to be paid. Credit (or Cash) transactions will also have a due date although for these transactions they will generally be configured to be the same date as the transaction date.

If a transaction is unpaid, the age of that transaction (i.e. how old is the debt) is determined using Company Control settings e.g. if a transaction has passed it’s due date by 60 days, it might be considered to be 2 periods overdue. These ranges are configured in AR Company Controls which provides the basis for the aged debt calculations and in particular, transactions can then be ‘grouped’ into 1 period overdue, 2 periods overdue etc. (or even grouped into periods that are not yet due i.e. future due). This will have been configured during implementation. Example below of the AR Company Controls.

These aged debt periods are held against each customer and are recalculated daily, so they store the current representation of the debt that is owed to the organisation. As new transactions are entered or existing transactions paid/updated in data entry, collection processing etc., these aged debt periods are automatically updated from the changes to the transactions and reflect the exact picture of the current debt.

The customer aged debt can be viewed online by accessing the following menus:

Accounts Receivable>AR Enquiries>Transaction Enquiries>Transactions by Customer

Enter the Ledger code and Account and click on OK. Then click the Aged Debt button.

The customer current debt is shown.

Selecting the Overdue/Future Balances tab will show the period breakdown.

Advanced Reporting Aged Debtors

Aged debt reports can also be accessed from the Advanced Reporting tool. Access the Reporting tile from MyWorkplace.

Access the Repository from the View drop down menu.

Followed by Accounts Receivable > Sample Reports > Ad Hoc views and Reports

A list of sample reports will be retrieved in the right hand panel.

AR1.1 Aged Debt Balance by Ledger. Lists all Customer Accounts with an outstanding balance, grouped by Ledger, and displaying values relative to their ageing bands.

Select the hyper link to open each report.

You can take a copy, amend the fields and filters, and create a similar report that meets your requirements.

There are other reports available, just click each hyperlink to view the output of each.

Retrospective Aged Debt Reporting

The retrospective aged creditors will calculate the aged debt of all AR transactions for a specific company as at a specific date. If this date is prior to today’s date, then the system will perform retrospective aged debt and calculate the age and outstanding balance of each transaction as at that date (as it may have been paid subsequently to the retrospective date).

A provision of retrospective aged debt reporting would allow you to continue processing in the new month and still run an accurate report of the debtors’ position as at a particular date. In addition, if a there is a need to go back and re-run the report (e.g. if the task was missed or report had been lost) then there is the ability to re-create this data to avoid complications and in particular any necessary auditing requirements.

Parameters limit the selection of customers and transactions and to govern the sorting and totalling of the report production.

Please not the report is not driven by year/period of postings, they are driven by AR transaction dates. So in effect, they would show you on the date of the retrospective date you enter on the parameter screen, the position of how much that customer owes you (as if you went into AR and AP enquiries and looked at their respective accounts).

It does not take into account the period of posting so it can’t be used to reconcile against GL year/periods.

 Retrospective Aged Debt Reporting Parameters

The following parameters are available to identify the processing date for the transactions:

Retrospective Date - If this date is prior to the current date, then all transactions created (or dated – see parameter below) on this date or prior will be extracted and the outstanding balance and age of the transaction re-calculated as at that date. If this date is the same or greater than the current date, then all outstanding transactions will be extracted (except when using ‘tran date’ parameter – see parameter below) and the age of the transaction re-calculated as at that date.

Retrospective Days - This field identifies the number of days prior to the current date that is to be calculated as the Retrospective Date (e.g. if 7 was entered, then the retrospective date is current date – 7 days). If this parameter is entered, then the transaction processing will be as per the Retrospective Date parameter.

The following parameters are available to identify the selection of customers:

Ledger - If entered, then only transactions for customers for this specific ledger code will be extracted. If not entered, then all ledgers will be selected.

Department - If entered, then only transactions for customers which are defined for this department will be extracted. If not entered, then all departments will be selected.

Area - If entered, then only transactions for customers which are defined for this area will be extracted. If not entered, then all areas will be selected.

The following parameters are available to identify the selection of transactions:

Currency - If entered, then only transactions for this specific currency code will be extracted. If not entered, then all transactions regardless of currency will be selected (note, for foreign currency transactions, only the base equivalent value will be considered).

Element - If entered (for an ICA Company), then only transactions for the specific ICA element will be extracted. If not entered, then all ICA elements will be selected.

Location - If entered (and AR Structures are in use), then only transactions for the specific AR location will be extracted. If not entered, then all AR locations will be selected.

Date Selection - Can be set to ‘Create Date’ or ‘Tran Date’. This parameter will be used to identify when a transaction can be included in the retrospective process. When set to ‘Create Date’, then transactions that are created after the retrospective date (regardless of their transaction date) will not be considered. When set to ‘Tran Date’, then transactions that are dated after the retrospective date (regardless of when they were created in the system) will not be considered. This option will generally be used when transactions were entered ‘late’ into the system and were required to be included in the previous reporting period.

The following parameters are available to govern the production of the report:

Report Sequence 1-5 - Up to 5 report sequences can be specified. A reporting sequence will govern the order that the aged debt balances are shown and the sub-totalling required. The reporting sequence can be one of: Ledger Department Area Element Location A separate overall Company total will always be calculated and displayed regardless of the reporting sequences chosen.

Report Details - This setting governs the level of detail that is required on the retrospective aged debt report. This option is in addition to the sub-totalling reporting sequences that can also be requested. The level of detail may be ‘None’ (i.e. no customer or transaction details will be shown), ‘Customer’ (i.e. summary totals per Customer will be shown) or ‘Customer/Transaction’ (i.e. Individual transaction details will be shown with a summary sub-total per Customer displayed).

Include Zero Balances - This setting only applies when the Report Details are set to ‘Customer’ or ‘Customer/Transaction’. When set, then transactions that were extracted as part of the transaction selection parameters but were fully paid as at the retrospective date will be extracted and shown on the report. When not set, then fully paid transactions will be excluded from the report. Similarly, for customers that have no transactions extracted as at the retrospective date (i.e. their total debt is zero), will be suppressed unless this flag has been set.

Running a Retrospective Aged Debt Report

Access the following menu options:

Accounts Receivable>AR Reports>Retrospective Aged Debt Reporting

As an example if the following parameters have been entered.

• Company: xx
• Retrospective Date: 31 Dec 2020
• Date Selection: Create date
• Report Sequence 1: Element
• Report Sequence 2: Ledger
• Report Details: Customer

The above parameters, will extract all transactions for the company and calculate the outstanding balance/age of these transactions as at 31-DEC-2020 and produce a detailed report showing Element then Ledger code order, totalling per Ledger within Element.

The file can be accessed from File Management area.

If no details were required except for element totals, the following will be shown:

• Company: xx
• Retrospective Date: 31 Dec 2020
• Date Selection: Create date
• Report Sequence 1: Element

Example:

The following is an example of the retrospective processing. The assumption is that the system is using ageing of 30 Day periods (e.g. 1 to 30 days passed the due date is define as ‘1 Period Overdue’, 31-60 Days passed the due date is defined as ‘2 Periods Overdue’ etc., although other options for ageing are available, e.g. calendar, monthly).

Transactions currently exist with the following data (as at 25-MAR-2021)

A cash payment CASH-01 was made on 14-JAN-2021 to pay INV-01 and INV-02

A cash payment CASH-02 was made on 10-FEB-2021 to part pay INV-03

The ‘current’ aged debt (as at 25-MAR-2021) is:

The retrospective aged debt (as at 11-JAN-2021) is:

The retrospective aged debt (as at 09-FEB-2021) is: