Summary
The best method to analyze accounts receivable combines three things: an aging report that buckets unpaid invoices by age, a small set of ratios (DSO, AR turnover and CEI) tracked over time, and automation that keeps the underlying data current. Spreadsheets and accounting software can produce the numbers, but they do not flag risk or run the follow-up.
- Core metrics: DSO (average days to collect), the AR turnover ratio (net credit sales divided by average receivables) and CEI (the percentage of collectable receivables actually collected).
- Core method: Aging analysis buckets unpaid invoices into 1 to 30, 31 to 60, 61 to 90 and over 90 days, so follow-up goes to the oldest and riskiest first.
- Where automation helps: Paidnice reads DSO, days sales overdue, on-time payment rate and an aged receivables report live from Xero or QuickBooks Online, and runs the reminders and late fees that move them.
What is accounts receivable analysis?
Accounts receivable analysis is the review of the money customers owe on credit sales to measure how efficiently a business collects it, where the risk of non-payment sits, and what that means for cash flow. It answers three questions: how long payment takes, how much of what was collectable came in, and which invoices and customers need attention now.
The output is a handful of numbers that a business owner, bookkeeper or finance lead reads every month against last month and against the terms offered. Timely payment means the business covers wages, suppliers and growth from its own cash. Unpaid invoices push the cost onto an overdraft, and the analysis is how you see that happening before the bank balance does.
Free: Payment reminder email templatesCopy the reminder emails for before, on and after the due date.What are the key accounts receivable analysis metrics?
The three core accounts receivable analysis metrics are days sales outstanding (DSO), the accounts receivable turnover ratio and the collection effectiveness index (CEI). DSO measures speed, turnover measures how often the balance is collected, and CEI measures how much of the collectable balance actually came in.
| Metric | Formula | What good looks like |
|---|---|---|
| Days sales outstanding (DSO) | (Accounts receivable / Credit sales) x days in the period | Within about 1.5 times your payment terms; under roughly 45 days on net 30 |
| Accounts receivable turnover | Net credit sales / Average accounts receivable | Higher is better; a falling ratio means receivables are growing faster than sales |
| Collection effectiveness index (CEI) | (Begin AR + Sales - End AR) / (Begin AR + Sales - End current AR) x 100 | Above 80%; near 100% is strong |
Days sales outstanding (DSO) is the average number of days between a credit sale and the cash arriving. A business with $200,000 of receivables and $600,000 of credit sales over a 90-day quarter has a DSO of 30 days. The DSO formula guide covers the calculation and its traps.
The accounts receivable turnover ratio shows how many times a business collected its average receivables balance in a period. A business on net 30 terms that collects on time turns its receivables about twelve times a year. Read it quarterly.
The collection effectiveness index (CEI) is the quality measure. DSO can fall because sales rose; CEI only rises when collections improve. Take receivables at the start of the period plus credit sales, subtract receivables at the end, and divide by the same figure with only the current (not yet due) receivables subtracted. Multiply by 100. A CEI of 85% means 85 cents of every collectable dollar was collected. The CEI dictionary entry has an example.
What is aging analysis?
Aging analysis groups every unpaid invoice by how long it has been outstanding, in buckets of 1 to 30, 31 to 60, 61 to 90 and over 90 days, and reports the balance and invoice count in each. It is the method that names the customers behind the ratios.
Read the aging report two ways. By bucket, the over-90-day balance is the money most likely never to arrive and the first place to look for next quarter's write-offs. By customer, the report shows who is consistently late, who has slipped from 30 to 60 days, and which invoices need a call rather than a fourth email. Both Xero and QuickBooks Online produce an aged receivables report; the AR aging analysis tool builds the buckets from a list of invoices.
Aging is also the input to the other methods. Trend analysis tracks DSO, CEI and the over-90 bucket month by month so a slow drift shows up early. Concentration analysis checks how much of the balance sits with the top few customers, because one large slow payer can hide a healthy ledger, or a healthy customer can hide a failing one.
Group receivables by age. Of invoices 7 days overdue, 1 in 5 is still unpaid 180 days after the due date. At 30 days overdue it is 2 in 5, and at 90 days 3 in 4.
What are the traditional methods to analyze receivables?
The two traditional methods of receivables analysis are a spreadsheet built from exported invoice data and the standard reports inside accounting software. Both produce the numbers; neither keeps them current or does anything about them.
Method 1: a spreadsheet
A spreadsheet stops working as a receivables analysis method once the invoice count grows, because every number in it is only as current as the last manual update. Entering invoices, payments and due dates manually takes time that scales with volume, one broken formula or misplaced cell reference produces a wrong DSO that nobody notices, and the data lives apart from the ledger, so the spreadsheet and the accounting file disagree by the end of the month.
A spreadsheet can hold every formula above. It cannot flag a customer who has slipped into the 61 to 90 bucket, and it cannot send the reminder. Better Tuition Academy, a tutoring business in Australia with 700 students, ran collections manually until the follow-up time became the problem itself; automating reminders and late fees freed the hours and moved late payers onto time.
Method 2: accounting software reports
Accounting software gives you an accurate aged receivables report and customer balances, and stops there. Xero and QuickBooks Online both show who owes what and how overdue it is, but DSO, AR turnover and CEI are not standard reports, so someone still exports the data and calculates them. The reports describe the ledger; they do not rate payers, trend the metrics or run the follow-up, and native reminders are a single schedule for every customer.
Accounting software is the right starting point for a business moving off a spreadsheet, and the right system of record for everything else. The gap is between reporting and acting, and that gap is what Venture Workspace, a coworking provider in South Africa, closed: with collections automated, its late payments fell by 75% within 30 days.
How we compared the three methods
We compared a spreadsheet, accounting software reports and accounts receivable automation on the four rows of the table below. We give no weights, because the right method depends on how many invoices you raise.
- Aging report: whether the report updates itself. Sources: the aged receivables reports in Xero and QuickBooks Online, and the Paidnice aged receivables by contact report.
- DSO, turnover and CEI: whether each metric is a standard report or a formula you build. The formulas are in the metrics table above.
- Payer risk: whether the tool rates a customer from their payment history. Source: the Paidnice payer rating described below.
- Follow-up: whether reminders, fees and statements run without a person. Sources: Xero's native reminders, which use one schedule for every customer, and Paidnice schedules by customer group.
Paidnice is our product and is the automation example. The spreadsheet and accounting software columns describe what any spreadsheet, Xero or QuickBooks Online does.
How does automation change accounts receivable analysis?
Accounts receivable automation changes the analysis by reading the metrics live from the ledger and then acting on them, so the aging report, DSO and on-time rate update themselves and the follow-up runs on a schedule instead of on someone's memory. The comparison with the traditional methods comes down to currency and action:
| Spreadsheet | Accounting software | Accounts receivable automation | |
|---|---|---|---|
| Aging report | Manual, from exports | Yes, live | Yes, live, with per-customer history |
| DSO, turnover, CEI | Manual formulas | Not standard; calculated manually | DSO and days sales overdue on the dashboard |
| Payer risk | Whatever you remember | Balances only | Internal payer rating from payment history |
| Follow-up | None | One reminder schedule for all customers | Reminders, late fees and statements by customer group |
Paidnice is accounts receivable automation for businesses on Xero and QuickBooks Online. Its dashboard shows outstanding and overdue balances, days sales outstanding, days sales overdue, average overdue age, on-time payment rate and average days to pay, with widgets for the slowest payers and the oldest invoices, and an aged receivables by contact report in 1 to 30, 31 to 60, 61 to 90 and over 90 day buckets that exports to CSV. Every customer carries an internal payer rating built from their own payment history with you, which is the concentration and risk view a spreadsheet never had.
The analysis then feeds the action. Email and SMS reminders go out before, on and after the due date on a schedule set per customer group. Late fees and interest apply on the ledger under the terms the customer agreed, and prompt payment discounts reward the customers who pay early. UK businesses can index interest to the Bank of England base rate automatically. Paintvine, an events company in New Zealand, took late payments from over 20% to zero within the first month of running reminders and late fees this way.
If you are choosing between apps rather than methods, the best accounts receivable software guide compares the options by business size and platform.
How do you set up automated accounts receivable analysis?
Automated accounts receivable analysis takes four steps: connect the accounting file, set the reminder templates and schedule, set the late fee and discount rules, and read the dashboard. Sign-up is free with no card, and the first 20 actions are free.
- Connect Xero or QuickBooks Online. Paidnice imports invoices, contacts and payment history, so the aging report and DSO are populated from the ledger on day one.
- Set reminder templates and schedule. Edit the email and SMS wording, then set when each goes relative to the due date, by customer group where different customers need different handling.
- Set late fees and discounts. Choose a fee or interest rate, a grace period before it applies, and any prompt payment discount, all under the terms already on your invoices.
- Read the dashboard monthly. Compare DSO, days sales overdue, the on-time rate and the over-90 bucket against last month, and use the slowest-payers widget to decide who gets a call.
Get started here, or follow the getting started resources. Paidnice customers see half as many late invoices in the first 30 days after switching collections on.
Why is automated analysis the best method for small businesses?
Automated accounts receivable analysis is the best method for a small business because it is the only one of the three that keeps the metrics current without staff time and acts on them in the same system. A spreadsheet is free and stale by the end of the week. Accounting software is accurate and stops at the report. Automation reads the same ledger, adds the ratios and payer ratings the reports leave out, and runs the reminders and fees that improve them.
The businesses in this guide changed their metrics by making follow-up consistent, and the analysis is what showed them the change. Book a demo with Paidnice to see the dashboard on your own Xero or QuickBooks Online data.
Paidnice features in this article
About the editorial team
Denym started Paidnice in 2022 after customers paying late left his own business short on payroll. He has led it to $1 billion collected on time across more than 1 million invoices, and writes about credit control, late fees and getting paid on Xero and QuickBooks.
Ashley is a co-founder and lead developer of Paidnice and has built accounting software for more than 10 years. He builds the ledger connections that put reminders, late fees and payments into Xero, QuickBooks and ERPs such as NetSuite and Business Central. Before Paidnice he co-founded A2X, ecommerce accounting software for Xero and QuickBooks.
App of the Year