Creating an accurate bad debt forecast can be similar to reading tea leaves or consulting a crystal ball – it is very difficult to make actual results come anywhere near the forecast. The usual approaches are to either create a forecast based on specific expected losses or to assign a loss probability based on the age of various receivables. Neither approach works especially well.
An alternative with a greater level of accuracy involves assigning a risk class to each customer, and then assigning a loss probability to open receivables based on the risk class. Risk classifications can be calculated with elaborate in-house risk scoring systems, but there are many commercially-available alternatives available, such as FICO scores for individuals or the Dun & Bradstreet Paydex and Financial Stress scores for businesses.
Here are the steps needed to create a bad debt forecast based on risk scoring:
- Periodically obtain new risk scores for all current customers, excluding those with minimal sales.
- Load the scores for each customer into an open field in the customer master file.
- Print a custom report that sorts current customers in declining order by risk score.
- Divide the sorted list into fourths (low risk through high risk), and determine the bad debt percentage for the previous year for each category.
- Use the format in the following example to derive the bad debt percentage:
Risk |
Current Receivable Balance |
Historical Bad Debt Percentage |
Estimated Bad Debt by Risk Category |
Low risk |
$9,500,000 |
0.8% |
$76,000 |
Medium low |
7,250,000 |
1.6% |
116,000 |
Medium high |
3,875,000 |
3.9% |
151,125 |
High risk |
750,000 |
7.1% |
53,250 |
Totals |
$21,375,000 |
1.9% |
$396,375 |