Accounts Receivable Aging Report

A record of invoices due to be paid by customers

A/R aging report is a record of invoices due to be paid by customers. Businesses use such a report to keep track of outstanding payments (current and overdue) and how promptly a client manages to pay.

A/R Aging Report

It is a crucial piece of paper that signals cash flow problems. If businesses do not sustain a healthy cash flow, it could ultimately lead to bankruptcy.

With all the data collected in such a report, it is easier for firms to estimate the percentage of late-paying customers or customers who default on their payments.

This information plays a significant role in adjusting credit policy.

Companies can change or adjust their credit policy to achieve their goals. These goals can be profit maximization, cash flow efficiency, etc.

Firms can also use this document to determine the efficiency of collection practices, which will lead to a better cash flow.

Using past data from such a report, businesses determine their margin of default on expected payments from customers.

Lastly, if firms wish to have their invoices factored, the one piece of documentation they will have to provide is the A/R aging report.

Paper

This article is a guide to understanding the A/R aging report better. The major sections are:

  1. When do we need the accounts receivable aging report?

  2. What does each term mean?

  3. How do you create the report?

  4. How do you maintain the report?

  5. Finally, why is the report important?

  6. The bottom line

When do we need the accounts receivable aging report? 

Everything explained here revolves around the buy now, pay later payment option. Therefore, there is no need for an A/R aging report if the company does not adopt this payment method in any form or case.

However, if the firm adopts such a payment option, the report will come in handy for several reasons.

Building

Buy now, pay later (BNPL) payment option. 

Clients can get the goods or services they need without paying beforehand.

Maybe a client, think of a large corporation, is illiquid every July, and suddenly a big order comes in.

The client gets the goods or services to fulfill the order and is informed when the due date is. This is a business-to-business example.

Handshake

Another example is when a shopper wants to buy that expensive pair of shoes now, but the paycheck kicks in only next month. So the shopper gets the shoes and agrees to pay later in the future.

You need to know that the good or service received has been obtained on credit by the client.

What does each term mean?

This credit we spoke about is an asset to the business, so it must be displayed in some way to investors, resulting in the term accounts receivable.

Investors care about all the cash inflows and outflows, whether incoming or current.

Ideas

Moreover, an element of risk associated with owed payments is the risk of not getting paid.

The A/R is recorded in a separate report. Accountants apply the aging method to the A/R in the report.

This method classifies accounts by the length of time up to and beyond the due date. Lastly, after applying the method, all the information is put into the A/R aging report.

Once this report is complete, anyone can filter out the information they are looking for or take the report as a whole.

Accounts receivable (A/R) 

These refer to the amount of money a company expects to receive in exchange for the goods or services already provided to clients. These clients can be individuals, institutions, or governments.

Calculations

You will find this term in the balance sheet under current assets as A/R net, provided that payments are due within one calendar year.

It is also used in the cash flow statement under changes in operating assets and liabilities as A/R net.

The delivery of goods or services obtained on credit is invoiced to the client by email or other means, allowing the amount of money owed to be classified as an account receivable.

If the firm does not manage to invoice the customer despite having provided the goods or services, the amount will go under the name accrual revenue until invoicing.

This happens if the firm adopts the accrual method of accounting. The other option is the cash accounting method, in which the revenue is not reported until payment is received.

Invoice

Businesses can secure loans by using their A/R as collateral assets.

This method is called asset-based lending, which means that if the loan is not repaid, the lender can take the asset from the borrower.

In theory, all clients should pay for the goods or services they ordered, but in reality, some clients default on their payments. This makes the financial statements inaccurate and causes cash flow problems.

To offset the highly optimistic scenario in which clients pay all A/R, businesses record an allowance for doubtful accounts on their balance sheet.

This acts as a counterweight to the optimistic scenario and gives a better estimate of the actual numbers. A/R aging reports and data on past payments are needed to determine the allowance for doubtful accounts.

Aging method 

Next, the term comes from the aging method applied to A/R. The overdue payments are classified by the number of days passed since the due date, known as the age of receivables.

Hourglass

