Ageing Analysis in Excel Using IF Formula & PivotTables | Accounts Receivable Report
77.4 هزار بار بازدید -
3 سال پیش
-
Download the featured file here:
Download the featured file here: https://www.bluepecantraining.com/wp-...
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
------------------------
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
------------------------
3 سال پیش
در تاریخ 1400/07/07 منتشر شده
است.
77,475
بـار بازدید شده