An accounts receivable schedule is a dated list of every unpaid customer invoice, sorted by how long each balance has been outstanding. It is the working document behind cash flow forecasting, credit decisions, the allowance for doubtful accounts on your balance sheet, and the bad-debt deduction on your tax return. Build it well and your financial statements reflect what you will actually collect. Build it poorly and cash problems stay hidden until they are urgent.
What Each Line Needs to Show
Every row on the schedule represents one open invoice. A handful of fields do the real work, supporting both the aging math and the follow-up your collections team has to do.
- Customer name and account number. The account number ties the row to your accounting system and any CRM records, which matters when a single customer has dozens of open invoices.
- Invoice number, so you can trace a line back to the original transaction when a balance is disputed or misapplied.
- Invoice date. This is the anchor for the aging calculation.
- Original invoice amount and current balance. These differ when the customer has made partial payments, and you want both visible.
- Payment terms. Net 30 and Net 60 age differently, and a 45-day-old invoice on Net 60 is still current.
- A dispute or status flag, so invoices in active dispute stay visible without distorting the aging.
Skipping any of these creates problems downstream. Without the invoice date you cannot age the balance. Without payment terms you cannot tell which invoices are actually overdue. Without dispute flags your oldest bucket looks worse than it is, and your allowance estimate inflates with it.
Pulling and Cleaning the Data
Start by exporting all open invoices from your accounting system or ERP as of a specific reporting date. That cut-off date is the single most important discipline in the process. Any invoice posted after the cut-off, or any payment received after it, gets excluded from this run. Let late transactions bleed in and the schedule will not reconcile to the general ledger, and you will spend hours hunting the difference.
Before aging anything, confirm that cash receipts through the cut-off have been applied. An unapplied payment sitting in the system makes it look like a customer still owes money they have already paid. Check unapplied credit memos the same way. Applying credits against the customer’s oldest outstanding invoices is the better practice, because leaving them unapplied inflates both the total receivable and the oldest aging buckets.
Once payments and credits are applied, export to a working format. A spreadsheet handles smaller volumes; larger operations generate the schedule directly from the ERP. Either way the output should show one row per invoice, sorted by customer, with every field above populated.
Aging the Balances
Aging sorts each invoice into a bucket based on how far past the invoice date it sits, measured against the payment terms. The standard buckets are:
- Current: not yet past due under the stated terms.
- 1–30 days past due: recently overdue, usually resolved with a reminder.
- 31–60 days past due: needs structured follow-up.
- 61–90 days past due: elevated risk, and collection probability drops here.
- 91+ days past due: the highest-risk category. Industry data suggests that once an invoice passes 90 days, the chance of collecting drops below 20 percent, and this is where write-offs concentrate.
The buckets are not fixed. Healthcare and construction, where payment cycles run longer, often split the oldest column into 91–120 and 120-plus.
The distribution tells a story at a glance. Balances clustered in Current and 1–30 mean credit policies and follow-up are working. A heavy tail in the 61-plus columns signals either that credit is being extended to customers who cannot pay, or that the collections process has gaps. That diagnostic value is why the aging schedule beats the single AR number on the general ledger.
Reading the Schedule: DSO and CEI
Two metrics come straight off the schedule.
Days Sales Outstanding
DSO is the average number of days it takes to collect payment after a sale:
DSO = (Accounts Receivable ÷ Total Credit Sales) × Number of Days in the Period
If AR is $150,000, credit sales for the quarter were $450,000, and the quarter had 90 days, DSO is 30. That number is meaningful only in context. A 30-day DSO on Net 30 terms means collection is on schedule. A 45-day DSO on the same terms means the average invoice runs two weeks late, which is a collection problem worth investigating even if nobody is technically in default.
DSO has a blind spot. It does not distinguish current invoices from severely overdue ones. A company with half its receivables current and half at 120 days can have the same DSO as one where everything is moderately late. The buckets and the next metric fill in that picture.
Collection Effectiveness Index
CEI measures the percentage of receivables you actually collected during a period, and it accounts for new credit sales generated in the same window:
CEI = (Beginning AR + Credit Sales – Ending Total AR) ÷ (Beginning AR + Credit Sales – Ending Current AR) × 100
A CEI of 100 percent means you collected everything collectible. Well-run companies target 80 percent or higher. When CEI is falling while DSO stays flat, new sales are usually masking a growing pile of stale receivables that nobody is chasing.
What the Aging Distribution Tells You
A steady rise in the share of balances in the 61-plus buckets over several reporting periods suggests either that you are approving customers who should not qualify, or that follow-up is too slow. The opposite pattern, where nothing ages past 30 days, may mean terms are too tight and could be selectively loosened without meaningful risk. Balances in the Current and 1–30 buckets can be projected as near-term cash inflows; anything in 61-plus should be discounted heavily or left out of short-term forecasts. Relying on aged receivables to cover upcoming obligations is how companies end up drawing on credit lines they did not plan to need.
Estimating the Allowance for Doubtful Accounts
The aging schedule is the primary input for the allowance for doubtful accounts, the contra-asset that reduces AR on the balance sheet to what you realistically expect to collect. Assign an estimated loss percentage to each bucket, multiply, and sum.
A typical set of loss rates:
- Current: 1 percent
- 1–30 days past due: 3 percent
- 31–60 days past due: 5 percent
- 61+ days past due: 20 percent
If the schedule shows $45,000 current, $25,000 in the 1–30 bucket, $20,000 in the 31–60 bucket, and $10,000 over 60 days, the estimated uncollectible amount is $4,200 ($450 + $750 + $1,000 + $2,000). That $4,200 is the target ending balance in the allowance account. If the allowance sits at $1,000 from the prior period, you record $3,200 of bad debt expense to reach the target.
The loss percentages should reflect your own write-off history, adjusted for current conditions. Historical rates get overridden when the facts change; if a major customer in the 1–30 bucket has just filed for bankruptcy, this period’s rate for that bucket should be higher.
For companies reporting under U.S. GAAP, the current expected credit losses framework (often called CECL) applies. FASB’s guidance on Topic 326 covers current accounts receivable and contract assets, and it requires estimating losses over the remaining life of the receivable using reasonable and supportable forecasts rather than historical rates alone.
Reconciling the Schedule to the General Ledger
The total of every open invoice on the schedule must equal the accounts receivable control account in the general ledger. When those two numbers disagree, something has gone wrong between the subledger and the GL, and it has to be found before financial statements go out.
The most frequent cause is a manual journal entry posted directly to the AR control account with no corresponding transaction in the subledger. Unposted subledger transactions, cut-off timing differences on payments, and duplicate entries account for most of the rest. The fix is the same in every case: pull detail from both sides, line them up, and find what appears in one but not the other. Most ERP systems produce a reconciliation report that flags the differences.
This reconciliation is a core internal control, not just bookkeeping hygiene. The people who record invoices and apply payments should not be the same people who reconcile the accounts or approve write-offs. That separation prevents a situation where one person can create a fictitious invoice, collect the payment, write off the balance, and cover the tracks inside a single workflow.
What Auditors Test
In a financial audit the schedule gets examined closely. Auditors are testing whether the receivables on your balance sheet exist and whether the amounts are accurate. The standard approach, set out in PCAOB Auditing Standard 2310, involves sending confirmation requests directly to your customers asking them to verify what they owe.1PCAOB Public Company Accounting Oversight Board. AS 2310: The Auditors Use of Confirmation
Auditors control the process themselves. They pick which accounts to confirm, send the requests, and receive responses directly, so nobody at the company can intercept or alter them. A blank confirmation, where the customer fills in the balance rather than being told what it should be, is treated as more reliable than one that states the amount and asks for agreement.
When customers do not respond, auditors turn to alternative procedures: examining subsequent cash receipts, reviewing shipping documents, or checking signed contracts. The cleaner the schedule and the better the supporting documentation, the faster it goes. Companies that cannot produce an invoice or proof of delivery for balances under audit tend to end up with adjustments.
Writing Off Bad Debts for Tax
When a receivable becomes uncollectible, the write-off has tax consequences beyond the book entry. Federal tax law lets businesses deduct wholly or partially worthless debts, but only if the amount was previously included in gross income.2Office of the Law Revision Counsel. 26 USC 166 Bad Debts
Accrual-basis businesses report revenue when they bill the customer, so an unpaid invoice was already in income and the write-off is deductible. Cash-basis businesses report income only when payment arrives, so the unpaid amount was never in income and there is nothing to deduct. A cash-basis consultant whose client never pays a $10,000 invoice cannot take a bad debt deduction for that amount, because it was never taxed in the first place.3Internal Revenue Service. Topic No. 453, Bad Debt Deduction
For accrual filers, the IRS requires proof that the debt is genuinely worthless before allowing the deduction. That means reasonable collection steps such as demand letters, phone calls, or a collection agency. You do not have to sue, but you do have to show a judgment would be uncollectible even if you obtained one. The deduction is taken in the year the debt becomes worthless, not the year you get around to writing it off.3Internal Revenue Service. Topic No. 453, Bad Debt Deduction
Partially worthless debts are deductible only for the amount actually charged off on the books during the tax year.2Office of the Law Revision Counsel. 26 USC 166 Bad Debts The AR schedule and the collection history behind it are your documentation trail. Keep records of every collection attempt, every returned letter, every bounced payment. If the IRS questions the deduction, the aging schedule showing the invoice unpaid for months alongside those documented efforts is the evidence you need.