Formula For Disaster?
Several years ago, the smoke alarm in my house developed a mind of its own. Suddenly, in the middle of the night, it would shake the house with its buzz and clatter. And then, 5, 10, 15 minutes later, it would stop. And all the while, there was no smoke and no fire. The alarm upset my sleep, of course, and my disposition. Worse, there was always the concern: will it go off when it should? This concern finally overcame my Scottish frugality, and I replaced the alarm.
Most businesses monitor their accounts-receivable balances with a formula that works much like my old smoke alarm, giving warnings when none is needed and ignoring real problems. It's called the "accounts-receivable collection period," or the "days sales outstanding in receivables" (DSO), and it's found by dividing the accounts-receivables balance by average daily sales over a specified period -- monthly, quarterly, whatever. The answer is supposed to give you the number of days it is taking you to collect on your invoices so you can spot, and act upon, any trends in laggardly payments. The information is especially critical these days as a growing number of small businesses report that their customers are taking longer to pay their bills. But, as I found out to my dismay 12 years ago, the DSO formula isn't very accurate.
At the time, I was the controller of a small company that had just been acquired by a Fortune 500 firm, and we had to change our accounting practices to match the firm's. We were growing at about 100% a year, compared with 20% for our new parent corporation, and I'd been basing our DSO reports on quarterly sales averages. Then word came down that reports were to be submitted to headquarters once a month, calculated on annual rather than quarterly averages. As soon as we switched to the longer period, our receivables looked terrible. We were in continual trouble with headquarters over those DSO reports, and they wouldn't listen when we tried to explain that it wasn't our collections that were off, it was the formula. That's when I arrived at my first generalization about the risks of using the DSO formula: when sales are rising, the longer the averaging period, the worse the receivables will look.
The reason the DSO isn't a good monitor of accounts receivable is that its calculation depends on two factors that have nothing to do with how quickly customers pay. One is recent trends in sales; the other is the time period used to calculate the daily averages. To illustrate now this works, let's take a hypothetical business, Company X, and say that for a whole year all its customers pay on exactly the 45th day after invoicing. Over the course of the year, though, its sales rise for three months (Figure 1, January February, March); 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 Figure 1 demonstrates, when we calculate the DSO (30 days per month multiplied by the A/R balance, divided by the average monthly sales for the appropriate averaging period, be it 30, 60, or 90 days) for those four quarters, the numbers are all over the map. Not only do the sales shifts cause the DSO to vary from one quarter to the next when we base our calculations on the same averaging period, say 30 days, but there's also a wide range within each quarter depending on the averaging period used. Look at January-February-March, for example, when sales are rising. Our hypothetical company is behaving just the way my real company behaved years ago: with rising sales, the longer the averaging period, the worse the days sales outstanding look. For Company X, they range from 40 days in the 30-day averaging period to 60 days when calculated on a 90-day period. The true collection time, remember, is 45 days. But the only time the DSO is right on target is when sales are flat in the April-May-June period.
DAYS SALES OUTSTANDING IN RECEIVABLES (DSO)
DSO AVERAGING PERIOD
SALES 30Days 60 Days 90 Days
JAN. $100 AVERAGE
FEB. $200 MONTHLY $300 $250 $200
MAR. $300 SALES
* MAR. A/R DAYS SALES
BALANCE $400 OUTSTANDING 40 48 60
APR. $200 AVERAGE
MAY $200 MONTHYL $200 $200 $200
JUNE $200 SALES
* JUNE A/R DAYS SALES
BALANCE $300 OUTSTANDING 45 45 45
UJLY $250 AVERAGE
AUG. $250 MONTHLY $100 $175 $200
SEPT. A/R DAYS SALES
BALANCE $225 OUTSTANDING 68 39 34
OCT. $100 AVERGE
NOV. $400 MONTHLY $100 $250 $200
DEC. A/R DAYS SALES
BALANCE $300 OUTSTANDING 90 36 45
* Company X's customers all pay on the 45th dat after invoicing. At the end of the month, therefore, the A/R balance is equal to sales for the current month plus one-half of the sales for the previous month.
Recent drops in sales, as in Company X's July-August-September quarter, distort the DSO in their own way, and lead to another generalization: when sales are falling, the longer the averaging period, the better receivables will look. Company X's sales plummeted in September, driving the 90-day DSO to 34 days, compared with 68 days for the 30-day period. The most dangerous fault of the DSO calculations when sales are falling, however, is that they can fail to signal serious problems. To illustrate this, let's go back to the July-August-September quarter, and change our assumptions about when customers are paying their invoices. Let's say that they have fallen behind to the point that the September accounts-receivable balance goes up to $300 instead of the $225 shown. When we average sales over 90 days and use the standard formula (30 days per month times $300 in A/R balance, divided by $200 average monthly sales), the DSO equals 45 days. So, while sales crash and customers stop paying, the DSO are telling us that customers are paying within a reasonable time.
People often think that there must be a way to tinker with the averaging period so that the DSO numbers will be more realistic -- try a shorter period when sales are rising, maybe, and a longer period when sales are falling. If 90 days isn't the correct period, perhaps 30 days, 60 days, or even a year, as my Fortune 500 company thought. The problem is that whenever you have a large blip in sales, as happened to Company X in November, you get unreasonable DSO figures no matter what period you use. (You can, of course, hit the right number by accident, which was the case with December's 90-day-based calculation.)
All of this brings me to my final generalization: there is no way to accurately express the unpaid accounts-receivable balance in terms of days sales outstanding. What you can do, however, to generate accurate and complete data about accounts-receivable performance is to track your actual collection history. That may sound overwhelming, but once you get the spreadsheet system in place, it takes only a couple of minutes a month to keep it current. I've done this by hand for years, and only recently adapted it to computer spreadsheets for Lotus 1-2-3. For me, the advantages of tracking actual collections far outweigh any inconvenience caused by having to dig up aging receivables to get the system set up. The fact is, it's the only way I have discovered to understand past collection performance.
Figure 2 shows a spreadsheet analysis of a year's receivables for another hypothetical business, Company Y. I start by taking data available to most businesses each month -- credit sales and month-end aging reports -- and from that information calculate the numbers that are useful to me (see formulas in Figure 2). If you run across row 11, January's figures, you'll see what those numbers are. Column H shows the amount of January's sales actually collected in January; column I contains the amount of January's sales collected in February; and column J contains the same for March. Column K contains the amount of January's sales not yet collected after 90 days. The data in columns L, M, N, and O show the same collections by percentages; and the final column, P, shows me the average collection period in days for each month's sales.
So, I not only can see at a glance the dollar sales collected in 30-, 60-, 90-, and over-90-day periods for each month, I also know the percentage collected in each period, which I graph to give me a clear picture of how my customers have been paying from month to month. By looking at the graph for Company Y, Figure 3, for example, I can quickly see that the percentage of sales paid within 60 days has shown a stead decline over the past year. The graph dramatizes the deterioration of payments received in less than 30 days and, of real concern, that November's 60-day collections and December's 30-day collections -- the most recent data available -- are worse than any experienced during the previous year. (An unforeseen benefit of the spreadsheet, I found, is that the percentages also can be used to improve cash-flow forecasts. When I forecast A/R collections, I begin with a sales forecast, then estimate when customers will actually pay for each month's shipments based on past collection performance as shown in columns L, M, N, and O.)
The average collection period for each month, column P, is the alarm I've found most trustworthy over the years to signal any problems with receivables. If both the collection history graph and the average collection period numbers look reasonable, I move right on to my next project.