Generally, companies take intervals rather than days to arrange invoices. Therefore, invoices are divided into:

  • Current

  • 1-30 days past due

  • 31-60 days past due

  • 61-90 days past due

  • 91-120 days past due

  • 121+ days past due

However, each company will select intervals in the most useful manner for their business model. This represents the aging schedule.

Current stands for payments that are not due yet, but can be paid for if the customer decides to. Typically, the company will approach the customer through email or other forms a few days before the due date.

The company decides the length of time allowed to receive payment fully. For example, the Net 30 days means the client has 30 days to pay in full for the delivery of the goods or services.

The remaining categories or intervals signal a delay of payment. This is the problematic territory when businesses must get in touch with the client and make sure that payment is somehow made.

The more delayed the payment gets, the worse for the business. The cash flow is affected, but the likelihood of eventually being paid decreases.

Report

A report is a spreadsheet in Excel or accounting software that contains information for each client that owes the company money. There are two ways to illustrate an A/R aging report.

Potato

Either you take the summarized version, which displays the outstanding payments and the interval they correspond to, or a detailed version which, in addition, contains information about the client and the invoice.

Summary and detailed versions 

In the summary version, each interval is assigned a column, plus there is a Total column to sum each client's outstanding payments.

The rows contain details about the client, such as name, current and due amount. Similarly, there is a Total row to sum all outstanding payments corresponding to each interval.

Finally, in the bottom right corner, you should have all the money the company is waiting to collect.

Calculator

The detailed version includes all the above, plus columns dedicated to:

  • Location of transaction

  • Date when the transaction happened

  •  Number of the transaction

  • Possibly some contact information, depending on the Privacy Policy adopted

How do you create the report?

Checklist

1. Collect and go through your invoices

2. Set an aging schedule that works best for your business model

3. Create a row for each client and label columns

4. Insert the information from invoices appropriately (row and column)

Here is a summary of an A/R aging report:

WSO Table

Here is a detailed A/R aging report:

Detailed WSO Table

The story behind this accounts receivable aging report 

This example shows the hypothetical situation that Wall Street Oasis is in as of 18/06/2022.

Five clients owe money for the WSO courses and boot camps they received when WSO adopted a buy now, pay later payment option.

Pete Jenkins, a first-year undergraduate, who wanted to better prepare for a career in finance, took the WSO 13-Week Cash Flow Modeling, costing him £168.

Free DCF Crash Course

Sign Up for our Free DCF Modeling Crash Course

Begin your journey into financial modeling with our free DCF Modeling Crash Course.

Learn More

I know inflation must have been extremely high back then.

Nevertheless, because he found the course valuable and insightful, he carried on with the WSO Excel Modeling Course, costing him an additional £500.

Excel Modeling Course

Everything You Need To Master Excel Modeling

To Help You Thrive in the Most Prestigious Jobs on Wall Street.

Learn More

To complete the package and get full access to the WSO Company Database, he took an additional £135 giving him 1-year access to resources provided by certified professionals.

Forgetting that he does not generate any income and that his parents are no longer giving him extra money to spend, he found himself in an uncomfortable position.

Pockets

Fortunately, Pete Jenkins and WSO agreed that he would pay back with the first money he ever received, whether from an internship or a job.

Not the same level of understanding was given to Y institution. Being a for-profit company, unlike X institution, which is a non-profit organization, WSO reached out to a collection agency.

In this way, WSO can focus on delivering high-quality content, courses, and an application allowing users to receive advice or feedback on any finance-related topic or situation while having someone deal with the client.

The remaining three clients agreed with WSO to pay in the future. However, realizing that clients fall behind paying on time, the board initially tightened the policy by requiring higher credit ratings.

Unfortunately, that was insufficient to solve the problem; thus, the company decided to move to another payment option. Therefore, the buy now, pay later payment option was replaced by advance payments.

Meeting

The A/R aging report came in handy for WSO to identify late-paying clients, take proactive steps to solve the issue, and ultimately swift from a policy that was not working to a better method.

How do you maintain the report?

You will have to automate some things using Excel formulas to keep adding new invoices to the report while doing minimal thinking regarding which amount goes in which interval.

Here is one video explaining the automation process:

This video is better suited for the summary version.

