Use Excel to Fix Your Broken AR Measure of Days Sales Outstanding in Receivables
If you track Accounts Receivable the way most companies do—with Days Sales Outstanding in Receivables (DSO)—you probably know less about your receivables than you think. There's a better way, which also can improve your cash flow forecasts.
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
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 you 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.Source: exceluser.com