Excel Magic Trick 1129: Aging Accounts Receivable Reports Using Slicer, Excel Table, Page Setup

See how to create Aging Accounts Receivable Reports using Slicer, Excel Table Feature and formulas:
1. (00:09 min) Overview
2. (01:05 min) Text Formula For Reporting Categories in Lookup Table
3. (01:59 min) Days Late Formula. The correct formula for Days Late is: =TODAY()-D2
4. (02:26 min) Lookup Table
5. (03:26 min) VLOOKUP function For Reporting Categories in Transaction Table (Used For Slicer)
6. (04:33 min) Excel Table
7. (05:08 min) Slicer to Filter Report
8. (06:45 min) Page Setup

What is an Aging Report:
Aging means how many days past the due date the invoice is. Accounts Receivable Customers must pay their bill by a certain date, if they pay late, they would be listed in one of the aging reports. If they are 1-30 days late, they are listed in the first report. 31-60, the second. And so on. Further, once the customer is so late, like 300 days, the company stops wasting its time trying to collect and writes the Accounts Receivables amount as "Bad Debt" and runs that expense through the Income Statement.

[excelisfun]

Excel Magic Trick 1129: Aging Accounts Receivable Reports Using Slicer, Excel Table Feature, Page Setup
See how to create Aging Accounts Receivable Reports using Slicer, Excel Table Feature and formulas:
1. (00:09 min) Overview
2. (01:05 min) Text Formula For Reporting Categories in Lookup Table
3. (01:59 min) Days Late Formula
4. (02:26 min) Lookup Table
5. (03:26 min) VLOOKUP function For Reporting Categories in Transaction Table (Used For Slicer)
6. (04:33 min) Excel Table
7. (05:08 min) Slicer to Filter Report
8. (06:45 min) Page Setup

[umarchetty]

I can only say this. You are very generous. Sharing these tricks with a bunch of strangers you will certainly not meet requires a lot of selflessness. I admire you for this. Great videos and very simple. One question though. How do you address multiple part payments received through out the year from receivables. If it were me, I would insert various columns to show these payments and show the ageing at the end of that period. Is there a shorter way?

[MikeMNE]

This is great, but what would be the formula for aging of invoice that i can pay in installments. 100€, 5 month, every month 20€. If i don't pay first month it will be 20€ from 1-30 days, if i pay 15€ next month it will be 20€ 1-30days and 5€ 30-60 days ? :)

[SyedMuzammilMahasanShahi]

Awesome Mike with EXCELlent video on Aging accounts

[ibrahimdaas]

Man .. you have great videos ... your way of explaining is great too

[monica]

Thank you so much, this is very helpful!!!

[reytagerina]

thanks, this video really help my work

[alphaone]

Hi. How to link two slicers here 1 for Report Category" and 2 "Customer"??

[techyjohn]

Hi can you tell about compatibility function of excel please