DIY Aging Report in Excel

AP/AR Aging Reports are pretty much a standard report which one can generate directly from both ERP systems or smaller off-the-shelf accounting packages.

The possible reasons for not being able to churn out aging reports directly from your respective systems are most likely due to:

• 1. The setup not done up correctly (E.g Credit terms of vendors)

  2. You are working with ERP in a large organization with substantial layers of bureaucracy ~ Raising tickets for systems setup and change requests takes quite a while...

  3. You are in the midst of system migration (Direct Changeover)

Under such circumstances, one under time constrain may have no choice but to do up the aging manually in spreadsheet. The pieces of information required will be:

~For back-dated aging reports:

• • List of invoices (YTD) and list of payments made (YTD)

  • List of credit terms of vendors / customers

~For current aging reports:

• • List of unpaid invoices

  • List of credit terms of vendors / customers

With the above, one can then derive the due dates of the respective invoices (through lookup to vendors credit terms + invoice date) for tagging to the appropriate aging bucket (Through lookup to aging buckets)

DIY Excel Aging Report Template

For the case of current aging reports, I have done up a simple template (available for download at the bottom) which helps automate the generation of the aging report with pivot table. How it works as per follow. Hope it helps.

• • Paste the list of pending invoices in the specified location on the left. Input date of aging and select required aging bucket before Clicking Run Report.

• • Output as per below

DOWNLOAD AGING TEMPLATE