How to Manage Accounts Payable Aging Reports?

What is an aging report?

An accounts payable aging report is a report that lists all unpaid vendor transactions and uncleared debit memos by date ranges. It is the primary tool used by payable department or manager to determine which vendor invoices are due or overdue for payment.

Creating an aging report in Excel:

In Excel, you can create a date-wise accounts payable summary using simple formulas. By following the steps below, you can view the status of your invoices as to paid, unpaid or delayed payment.

Accounts payable aging is a periodic report that classifies an entity’s account payables as per the duration of time an invoice has been pending. It is an important analytical as well as management tool that can be helpful in determining the overall financial health of an entity, its liquidity and creditworthiness to lenders.

Below are few quick tips to help you make your own aging abode on the basis of raw data. You can use many IT-enabled tools to do that, for example in Oracle or you can get a list of invoices and quickly make your aging without having a trouble of going into another process, which can be cumbersome.

So, let’s suppose that we have rough data of vendors numbers or reference, which could be their names, PO numbers, invoice numbers and the invoice date. Now, one very important thing is that the invoice date will not determine how long it has been outstanding and what was the criteria to ascertain that it’s an outstanding payment or with you. For that reason, another field of ‘due date’ shall be required.

For example, with my vendor has a contract with the payment terms of 30 days. Hence, if I have a 30-day payment contract term which demands payment 30 days after the invoice date. To determine the due date, just take the invoice date and add 30 days to it.

Now we shall calculate the days overdue. To calculate it, you can minus the due date from today.

Now to make a summary of the overdue days of all invoices, just go to pivot table option. You may not be required to select all the data. If your data is fine and there are no empty columns, it will automatically select all your data. Simply press recommended table and it will select all the data.

For aging summary, we can either use due date or the days overdue. We will start with the due date. To put the due date in a pivot table, simply click on “due date” option on right hand side. when you right-click on this, you can see a group option again. Click on the grouping and you have quite a lot of information here, which has automatically picked the starting date and the end date. This is a range of dates that is included in this column. Now you can simply split the dates according to your own requirements to prepare an aging report and analyse the data of your vendors quickly.

For more professional services, you can also contact a firm providing accounting services in Singapore.