Ageing Analysis in Excel Using IF Formula & PivotTables | Accounts Receivable Report

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
------------------------

[IvanCortinas_ES]

Excellent tutorial Chester. Thanks for your telling it so well.

[makeupwow]

Thank you. It’s really good and easy to understand.👍👍👍👏

[ramcacpa]

Really Good Explanation and Easy to understand

Thanks

[iuluhny]

Thanks for sharing. It's clear & helpful.

[economiaustm]

Thank you for the tutorial, it helped so much

[rickytelavika]

in the detail work sheet, how could I include comments, contact person ands number, date time called customer

[uiglomj]

i have aging days for SKU "X". i bought this sku 3 times in last 80 days. first time 5, second time 10, 3rd time 7 units. now i have total quantity of 17. my aging return time is under 90 days. how i will calculate aging

[mahaboobbasha]

Thanks for sharing this video, could you please explain once how does it work data model in pivot table.

[ybtskgx]

Thank you so much for this vedio, really saved my ass** for Monday presentation .

[sunracemangesh]

Team...need your help ..I am working on formula for ageing bucket... Here formula is taken from approved date ( Column B) ...

Now If there is no approved date then need to take the created date ( column A). I tried isblank but not able to go ahead ..

Request if can help please

[marcoscardona]

Hello can you explain why is that the days overdue is 101? Thank you.

[thegreatmarvinoliveros]

What if accounts have several credit terms apart from 30 days.

[rickytelavika]

I actually want to design the same kind of report where by I could have it accessible to my staffs