Written by: Andrew Cohen, CFA, CPA, Managing Partner, Condesa Financial Group

Key Takeaways

• Accounts receivable aging analysis sorts unpaid invoices into time-based buckets to measure collection risk, cash flow exposure, and potential bad debt.
• The report is defensible only when its total ties to the AR control account in the general ledger, so reconciliation is a critical control step.
• Building an accurate aging report requires a complete subledger, clear bucket definitions, a fixed as-of date, and the correct aging method in QuickBooks Online or Excel.
• The aging method calculates the allowance for doubtful accounts by applying escalating uncollectibility percentages to each bucket, which supports GAAP-compliant bad debt estimates.
• Condesa Financial helps SMEs produce accurate, reconciled AR aging reports that support collections, lender reporting, and month-end close.

Talk With Condesa Financial

Before You Begin: Confirm Your AR Foundation

Start by confirming three baseline conditions. First, confirm the AR subledger is current so the aging report reflects the true open receivable population. Every invoice for the period should be posted, and every payment received through the as-of date should be applied to a specific invoice. Second, identify unapplied cash and unapplied credit memos, because they distort bucket totals and the GL tie-out. Third, confirm revenue recognition timing so invoices land in the correct period before you set the as-of date.

Several terms recur throughout the build process, and getting them straight now prevents many of the reconciliation errors covered later. Keep these definitions in mind as you work through the steps:

• AR aging report: The sorted view of open receivables grouped by days past due.
• Aging of accounts receivable formula: Days Past Due = As-Of Date − Invoice Due Date, with each invoice then assigned to a bucket based on the result.
• Contra-asset: An account that offsets a related asset account; the allowance for doubtful accounts is a contra-asset that reduces gross AR to net realizable value.
• Allowance for doubtful accounts: The estimated portion of AR that will not be collected, recorded as a contra-asset under GAAP.
• GL control account: The general ledger account that carries the aggregate AR balance; the subledger detail must tie to this account.
• Cross-aging: A lender concept that makes an entire customer balance ineligible for a borrowing base when a defined percentage of that customer’s receivables is past due.
• 10% rule: A common cross-aging threshold in asset-based lending credit agreements (see the cross-aging section below for how it is applied).

The process varies along several dimensions: company size, customer concentration, industry payment terms, geographic complexity, entity structure, and whether finance is handled internally or by an outsourced accountant. A staff accountant at a 15-person professional services firm and a controller at a 90-person manufacturer face different subledger volumes and complexity, but the same control logic applies.

An internal bookkeeper can produce and maintain the aging report when the subledger is clean and payment terms are straightforward. Fractional CFO or outsourced accounting support becomes necessary when the aging does not tie to the GL, when the allowance calculation requires judgment about forward-looking credit conditions, or when collection decisions require executive authority such as placing a customer on credit hold or negotiating a payment plan on a large balance.

The standard toolset for US SMEs is QuickBooks Online for accounting and AR reporting, NetSuite for larger or more complex organizations, and Excel for custom aging schedules, reconciliation workpapers, and allowance calculations. Condesa Financial Group works across all three environments.

Get Help Building Your Aging Report

How To Do An Accounts Receivable Aging Report

Step 1: Confirm the AR Subledger Is Complete

This step ensures the aging report reflects the true open receivable population. Review the open invoice list, the unapplied cash report, and the unapplied credit memo list. Completion looks like this: every invoice for the period is posted, every payment received through the as-of date is applied to a specific invoice, and every credit memo is either applied or flagged as a reconciling item. Old items that appear to be bad debt are frequently unapplied payments or unprocessed credit notes rather than genuine uncollectible balances. Investigate before writing anything off.

Step 2: Define the Aging Buckets and the As-Of Date

