Collection ratio — collections over production — is the dental practice’s cash-health metric. Production is dentistry done at full fee; collections are dollars actually banked.
The example
$73,600 collected, $80,000 produced.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 73,600 / 80,000 | — |
| 3 | Collection | → 92% |
The formula
The formula:
How it works
How it works:
- Production = dentistry performed at the practice’s full fee.
- Collections = dollars actually received (insurance + patient).
- The ratio = collections ÷ production — target is typically 98%+ adjusted.
- The gap is write-offs + uncollected — split it to see which to fix.
Adjust production before judging the ratio. If you write off insurance discounts as part of production, raw collection looks low even with great cash habits — so most practices track adjusted production (full fee minus contractual write-offs) and aim for ~98%+ collection on that. A low adjusted collection ratio is a real AR problem; a low raw one may just be insurance discounting. Separate the two.
Try it: interactive demo
Collections and production.
Variations
Adjusted collection
Net of write-offs:
Write-off rate
Discount share:
Uncollected
The shortfall:
Pitfalls & errors
Adjusted vs gross. Net out contractual write-offs for the meaningful ratio.
Same period. Collections lag production — compare comparable windows.
Zero production. No production gives #DIV/0!.
Practice workbook
Frequently asked questions
How do I calculate the collection ratio in Excel?
What's adjusted collection?
Why is my collection ratio low?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class