Home > Excel Solutions > 

Use Excel to Fix Your Broken
Accounts Receivable Measure

If you track Accounts Receivable the way most companies do, you
probably know less about your receivables than you think. There's
a better way, which also can improve your cash flow forecasts.

by Charley Kyd
May, 2008

During tough economic times it's particularly important to manage your Accounts Receivable balance carefully. Unfortunately, the traditional way to measure AR performance is badly broken. Using the method is like measuring precision machinery with a rubber band.

The measure you probably use is the "accounts-receivable collection period," also called the "Days Sales Outstanding in Receivables" (DSO). Typically, it's found by dividing your Accounts Receivable balance by average daily sales.

This calculation is intended to give you the average number of days it takes to collect your invoices. Supposedly, by tracking the measure month after month, you see the trend in how your customers are paying their bills. But as I learned many years ago, the standard DSO metric is worse than useless.

At the time, I was the controller of a small company that had recently been acquired by a public company, and we had to change our reporting practices to satisfy our new parent. 

We were growing at about 100% per year, compared with 20% for our parent, and I'd been basing our DSO reports on average sales over the prior three months.

Then word came down that AR collection statistics were to be calculated using annual rather than quarterly averages. As soon as we switched to the longer period, our collection performance looked terrible, as this figure illustrates.

The dark green line segments emphasize the sales for the most recent quarter for the two different growth rates. If a company offers 30-day terms, and if Receivables are well-managed, most or all of the AR balance is made up of the sales in those green segments.

But if the DSO calculation uses average daily sales over the past 12 months -- a value marked by the brown lines for the two different growth rates -- then the faster a company is growing, the more inaccurate the 12-month DSO calculation becomes.

You can see why our parent, with a growth rate of only 20%, had few problems using a one-year average: There's only a small difference between the averages for the past 12 months and for the past three months.

But there's a huge difference when sales grow quickly. And this difference distorts the DSO metric significantly. One month, in fact, when I used our parent's mandatory one-year averaging period, my DSO came out to be more than 90 days. I got this number even though all my receivables were less than 90 days old.

Unfortunately, as you'll see, the problem with the DSO isn't limited to fast-growing companies.

The Problem With the DSO Metric

The basic problem with the DSO is that its calculation is influenced by two factors that have nothing to do with how quickly customers pay. One factor is the variability of your sales; the other factor is the time period used to calculate average daily sales.

To illustrate how this works, let's take a hypothetical business, EG Corporation. All of EG's customers pay on exactly the 45th day after invoicing. Let's say that over the course of a year, EG's sales rise for three months (January, February, and March in the figure below); are flat for three months (April, May, June); fall (July, August, September); and, finally, have a big jump in one month out of three (October, November, December).

As the figure demonstrates, when we calculate the DSO for those four quarters, the results vary considerably. If you've been relying on the DSO calculation to monitor the performance of your receivables, this table should set off some very loud alarms in your head.

Here are how the DSO numbers are calculated for March:

40 =30 days/per month * $400 AR / $300 average monthly sales
48 =30 days/per month * $400 AR / $250 average monthly sales
60 =30 days/per month * $400 AR / $200 average monthly sales

The other DSO numbers use the same logic.

When you look at this table, first notice that the monthly changes in sales activity cause the DSO to vary considerably from one quarter to the next for any averaging period. In the 30-day column, for example, the DSO varies from 40 days to 90 days. (All invoices actually are paid on the 45th day, remember.)

Second, notice that for any pattern of sales activity, the averaging period you use makes a huge difference in your results. When sales are rising, as in March, the longer the averaging period the greater the DSO. And when sales are falling, as in September, the longer the averaging period the lower the DSO. And if sales vary considerably from month to month, as in December, the DSO could be virtually any number.

People often think that there must be a way to tinker with the averaging period so that the DSO numbers will be more accurate. But if you play around with a simple analysis like this you'll quickly realize this fact:

There is no way to express your unpaid accounts-receivable balance accurately in terms of Days Sales Outstanding.

Luckily, however, there's an excellent alternative to DSO. Not only does it provide a more accurate measure of collection performance, it offers an excellent way to forecast future collections when your prepare cash flow forecasts.

An Improved Way to Monitor AR Performance

A better approach is to track your actual collection history.

To do this precisely, you would need to get a programmer involved. But you can use a spreadsheet to get a fairly good grasp of collection performance. I originally did this by hand, then adapted it to VisiCalc, Lotus 1-2-3, and finally, Excel. The only data you need are the balances found in your month-end Accounts Receivable Aging Schedules, plus the amount of your monthly credit sales.