Set the as-of date to match the period close date. Running an aging report through August 31 against a balance sheet dated September 1 creates a timing difference that looks like a reconciling item but is really a parameter error. The aging basis, invoice date versus due date, must also be documented, because switching between the two makes trends and thresholds unreliable across periods. For collections work, aging from the due date is more useful because it directly measures how late a payment is relative to agreed terms. The standard buckets are Current, 1–30, 31–60, 61–90, and 90+ days past due.

Step 3: Build the Report in QuickBooks Online

In QuickBooks Online, navigate to Reports → Who Owes You → Accounts Receivable Aging Summary. Set the report date to the period-end date. Under Customize, switch the Aging Method from Current (today’s date) to Report Date. This setting is critical for reconciling the aging to the balance sheet. QuickBooks Online defaults to the Current aging method, which ages invoices based on today’s date; to reconcile the AR Aging Summary to the Balance Sheet, the aging method must be switched to Report Date. Filter by customer if needed, then export to Excel using the Export/Print dropdown. Save the customization so the Report Date setting persists for future runs.

The AR Aging Detail report, accessible from the same menu path, shows individual invoices with due dates and amounts. Use the Detail report for invoice-level collection work and the Summary report for the GL tie-out.

Step 4: Build the Same Report in Excel

Use Excel when you need a custom aging schedule for reconciliation workpapers, lender reporting, or allowance calculations. Set up columns for Customer, Invoice Number, Invoice Date, Due Date, Amount, Days Past Due, and Bucket.

The aging of accounts receivable formula for each invoice is:

Days Past Due = As-Of Date − Due Date

Assign each invoice to a bucket using a nested IF formula. A standard spreadsheet formula for assigning aging buckets is: =IF(TODAY()-D2<=0,”Current”,IF(TODAY()-D2<=30,”1-30″,IF(TODAY()-D2<=60,”31-60″,IF(TODAY()-D2<=90,”61-90″,”90+”)))), where column D holds the due date. Replace TODAY() with a fixed as-of date cell reference when building a period-end workpaper so the buckets do not shift when the file is reopened.

Step 5: Add a Worked Numeric Example

The following table illustrates how four invoices from three customers land in the standard buckets as of a single as-of date and how those amounts roll up.

Total AR: $32,300. Current: $12,000 (37%). 1–30: $8,200 (25%). 31–60: $3,100 (10%). 61–90: $9,000 (28%). 90+: $0. The 61–90 bucket concentration, driven entirely by one customer, is the immediate collection priority.

Step 6: Calculate the Allowance for Doubtful Accounts Using the Aging Method

Under GAAP, accountants and auditors use the aging of accounts to determine a reasonable amount to report as bad debt expense and to establish a sufficient balance in the allowance for doubtful accounts, which is a contra-asset deducted from accounts receivable on the balance sheet. The aging method assigns escalating uncollectibility percentages to each bucket and sums the results to arrive at the required ending allowance balance.

The following table applies escalating uncollectibility rates to the same balances, extended to include a 90+ bucket. Notice how the 61–90 bucket, though only 28% of total AR, drives most of the required allowance.

Required ending allowance balance: $2,640.

The adjusting journal entry depends on the existing allowance balance. The adjusting bad debt expense is the difference between the required ending allowance and the existing allowance balance; if the allowance already has a debit balance from write-offs exceeding prior estimates, that deficit is added rather than subtracted.

Scenario A — Allowance carries an existing credit balance of $800:

• Bad Debt Expense: $1,840 (= $2,640 − $800)
• Debit Bad Debt Expense $1,840 / Credit Allowance for Doubtful Accounts $1,840

Scenario B — Allowance carries an existing debit balance of $200 (prior write-offs exceeded the prior estimate):

• Bad Debt Expense: $2,840 (= $2,640 + $200)
• Debit Bad Debt Expense $2,840 / Credit Allowance for Doubtful Accounts $2,840

