Excel Reporting Ideas
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.
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 "accountsreceivable 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 30day terms, and if Receivables are
wellmanaged, 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 12month DSO calculation becomes.
You can see why our parent, with a growth rate of only 20%, had few problems using a
oneyear 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 oneyear 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 fastgrowing 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 30day 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 accountsreceivable 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 123, and finally, Excel. The only data you need are the balances found in
your monthend 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 dataentry 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.
H7: 
=IF($B7=0,0,B7C7) 

This month's sales minus our 030 day receivables at monthend tells us how much we
collected during the month of the sale. 
I7: 
=IF($B7=0,0,C6D7) 

Last month's ending 030 bucket minus this month's ending 3060 day bucket tells us how much of
this month's beginning 3060 bucket we collected this month. 
J7: 
=IF($B7=0,0,D6E7) 

Last month's ending 3060 bucket minus this month's ending 6090 bucket tells us how much
of this month's beginning 6090 day bucket we collected this month. 
K7: 
=IF($B7=0,0,E6+F6F7) 

Last month's 6090 bucket plus its over90 bucket minus this month's over90
bucket tells us how much of thismonth's beginning Over90 day bucket we either collected or
wrote off this month. 
L7: 
=IF($B7=0,NA(),H7/$B7) 

The percentage of this month's sales collected this month. 
M7: 
=IF($B8=0,NA(),I8/$B7) 

The percentage of this month's sales collected in 3060 days. 
N7: 
=IF($B9=0,NA(),J9/$B7) 

The percentage of this months sales collected in 6090 days. 
O7: 
=IF($B10=0,NA(),K10/$B7) 

The amount of the 90day receivables that we either collected or
wrote off after 90 days, as a percentage of this month's sales. Even though Over90 receivables were generated
for several periods,
this calculation divides by this month's sales amount. It's typically safe
to use this simplifying calculation because this bucket category typically is a small
percentage of AR. 
P7: 
=SUM(L7:O7) 

Find the total of all collections for the sales entered in this row. This
usually isn't exactly 100% because of the inaccuracies imposed by the Over90day
bucket. 
Q7: 
=IF($B5=0,0,SUMPRODUCT(L7:O7,{15,45,75,105})) 

Multiply the 030 day percentage by 15 days (the average age of the
receivables in the 030 bucket), the 3060 day percentage by 45
days, and so on, and then total the results to find the Average Collection Period.
(The "{15,45,75,105}" in the formula is an array constant that contains the four
values for the number of days to be applied to the four cells in the range L7:O7, which contain
our collection percentages.) 
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 writeoffs. 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 Jan07, 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 Jan2008, 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 Over90
percentage probably will need to be adjusted to account for your average amount of AR
writeoffs 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 dollarweighted 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 dollarday results; then
divide by the total amount due. Comparing the dollarweighted 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 periodending Accounts Receivable balance. But if you have access to
internal information, never use the DSO metric for managing your receivables.
