The Bad Debt Forecast
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).
|Estimated Bad Debt
by Risk Category