A common error is recording the required ending balance ($2,640) as the expense regardless of the existing balance. Ignoring the pre-existing balance leads to an overstated allowance. The rates above are illustrative. Under the Current Expected Credit Losses standard (ASC 326), companies must incorporate current economic conditions and reasonable forecasts alongside historical loss experience. A rising unemployment rate in the customer base or a known industry downturn must be factored in even if it has not yet appeared in the aging.

For tax purposes, the IRS does not permit a deduction for the GAAP allowance; most businesses must use the specific charge-off method, deducting a wholly worthless debt in the year it becomes worthless.

Step 7: Reconcile the Aging to the GL

The aging total must equal the AR control account balance on the accrual-basis balance sheet as of the same date. The next section walks through the reconciliation process in detail.

Step 8: Turn the Aging into Collection Action

Use the aging report to drive focused collection work. Prioritize collection effort by a combination of bucket and dollar value. A $40,000 invoice at 45 days outstanding usually deserves attention before an $800 invoice at 95 days. The aging also feeds credit limit decisions. A customer consistently appearing in the 61–90 bucket warrants a credit limit review before new work is authorized.

Collection action escalates by bucket: reminders for 1–30 days, direct contact for 31–60 days, senior escalation for 61–90 days, and formal demand or write-off evaluation for 90+ days. This structure keeps the team aligned on which customers to call, when to escalate, and how to document each step.

Get Support Improving Collections

How To Reconcile AR Aging To GL

AR aging-to-GL reconciliation is the most commonly skipped step in the month-end close and the most consequential. An aging report that does not tie to the general ledger cannot function as a control. The tie-out sequence is as follows.

1. Freeze the as-of date. Every report in the comparison, including the AR Aging Summary, the balance sheet, and any subledger extract, must share the same cutoff date and the same accounting basis (accrual).
2. Extract the aging total. Pull the grand total from the AR Aging Summary in QuickBooks Online with the aging method set to Report Date.
3. Pull the AR control account balance. Run the accrual-basis balance sheet as of the same date and locate the Accounts Receivable line.
4. Compare. If the two figures agree, the subledger ties to the GL. If they do not, trace the difference to its source rather than forcing a match with a journal entry.

The following reconciling items are the most common causes of a break between the aging total and the GL control account balance:

• Unapplied cash: A payment received and deposited but not yet matched to a specific invoice. Until it is applied, the customer still appears to owe the balance, the AR aging overstates what is outstanding, and the cash line does not tie. Resolution: apply the payment to the correct invoice using remittance advice.
• Credit memos not applied: Credits issued to a customer but not matched to an open invoice remain as separate negative balances. They reduce the GL control account but inflate the aging total if not included in the aging report parameters. Resolution: apply the credit memo to the related invoice or flag it as a reconciling item with a documented explanation.
• Direct GL journals to the AR control account: A journal entry posted directly to the AR control account changes the GL balance without creating a customer-level subledger entry. In QuickBooks Online, a journal entry to Accounts Receivable without a customer name on the AR line will move the control account without appearing in the aging. Resolution: reverse the journal and re-enter the transaction through the proper AR workflow, or document the journal as a supported reconciling item.
• Cutoff differences: An invoice dated in the wrong period, or a payment dated after the as-of date but applied to an invoice within the period, creates a timing difference. Resolution: verify transaction dates against source documents and correct the posting date.
• Duplicate invoices: A customer balance overstated by a duplicate invoice inflates both the aging and the GL. Resolution: void the duplicate and verify the customer balance against the original invoice.
• FX revaluation: For companies with foreign currency receivables, period-end revaluation entries adjust the GL control account to the closing exchange rate. The aging report may not reflect the revalued amount if it is denominated in the transaction currency. Resolution: confirm the aging report currency setting and book the revaluation entry to the appropriate FX gain/loss account.
• Payments recorded but not matched: A matched payment can still be wrong if it lands on the wrong invoice, creating a false sense of collection progress. Resolution: trace the payment to the specific invoice it was applied to and correct any misapplication.

