The bad debt forecast

It is quite difficult to 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:

  1. Obtain risk scores for all customers, except those below a threshold credit level (to reduce the cost of obtaining risk scores).
  2. Enter the risk scores into a designated field in the customer master file.
  3. Export the risk score information into a spreadsheet and sort the spreadsheet by risk score.
  4. Divide the sorted scores into quartiles and assign a risk percentage to each quartile, based on recent bad debt history.
  5. Derive the bad debt forecast using the following template (with example included).
Current Receivable
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

When deciding upon which forecasting method to use, a company with minimal bad debts can probably manage with one of the first two alternatives, which are easy to compile. However, if the amount of bad debt experienced from period to period is highly variable and the amounts are substantial, it may be necessary to use the risk scoring method.