• Prepare an accounts receivable aging analysis

 

Let’s go back to Larkin, Co.’s subsidiary ledger for last year, but let’s add some details:

Customer	Amount Owed	Age of Invoice, in days
		0-30	31-60	61-90	90+
A	57,500	40,000	17,500		0
B	22,000	22,000			0
C	74,500	14,500	50,000	10,000	0
D	8,000				8,000
E	12,500			12,500	0
F	25,000	25,000			0
G	50,500	1,500	30,000	17,000	2,000
	Single Line250,000Double Line	Single Line103,000	Single Line97,500	Single Line39,500	Single Line10,000

Let’s also assume that Larkin Co. extends normal credit terms of net 30, meaning the invoice is due within 30 days. Each customer may have several outstanding invoices. Let’s say this schedule was prepared on 12/31 and Customer A had two outstanding invoices: a bill for $40,000 dated December 15, making it not overdue, and a bill for $17,500 dated November 30, making it just slightly (one day) overdue.

Sometimes this schedule is prepared using “days past due.” Different companies do it according to their own internal needs. In any case, this is an accounts receivable aging schedule. It’s that simple and is a canned report in most, if not all, accounting packages. We can use this report to more precisely calculate the allowance for doubtful accounts and therefore the net realizable value of accounts receivable.

First, based on a historical analysis of collectibility, we assign a probability of collection to each category. Obviously, the older an account is, the less likely we will be able to collect it.

Let’s say over time we have learned this:

In other words, our experience is that we collect 99% of the current billings, but that likelihood drops to 95% if they go past the due date, and drops again to only 85% if they haven’t paid by day 61 (more than 30 days past due). After 90 days, we don’t have much hope, only a 5% probability of getting our money, which means that a few people who don’t pay on time still eventually pay, but not many.

We’re accountants, but we also have to be statisticians. We apply the probability of collecting the accounts to the aging analysis like this:

Customer	Amount Owed	Age of Invoice, in days
		0-30	31-60	61-90	90+
A	57,500	40,000	17,500		0
B	22,000	22,000			0
C	74,500	14,500	50,000	10,000	0
D	8,000				8,000
E	12,500			12,500	0
F	25,000	25,000			0
G	50,500	1,500	30,000	17,000	2,000
	Single Line250,000Double Line	Single Line103,000	Single Line97,500	Single Line39,500	Single Line10,000
		99.0%	95.0%	85.0%	5.0%
NRV	Single Line228,670Double Line	Single Line101,970Double Line	Single Line92,625Double Line	Single Line33,575Double Line	Single Line500Double Line

According to this calculation, the net realizable value, which is the cash value, or the amount we expect to ultimately collect, is $228,670 against gross receivables of $250,000, which means the balance of the contra-asset account we set up called Allowance for Doubtful Accounts should be $21,330.

You can then prepare the adjusting journal entry.

Say the balance in the allowance account is currently a credit of $10,000, which would be the combination of all the prior entries to that account, commonly prior period-end adjustments and write-offs. You would calculate the adjusting journal entry like this:

Let’s say the balance before your adjustment was a debit of $2,000 because you had written off more accounts during the year than you had allowance for. You would calculate the adjustment like this:

Just to review: There is no entry to Accounts Receivable because it matches the subsidiary ledger that includes all the accounts, and we are making an allowance for future accounts that will go bad, but we don’t yet know which. When we actually write off an account some time in the future, we will reduce Accounts Receivable (with a credit entry) and the AR subsidiary ledger, and we will also reduce the Allowance account (with a debit).

Some companies prepare the aging analysis like this:

Customer	Amount Owed	Age of Invoice, in days
		0-30	31-60	61-90	90+
A	57,500	40,000	17,500		0
B	22,000	22,000			0
C	74,500	14,500	50,000	10,000	0
D	8,000				8,000
E	12,500			12,500	0
F	25,000	25,000			0
G	50,500	1,500	30,000	17,000	2,000
	Single Line250,000Double Line	Single Line103,000	Single Line97,500	Single Line39,500	Single Line10,000
		1.0%	5.0%	15.0%	95.0%
Uncollectible	Single Line21,330Double Line	Single Line1,030Double Line	Single Line4,875Double Line	Single Line5,925Double Line	Single Line9,500Double Line

The answer is still the same, just arrived at in a different manner by using the amount of the account that is UNcollectible rather than the amount that is collectible.

Another variation could look like this:

Customer	Amount Owed	Age of Invoice, in days
		Current	0-30	31-60	60+
A	57,500	40,000	17,500		0
B	22,000	22,000			0
C	74,500	14,500	50,000	10,000	0
D	8,000				8,000
E	12,500			12,500	0
F	25,000	25,000			0
G	50,500	1,500	30,000	17,000	2,000
	Single Line250,000Double Line	Single Line103,000	Single Line97,500	Single Line39,500	Single Line10,000
		1.0%	5.0%	15.0%	95.0%
Uncollectible	Single Line21,330Double Line	Single Line1,030Double Line	Single Line4,875Double Line	Single Line5,925Double Line	Single Line9,500Double Line

One final note: the information in an aging schedule also is useful to manage other purposes. Analysis of collection patterns of accounts receivable may suggest the need for changes in credit policies or for added financing.

For example, if the age of many customer balances has increased to 61–90 days past due, collection efforts may have to be strengthened. Or, the company may have to find other sources of cash to pay its debts within the discount period. Preparation of an aging schedule may also help identify certain accounts that should be written off as uncollectible.