Vendor ageing report
Application of vendor ageing report
In Microsoft Dynamics 365 Finance, vendor ageing report is a great tool to help manage debts and following are a few application where this report can be utilized effectively.
- Vendor ageing report helps to negotiate cash discount terms with vendors if the payments are frequently done before the due date. It can help to extend credit terms if invoices are paid late frequently.
- Vendor ageing report can be used to determine which invoices to be paid now and the invoices to be put on hold.
- Moreover, the vendor ageing report can be used to bifurcate vendor liabilities into current liability and non-current liability.
- Vendor ageing report can be utilized to plan and handle the cash flow.
Report parameters
- Ageing as at: The date entered in this field is the basis for calculating ageing period buckets.
- Balance as at: Total outstanding balance for the vendor is calculated as at the date entered here.
- Criteria: There are three options provided for the selection of date for calculating ageing of the transaction.
- Transaction date: Transaction posting date (TransDate) is considered to calculate ageing of each open transaction.
- Due date: Due date from each transaction is used for calculating ageing of each open transaction. Due date is either calculated by the system by adding user-defined Document date and Terms of payment or it is manually overridden by the user at the transaction level.
- Document date: The user-defined date (typically Vendor invoice date) is considered for the calculation of ageing of the transaction.
- Ageing period definition: Select user-defined definition for bifurcating ageing buckets. The ageing period definition is optional and you can leave it blank. If you leave it blank then you have to specify values for Interval, Day/Mth, Printing direction
- Currency: Two options are available here
- Accounting currency: The report will show all balance amount from the accounting currency field.
- Reporting currency: The report will display all balance amount in reporting currency.
- Print ageing period description: If the ageing period definition is selected and you set this field to Yes then period descriptions from the ageing period definition is printed in the report to better describe each ageing bucket.
- Interval: Value from this parameter is only used if the Ageing period definition parameter is left blank. The number entered is considered in conjunction with Day/Mth parameter. If Day/Mth parameter is set to Day then ageing buckets are created in terms of day e.g. if 30 is entered here then the buckets are created by adding 30 to Ageing as at date until 5 buckets are created. Similarly, if Day/Mth is set to Month then ageing buckets are created by adding this many months.
- Day/Mth: This is the unit for the number entered in Interval field.
- Printing direction: If Ageing period definition parameter is left blank then the value of this field determines how the ageing buckets are created. If this is set to Forward then ageing buckets are created by adding Interval value to the Ageing as at parameter value else ageing buckets are created by subtracting Interval value from the Ageing as at parameter.
- Details: If this parameter is set to No then vendor wise ageing report is generated else Vendor wise invoice wise detailed ageing report is generated.
- Include amounts in transaction currency: Setting this parameter to Yes then amount in Transaction currency is also displayed in the report.
- Negative balance: If Negative balance parameter is set to Yes, then vendor records having a negative balance (debit balance) will be part of the report. If you set it to No then vendors having a negative balance (debit balance) will be excluded from the report. We will discuss more on this parameter later.
- Exclude zero balance accounts: Setting this parameter to Yes will ignore vendor records having zero balance.
- Payment positioning: This parameter determines how unsettled vendor debit transactions are bifurcated in ageing buckets. If this is set to No then transactions are bifurcated according to its ageing else transactions are shown in the first column.
Test cases for Vendor ageing report
All the test cases to test this report are listed in the table below. I have created a new vendor account and posted the following transactions to better understand the Vendor ageing report. Test cases include thirteen vendor invoices, two unsettled vendor payments and two unsettled journal vouchers. We will understand the importance of each parameters keeping following transactions in mind.
| Voucher | Date | Due date | Invoice | Amount |
|---|---|---|---|---|
| APINV-000017 | 04-05-2020 | 01-06-2020 | INV-013 | -845.00 |
| APINV-000016 | 29-05-2020 | 23-06-2020 | INV-012 | -745.00 |
| GJN-000005002 | 23-06-2020 | 23-06-2020 | 2,525.00 | |
| APINV-000015 | 23-06-2020 | 20-07-2020 | INV-011 | -950.00 |
| APINV-000014 | 18-07-2020 | 14-08-2020 | INV-010 | -1,150.00 |
| APPM-000000034 | 06-09-2020 | 06-09-2020 | 5,050.00 | |
| APINV-000013 | 12-08-2020 | 07-09-2020 | INV-009 | -1,350.00 |
| APINV-000012 | 06-09-2020 | 01-10-2020 | INV-008 | -1,250.00 |
| APINV-000011 | 01-10-2020 | 25-10-2020 | INV-007 | -1,000.00 |
| APINV-000005 | 26-10-2020 | 19-11-2020 | INV-001 | -2,250.00 |
| GJN-000005001 | 20-11-2020 | 20-11-2020 | 5,025.00 | |
| APINV-000006 | 20-11-2020 | 15-12-2020 | INV-002 | -1,350.00 |
| APINV-000007 | 15-12-2020 | 09-01-2021 | INV-003 | -1,150.00 |
| APPM-000000033 | 09-01-2021 | 09-01-2021 | 1,825.00 | |
| APINV-000008 | 09-01-2021 | 04-02-2021 | INV-004 | -10,500.00 |
| APINV-000009 | 03-02-2021 | 03-03-2021 | INV-005 | -2,520.00 |
| APINV-000010 | 28-02-2021 | 27-03-2021 | INV-006 | -2,525.00 |
Let’s test the vendor ageing report
- Navigate to Accounts payable >> Inquiries and reports >> Vendor ageing report
- Select the required parameters on the Vendor ageing parameters form. Each parameter is explained in detail above.
- Expand Records to include fast tab
- Click the Filter button and then set Vendor account to the vendor account created for this demonstration.
- Now click Ok.
Cast 2 – Negative balance parameter
Let’s run the vendor ageing report to understand the impact of Negative balance parameter on the ageing report.
Set Vendor ageing report parameters as per below tables and then run the report.
| Parameter | Value |
| Ageing as at | 16-10-2020 |
| Balance as at | 16-10-2020 |
| Ageing period definition | Blank |
| Negative balance | No |
| Records to include | |
| Vendor account | VendAgeing |
The resulting report says there is no data available. This is because if you sum up the Balance field for all the records having transaction date less than 16-10-2020 (Balance as at) it results in a positive balance of 285.00. But because these are payable transactions it is considered as negative, typically payable transactions results into negative balance (credit transactions).
| Voucher | Date | Due date | Invoice | Amount |
|---|---|---|---|---|
| APINV-000017 | 04-05-2020 | 01-06-2020 | INV-013 | -845.00 |
| APINV-000016 | 29-05-2020 | 23-06-2020 | INV-012 | -745.00 |
| GJN-000005002 | 23-06-2020 | 23-06-2020 | 2,525.00 | |
| APINV-000015 | 23-06-2020 | 20-07-2020 | INV-011 | -950.00 |
| APINV-000014 | 18-07-2020 | 14-08-2020 | INV-010 | -1,150.00 |
| APPM-000000034 | 06-09-2020 | 06-09-2020 | 5,050.00 | |
| APINV-000013 | 12-08-2020 | 07-09-2020 | INV-009 | -1,350.00 |
| APINV-000012 | 06-09-2020 | 01-10-2020 | INV-008 | -1,250.00 |
| APINV-000011 | 01-10-2020 | 25-10-2020 | INV-007 | -1,000.00 |
| Total | 285.00 |
Following is the vendor ageing report generated when you set Negative balance parameter to Yes.
When you aim to reconcile the Trial balance with Vendor ageing report do not forget to set Negative balance parameter to Yes.
Vendor ageing report with Forward printing direction
| Parameter | Value |
|---|---|
| Ageing as at | 15-08-2020 |
| Balance as at | 15-08-2020 |
| Ageing period definition | Blank |
| Criteria | Due date |
| Negative balance | Yes |
| Details | Yes |
| Interval | 30 |
| Day/Mth | Day |
| Printing direction | Forward |
| Payment positioning | No |
Transactions considered for generating the vendor ageing report are shown in below table. Microsoft Dynamics 365 Finance filters all the open vendor records having transaction date less than or equal to date entered in Balance as at parameter.
| Voucher | Date | Due date | Invoice | Amount |
|---|---|---|---|---|
| APINV-000017 | 04-05-2020 | 01-06-2020 | INV-013 | -845.00 |
| APINV-000016 | 29-05-2020 | 23-06-2020 | INV-012 | -745.00 |
| GJN-000005002 | 23-06-2020 | 23-06-2020 | 2,525.00 | |
| APINV-000015 | 23-06-2020 | 20-07-2020 | INV-011 | -950.00 |
| APINV-000014 | 18-07-2020 | 14-08-2020 | INV-010 | -1,150.00 |
| APINV-000013 | 12-08-2020 | 07-09-2020 | INV-009 | -1,350.00 |
| Total | -2,515.00 |
- During the execution of the report, Microsoft Dynamics 365 for Finance considers all open vendor transactions having transaction date less than or equal to the Balance as at date (as per the table above) and calculates the balance by summing up the open transaction balance as at that date.
- The filtered transaction recordset is now bifurcated into different buckets based on transaction due date. The due date wise bucket bifurcation happens because we have set Criteria parameter to Due date.
- Notice how the ageing buckets are created. Since the Printing direction is Forward, ageing buckets are created by adding Interval to the end date of each bucket. Maximum six buckets are supported by Vendor ageing report.
Vendor ageing report with Backward printing direction
| Parameter | Value |
|---|---|
| Ageing as at | 31-12-2020 |
| Balance as at | 31-12-2020 |
| Ageing period definition | Blank |
| Criteria | Due date |
| Negative balance | Yes |
| Details | Yes |
| Interval | 30 |
| Day/Mth | Day |
| Printing direction | Backward |
| Payment positioning | No |
Following is the dataset extracted for generating the report.
| Voucher | Date | Due date | Invoice | Amount |
|---|---|---|---|---|
| APINV-000017 | 04-05-2020 | 01-06-2020 | INV-013 | -845.00 |
| APINV-000016 | 29-05-2020 | 23-06-2020 | INV-012 | -745.00 |
| GJN-000005002 | 23-06-2020 | 23-06-2020 | 2,525.00 | |
| APINV-000015 | 23-06-2020 | 20-07-2020 | INV-011 | -950.00 |
| APINV-000014 | 18-07-2020 | 14-08-2020 | INV-010 | -1,150.00 |
| APPM-000000034 | 06-09-2020 | 06-09-2020 | 5,050.00 | |
| APINV-000013 | 12-08-2020 | 07-09-2020 | INV-009 | -1,350.00 |
| APINV-000012 | 06-09-2020 | 01-10-2020 | INV-008 | -1,250.00 |
| APINV-000011 | 01-10-2020 | 25-10-2020 | INV-007 | -1,000.00 |
| APINV-000005 | 26-10-2020 | 19-11-2020 | INV-001 | -2,250.00 |
| GJN-000005001 | 20-11-2020 | 20-11-2020 | 5,025.00 | |
| APINV-000006 | 20-11-2020 | 15-12-2020 | INV-002 | -1,350.00 |
| APINV-000007 | 15-12-2020 | 09-01-2021 | INV-003 | -1,150.00 |
| Total | 560.00 |
The following report is generated while Payment positioning parameter is set to No. Carefully observe how the open payment transactions are positioned in the corresponding bucket according to their ageing.
Also, observe how the buckets are created for past periods because Printing direction parameter is set to Backward.
Now set the parameter Payment positioning to Yes and then generate the the report. Notice how all the open payment transactions (unsettled) are positioned in the current ageing bucket.
Vendor ageing report using Ageing period definition
When generating the report if you leave Ageing period definition parameter blank then the ageing buckets are always either Forward or Backward depending upon the Printing direction parameter value. In such cases, buckets are always evenly spaced based on Interval.
In certain reporting scenarios, it is desired to have a hybrid approach for reporting ageing of vendor balances where a few buckets are Forward and remaining are Backward. Also, sometimes it is required to unevenly distribute the ageing bucket periods.
In all such reporting cases, Ageing period definition can be used. How to create an ageing period definitions is just out of scope but will be covered in a separate article later. Here is the detailed guide to the Ageing period definitions.
All other parameter selection remains the same as we discussed earlier in this article and in addition to that, you must select the predefined ageing period definition. I have pasted one such vendor ageing report using the ageing period definition for reference.
Please share your thoughts and suggestions in the comment section.
One thought on “Vendor ageing report”