DSO Calculation Methods - What Are The Best Practices?

Brett Bennett's Profile

DSO Calculation

Wondering what your company's best practices and methods for calculating DSO?  Currently, our company includes unbilled revenue or amounts from sales contracts that have been signed but no revenue has been generated yet.  It appears this is skewing the calculation.  We also only invoice customers at month end.  When calculating DS during the month, our DSO calculation methodology is all over the board.  Wondering any best practices for including/excluding unbilled revenue and calculating inter month DSO when invoices are not generated until month end?  Thank you.

Answers

Member's Profile

Well, the fact that you only send invoices at the end of the month is a major factor in this. To say nothing of the very real delay this causes in your receivables. To wit (and I'm sure you realize this), the same invoice you could send on the 1st of the month gets delayed to the 31st, thus adding 30 "shadow" days to your receivable and increasing your DSO. That's just the CFO in me twitching :).

Interestingly, by excluding unbilled revenue one would think that you would remove the measurement bias of your once-monthly invoicing behavior. And that so long as you used this to understand your DSO of only billed customers, you should be in good shape.

However, heeding the CFO in me again, I would say that only measuring based on billed customers will likely lead to confusion amongst management b/c the constant question will be "wait, have we billed them yet...?", or simply "okay, which of our deals does this DSO assumption cover?" My two cents (and I may owe you some change) would be to increase your billing frequency and use a DSO calc that includes all closed deals, knowing that you don't need to carve any deals out of the calculation. IDK whether that is a "best practice", but it seems like it would a)reduce your overall DSO, b)increase your cash flow (rarely a bad thing) and c)make DSO a more broadly understood and therefore more meaningful measurement within your company. Hope that helps.

To comment, and for full access, login or register
Member's Profile

Yes, Bryan, that can add a tremendous amount of time.

Brett, if you're looking to improve business KPIs and don't already have the benefit of one, consider an ERP system. Take a look at
"The Proformative Community Guide To ERP Selection & implementation:"

http://www.proformative.com/whitepapers/proformative-community-guide-erp-selection-implementation

You might also be interested in Proformative's free...

"Accounting Resources Guide:"

http://www.proformative.com/resources/accounting-resources-guide

Best... Sarah

Enjoy!

Best... Sarah

To comment, and for full access, login or register
Member's Profile

I must agree with many of the comments by Bryan - especially the twitchy ones:).
Perhaps a refinement of his idea is to see if you can gather two sets of data consistently: Actual Billings per month (NOT revenue recognized), and Month-end totals for Unbilled Amounts on Signed Orders.
My reasoning: 1. First metric:calculate DSO on AR and Actual Billings. Use the reduction method where each month's billings = 30 days of AR.
2. Track the trend on Unbilled Amounts.
Then compare this with (a) the DSO above and (b) the Monthly total of new orders booked.
That way you can show your execs:
1. DSO efficiency-cash collection efficiency
2. Unbilled amounts - billing efficiency on signed deals
3. New business booked-sales efficiency in signing new deals

Out of that you may be able to see if any processes need improving:)

To comment, and for full access, login or register
Member's Profile

Comments by both Brian and Len are terrific. My experience is that inclusion of unbilled AR in your DSO calculation methods will sway the days, especially if your billing of new contracts is erratic which it probably is. I have had two calculations, one using billed AR and one using unbilled AR. I found that if you invoice and collect routinely, the billed AR DSO will remain largely range bound. Good luck!

To comment, and for full access, login or register
Member's Profile

Thank you everyone for your comments, I am in agreement as well. Twitch Twitch. Ross, would you be able to share your billed AR and unbilled AR DSO calculations? Again, thank you very for expert comments and suggestions.

To comment, and for full access, login or register
Member's Profile

Brett, Do you know if there was any reason why your company chose to use this measurement for collection performance? Different compamnies may use different methodology depending on their access to measurement data and other preferences, for example, analysts may look at DSO based on quarterly average days sales if that information is publicly available, whereas internal data may be used to calculate DSO based on lifo or monthly average days sales.

The important thing is that users understand how the data is calculated, what it means, and most important, are able to monitor and explain changes in the trends.

To comment, and for full access, login or register
Member's Profile

If your revenue is unbilled because you have not "earned" it, then your results will really be skewed. Then your contract to recognized revenue to billed revenue delays can cause big swings in addition to only billing at month end. From a cash flow perspective, are you able to bill throughout the month?

To comment, and for full access, login or register
Member's Profile

Brett,

Although DSO is a widely accepted metric used in assessing collection effectiveness/capability/performance (as noted above in a few responses), it merely reveals how long it is taking for your customers to pay you. From there, you must employ additional metrics to determine if a high DSO is perhaps caused by (a) pressures/circumstances your customers are facing (economy?), (b) your own internal collection performance (most widely associated issue), (c) some other factor(s) and (d) some combo of the above.

So my suggestion is to think beyond what your eventual DSO calc reveals... don't just conclude that a high number means your team is doing a bad job.

If you get an unacceptably high number, yes, I agree, you should first measure what you control: your collection team and your collection process/policies. So implement additional metrics that show your performance there. For example, you can take the AR Trial Balance and compare the frequency of calls the team makes to the 'Over 30' bucket vs. the 'Over 60" vs. the 'Over 90', etc. Assuming you have a Collection Department policy requiring X number of calls per month to each bucket, and that those calls are documented, you can create at least two derivative metrics to assess performance: (1) % of accounts called per bucket and (2) collection agents in compliance with the call policy.

Additional metrics to consider are: your invoicing accuracy, billing frequency (as you/others mention above), policy on timing to engage external collection agency, etc.

To comment, and for full access, login or register
Member's Profile

There are many things in addition to those already listed.

1. include or exclude intercompany sales, depending on what is desired
2. exclude items in A/R but were unusual items which may not have run through sales, e.g. you may bill for co-op advertising (manufacturer assists a franchisee/dealer with advertising) or other items. What are the things that are included in the numerator but not in the denominator and vice versa
3. exclude sales allowance accruals, the actual amount billed should be used, not "net sales" from the P/L
4. is this a "month end" only calculation or is it practical to calculate more frequently? Depending on your billing and payment due dates the DSO could misrepresent reality if the billing and terms cause payments to always come in the first few days of the month
5. What you are able to do may be restricted by the capabilities of your IT system.
6. Ultimately, in my opinion, don't worry so much about the DSO number and focus on the individual accounts past due and the DSO will take care of itself.

To comment, and for full access, login or register
Proformative Advisor
Member's Profile

1. If you are a public company, then I would make sure your method of DSO calculation aligns with other public companies in your industry. This way analysts can have an apples to apples comparison.
2. If the DSO calculation is for internal reporting only, the key is to pick a method(s) and stick with it over time to be able to spot any trends.

To comment, and for full access, login or register
Member's Profile

If you don't have access to internal data, DSO is the best measure available. However, I would NEVER use DSO if you have access to internal data.

The major problem with DSO is that it's strongly influenced by the growth rate of your sales. This is a problem that can't be solved by finding a "good" averaging period, or by using a consistent calculation method. The table here:
http://www.exceluser.com/solutions/ar_fix.htm
illustrates this point.

Another problem is that DSO can be influenced by a change in the mix of sales made under different credit terms.

Still another problem is that the calculation produces a measure that is too gross. Instead, if you want to improve the time between shipment and collection, you need visibility into the various delays in the chain of events that connect those two end points.

The solution is to directly calculate the length of time it takes for each delay in the collection process: from shipping to billing, to transmission of the invoice, to the customers' payment approval, to check generation, to mailing, to depositing the check, and so on.

Most of those steps leave a data trail that can be captured and analyzed using a variety of statistical techniques, from simple to sophisticated. SMB-type companies can use Excel, as suggested in the article. Larger companies have additional choices.

But NO company should use the DSO for internal control. It tells you nothing useful.

To comment, and for full access, login or register
Member's Profile

Thanks for the reference to the excel article. We've been looking for a better more explainable way to inform key business owners on the time to collect debt. The article was very helpful and thought provoking.

To comment, and for full access, login or register
Member's Profile

I would suggest that normalizing the results would be helped by extending the period of calculation to 90 days (one quarter) instead of 30 (I believe most public companies do just that when measuring their AR performance). In terms of unbilled revenue, I agree with one of the previous comments- have two metrics one including the Unbilled revenue and one excluding it (from both numerator and denominator).
Not including the Unbilled Revenue in the numerator of the formula and using pure billngs in the denominator really helps measure the effectiveness of your collection process and true aging. However, if your other metric which includes Unbilled revenue is significantly higher than the industry benchmark, then you really have to fix your Order to Cash process when it comes to generating invoces for the work performed.

To comment, and for full access, login or register
Member's Profile

Suppose that all customers actually pay on the 45th day after invoicing and that sales are constant during the month. Under these assumptions, the AR balance at month-end consists of all sales for the current month plus half the sales for the prior month.

-- If sales were $100, $200, and $300 for the past three months, 90-day average sales would be $200 and AR would be $400 (this month's sales plus half of last month's). So our DSO would be 30 * 400/200 = 60 days.

-- If sales were $300, $200, and $100 for the past three months, 90-day average sales would still be $200. But AR would be only $200. So our DSO would be 30 * 200/200 = 30 days.

In other words, the growth rate of sales SIGNIFICANTLY distorted our actual collection period of 45 days.

This problem NEVER can be fixed by changing the averaging period. There is no "correct" averaging period for DSO.

If you use DSO for internal collection management, I guarantee that:

1. You've worked to fix collection problems that didn't exist because the DSO gave you false information.

2. You've ignored collection problems that actually did exist because the DSO gave you false information.

The solutions are easy.

--Your AR database includes every invoice date. Just calculate the average age of all your outstanding invoices. If you have a variety of payment terms, you also should find averages for each set of terms. (In all cases, a dollar-weighted calculation sometimes will give you a different answer. This is because some customers wait longer to pay large invoices.)

