Retrospective Aged Creditors

The Process

Aged Creditors Reporting from Advanced Financials

There is a report available that can be ran at the end of the month (or whatever frequency is required) to produce a picture of the creditors of a company at that month end. This report is a useful guide as to how much exposure the company has to its’ creditors and how much is owed by the organisation over the next periods. 

These reports are at a point in time and reflect the actual position of the creditors at the time the process is ran. However, it is not always possible to run these reports at a period end and this in turn means that users may have to hold off entering new transactions until the prior month is closed and the report is run. 

Each AP transaction created will be assigned (or calculated using the supplier’s payment terms) a due date of when the transaction is to be paid. The process defines the age of each AP transaction using parameter settings to identify the period ranges for ageing and compares a ‘Base Date’ against the payment due date of the transaction to determine the transaction age. For example, if the base date was ‘today’ and the payment due date for a transaction is 30 days after ‘today’, then using ‘days’ as the parameter settings (with ranges defined every 20 days), then the transaction age falls within the 20 - 40 days ‘period’. Up to 5 periods are defined for this ageing with the first period also including transactions that are overdue for payment. 

For example, a supplier has the following transactions defined: 

The process is submitted with the following parameters:

The ‘base date’ is 12-APR-2021 and the 5 ‘period length’ columns are defined as ’days’ due in the future and from this, the payment due date range to determine which period the transaction will fall into is calculated as: 

Using the above calculation, the ‘period’ each transaction will be accumulated into is: 

To run the report access the following menus:

Accounts Payable>AP Reports>Supplier Age Analysis

The following needs require an entry.

• Company: Company code
• Currency: Leave blank and all currencies will be reported
• Element: If you ICA, you can leave blank to report on all elements
• Period: Select Daily, Weekly or Monthly (this is how the data will be extracted)
• Base Date: This date acts as a base date for the extraction of transactions.
• Column: If the period option is M, the base date is 23 June 2023 and the column value is 1, all overdue invoices within a month of the Base Date will be extracted and reported.

The ‘base date’ is 12-APR-2021 and the 5 ‘period length’ columns are defined as ’days’ due in the future and from this, the payment due date range to determine which period the transaction will fall into is calculated as: 

Once complete click on the SUBMIT button twice.

The reports can be accessed from the spool.

Menu access: Systems Admin>Spool

Three reports will be produced. Enter GMB in the process field and click on OK.

Click the Paperclip icon or the use View Document button to view each report.

A summary report for each currency is produced - Age Analysis Final Summary

Age Analysis by Supplier - This will breakdown each supplier and currency

Age Analysis by Account Group - When set, an additional report is produced that shows the expense postings on each line (or split analysis line) accumulated by the group assigned to the nominal code of the expense account. If a transaction was part paid, then the amount outstanding shown against each group will be pro-rated based upon the value of each line on the invoice.

Advanced Reporting Cash Outflow Forecast / by Element

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

Access the Repository from the View pull down menu.

Followed by Accounts Payable>Sample Reports>Ad Hoc Views and Reports

A list of sample reports will be retrieved in the right hand panel. Select the hyperlink to open each report.

AP2.1 Cash Outflow Forecast, AP2.1a Cash Outflow Forecast by Element. This Displays cash outflow broken into separate buckets, including Overdue values, 7 & 14 days, future due and grand total due and/or broken down by Element

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

Retrospective Aged Creditors Reporting 

There is a report available that will calculate the age of all AP 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 ageing and calculate the age and amount outstanding of each transaction as at that date (as it may have been paid subsequently to the retrospective date). 

A provision of retrospective aged creditors reporting would allow users to continue processing and still run an accurate report of the creditors’ position as at a particular date. In addition, if a there is a need to go back and re-run the report there the ability to re-create this data to avoid complications and in particular any necessary auditing requirements. 

Retrospective Aged Creditors Reporting Parameters 

The following parameters will be 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 amount outstanding 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. 

Base Date Indicator - This field identifies if the Base Date (i.e. the ‘current date’ for ageing purposes), is to be different to the Retrospective Date. When set to ‘Retrospective’, the ageing calculations will be based upon the Retrospective Date, when set to ‘Current’, then the ageing is based upon a different date to the Retrospective Date. This option will generally be used if the ageing of the transactions is required for a different period to the current period (e.g. age the transactions as per the start of the next period). 

Base Date - This date is used to determine the date to be used as the ‘current date’ for ageing purposes. Note, this can be different to the Retrospective Date that is used to determine which transactions are to be extracted. 

Base Days - This field identifies the number of days prior to the current date that is to be calculated as the Base Date (e.g. if 7 was entered, then the base date is current date – 7 days). 

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. 

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: 

Period Option - Identifies how the classification of the periods are defined for ageing. This will be one of ‘Daily’, ‘Weekly’ or ‘Monthly’ with the length of each period determined by the entry in the Period values 

Period Length 1 – 4 - These 4 period values identify the length of each period to determine the age of each transaction. The values are dependent upon the Period Option in use (e.g. for a ‘Weekly’ Period Option with a Period Value #1 of ‘3’ implies a length of up to 3 weeks from the ‘Base Date’). 

Report Sequence 1 - If using ICA, the report can be sequenced by an ICA element code and generate sub-totalling per ICA element if required. 

 A separate overall Company total will always be calculated and displayed. 

Report Details - This setting governs the level of detail that is required on the retrospective aged creditors report. The level of detail may be ‘None’ (i.e. no supplier or transaction details will be shown), ‘Supplier’ (i.e. summary totals per Supplier will be shown) or ‘Supplier/Transaction’ (i.e. Individual transaction details will be shown with a summary sub-total per Supplier displayed). 

Include Zero Balances - This setting only applies when the Report Details are set to ‘Supplier’ or ‘Supplier/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 suppliers that have no transactions extracted as at the retrospective date (i.e. there is nothing owed at that date), will be suppressed unless this flag has been set.  

Running a Retrospective Aged Creditors Report

Access the following menu options:

Accounts Payable>AP Reports>Retrospective Aged Creditors Reporting

As an example if the following parameters have been entered:

• Company: xx
• Retrospective Date: 31 December 2022
• Date Indicator: Retrospective
• Date Selection: Create Date
• Period Option: Daily
• Period Length 20/20/20/20
• Report Details: Supplier

The above parameters, will extract all transactions for the company and calculate the amount outstanding/age of these transactions as at 31-DEC-2020 and produce a report with the transaction details summarised per Supplier. 

The file can be accessed from File Management area.

Retrospective Aged Creditors Reporting Example 

The following is an example of the retrospective processing. The assumption is that the system is using ageing of 30 Day periods. 

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

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

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

If the GUA process was ran with a Retrospective and Base of 01-MAR-2021 and the 5 ‘period length’ columns are defined as 20 days. To determine the date range for the ageing periods, the following calculations are made: 

As the current date is 01-MAR-2021, the amount outstanding is not recalculated but the ageing period for the non-zero transactions is allocated as follows: 

The retrospective creditors report (as at 01-MAR-2021) is therefore: 

If this report was re-run with a retrospective and base date of 10-JAN-2021 with the 5 ‘period length’ columns are defined as 20 days. The date range for the ageing periods, are now: 

As at the date 10-JAN-2021, the amount outstanding is recalculated for the transactions (with some transactions ignored as they were created subsequent to the retrospective date) and the ageing period assigned as follows: 

The retrospective creditors report (as at 10-JAN-2021) is therefore: