Power BI with your accounting data

Days sales outstanding (DSO) - A useful or misused KPI?

Posted by Narayani Ambar on 22-Aug-2018 11:07:50

Days%20Sales%20Outstanding-01

What is DSO?

DSO is a summary calculation often used as a key performance indicator (KPI) for collections teams.  It is sometimes also called the Average Collection Period or Average Debtor Days. As the name suggests, the calculation is often used to indicate on average how quickly a company collects money after a sales invoice has been raised.  This can be misleading though, because strictly speaking, DSO is not calculating this.  

DSO Calculation

The DSO calculation is quite simple.  You take your accounts receivable value and divide this by your average daily credit sales for a given period.  

In our Accounts Receivable solution for Sage 50 we have calculated this over 91 days and 364 days to give you two alternatives.  The formula that we use for 364 days is shown below:

DSO formula-2

We use 52 weeks (364 days) instead of 365 days because there will always be the same number of weekdays vs weekends in 364 days and the number is updated every time that Power BI is refreshed.

What period should be used to calculate Average Daily Sales?

The main decision to take when calculating DSO is over what time period you calculate the average daily sales.  Some people take 30 days, , some 90 days, some 12 months.  Personally, I think that 30-days is too short for most situations as this will generate an Average Daily Sales number that will fluctuate wildly from one day to the next relative to the Accounts Receivable number.  I think that 12-months can be a bit long since you are comparing today's Accounts Receivable number, so my tendancy is to go for 91 days.  I chose 91 days rather than 90 as it is a multiple of 7 and so will generally include the same number of working days.

What are the limitations of DSO as a measure?

If you look at the formula for calculating DSO you can see that it is very sensitive to the Accounts Receivable number.  If you have a bumper week of credit sales, then your Accounts Receivable number will go up and so will your DSO figure, potentially by quite a large percentage.  Perhaps ironically, a company with an extremely good and effective collections team will be more susceptible to a swinging DSO figure based on very recent sales activity because the underlying Accounts Receivable value is low.  A snapshot of today's DSO number on it's own does not tell you how well your collections team is performing.  The chart below shows a real example of DSO plotted for every day over the last 52 weeks.

DSO fluctuations

Over the 52-week period, we see a minimum DSO value of 36, a maximum of 84 and a single-day increase on 29th March of 19 days.  It is reasonable to speculate from the chart that the 29th March was a great day for sales and perhaps it is no coincidence that this is right at the end of a quarter, and actually for this company which has a financial year running from 1st April to 31st March it is also at the year end.  Given these fluctuations of your daily DSO figure, it is clearly not a reliable number to use on its own as a proxy for average collection period.

Average DSO and DSO trend over time

One thing we can do quite easily though if we are visualising this data in Power BI is to look at the average DSO number and the DSO trend over time.   In the chart below, I have added a trend line and an average line.  In Power BI, this is really simple with a graph like this.  All you need to do is to select the analytics option for your line chart.  I have chosen a Trend Line and an Average Line.

DSO trend over the last 52 weeks

While your DSO number can fluctuate quite a lot from one day to the next, the average DSO will give a measure for the average time to collect against an invoice and seeing the underlying trend will also be very useful.  We can see immediately from the chart that our current DSO value is below the rolling average over the last 52 weeks, which is probably good.  We can also see though that the underlying trend is up, which is probably bad.  I say "probably", because we still need more context - specifically my sales figures.  My currently low DSO number could be because my recent sales have been low (which would be bad), and the increasing trend could be because my sales trends have also been increasing (which would be good as long as I am not overtrading).

True meaning of today's DSO measure

Having thrown some criticism at DSO as a KPI, you may ask why have we chosen to include it in our Accounts Receivable solution in Power BI?  The answer is simple.  It gives you, in my opinion, a very good measure of the current workload (in value rather than volume terms) facing your collections team.

Topics: Sage 50, Power BI, Accounts Receivable, Days Sales Outstanding

Power BI with your accounting data

Time-saving tips, tricks, templates and tutorials

Subscribe to this blog for practical shortcuts to driving real insights from your accounting data with Power BI.

Expect:

  • How-to videos
  • Example reports
  • Sample formulae
  • Data modeling tips
  • Free Power BI templates
  • Tips for optimising Sage 50cloud

Subscribe to Email Updates

Recent Posts