For better automation, it would be easier to create cells for each end of every interval (1, 30, 31, 60, 61, 90, 91, 120, 121) and use the cell instead of typing the number in the 'if statements' below the Past Due Amounts.

If you lock those cells, you can drag down the formula to autofill for all rows.

Here is another video describing the automation process:

This video is more comprehensive and fits better for the detailed version.

Here the automation process is better, and the only thing you need to do is add new invoices with their respective data.

The pivot table provides a better display of the information you want to see from the entire table.

Why is the report important?

Some clear benefits come with using and paying attention to the report. All these benefits contribute to the success of the company.

Benefits of accounts receivable aging report:

  • Reveals cash flow problems

  • Adjusting credit policies

  • Adjusting collection practices

  • Estimating bad debts expense and the allowance for doubtful accounts

  • Required for factoring invoices

A few questions to address are:

  1. What should be done to prevent cash inflow problems from not happening in the future, as identifying them could be difficult when dealing with many outstanding invoices?

  2. How do businesses handle clients who step on the wrong foot when paying on time?

  3. What margin of default should be considered moving forward, and how to derive it? Is there an alternative to waiting for clients to pay their invoices?

Progress

Reveals cash flow problems

Cash flow refers to the inflow and outflow of money business experiences in a defined period. This movement is recorded thoroughly in the financial statements.

Typically, businesses spend money to make money. Therefore, having cash outflow is part of running a business operation.

As long as the cash inflow is more significant than the cash outflow, also known as positive cash flow, the business should increase its liquid assets (cash is a liquid asset).

Late payments can negatively impact a company's cash inflow.

This impact could result in the inability to take advantage of seasonal opportunities, pay suppliers, make timely payments or pay liabilities.

Cash Flow

A 2020 European Central Bank (ECB) survey revealed a 37% increase over the past six months (April to September) in the percentage of large European firms experiencing occasional late payments.

This possibly resulted in firms owing to their supposedly wider business networks.

The A/R aging report helps determine the efficiency of collection practices, how well the cash flow operates, and who is not paying timely or not paying at all.

According to a study by U.S. Bank, 82% of companies fail because of mismanaged cash flow. Without such a report, it is difficult to maintain a healthy cash flow and identify risks affecting the company.

With the help of this report, any company can stop doing business with a client who is not paying on time before all those late payments impact the cash flow significantly.

Stop

Adjusting credit policies 

Why do companies go about making this report? Is it to get their money?

The company can change its credit policy by having data on how long it takes for clients to pay.

Calculating

In a singular case, if a client differs payment beyond the due date repeatedly, the company will take action to get its money and likely change the terms of the policy for that client or stop doing business in the future.

If most clients considerably fall behind paying on time, leading to a big uncollected sum of money, the company could decide to tighten the policy.

For example, it can add a cap on how much you could owe or demand a higher credit rating. It will not lack creativity if the money doesn't hit the bank account.

Conversely, if everything goes smoothly, that might encourage the firm to loosen up the policy and take on more risk. As a result, it would sell more goods or services and, in theory, make more money.

When establishing policy, companies could have profit maximization and liquidity in their mind.

Adjusting collection practices 

So now the company knows who owes how much and for how long. The event in which the company gets the money is straightforward, but what if the client doesn't pay?

The first thing that would happen is that the client will get notified via emails or phone calls that there is an overdue payment and action is required.

Deadline

From that point onwards, you can think about the company suing the client and going for anything collateral, if not money. That might involve hustle, phone calls, emails, lawyers, time, money, etc.

No company wants to spend their days getting the money that they rightfully earned like this. Yet, at the same time, they prepare for some clients to default on their payments.

One thing the company can do to diminish this burden is to reach out to a collection agency known as a debt collector.

The collection agency will pursue to recoup the money on the company's behalf at a cost that is typically a percentage of the amount or a fee.

If it is a fee, the agency gets paid irrespective of the outcome of their work. If it is a percentage, the collection agency is incentivized to succeed in collecting the payment because if they fail to do so, they don't get paid.

Dollars

This third party must abide by the rules outlined in the Fair Debt Collection Practices Act (FDCPA) if it operates in the United States. 

Another thing the company can do to salvage something out of the entire owed amount, or put debt, is to package these accounts receivable and sell them to a debt buyer.

