Accounts Receivable (AR) Aging Report & CECL Bad Debt Allowance Calculator Excel Workpaper
01_Executive_Summary_KPIs — Interactive Spreadsheet
| # | A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ACCOUNTS RECEIVABLE AGING & CECL BAD DEBT ALLOWANCE WORKPAPER | ||||||||||
| 2 | US GAAP ASC 326 / IFRS 9 Valuation | Entity: Apex Global Technologies Inc. | Period Ended: September 30, 2026 | WP Ref: WP-AR-100 | ||||||||||
| 3 | |||||||||||
| 4 | TOTAL GROSS AR PORTFOLIO | CURRENT / NOT PAST DUE | DELINQUENT > 60 DAYS | TOTAL CECL ALLOWANCE | NET REALIZABLE VALUE (NRV) | ||||||
| 5 | $1,526,800.00 | $307,700.00 | $466,100.00 | $321,464.63 | $1,205,335.37 | ||||||
| 6 | |||||||||||
| 7 | 1. PORTFOLIO AGING BRACKET SUMMARY & ASC 326 CECL ALLOWANCE MATRIX | ||||||||||
| 8 | Aging Bracket | Overdue Range | Invoice Count | Gross AR Balance ($) | % Portfolio | Base Loss % | Macro Factor | CECL Loss % | Required Reserve ($) | Net Carrying Value ($) | |
| 9 | Current / Not Yet Due | 0 Days | 3 | $307,700.00 | 20.2% | 0% | 1.15 | 0% | $1,415.42 | $306,284.58 | |
| 10 | 1 – 30 Days Past Due | 1 – 30 Days | 5 | $506,900.00 | 33.2% | 2% | 1.15 | 2% | $10,492.83 | $496,407.17 | |
| 11 | 31 – 60 Days Past Due | 31 – 60 Days | 4 | $246,100.00 | 16.1% | 6% | 1.15 | 6% | $15,578.13 | $230,521.87 | |
| 12 | 61 – 90 Days Past Due | 61 – 90 Days | 4 | $178,200.00 | 11.7% | 16% | 1.15 | 18% | $32,788.80 | $145,411.20 | |
| 13 | 91 – 120 Days Past Due | 91 – 120 Days | 3 | $105,400.00 | 6.9% | 42% | 1.15 | 48% | $50,908.20 | $54,491.80 | |
| 14 | Over 120 Days Delinquent | > 120 Days | 7 | $182,500.00 | 12.0% | 75% | 1.15 | 86% | $157,406.25 | $25,093.75 | |
| 15 | Specific Impairment Reserve | Specific Risk | 3 | Included Above | - | - | - | - | $52,875.00 | - | |
| 16 | TOTAL PORTFOLIO SUMMARY | 0 – 120+ Days | 26 | $1,526,800.00 | 100.0% | - | - | 21% | $321,464.63 | $1,205,335.37 | |
| 17 | |||||||||||
| 18 | 2. ASC 326 ALLOWANCE ROLLFORWARD & GL RECONCILIATION | 3. DSO & COLLECTION EFFICIENCY | |||||||||
| 19 | Beginning Allowance for Credit Losses (June 30, 2026) | Prior Period Trial Balance Tie-Out | Trailing 90 Days Net Credit Sales ($) | ||||||||
| 20 | Add: Current Period Bad Debt Provision / Expense (Q3 2026) | $278,764.63 | Calculated Adjusting JE Required | Days in Analysis Period | |||||||
| 21 | Less: Actual Write-Offs / Uncollectible Charge-Offs | Board / Credit Committee Authorized | Average Daily Credit Sales ($) | $42,666.67 | |||||||
| 22 | Add: Subsequent Recoveries of Written-Off Receivables | Deposited & Credited to Allowance | Days Sales Outstanding (DSO) | 35.8Days | |||||||
| 23 | Ending Required Allowance for Credit Losses | $321,464.63 | ASC 326 / CECL Valuation Balance | Best Possible DSO (BPDSO - Current AR) | 7.2Days | ||||||
| 24 | General Ledger Balance (GL #11050 Trial Balance) | $321,464.63 | Tied to Ending Schedule Balance | Delinquent Days Gap (DSO - BPDSO) | 28.6Days | ||||||
| 25 | Reconciliation Variance (Schedule vs. General Ledger) | $0.00 | Target Variance = $0.00 | Target Credit Policy Limit | |||||||
| 26 | GL Tie-Out & Audit Verification Status | TIED — ZERO VARIANCE (PASS) [ GL ✓ ] | Credit Performance Compliance | COMPLIANT ✓ | |||||||
Free Instant Download
No email signup • Unlocked formulas
Built-In Excel Formulas & Calculation Logic
Unlocked native Microsoft Excel functions pre-programmed inside this workbook:
=MAX(0, $E$4 - G11) Compares each invoice contractual due date (G11) against the global portfolio cutoff as-of date in cell $E$4. If the invoice is not yet due, MAX(0, ...) returns 0 days, preventing negative overdue values and automating bracket sorting.
=IF(AND(I11>=31, I11<=60), M11, 0) Evaluates the calculated overdue days (I11) against specific aging tier thresholds (e.g., 31 to 60 days). If true, it allocates the full open receivable balance (M11) into that respective aging bucket column; otherwise, it outputs $0.00.
=ROUND(C6 * D6, 4) Multiplies the entity's 3-year historical default loss rate (C6) by the macroeconomic forward-looking multiplier (D6, e.g., 1.15x for recessionary or interest rate risk) to compute the legally defensible CECL lifetime loss rate per US GAAP.
=EndingRequiredAllowance - PriorAllowance + ChargeOffs - Recoveries Calculates the exact debit to Bad Debt Expense (GL #60500) and credit to Allowance for Credit Losses (GL #11050) required at period end to align the general ledger balance with the comprehensive CECL aging valuation model.
Full Workbook Print Layout Preview (.XLSX)
Overview & Industry Standard Alignment
Managing accounts receivable and assessing credit risk is a foundational responsibility for corporate controllers, CPAs, and treasury executives. In traditional accounting environments, finance teams relied on static spreadsheet exports from accounting packages that failed to account for forward-looking economic conditions or customer-specific impairment signals. Under US GAAP ASC 326 (Financial Instruments - Credit Losses), entities are strictly required to transition from the legacy incurred loss model to the Current Expected Credit Losses (CECL) methodology. This requires estimating lifetime expected credit losses even on brand-new, non-delinquent trade receivables based on historical loss experience, current macroeconomic indicators, and reasonable supportable forecasts. The SheetAtlas Accounts Receivable Aging Report & CECL Bad Debt Allowance Calculator Excel Workpaper bridges the gap between raw ERP transactional subledgers and audit-ready corporate financial statements. Engineered by senior CPAs with extensive Big-4 and controller experience, this four-sheet model combines granular invoice-level tracking with institutional CECL valuation logic. Users can input or export hundreds of individual customer billing lines, establish custom credit terms (Net 30, Net 45, Net 60), define a global portfolio cutoff date, and observe the workbook dynamically assign receivables into standard aging brackets with zero manual copy-pasting. Beyond basic aging distribution, the workbook features an integrated ASC 326 loss-rate matrix that pairs rolling 3-year historical loss rates with forward-looking qualitative multipliers, dedicated specific identification reserves for bankrupt or legally disputed accounts, a complete Allowance for Credit Losses rollforward schedule, Days Sales Outstanding (DSO) and Collection Effectiveness Index (CEI) metrics, and automated month-end adjusting journal entries formatted for direct upload to NetSuite, QuickBooks Enterprise, SAP, or Sage Intacct.
Practical Operational Use Cases:
Corporate Month-End Close & Trial Balance Tie-Out
Used by corporate accounting teams during the monthly financial close to reconcile the accounts receivable subledger to GL Account #11000, calculate the required CECL allowance true-up, and generate balanced adjusting journal entries for Bad Debt Expense (GL #60500) and Allowance for Credit Losses (GL #11050).
External Audit Substantive Testing & Big-4 Workpaper Documentation
Serves as primary audit workpaper evidence (WP-AR-100) for external financial statement audits. Includes standardized Big-4 audit tickmarks (✓, GL, BC, SC, AW, CECL, PY), mathematical cross-footing verifications, sample confirmation logs, and formal accounting policy disclosures.
Credit Committee Delinquency Review & Credit Limit Adjustments
Utilized by Credit Directors and AR Managers to evaluate portfolio aging trends, monitor overdue accounts exceeding 60 and 90 days, identify deteriorating customer credit ratings, place delinquent accounts on credit hold, and approve formal uncollectible write-offs.
M&A Financial Due Diligence & Quality of Earnings (QofE)
Employed by private equity investors, transaction advisory CPAs, and corporate development analysts to assess target company working capital quality, analyze historical bad debt write-off patterns, verify net realizable value (NRV), and detect hidden revenue collectibility risks.
Designed For:
What’s Inside the Workbook (Tab-by-Tab Breakdown)
Worksheet 1
Executive dashboard featuring 5 high-impact KPI summary cards (Total Gross AR, Current/Not Due, Delinquent >60 Days, Required CECL Reserve, Net Realizable Value), an aging bracket distribution matrix, an ASC 326 Allowance Rollforward schedule with GL tie-out verification, and Days Sales Outstanding (DSO) / Collection Effectiveness Index (CEI) metrics.
Worksheet 2
Granular transaction subledger accommodating individual customer invoices with dynamic credit terms, automated due date calculation, days overdue formulas, automatic allocation across 6 aging tiers (Current, 1-30, 31-60, 61-90, 91-120, >120 days), specific impairment rate overrides, and mathematical cross-foot checks.
Worksheet 3
US GAAP ASC 326 / IFRS 9 Expected Credit Loss calculation engine combining historical 3-year loss rates with forward-looking macroeconomic factors, linked directly to subledger balances, plus a standardized Month-End Adjusting Journal Entry schedule with automated debit/credit balancing tie-outs.
Worksheet 4
Institutional accounting governance schedule containing standardized CPA audit tickmarks, substantive testing procedures, formal corporate accounting policy notes on CECL segmentation, specific impairment triggers, write-off approval authorization thresholds, and month-end close procedures.
Practitioner Tips & Best Practices
-
✓
Separate Specific Impairment Reserves from Collective Aging Pools: Under ASC 326, accounts with distinct credit deterioration (such as Chapter 11 bankruptcy filings or active litigation) must be evaluated individually rather than subjected to collective aging pool loss rates. Tag these accounts in Column T of Tab 02 to apply a 50% to 100% specific reserve without distorting general portfolio statistics.
-
✓
Document Supportable Macroeconomic Multipliers for Auditors: When applying forward-looking macroeconomic multipliers (e.g., 1.15x) in Tab 03, maintain a documented file memo citing credible economic benchmarks—such as Federal Reserve interest rate projections, industry default rates, or regional GDP forecasts—to withstand external audit scrutiny.
-
✓
Perform Subsequent Cash Collections Cutoff Testing: External auditors prioritize cash collected subsequent to the balance sheet date as primary substantive evidence of collectibility. Track subsequent receipts through the 20th of the following month to validate that receivables categorized as Current or 1-30 days have successfully converted to cash.
-
✓
Reconcile Unapplied Customer Cash & Credit Memos: Ensure unapplied customer cash deposits or unallocated credit memos are matched against open invoices before finalizing the aging schedule. Leaving unapplied credits in the subledger can artificially suppress aging tiers or create negative balance anomalies that distort the CECL reserve calculation.
-
✓
Enforce Strict Dual-Approval Thresholds for Bad Debt Write-Offs: Never allow uncollectible balances to be removed from the subledger without formal authorization. Maintain strict internal control thresholds: write-offs under $10,000 approved by the Corporate Controller, and write-offs exceeding $10,000 approved by the CFO and Credit Committee.
Frequently Asked Questions
What is the difference between the legacy Incurred Loss Model and ASC 326 CECL?
Under the legacy US GAAP incurred loss model (ASC 450), companies were only permitted to record a bad debt reserve when an uncollectible loss event was considered 'probable and estimable'—typically after an account became significantly past due. Under ASC 326 (Current Expected Credit Losses), entities must estimate lifetime expected credit losses on all trade receivables starting on day one, including newly issued, current invoices, by incorporating historical default data, current credit conditions, and reasonable forward-looking economic forecasts.
How does the practical loss-rate aging matrix method work under CECL?
FASB ASC 326 specifically permits non-financial entities to use a practical loss-rate matrix based on aged trade receivable categories. In this workbook, historical 3-year write-off data is calculated as a percentage of gross billings for each aging tier (e.g., Current: 0.4%, 1-30 days: 1.8%, 31-60 days: 5.5%, etc.). This historical rate is multiplied by a forward-looking macroeconomic factor (e.g., 1.15x) to derive the effective CECL loss rate applied to the open balance in each bracket.
Can I customize the aging brackets and credit terms in this Excel workpaper?
Yes. The workbook is engineered with 100% transparent Excel formulas and zero locked macros. You can easily adjust the day thresholds in the formula logic or add customized credit terms (such as Net 15, Net 90, or 2/10 Net 30) by modifying the dynamic formula in Column G of the Detailed Invoice Aging Log.
What is the correct journal entry to adjust the Allowance for Credit Losses at month-end?
At month-end, the adjusting journal entry debits GL Account #60500 (Bad Debt Expense / Credit Loss Provision) on the Income Statement and credits GL Account #11050 (Allowance for Credit Losses) on the Balance Sheet. If previous reserves exceeded actual requirements, a recovery entry is made by debiting the Allowance and crediting Bad Debt Expense.
How is Days Sales Outstanding (DSO) calculated in this model?
Days Sales Outstanding is calculated in Tab 01 using the standard credit management formula: DSO = (Total Gross Accounts Receivable / Trailing 90 Days Net Credit Sales) * 90 Days. The model also calculates Best Possible DSO (BPDSO), which evaluates only non-delinquent current receivables to measure the operational collection gap caused by delinquent accounts.
How does the workbook handle subsequent cash recoveries of written-off accounts?
When a customer pays an invoice that was previously written off, US GAAP requires the cash receipt to be credited back to the Allowance for Credit Losses rather than recorded as miscellaneous income. In this workbook, subsequent recoveries are tracked in the Allowance Rollforward (Tab 01) and reflected in Adjusting Journal Entry AJE-03 (Tab 03) by debiting Operating Cash and crediting the Allowance.
Is this template compatible with Google Sheets and cloud spreadsheets?
Yes. The workbook is built using universal Microsoft Excel functions (SUM, SUMIFS, COUNTIF, COUNTIFS, MAX, ROUND, IF, IFERROR) and standard OpenXML styling without VBA or external add-ins. It opens seamlessly in Microsoft Excel 2016+, Microsoft 365, Google Sheets, LibreOffice, and Apple Numbers.