The Receivables Aging (or Ageing, if you prefer British English) report is a tool that lists all unpaid customer balances by pre-defined date ranges (buckets). It shows the relationship between open invoices and their due dates.

It is the primary tool to determine overdue balances for collection. It’s useful for the company’s management, as it helps to evaluate the effectiveness of the credit control function.

Typically, we separate the outstanding balances in buckets, at multiples of 30 days. Doing so is not a requirement, and you can separate the buckets in a way that best fits your organization. In most cases, you will see some close variation of the following ranges:

• Not due (invoices where the due date has not passed yet);
• 0 to 30 days overdue;
• 31 to 90 days overdue;
• 91 to 180 days overdue;
• 181 to 365 days overdue;
• 1 to 2 years;
• Above 2 years.

How Useful is the Aging Report

Businesses use the Accounts Receivable Aging to evaluate the financial health of the company’s client base. If the collection of outstanding balances from customers slows down, this can warn that business is also slowing down. It can suggest the firm is taking higher credit risk.

Such knowledge can be crucial for the cash flow management function of the company.

Aging reports help the management to identify clients that are regularly late with their payments. Then they can assess whether to change the credit terms and policies for such customers or stop doing business with them altogether if they pose a significant risk. The Aging Analysis can also give a starting point to take action in collecting overdue balances

The sales department should pay attention to the report as well, as it can help determine selling practices and credit terms. Clients always running late with payments can be switched to prepayment only to mitigate the risk.

The company’s credit control department can also use the Aging Report to review the status of outstanding balances and adjust specific customers’ credit limits accordingly. This is especially helpful, as, in many companies, the compensations within the department have a direct link to their collectability levels.  This is not ideal, as past paid invoices should also be part of the review. However, the report is still a great starting point, as it provides a clear indication of potentially problematic clients.

Benefits of Preparing an Aging Report

There are many advantages to running a regular Accounts Receivable Aging Report.

It allows companies to maintain an adequate level of interaction with their customers by sending them reminders and following-up whenever a client starts to delay their payments. Another benefit comes from the data the report provides regarding the clients’ behavior over time. That way, management can re-evaluate payment and credit terms and stop business with customers causing cash flow problems.

Such reports can be automated so that selling to a client is blocked within the system if they accumulate a large outstanding balance, and only continue again after the customer clears their balance.

The Receivables Aging helps the company to maintain a healthy cash flow budget and identify potential risks of lousy credit promptly.

Issues with the Accounts Receivable Aging Report

It is crucial to remember that the Aging report can sometimes be misleading. If a client has a sizeable outstanding due on Monday, but their payment day is Friday, they will end up in our report as delayed. This might indicate they are struggling with paying when it is merely due to their misaligned policies.

Also, if we set up our brackets improperly, we might face the following issue. A large amount might be just a single day within a category of ‘181 to 365 days’, but we will treat it the same way as one almost at the next bracket of ‘1 to 2 years’, even though one is six months more overdue. Therefore, it is essential to set reasonable date ranges. It is also a good idea to do a quick flux analysis comparing how the brackets’ amounts changed from the previous Aging Report, and investigate large variances.

Doubtful Debt Allowance

Aging reports are a valuable tool for assessing and estimating bad debts and the corresponding doubtful debt allowance (DDA). Generally, the longer an invoice remains open, the slimmer the chance to collect gets.

A common approach is to look at historical data and see what portion of each bucket ended up uncollectible in prior periods. We then take the average percentage and apply it to our current aging report’s balance in each bracket. The sum of these totals gives us the Expected Credit Loss for the business. And it is also this value that the accounting department books as Doubtful Debt Allowance.

Average Collection Period

One way to evaluate the company’s performance in terms of collectability and minimizing long overdue receivables is to calculate the Average Collection Period. We then assess its trend, looking at how it develops over time.

We can calculate it from the Days Sales Outstanding ratio from the Cash Conversion Cycle.

Average Receivables here is the sum of the opening and closing balances of accounts receivable, divided by two.

The Days Sales Outstanding ratio shows us the average period between the date a sale originates and when the customer settles the amount. However, to arrive at the Average Collection Period, we need to deduct the credit period given to the company’s clients.

One option is to take an average number of days for all outstanding balances. Another is to separate them into groups if the terms vary a lot for different customers.

Accounts Receivable Aging Report in Excel

Preparing the report is more accessible when the company uses an ERP or specialized accounting software. Such systems usually have an integrated Aging Analysis functionality, where the company can specify the date ranges and a lot more. One example is the option to send automated e-mails to clients that have outstanding balances above a certain threshold.

However, many businesses do not have the option to generate an Accounts Receivable Aging Report automatically. Let’s take a look at how to prepare one in Excel easily.

Here we have a list of all open invoices from our accounting software. As a minimum, we need three columns to work with – client, invoice amount, and maturity date. The reason we work with the maturity date, instead of the invoice date, is that the invoice does not become overdue until the maturity date has passed.

In many cases, we don’t have the maturity of the sales invoices within the system. Then we can add an average number of days to each invoice and calculate their due date. Or, if we have specific terms with each client, we can prepare a summary of the credit terms of all customers and match them in our data.

Our company has a 30-day payment term with all clients, so it’s easy to calculate the maturity date for all invoices.

The next step is to compare the due date to the date of the review and see whether clients are late with payments or not.

We do that by subtracting the maturity date from the review date, which in our case, is as of 31 December 2020.

A positive result means there is a delay with the invoice settlement, and a negative result means it’s not yet due for payment.

Now, for the fun part. Based on the days_overdue data, we will assign an ageing_group. The way this works is by providing brackets of values and looking at where the current invoice fits within those ranges, based on its days_overdue. Remember how we split into groups, usually an increment of one month. That’s precisely what we are doing here.

The formula we use is LOOKUP. It allows us to take a value and place it in between value ranges. Based on where it fits, we then return the name of that bracket.

Here you can see that on the second row, that days_overdue is 29. This is the lookup value. The formula takes that and figures out that it sits between the numbers 1 and 31, our second bracket in the lookup vector (first one would be between -9999 and 1). Then the formula returns the second value from the results vector, “1. 0 to 30 days”.

Now that we have our categories for each invoice, we can aggregate the data to make it easier to review and analyze. To do that, we create a Pivot table (Insert tab -> PivotTable).

We put our clients’ data in the rows and split the balance of each client per the aging groups in the columns.

At this point, an analyst will most probably have to involve other departments. As we review the data, we can highlight problematic balances. Usually, we won’t focus on balances overdue with less than 30 days, but this depends entirely on the business and industry. However, we can immediately notice some potential issues. Our largest balance is with Dickens-Bradtke. We can see that credit control missed this one, as we are still delivering to them, even though they have outstanding balances for over one year. This client might become a significant loss for our company, so we have to raise this to the management and further investigate with the sales and credit control departments.

Another thing we can do is not only look at individual customers but take a look at the totals for each aging group to get a feel of the collectability within the company. We have around 6% of our balances delayed by more than 90 days. Whether this is acceptable depends on the industry and the time of year. Remember, seasonality will also have a substantial impact on how clients manage to settle their balances.

We can take our analysis one step further by calculating the Doubtful Debt Allowance to book at the review date. By employing the average historical percentage of uncollected balances for each bracket, we arrive at DDA of €1,120 thousand. Remember, this is not the expense we have to book for the period, but the balance of the provision. What we usually do in practice is to book only the change from the previous period’s DDA balance.

Conclusion

The Accounts Receivable Aging is a popular management tool to evaluate outstanding customer balances and identify potential irregularities and issues. It is a great idea to prepare it regularly and involve different departments in reviewing the data, allowing the company to draw the maximum amount of valuable insights. Doing so will improve the cash flow by allowing management to make informed decisions regarding client terms and conditions.

Show your support by sharing this article with colleagues and friends.

Also, don’t forget to download the Excel model below.