If the debt buyer purchases the package, it will own the entire sum. Therefore, there is a big incentive for the debt buyer to collect the owed amount.

Sometimes a collection agency can act as a debt buyer too.

Estimating bad debts expense and the allowance for doubtful accounts

1. Bad debts expense

As the age of receivables increases, it worsens for the company because it is unlikely they will get paid.

Some of these overdue payments might be bad debts, and the company wants to estimate the bad debts expense.

In this case, the report helps estimate the bad debts expense since it keeps track of the number of days passed since the agreed due date for overdue payments.

The bad debts expense is recorded in the income statement under the operating expenses section.

Debt

2. Allowance for doubtful accounts

Each company has its allowance for doubtful accounts. This is the predicted default payment the company is willing to take on.

Generally, companies calculate the percentage of invoices that turned out to be bad debts for each interval from previous A/R aging reports.

Piggy Bank

As an example, let's assume a:

  • 2% bad debt allowance on 1-30 days total overdue amount
  • 7% bad debt allowance on 31-60 days total overdue amount
  • 13% bad debt allowance on 61-90 days total overdue amount
  • 20% bad debt allowance on 91-120 days total overdue amount
  • 30% bad debt allowance on 121+ days total overdue amount

Using the numbers from the summary report above, we calculate the allowance for doubtful accounts:

Allowance for doubtful accounts=

2% x 1524 + 7% x 1165 + 13% x 525 + 20% x 300 + 30% x 236 = £311.08

Required for factoring invoices 

Invoice factoring may also be known as accounts receivable factoring and debt factoring.

Companies that factor in outstanding invoices as a financing tool must provide an A/R aging report. When companies factor their invoices, they sell all or a part of their invoices to a third-party firm called the factor.

Invoice Paper

Since the factor has taken over the invoices, clients will make payments directly to the factor. The factor could also go about chasing the client for payment, which takes some of the hassles away from the company.

The factor will pay the company a percentage of the factored invoices, which may range from 80%-90%, also known as the advance rate, once the invoices are sent to customers and considered valid.

The remaining percentage is paid once the client completes payment minus a fee going to the factor agreed upon by the company.

In this case, the factor will require the A/R aging report and other documents to determine the credit risk of invoices. These will help in calculating the fee and the advance rate.

The factor will also consider the industry the company belongs to before deciding on the fee and the advance rate.

Calculator

Advantages of invoice factoring:

  • Companies that struggle to receive timely payments can reach a factor to improve their cash inflow and, consequently, the overall cash flow.

  • Cash flow predictability will allow for better business planning, forecasting, and execution.

  • Usually, invoice factoring is better than taking bank loans in the short run because it could be cheaper and easier to secure.

Disadvantages of invoice factoring:

  • There may be an element of commitment to the factor, meaning as a company, you can't factor invoices now and then or when only needed.

  • If customers default on payments, the factor will require that the company repays the amount paid initially to them.

  • If the company has a non-recourse factoring agreement, the factor will not charge back for the bad debts but will demand a higher fee and maybe a lower advance rate.

  • Provided that the factor controls the unpaid factored invoices and aggressively chases payment, it risks harming relationships with the company's clients.

The bottom line

The accounts receivable aging report is an important document for any business that adopts, in any fashion or instance, the buy now, pay later payment option.

Learn

It classifies invoices by the number of days passed since the due date, usually grouped to form intervals, following an aging schedule. Each business should use an aging schedule that is most useful for them.

The more invoices a business accumulates, the harder it is to be on top of them constantly. Therefore, it makes it a no-brainer to record all information that comes with them in a report and use it to its advantage.

The outlined benefits play a significant role in the proper functioning of the business. Sometimes, documentation must provide to any other involved party.

Maintaining a healthy cash flow could make the difference between success and failure; hence, underestimating the report's importance can lead to bankruptcy.

Free Excel Crash Course

Sign Up for our Free Excel Modeling Crash Course

Begin your journey into Excel modeling with our free Excel Modeling Crash Course.

Learn More

Researched and authored by Bogdan-Remus Pintilie | LinkedIn

Reviewed and Edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: