Download the featured file here: https://www.bluepecantraining.com/wp-content/uploads/2021/09/Ageing-Analysis.xlsx

In this video I demonstrate how to perform ageing analysis in Excel. We want to show overdue invoice amounts in four columns: 0-30 days overdue; 31-60 days overdue; 61-90 days overdue and over 90 days overdue.

The process is:
1) Calculate when the invoice is due
2) Calculate the number of days the invoice is overdue (using the TODAY() function to keep the calculation up-to-date). Include an IF statement to return 0 days for invoices that are not yet due (this gets rid of minus values)
3) Put the invoices in buckets (0-30, 31-60, 61-90, greater than 90 days overdue) using an approximate match VLOOKUP or exact match or next smaller item XLOOKUP.
4) Generate a PivotTable report to display the analysis
------------------------