Collection effectiveness index (CEI): formula, targets and why it beats DSO

Every collections manager has had this conversation. DSO went up, so the team is under scrutiny. But sales also fell 20% last month, which mechanically raises DSO whatever the team does. The collection effectiveness index exists to settle that argument with arithmetic instead of adjectives.

Collection effectiveness index (CEI)

The percentage of the receivables that were available to collect in a period that you actually collected. It compares cash collected against everything that came due, so a CEI of 100% means every invoice that could have been collected was collected.

The CEI formula

           beginning AR + credit sales − ending TOTAL AR
CEI  =  ──────────────────────────────────────────────────  × 100
           beginning AR + credit sales − ending CURRENT AR

Four inputs, and the whole metric hinges on the last one.

The logic is simpler than the formula looks. The numerator is cash collected: what you started with, plus what you billed, minus what is still owed. The denominator is what was collectible: everything except the invoices that had not yet fallen due. Collectible excludes not-yet-due invoices because nobody can be blamed for failing to collect money that is not yet owed. That single exclusion is what makes CEI fair.

Worked example: 70.4%

One month for a manufacturer on net-30 terms.

• Beginning AR: $2,400,000
• Credit sales in the month: $1,800,000
• Ending total AR: $2,300,000
• Ending current AR (not yet due): $1,500,000

Cash collected (numerator)  = 2,400,000 + 1,800,000 − 2,300,000 = 1,900,000
Collectible  (denominator)  = 2,400,000 + 1,800,000 − 1,500,000 = 2,700,000

CEI = 1,900,000 ÷ 2,700,000 × 100 = 70.4%

Read that as one sentence: 70.4% of the money that could have been collected in the month was collected, and $800,000 that was due went uncollected. That is a weak month — it means nearly three dollars in ten of the collectible book stayed on the ledger.

A shortcut that shows what CEI really measures

Subtract the numerator from the denominator: $2,700,000 − $1,900,000 = $800,000. That is exactly the ending overdue balance ($2,300,000 total − $1,500,000 current). It is an identity, not a coincidence — the two expressions differ only in that term. So:

CEI = (1 − ending overdue AR ÷ collectible AR) × 100
    = (1 − 800,000 ÷ 2,700,000) × 100
    = (1 − 0.2963) × 100 = 70.4%

CEI is your overdue balance expressed as a share of what was collectible, flipped round. That is why it moves when collections behaviour moves and stays put when only volume moves — and it is also why the aging report and CEI always tell a consistent story if both are built from the same data.

Why CEI beats DSO for judging the collections team

DSO has a sales figure in its denominator. Change sales and DSO changes, even if nobody in collections behaves differently. CEI has sales in both its numerator and its denominator, so the volume effect largely cancels.

Here are three months with identical collections performance — in each one, the ending overdue balance is exactly 25% of the collectible amount. Beginning AR is $2,000,000 every time; only sales volume changes.

DSO says the team did their best work in the month sales jumped (30.6 days) and their worst in the month sales collapsed (43.1 days). Both readings are artefacts of the denominator. CEI holds at 75.0% across all three, because the extra sales inflated the collectible pool and the collected amount in the same proportion.

What is a good CEI?

Targets that circulate in credit-management practice apply to monthly CEI, and they are directional rather than researched: above 80% is generally considered good, above 90% strong, and below 70% a signal that something structural is wrong. Treat those as orientation and build your own baseline from six months of your own data before you set a bonus against any of them.

A sub-70% CEI is rarely a diligence problem. The usual causes are invoices that were disputed on arrival, terms granted by sales that credit never approved, and cash received but never applied. All three sit upstream of the phone call, and all three appear in the DSO lever list.

The period-length trap

CEI is not comparable across period lengths, and this catches people out badly. Take the flat-sales column above and run it for a year instead of a month: beginning AR $2,000,000, credit sales $19,200,000 (twelve months at $1,600,000), ending current AR $1,200,000, ending total AR $1,800,000 — the same closing position and the same behaviour.

Collected   = 2,000,000 + 19,200,000 − 1,800,000 = 19,400,000
Collectible = 2,000,000 + 19,200,000 − 1,200,000 = 20,000,000

Annual CEI = 19,400,000 ÷ 20,000,000 × 100 = 97.0%

The reason is that a fixed overdue balance is a small fraction of a year's collectible pool and a large fraction of a month's. Annual CEI will always look flattering. Fix the period, label it on the chart, and only ever compare month against month.

Four ways CEI can mislead you

1. 1.Write-offs flatter it. Writing off $200,000 of overdue AR cuts ending total AR to $2,100,000 and leaves ending current AR untouched, so CEI becomes (2,400,000 + 1,800,000 − 2,100,000) ÷ 2,700,000 = 77.8%, up from 70.4%, with no cash collected. Report write-offs on the same slide, always.
2. 2.Credit memos do the same thing. A large credit issued at period end reduces ending total AR and lifts CEI. Net credit memos against sales rather than letting them shrink the closing balance quietly.
3. 3.Ending current AR depends on the aging basis. If your aging report ages by invoice date rather than due date, "current" is wrong by the length of your terms, and the denominator is wrong with it. Check this before you trust a single CEI reading.
4. 4.Unapplied cash suppresses it. Cash sitting unmatched keeps invoices open, so ending total AR is overstated, the numerator shrinks and CEI understates real performance. Ironically this is the one distortion that makes the team look worse than they were.

CEI alongside the other collections numbers

All of these feed the same place in the end. Receivable days are one leg of the cash conversion cycle, and a CEI improvement shows up there as released working capital. Watching the aging mix weekly and CEI monthly is a reasonable rhythm for most mid-market teams.

Getting CEI out of your ERP

Three of the four inputs are easy. The fourth — ending current AR aged by due date, net of credit memos, trade only, by subsidiary — is the one that turns a five-minute metric into a half-day job. Most teams end up with a saved search, an aging export, a manual add-back for credits, and a spreadsheet that only one person can update.

Ask ERPray "what was our collection effectiveness index by month for the last twelve months, trade AR only, aged by due date?" and the number comes back computed from your own account with the query shown underneath, so you can check that "current" means what you think it means. Read-only by default, and it will tell you when your data cannot answer the question rather than guessing.