Enter each open invoice and its due date in the aging template above: days overdue are counted up to today, balances fall into aging buckets, an allowance for doubtful accounts is calculated at rates you set, and a collection priority list is built for your sales reps.
In the example, receivables total SAR 65,276.50, of which SAR 40,006.00 is more than 30 days overdue and SAR 12,720.25 more than 90 days. At estimated rates of 1%, 3%, 10%, 25% and 50%, the suggested allowance is SAR 6,016.24.
How to use the accounts receivable aging template
- Copy from your books every invoice that has not been paid in full: the customer, invoice number, due date and balance.
- Days overdue, the bucket and the allowance rate appear for each invoice, and overdue rows are highlighted.
- In Advanced mode, change the allowance rates in the settings and add the sales rep and any part payments.
- Open the Collection priority list, choose the minimum days overdue, and hand it to the sales reps.
You can download the aging report in Excel; days overdue are calculated with TODAY(), so the buckets update each time you open the file.
What is an accounts receivable aging report?
An accounts receivable aging report spreads unpaid customer balances across buckets by the number of days past the due date. It measures collection quality: the larger the old buckets, the weaker the collection and the more that may never be recovered, and it is the starting point for the allowance for doubtful accounts.
How to prepare an aging schedule
Subtract each invoice's due date from today, place its balance in a bucket (0–30, 31–60, 61–90, 91–180 or over 180 days), then total each bucket. Three customers from the example: Nesma Cafe owes SAR 4,280.00, 25 days overdue, in the first bucket; Al Madar Cafe owes SAR 6,915.75, 41 days overdue, in the second; and Rukn Al Qahwa Cafe owes SAR 5,640.25, 120 days overdue, in the 91–180 bucket.
Allowance for doubtful accounts
The allowance for doubtful accounts is an estimate of receivables that may not be collected, charged as an expense in the same period. The simple method multiplies each invoice balance by its bucket rate: Rukn Al Qahwa Cafe gives 5,640.25 × 25% = SAR 1,410.06, and Al Madar Cafe SAR 207.47. The rates are an accounting policy the business sets from its own history and reviews each period. The entry for the example total: debit bad debt expense SAR 6,016.24, credit allowance for doubtful accounts with the same amount.
In Advanced mode, the Expected credit loss matrix sheet is closer to IFRS 9; with its figures and a 10% adjustment, the expected loss is SAR 5,789.52.
How to improve collection after reading the report
- 0–30 days: a friendly reminder before and shortly after the due date.
- 31–60: a call from the sales rep, a written statement of account and a payment date.
- 61–90: stop credit sales to the customer until they pay or agree a schedule.
- Over 90: a formal written demand, then legal routes after taking legal advice.
Common mistakes
- Entering the customer's total balance on one row, so old invoices hide inside recent ones.
- Forgetting part payments, so the debt looks larger than it is.
- Writing off a debt as soon as it reaches an old bucket; an allowance is an estimate, not a write-off.
Accounting notes for Saudi Arabia
The allowance rates in the template are not regulatory rates. Businesses that apply the international standards endorsed in the Kingdom by the Saudi Organization for Chartered and Professional Accountants (SOCPA) measure expected credit losses under IFRS 9; check with your accountant or auditor. Deducting the allowance for zakat or tax has conditions that differ from the accounting treatment, so refer to the Zakat, Tax and Customs Authority (ZATCA).
When the sales rep does not know which invoice to collect first
At Microtec we build Microtec's ERP accounting system, which brings sales, purchasing, inventory and finance into one place, so customer balances come from the invoices and receipts already recorded.
Frequently asked questions
Which aging bucket is considered high risk?
Over 90 days for most businesses, because the chance of collection drops after that. Your credit terms matter, though: a customer on 60-day terms becomes a concern sooner than one on 90-day terms.
What is the difference between accounts receivable and accounts payable?
Receivables are what customers owe you on credit sales; payables are what you owe suppliers. This template is for receivables, and the same sheet works for payables aging if you enter suppliers instead of customers.
Is the aging report monthly?
It is prepared at each month end with the closing of the books, and the sales manager reviews it weekly when credit sales are heavy. The Excel file counts days from the date you open it, so it works on any day.
How do I change the allowance rates?
Switch to Advanced mode and change the rate for each bucket in the settings, and the allowance is recalculated for every invoice. In the Excel file the rates are on the Settings sheet.
What is the difference between an allowance and a write-off?
An allowance is an estimate made before you are sure a debt will not be collected, and the debt stays in your books. A write-off removes a debt that will definitely not be collected, and it is charged against the allowance already made.