Account receivable aging report is Excel template for recording and reporting lists unpaid customer invoices and unused credit memos by date ranges. Account receivable aging is the account receivable based on length of time an invoice has been outstanding in a company. It can be used for used as a tool for estimating potential bad debts, which are then used to revise the allowance for doubtful accounts. Mostly, more than 30 days debt means the higher risk in failure to collect all the owed money.
THE MENUS
account receivable aging report has 2 sheets:
Invoice Data
Menu for recording the information regarding the invoice which could be use for calculation in next elements. There are 2 parts here:
- Account Receivable Aging Report Filter: the filter used for sorting the information data based on customer. To use it, just click one of the customer data.
- Ageing Report Information Sheet: the tables for recording the information data. The fields are:
- Number: Invoice ID
- Date: Date the invoice generated.
- Customer: name or ID of the customer
- Description: title or small description of the invoice
- Amount: the AR amount
The rest of these fields are autogenerated:
- Days Outstanding: measure of the average number of days that a company takes to collect revenue after a sale has been made. It basically calculates how long the AR age in days up to today since it’s been generated or created by company.
- Outstanding amount: there are 4 columns based on age category. The category which an AR belongs to will has the amount on it, while the rest of the columns remain 0. It used for recording the AR amount, however due to differences in interest or tax calculation, it has not set the calculation yet.
- Trends: display the outstanding amount in small line chart.
Account Receivable Aging Report Forecast
Menu for recording the aging of accounts receivable in excel and the amount. There are 2 fields:
- Filter: the filter for this accounts receivable aging report template. Consist of two:
- Time Filter: it display the due date in selective period. To change the period, click on the small drop down symbol after Years. The periods are: Year, Quarter, Month, Day.
- Customer filter
- Account Receivable Aging Report Invoice table: it display the invoice and the calculation sorted by customer name.
HOW TO USE
It is optional, but on the header you can replace it to company header: logo contact details, and name.
On information sheet, input the Number ID, the Date, Customer Name, Description, and AR Amount field. Let the autogenerated calculate the AR outstanding amount. However as stated above, there are still not proper calculation formula yet due to the difference of interest in company. So, you may need to define the formula first.
Use the slicer at the header to filter the records based on customer. If you made any changes to aged information sheet, you have to update the record tables first or else it will show the old data. To update: select the slicer, right click > Refresh Data. To display all the data after filter, click the small icon on the slicer options.
The next sheet is for displaying purpose. Like previous filter, if you made any changes, you must update the tables and slicer first.
The aging report in excel can be printed in landscape orientation. To change the report themes, go to Page Layout > Themes or Color.
If you still having difficulties in using this template, we provide the accounts receivable aging report sample. You can follow the sample to use it.
CONCLUSION
You can use the template for doing aging analysis. With the report result, you can determine the risky transaction or troubled customer. It is best to integrate this template with other template such as invoice or ledger template.
You can use this for small to medium company, even for personal use. You can download account receivable aging report in excel for free here: