Accounts receivable aging report is an informative document for a business showing details about the receivables schedule from different clients.
Let’s discuss how to calculate accounts receivable aging and how this report can help a business in different types of analysis.
What is Accounts Receivable Aging?
Accounts receivable aging refers to the time taken to collect invoices from clients. Businesses prepare a report or schedule that sorts invoices and other receivables according to due dates.
Accounts receivable are by default invoices and payments receivable within 12 months of issuing. Then, a business must analyze the due date for each invoice and list unpaid invoices.
The aging report then sorts unpaid or overdue invoices from each client by due dates. Since the purpose is to know the delinquent payments, the report is sorted by date rather than by amount or client.
The aging report provides useful information to the management about each client. The management can then analyze unpaid invoices from each client and compare the aging period against company policies.
The accounts receivable aging report has several uses (listed below). The most common is to decide about bad debts and invoice factoring for better collection management.
How to Calculate Accounts Receivable Aging?
You can take two approaches to create the accounts receivable aging report.
The first one is to list all accounts receivable amounts, clients, invoice issuing dates, and due dates. You can then create categories for receivables by aging period.
The second one is to calculate the aged accounts receivable by using the formula listed below. Then, you can simply sort these receivable amounts according to aging periods for each client.
Formula
Accounts Receivable Aging = (Average Accounts Receivable × 360 Days) / Total Credit Sales
Average Accounts Receivable = (Initial Accounts Receivable + Ending Accounts Receivable) / 2
Categories of Accounts Receivable Aging
Once you calculate accounts receivable amounts for each client or invoice, you can then sort them into different categories as below.
- Zero Days or Current: Due immediately
- 1 – 30 days: Due in 30 days
- 31 – 60 days: Due Past 30 days
- 61 – 90 days: Two months overdue
- 91+ days: More than two months overdue
Categorizing accounts receivable aging helps you in assigning a percentage to these collectible amounts for bad debts or writing off unpaid invoices.
Example
Suppose a company ABC had accounts receivable amount of $500,000 on 01-01-2022 and $436,000 on 31-12-2022.
The total amount of credit sales for the year was $ 2,500,000.
We can calculate the aging accounts receivable for the company by using the formula:
Accounts Receivable Aging = (Average Accounts Receivable × 360 Days) / Total Credit Sales
Accounts Receivable Aging = (468,000 × 360)/ 2,500,000
Accounts Receivable Aging = 67.4 Days
You can use the same approach to calculate the aging accounts receivable for each client and prepare the report.
How to Prepare the Aging Accounts Receivable Report?
Once you know the accounts receivable amount for each client and the delinquency period, you can prepare the schedule/report accordingly.
Here is a sample aging accounts receivable report.
| Client | 0 Days | 0-30 Days | 31-60 Days | 61-90 Days | 90+ Days | Total |
| Atlas Co. | $7,000 | $ 10,000 | 0 | 0 | 0 | $17,000 |
| Brandon Inc. | 0 | $23,000 | $12,000 | 0 | 0 | $35,000 |
| Charming | $11,000 | 0 | 0 | $10,000 | 0 | $21,000 |
| David Bros | 0 | 0 | 0 | $12,000 | $20,000 | $32,000 |
| Total | $18,000 | $33,000 | $12,000 | $22,000 | $20,000 | $105,000 |
The next step in the calculation is to assign a percentage weightage to each category of accounts receivable to calculate bad debt allowance.
Allowance for Bad Debts
The allowance for bad debts is the amount that a business estimates will not be paid by clients. Usually, the longer the aging period the higher the chances of delinquency of the outstanding amount.
Continuing with our aging schedule listed above, let’s assume the company estimates the following percentage weightage of bad debts for each category.
- 1 – 30 days: 1%
- 31 – 60 days: 5%
- 61 – 90 days: 25%
- 91+ days: 50%
It means the company estimates 1% of the total unpaid invoices due within 30 days are historically not collected. Similarly, once an invoice goes beyond 90 days, there is a 50% chance it will not be paid by the client.
Let’s calculate the bad debt allowance using these estimates.
Bad Debt Allowance = ($33,000 × 1%) + ($12,000 × 5%) + ($22,000 × 25%) + ($20,000 × 50%)
Bad Debt Allowance = $330 + $600 + $5,500 + $10,000 = $16,440.
So, based on its historic estimates, the company should create a bad debt allowance of $16,440 to offset unpaid invoices.
What are the Uses of Accounts Receivable Aging Report?
The foremost benefit of preparing aging accounts receivable reports is to analyze the delinquent payments. You can then use this report to analyze several other factors discussed below.
Analysis of the Collection System
Every business has an average accounts receivable period. You can calculate the receivables aging report first and then compare it to the average period.
It will give you an overview of your collection system’s efficiency. If more clients remain within the average period, you have an efficient collection system.
Contrarily, if the receivables aging period is getting prolonged than the average receivable period, then you should revise the collection policy.
Client Analysis
You can take the analysis of the collection system one step ahead to analyze each client individually.
You can categorize clients based on their payment histories as well. It will give you an idea of the credit risk associated with each client.
If there are any clients consistently falling behind the payment schedules, you can discard them or take action to improve the collection system.
Cash Flow Analysis
Accounts receivable are an integral part of the cash flow system of any business. Analyzing receivable aging reports can help you analyze cash inflows too.
You can assess the collection period and amount receivable in the coming days to calculate cash inflow from credit sales.
It can help you plan operational expenses and other cash outflows accordingly. Also, you’ll be able to adjust the payable and receivable cycles to improve the cash flow of your business.
Credit Risk Assessment
If the accounts receivable aging report shows more clients are delaying payments with larger amounts, it is an indication of credit risk.
If your clients collectively start delaying payments, your business will face credit risk ultimately. The aging report is a useful tool to help you analyze this credit risk.
Similarly, you can assess the credit risk of each client individually as discussed above.
Bad Debt Assessment
You can estimate the delinquency period of clients with historic reports first. Then, you can set a period after which receivables turn into bad debts.
The aging report can help you analyze bad debts as well. You’ll be able to analyze which client payments are nearing the bad debt period limit.
You can then make better decisions about writing off bad debts. One way to tackle bad debts is to revise the accounts receivable terms and conditions.
Help in Invoice Factoring
Invoice factoring is an effective way to accelerate your accounts receivable collection. However, you need a detailed analysis of the outstanding bills before you can consider invoice factoring.
The accounts receivable aging reports can help you understand each client’s delinquency position. You can distinguish between one-off incidents and recurring delayed payments by analyzing this report.
Then, outsource invoice collection to a specialist to recover bad debts and delayed invoices.
Limitations of Accounts Receivable Aging Report
Accounts receivable aging reports can be misleading at times due to several reasons.
Foremost, it does not differentiate between recurring defaulters and a one-off delayed payment from an otherwise consistent client.
A business must regularly update the accounts receivable days to prepare the aging report accurately. It is a time-consuming and tedious task for many businesses.
Also, the aging report does not provide any specific information that the business cannot generate with other analyses. The delinquency reports and bad debt figures can be calculated easily directly from the invoice data management system too.