--Your AR system probably can tell you the invoice date, terms, and payment date for all past collections by invoice. Simple analysis of this data can tell you actual collection trends by month and by payment term. You might even want to compare collection periods by product category, because some product categories tend to be purchased by cash-poor customers. And again, dollar-weighted averages could give you different results.

Once you have accurate data, you can test the effect of different credit and collection policies. You won't need to guess about the effect of these changes; you'll have hard data to guide you.

But again, no amount of tweaking will fix the DSO. NEVER use it when you have internal data available.

Charley Kyd

To comment, and for full access, login or register
Member's Profile

At the risk of re-covering what has already been said above, I have two overriding comments:
1. DSO should always be calculated using the value of invoices posted in a given month and not what was recognized as revenue. Think of it from your customer's perspective. He/she is being asked to pay a specific amount, based on specific terms provided. That customer neither knows or cares what your organization's policy is regarding revenue recognition. The fact that the customer is, for example, paying in advance for a quarter, is all he/she knows. If you recognize that revenue evenly over the three months, you're still expecting payment of the full amount inside the terms given.
2. Following on from the above, average DSO is not able to recognize seasonal fluctuations. Using the quarterly payment above, your cash flow projections are going to be distorted if you apply an average DSO to every month's sales, when in fact the sales lag the amount invoiced by 30 and 60 days in every quarter.

If it's of any help, I've developed a very comprehensive, Excel based forecasting and analytics tool, that has an Accounts Receivable module. This module uses the lookup function to go back and add up all of the individual monthly invoice totals, up to a maximum of 12 months, to calculate an exact AR balance based on the DSO provided. If you think this can help you let me know. My email address is gkearneyatpraedx [dot] com

To comment, and for full access, login or register
Member's Profile

Brett,

People often use DSO to forecast collections in a cash flow forecast. Because you're a Controller, this might be a topic that interests you.

Think of a standard AR aging schedule with buckets for 0-30 days, 31-60 days, and so on.

--In January, the 0-30 day bucket shows the amount of sales billed in January that have not yet been paid in the month of the sale.

--In February, the 31-60 day bucket shows the amount of sales billed in January that have not yet been paid 30 days after the month of the sale.

--In March, the 61-90 day bucket shows the amount of sales billed in January that have not been paid 60 days after the month of the sale.

--Etc.

So if you arrange these numbers in a spreadsheet, you can start to see a pattern, perhaps like: about 5% of your billed sales are collected in the month of the sale, 50% is collected in the second month, 30% in the third month, and so on. And some (hopefully small) percentage of these sales never will be collected.

This collection pattern is very useful in cash flow forecasts when you convert forecasted sales into cash collections. I've found it to be a much more accurate method than merely using a formula based on the DSO.

Also, if your collection averages tend to vary significantly, you can vary the percentages in your forecast to give you a fairly accurate best-case / worst-case collections forecast. (There could be a seasonal pattern in these collection percentages, by the way.)

In contrast, the simple-minded method of scaling up recent sales by the DSO to forecast the AR balance tends to be much-less accurate...particularly if you forecast monthly sales to vary significantly from month to month.

Charley Kyd
www.ExcelUser.com

To comment, and for full access, login or register
Proformative Advisor
Member's Profile

I would attack this using two calculations. Before doing this, you should eliminate any thought of doing a “daily count back” DSO calculation as you do not have sales data for every day.

Your first calculation should be based on revenue. This is more encompassing of your total available cash collection “basket.” Even though, some of the other responders tell you not to do this, it will tell you how good or bad your collections are against the revenue you have recognized. I am sure you and your managers want to know how well you are doing against your “potential to collect.” This calculation would include unbilled revenue and or billed, but not recognized. The simple calculation would be taking you’re A/R balance and extinguishing it going backwards in time. For example, if you’re A/R (including unbilled) is $800 and revenue was $500 (30 days) in the most recent month and $700 (31 days) prior to that, DSO would be 43 days. 30 days + ((800-500)/700) X 31 days or 30 +13=43 days

If you just want to measure your collections on invoices billed, use only your billed dollars in the revenue calculation above and the accounts receivable balance (excluding unbilled/billed not recognized).

The first calculation would be what I would use for external reporting and for all levels of management. The second calculation is what I would use in my collectors performance reviews since this is what they can control. We only use the first calculation throughout the organization as our billing is done daily. If done properly, you may see more activity where you would have more billed-not recognized receivables. That would be the behavior to strive for. Eventually, you can eliminate one of these calculations based on what your organization favors.

To comment, and for full access, login or register
Proformative Advisor
Member's Profile

I agree with many of the comments above, especially Patrick's. We used DSO, as calculated above, to assess level and trend for industry benchmarking purposes. However, we used the collection efficiency ratio (% collected out of available to collect) at the individual collector level. That ratio was well respected at the National Association of Credit Management.

To comment, and for full access, login or register