Budgeting Basics and Beyond

17.2. Regression Approach

A more scientific approach to estimating cash collection percentages (or payment proportions) is to utilize regression analysis. We know that there is typically a time lag between the point of a credit sale and realization of cash. More specifically, the lagged effect of credit sales and cash inflows is distributed over a number of periods in this way:

By using the regression method discussed in Chapter 16, we will be able to estimate these collection rates. We can utilize Regression of Excel or special regression packages such as SAS, Minitab and SPSS.

It should be noted that the cash collection percentages, (b₁, b₂, ... b_i) may not add up to 100 percent because of the possibility of bad debts. Once we estimate these percentages by using the regression method, we should be able to compute the bad debt percentage with no difficulty.

Exhibit 17.1 shows the regression results using actual monthly data on credit sales and cash inflows for a real company. Equation I can be written:

This result indicates that the receivables generated by the credit sales are collected at these rates: first month after sale, 60.6 percent; second month after sale, 24.3 percent; and third month after sale, 8.8 percent. The bad debt percentage is computed as 6.3 percent (100 ...