Don’t forget, all the above is just a taste of our month end close checklist template, built from our collective expertise helping hundreds of companies streamline and organize their close process. Download a free copy of the Excel template for your accounting team’s month end close checklist and customize it as necessary. Forecasting DSO & Cashflow So we know how to calculate DSO, now we can use this as a way to forecast collection trends over time. Necessary information needed Three years of agings for a specific period Forecasted Revenues Template for Calculation of DSO.
A high DSO number shows that a company is selling its product to customers on credit and taking longer to collect money. This may lead to cash flow problems because of the long duration between the time of a sale and the time the company receives payment. A low DSO value means that it takes a company fewer days to collect its accounts receivable.
In effect, the ability to determine the average length of time that a company’s outstanding balances are carried in receivables can in some cases tell a great deal about the nature of the company’s cash flow. With a DSO of 21.7, Company has a short average turnaround in converting its receivables into cash. Generally speaking, a DSO under 45 is considered low; however, what qualifies as a high or low DSO may often vary depending on business type and structure. For example, a DSO of 40 may still cause cash flow problems for a small or new business that has little available capital. Because of the lower earnings that often accompany small or new businesses, such businesses often seek to retrieve their accounts receivable quickly in order to cover, wages, and other expenses. On the other hand, a DSO of 60 may cause few issues for a large and well-established corporation with high available capital. Though the company could likely improve its earnings by reducing its DSO and, thereby, maximize its potential to reinvest earnings, it is unlikely that the company will need to trim salaries or cut other costs in order to make ends meet.
While looking at an individual DSO value for a company can provide a good benchmark for quickly assessing a company’s cash flow, trends in DSO are much more useful than an individual DSO value. If a company’s DSO is increasing, it may indicate a few things. It may be that customers are taking more time to pay their expenses, suggesting either that customer satisfaction is declining, that salespeople within the company are offering longer terms of payment to drive increased sales, or that the company is allowing customers with poor credit to make purchases on credit. Additionally, too sharp of an increase in DSO can cause a company serious cash flow problems. If a company is accustomed to paying its expenses at a certain rate on the basis of consistent payments on its accounts receivable, a sharp rise in DSO can disrupt this flow and force the company to make drastic changes. Generally, when looking at a given company’s cash flow, it is helpful to track that company’s DSO over time to determine if its DSO is trending in any particular direction or if there are any patterns in the company’s cash flow history.
DSO may often vary on a monthly basis, particularly if the company is affected by seasonality. If a company has a volatile DSO, this may be cause for concern, but if a company’s DSO dips during a particular season each year, this is often less of a reason to worry.
( Head of Finance at Bugcrowd Inc) Aug 17, 2013 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?
(VP Finance/Corp Controller, ) Jan 16, 2011 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. (Performance Improvement Consultant and ERP Strategist, Haygarth Consulting LLC) Jan 16, 2011 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. 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:). ( CFO, Aargo Inc.) Jan 18, 2011 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. (SVP-Internal Audit, Wyndham Worldwide) Jan 19, 2011 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. (temp, temporary position) Jan 19, 2011 There are many things in addition to those already listed. 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. 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. (Founder, ExcelUser) Jan 20, 2011 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. 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. (Manager of Business Operations, Xerox Canada) Jan 24, 2011 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. (Founder, ExcelUser) Jan 24, 2011 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. 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. (CEO, Praedx, LLC) Jan 28, 2011 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. 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 gkearney praedx dot com.
(Founder, ExcelUser) Jan 28, 2011 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. 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. (Chief Financial Officer, Milk Source) Jan 17, 2012 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. (Director of Finance, Barbour Publishing, inc) May 21, 2012 My company is in private industry. We've traditionally used net sales in the DSO calculation. We live in a high returns industry (publishing).
Does anybody use gross sales and does anybody know of a good website that discusses the distinctions? When we began using Best Possible DSO, our owner became very confused because the number was so much higher than terms granted to our customers. When you use gross numbers over the same receivables things line up much better. What are the pros and cons of each?
(VP Finance & Operations, Omron Electronics ) Nov 8, 2013 If progress billings on projects (advances with no rev recognition) is significant, you should consider calculating DSO base on billings (all billings for cash). The only comments I'd add to the good advice from others is: 1) Consider calculating a 'Best Possible' DSO as a reference point.
(if your credit terms vary by customer or type of billing you'll need this yardstick to know how well you are really doing) 2) DSO by itself (expecially if you don't know the 'best possible') can be deceiving, so I always look at past due aging together with DSO for the full story PS.I loss my original commentary before I was able to post it.has anyone else been bumped off the website while typing? (Regional Credit Manager, Bunzl Processor Div) Nov 12, 2013 Everyone, I am just a credit manager, but for me, tracking and graphing my incoming daily/weekly receivables as a percentage against my outstanding AR @ month-end is a great way to understand how my customers are performing and if I'm on target in collections. Once you track this, you'll see that you typically have a trend by week. Another item I like to track is how much my receivables roll as a percentage of their original balance.for example, after 30 days what percentage of the once current amounts rolled into 30 day - how much of that original ends up in 60 days?
Once again, if you track over time, you know your trend and see what the true performance is. Sales go up and down, but tracking just your billed receivables gives a truer picture of your AR performance. (CFO, Corrigo Incorporated) Feb 12, 2016 Wow - this topic drew lots of responses. You may not be public now and you may not be seeking outside investment now but that may change. I would use the metric most often used in your industry and calculate it based on how an outsider would do it. Use the AR total that appears on your balance sheet and use the revenue as it appears on your P&L.
![]()
If you do not do that you will find yourself constantly reconciling to those numbers. Ideally take your quarterly revenue and divide by 90 to get revenue per day. Then divide that into AR to get DSO. Note that this includes unbilled AR and it should because the offset to unbilled AR is revenue. (President, Strategic Business Services) Feb 12, 2016 As noted above there are many reasons why DSO can be distorted and not provide a good metric for performance of the business and the collections process. Assuming that the issues related to billing frequency and the inclusion of revenue not billed are policy or process issues that need to be reviewed or changed you will still find distortions in the DSO due to growth or significant fluctuations in revenue. If you want a measure that reflects the effectiveness of the collections process, readily identifies actions and who should own the actions and is easy to calculate then you might try using past due as a% of A/R.
I have found that the Collections Team will readily take ownership of this metric and can quickly identify issues related to collections and issues that need to be addressed by other such as deductions, which might consist of proof of delivery - logistics, promotions - sales, pricing - finance, etc. When you consider that DSO is really made up of two components 1.) Days Given or terms and 2.) Days Taken or past due, then the opportunity for action is with the past due so then measuring this will drive actions that should improve cash flow, reduce risk and also improve DSO no matter how it is calculated. This of course assumes that the terms or days given are in line with the industry or geographic business practices.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |