OracleFree calculatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS AR Aging Bad Debt Reserve Calculator

The aging method is the simplest defensible way to size an allowance for doubtful accounts: multiply each aging bucket by a historical loss rate and add them up. This tool does that math and shows how sensitive the result is to your oldest bucket.

Your numbers

Current balance

1 to 30 days past due

31 to 60 days past due

61 to 90 days past due

Over 90 days past due

Loss rate: current

Loss rate: 1 to 30

Loss rate: 31 to 60

Loss rate: 61 to 90

Loss rate: over 90

Your results

Required allowance

$123,200

Allowance as share of AR

4.6%

Total receivables

$2,665,000

Net receivables

$2,541,800

Share of AR past due

32.5%

Loss rates are inputs you must support with your own write-off history. Under US GAAP CECL and IFRS 9 expected credit loss models, rates should also reflect forecasts. Confirm with your auditors.

Save results:

Allowance vs loss rate on over 90 bucket

Sweeps Loss rate: over 90 from 0 to 100, holding your other inputs fixed.

Aging analysis automation

Optionally discuss pulling aging buckets and write-off history into a private reporting view.

Optional. The tool and every export work without this. No spam.

How to use it

Run the Receivables aging report (such as the Aging - 7 Buckets report or the Aging - 4 Buckets report) as of your period end, ideally by operating unit and functional currency. Enter the five bucket totals, then enter loss rates derived from your own history of write-offs by bucket. Look at the allowance and the reserve percentage, then compare to the current balance in your allowance account in the general ledger to see the adjusting entry required.

Exclude disputed or credit memo items you intend to handle separately.
Calculate rates by customer segment if risk differs widely.
Document the source of every rate for the auditors.

The method

Allowance equals the sum over buckets of balance times loss rate. Loss rates come from historical data: for each bucket, the share of balances that eventually became uncollectible over a look-back of 2 to 3 years. The reserve percentage is allowance divided by total receivables, and net receivables is total minus allowance. Many companies layer specific reserves for known troubled customers on top of the aging-based amount, which this tool does not include.

Reading it and common mistakes

The oldest bucket usually dominates the answer, which is why the chart sweeps that rate. If a 10-point change in the over-90 rate moves the reserve by more than your materiality threshold, invest in better loss data for that bucket. Common mistakes include mixing currencies, using on-account cash and unapplied receipts incorrectly, and aging by invoice date when your policy is to age by due date.

Make sure unapplied cash is netted consistently in the buckets.
Review at least quarterly because loss rates drift.
Record the entry in the period, not retroactively.

Frequently Asked Questions

What loss rates should I use?

Use your own write-off history by aging bucket over two to three years, adjusted for current conditions. Industry rules of thumb are only a placeholder until you have your own data, and auditors will ask for support.

Does this satisfy CECL or IFRS 9?

An aging matrix is an accepted practical approach for trade receivables under both frameworks when rates are supported by history and adjusted for forecasts. Your accountants must confirm the specifics for your entity.

Which Oracle report supplies the buckets?

Oracle Receivables provides aging reports with 4 or 7 configurable buckets. Use the one configured to match your policy, run as of the period end, and reconcile the total to the AR control account.

How do I handle specific reserves?

Add them after this calculation for named high-risk customers, and remove those balances from the buckets above to avoid double counting. Document the rationale for each specific reserve.

If you want aging and reserve analysis refreshed daily from your Receivables data with the reasoning visible, we can set up a read-only private view over your EBS database.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Oracle tools

See all

Back to All Tools