The Bad Debt Forecast

Home >> Credit & Collection Topics

it is quite difficult derive a forecast of bad debts, since a number of variables impact the ability of a customer to pay an invoice, and those variables are difficult to anticipate. Typically, a collections manager estimates the amount of bad debt by guesstimating which specific invoices will not be paid, or by estimating the amount of losses that will arise from each 30-day time bucket in the aged accounts receivable report. Neither method is especially accurate.

A different approach is to assign a risk score to each customer, and then develop a loss probability based on these risk scores. It is possible to develop a risk score using an in-house scoring system, or by using a risk score developed by a third-party credit agency, such as the FICO score or the D&B Paydex score. With this score in hand, the following steps are needed to develop a bad debt forecast:

Obtain risk scores for all customers, except those below a threshold credit level (to reduce the cost of obtaining risk scores).
Enter the risk scores into a designated field in the customer master file.
Export the risk score information into a spreadsheet and sort the spreadsheet by risk score.
Divide the sorted scores into quartiles and assign a risk percentage to each quartile, based on recent bad debt history.
Derive the bad debt forecast using the following template (with example included).

Risk Category	Current Receivable Balance	Historical Bad Debt Percentage	Estimated Bad Debt by Risk Category
Low risk	$8,200,000	0.8%	$66,000
Medium low	6,050,000	1.7%	103,000
Medium high	4,605,000	2.9%	134,000
High risk	250,000	5.2%	13,000
Totals	$19,105,000	1.7%	$316,000

Podcast

A discussion of credit best practices is available on Episode 86 of the Accounting Best Practices podcast. Listen Now.