Financial
Allowance for Doubtful Accounts & Bad Debt Expense (AR aging SUMPRODUCT)
Illustrate an aging-based allowance estimate and the adjustment from an existing unadjusted allowance balance.
Why the aging method computes TARGET ending allowance—not period expense directly!
Multiplying each aging-bucket balance by its supplied estimated loss rate gives an illustrative target allowance of 10,800 on gross receivables of 190,000. These are assumed rates, not prescribed accounting rates. Historical losses alone may not capture current conditions and forward-looking expectations; the spreadsheet does not establish compliance with an accounting standard.
To book the month-end adjusting journal entry (Debit Bad Debt Expense, Credit Allowance for Doubtful Accounts), subtract the existing unadjusted Allowance balance (E2): AdjustingExpense = RequiredEndingAllowance - UnadjustedAllowance! Watch the classic controller trap: if heavy write-offs left Allowance with a net DEBIT balance (entered as E2 = -1500), subtracting -1500 properly INCREASES Bad Debt Expense to $10,800 - (-$1,500) = $12,300 to restore the $10,800 credit reserve!
Use nonoverlapping buckets on one reporting date, nonnegative receivable balances and rates from 0 to 1. E2 is the unadjusted allowance after write-offs and other entries. A positive adjustment increases the credit allowance and expense; a negative adjustment reduces them.
| AR Aging Bucket (A) | Gross AR (B) | Assumed loss rate (C) | Required Reserve |
|---|---|---|---|
| 0–30 days past due | $120,000 | 1.0% | $1,200 |
| 31–60 days past due | $40,000 | 4.0% | $1,600 |
| 61–90 days past due | $20,000 | 15.0% | $3,000 |
| 91+ days past due | $10,000 | 50.0% | $5,000 (Sum = $10,800; Net AR = $179,200) |
Estimated ending allowance
SUMPRODUCT weights every aging bucket by its expected credit loss rate in one formula—no row-by-row reserve column required.
=SUMPRODUCT(B2:B5, C2:C5)Period Bad Debt Expense adjusting entry and Net Realizable AR
Enter existing credit balance in E2 as positive (+3200 -> $7,600 expense) and unadjusted debit balance as negative (-1500 -> $12,300 expense).
=SUMPRODUCT(B2:B5, C2:C5) - E2Net receivables after the estimated allowance
Subtracts the modeled target allowance from gross receivables.
=SUM(B2:B5)-SUMPRODUCT(B2:B5,C2:C5)Try the example
Use the original values or change them to test your understanding.