Bad debt forecasting
/How to Forecast Bad Debt
Bad debt forecasting is needed to determine whether the amount in the allowance for doubtful accounts is reasonable. This allowance is paired with and offsets the balance in a company’s trade receivables account. A company’s auditors will examine how a bad debt forecast was devised, to see if it is reasonable.
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.
Related AccountingTools Courses
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 |
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.