A matching total does not guarantee a clean subledger. A matching AR Aging Summary total can still conceal problems such as an old customer credit, a payment applied to the wrong invoice, or a balance assigned to the wrong client, so the Aging Detail should be scanned even when totals agree.

Get Help Reconciling AR To GL

Common Mistakes, Watch Outs, And Troubleshooting

Even a well-built aging report can go wrong in predictable ways. The issues below explain most breaks between the aging total and the GL control account and often trace back to earlier steps in the process.

Common Mistakes

• Aging that does not tie to the GL. An aging report that has never been reconciled to the AR control account cannot serve as a control document, a lender exhibit, or a basis for the allowance calculation.
• Unapplied cash distorting the Current bucket. Payments sitting in a suspense or unapplied cash account make the Current bucket appear larger than it is and leave older invoices appearing open when they are already paid.
• Credit memos omitted from the report. Common items that distort an AR aging report include credit notes issued and never applied. Omitting them overstates gross receivables and produces an allowance that is too large.
• Using invoice date instead of due date. Reports that age from the invoice date make every customer look delinquent. A Net 60 invoice issued 45 days ago is not overdue, yet invoice-date aging places it in the 31–60 bucket.
• Ignoring cross-aging. A customer with a large current balance and a smaller 90+ day balance may look acceptable in the aging summary. Cross-aging analysis reveals that the entire balance may be at risk and ineligible as collateral if the past-due share exceeds the credit agreement threshold.
• Applying a single universal bad-debt percentage. A flat rate applied to total AR ignores the fact that a 90+ day invoice is materially more likely to become uncollectible than a current invoice. The aging method applies escalating rates by bucket to avoid this distortion.
• Failing to update the allowance when the aging changes. An allowance calculated in January and left unchanged through June is a stale number and does not reflect current collection risk.

Watch Outs

• A customer who appears in multiple buckets simultaneously, such as one invoice current and one 52 days overdue, signals credit risk. This pattern is worth addressing before extending further credit.
• Non-trade items such as tax refunds, employee advances, and intercompany balances sitting in the AR subledger distort the aging and the allowance. Reclassify them to the appropriate account.
• Weak documentation, unclear ownership of reconciling items, and an allowance that has not been updated in multiple periods often indicate an underperforming incumbent accountant. These are process failures rather than isolated data anomalies.

Troubleshooting

• If the aging does not tie to the GL, work through the reconciling items in order: unapplied cash first, then credit memos, then direct GL journals, then cutoff differences, then duplicates, then FX revaluation.
• If the variance amount matches a specific transaction, such as a difference equal to a single invoice amount, search the GL for that amount posted directly to the AR control account without a customer reference.
• If the aging ties but the allowance calculation produces an unexpected result, verify that the aging basis, due date versus invoice date, is consistent with prior periods and that the bucket percentages reflect current collection experience rather than a rate set years ago.

Review Your AR Process With Condesa

How To Evaluate Progress

Use a short set of indicators to confirm that your accounts receivable aging analysis process is working as intended over time.

• The aging ties to the GL every month without a forced journal entry.
• Bucket distributions are consistent period over period or improving, meaning the proportion of AR in older buckets is flat or declining.
• Unapplied cash and unapplied credit memos are cleared before the allowance is calculated each close.
• The allowance for doubtful accounts is recalculated from the current aging at every close, rather than carried forward from a prior period.
• Collection priorities are documented and assigned, not left to individual memory.

Two related metrics help you interpret results. The AR aging report shows the distribution of open receivables by age at a point in time. The AR turnover ratio, calculated as Credit Sales divided by Average Accounts Receivable, measures how many times receivables are collected and converted to cash during a period. A higher AR turnover ratio indicates faster collection. The aging report serves as the diagnostic tool, and the turnover ratio serves as the summary metric. A deteriorating aging mix will eventually show up as a declining turnover ratio, but the aging report reveals the problem earlier and at the customer level.