The following three figures show different sections of one spreadsheet that analyzes receivables for a hypothetical company. The first figure below shows the data-entry section.

Here, at the end of each month you enter credit sales for the month and the totals from the "buckets" in your Accounts Receivable Aging Schedule. If your aging schedule provides aging buckets for 120 days, or even more, you also add columns for that information.

The figure, below, shows the calculations that generate the Average Collection Period (ACP). Unlike the DSO calculation, the ACP measures your actual collection performance. Its calculation uses only minor simplifying assumptions, which I'll explain shortly.

The formulas for row 7 are shown below, with an explanation for each formula. Copy the formulas up and down their columns as needed.

The ACP isn't a perfect metric, but it's a significant improvement on the DSO. On the down side, the ACP is affected by the distribution of sales within the month. If a disproportionate percentage of your sales fall in the last week of the month, the ACP will be higher than if most sales come in the first week of the month.

Also, the ACP, like the DSO, is affected by write-offs. The more quickly you write off bad debts, the lower is the balance of your Accounts Receivable and the better your collection period looks.

On the up side, the ACP isn't affected by large swings in monthly sales, and it doesn't require that we divine the best averaging period. It also can help to improve your cash flow forecasts.

The ACP and Cash Flow Forecasts

This figure is from the same spreadsheet as the two figures above, but it has different columns hidden.

Row 4 shows that we had sales of $234 in Jan-07, and that we collected 5% of those sales in January, 51% in the next month, 42% in the following month, and 2% in the month after that. The other rows show the collection performance for other months of sales. (Because our data ends in December, we don't yet know what our complete collection performance will be for sales in October, November, and December.)

Row 3 in this figure shows the average collection performance by month. Here are the formulas involved:

L3:  =AVERAGE(OFFSET(L2,2,0,COUNT($A:$A)))
M3:  =AVERAGE(OFFSET(M2,2,0,COUNT($A:$A)-1))
N3:  =AVERAGE(OFFSET(N2,2,0,COUNT($A:$A)-2))
O3:  =AVERAGE(OFFSET(O2,2,0,COUNT($A:$A)-3))
P3:  =SUM(L3:O3)

The four averaging formulas rely on the OFFSET function, which has this format:
=OFFSET(reference, rows, cols, height, width)

The COUNT function returns the count of all numeric values in column A. Because date serial numbers are numeric values, this function returns the number of rows of data in the display.

Finally, in cell L3, the OFFSET function returns a reference to the range L4:L15, and the AVERAGE function returns the average of the data in this range.

When you prepare your cash flow forecast, you typically could use your results in row 3 to predict when each month's sales will be collected. For example, if you predict sales of 300 for Jan-2008, you could forecast collections of 3% of those sales in January, 41% in February, and so on.

However, you might need to adjust your percentages for at least three reasons. First, your Over-90 percentage probably will need to be adjusted to account for your average amount of AR write-offs as a percentage of sales.

Second, if your collection performance varies with the season, you probably should use seasonal collection percentages rather than one overall average percentage.

And third, if the trend in your collection performance has been changing over time, you should base your collection forecast on your expected collection performance.

Other Methods to Track Receivables

Using the ACP isn't the only alternative to the DSO. Here are some other ideas that you might consider:

• Calculate the average age of all unpaid invoices. If you work for a small company, you might be able to do this in Excel; if you work for a large company, your IT department should be able to do this for you.

• Calculate a dollar-weighted average age of your invoices. To do so, multiply the age of each invoice (in days) by the amount of the invoice; add up these dollar-day results; then divide by the total amount due. Comparing the dollar-weighted age to the average age helps you to understand how the size of an invoice affects your ability to collect it when it's due. 

• If your credit terms vary among different customer categories, calculate the average days overdue. Because invoices that aren't yet due for payment will have a negative value for days overdue, this number could be less than zero.

• To better evaluate your collection efforts, calculate the average days overdue - but only for invoices that actually are overdue. You want this number to be close to zero.

In short, there are several useful ways to track your collection performance using your own internal data. But the classic DSO is not one of them.

In fact, there is only one reason ever to use the traditional DSO calculation. If you're looking at a company from the outside, the only relevant information you have is its annual or quarterly sales and its period-ending Accounts Receivable balance. But if you have access to internal information, never use the DSO metric for managing your receivables.