For bad-debt estimation, the aging method and the percentage-of-sales method serve different purposes. The aging method is balance-sheet-focused and produces a required ending allowance balance tied to the current receivable population. The percentage-of-sales method is income-statement-focused and applies a historical loss rate to current-period credit sales. Under CECL (ASC 326), the percentage-of-sales method is generally not appropriate as the sole estimation approach because it does not produce a balance-sheet-focused allowance. The aging method is the standard for SMEs producing GAAP-compliant financial statements.

What Is A Good AR Aging Percentage?

Good AR aging percentages depend on your industry, terms, and customer base rather than a single universal benchmark. The most useful comparison is against your own historical data, tracking whether the proportion in each age category is improving or worsening over time.

Key variables include customer concentration, industry payment terms, and month-over-month trend direction. A construction firm with 18–25% of receivables in the 90+ bucket may be operating normally given industry payment cycles. A construction firm at 90 days DSO and a SaaS company at 90 days DSO are not the same story, and neither is automatically a problem. A professional services firm with the same 90+ concentration has a collections problem.

The most diagnostic signal is the direction of the bucket mix rather than the total AR balance. A flat total AR balance with a mix shifting rightward into older buckets signals deteriorating collections even though the headline receivables number has not moved. A rising 61–90 and 90+ bucket across two or three consecutive months is the warning sign, regardless of whether the total AR figure looks stable.

As a general orientation, a financially healthy SME should have 80%+ of total AR in the current bucket, under 12% in 31–60 days, under 5% in 61–90 days, and under 3% in 90+ days. Treat these figures as starting points for a conversation rather than pass/fail thresholds. Industry-specific distributions vary materially. Technology and SaaS businesses typically show 70–80% current with 3–6% in the 90+ bucket, while construction businesses show 45–60% current with 8–15% in the 90+ bucket.

What Is Cross-Aging In Accounts Receivable?

Cross-aging is the rule that makes an entire customer’s receivable balance ineligible as collateral when a defined percentage of that customer’s total outstanding balance is past due, including invoices that are currently within terms.

The OCC Comptroller’s Handbook on Asset-Based Lending states that most underwriting agreements specify that all of a party’s accounts are designated ineligible collateral when any, or some percentage, of that party’s receivables become ineligible, a mechanic referred to as cross-aging. The threshold is set by the credit agreement rather than by statute. The OCC glossary notes that cross-aging is sometimes called the 10% rule because 10% of an individual party’s accounts is a common delinquency threshold, but the threshold is deal-specific.

The practical consequence can be significant. Reducing a customer’s past-due bucket from $200,000 to $140,000 can move that customer from 28.6% past due to 21.9% past due, restoring eligibility on $500,000 of current balance, meaning $60,000 of collections can recover $500,000 of borrowing base availability through the cross-aging effect. For any SME with an asset-based lending facility, the AR aging report functions as both a collections tool and a direct driver of available credit.

Advanced Considerations And Next Steps

Once the core accounts receivable aging analysis process is in place, you can shift attention to automation and integration. At this stage, the report builds cleanly, ties to the GL, and supports a defensible allowance calculation.

In QuickBooks Online, saved report customizations and scheduled email delivery reduce the manual effort of producing the aging each period. In NetSuite, subledger-to-GL reconciliation can be automated through saved searches and period-end close checklists. Integrating AR workflows with spend management tools such as Ramp and payroll platforms such as Gusto creates a more complete picture of cash in and cash out, which then feeds into cash flow forecasting built from the aging report.

For multi-entity or multi-currency groups, standardizing bucket definitions, reconciliation steps, and allowance assumptions across entities improves comparability and speeds consolidation. At that scale, many SMEs partner with a fractional CFO or controller to design the AR process, train internal staff, and oversee the first few closes.

Schedule A Conversation About Your AR Process