by Hugh Johnson, Posted on 8/15/2018 2:03:21 PM

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

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.

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:

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.

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.

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.

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.

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.

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).

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.

- Connect Power BI to Sage 50 Accounts - Part 4 of 4
- BICharts - Sage 50 Accounts Charts of Accounts
- BICurrencies - Sage 50 Accounts Currencies List
- BICountries - Sage 50 Accounts Countries List
- BICustomers - Sage 50 Accounts Customer Records
- Sage 50 Accounts as one of your Power BI data sources
- Power BI templates for Sage 50 Accounts - an introduction
- How to update your Suntico BI Connector software
- How to uninstall your Suntico BI connector for Sage 50
- The 5 Ws of Power BI for SMBs and accountants
- Connect Power BI to Sage 50 Accounts - Part 4 of 4
- Power BI vs Tableau: who is winning the mindshare?
- My favourite three Power BI DAX functions
- DAX Running Total Calculation in Power BI
- Power BI Adoption By Country

- OData (21)
- Power BI Desktop (16)
- Sage 50 Accounts (14)
- Accounts Receivable (12)
- Sage 50 (11)
- Power BI (11)
- ODBC (8)
- Sage 50cloud (7)
- Reporting (5)
- Accounting (5)
- Power BI Pro (5)
- accountant (3)
- accountants (3)
- sales orders (3)
- DAX (3)
- Connect Power BI to Sage 50 (2)
- currencies (2)
- customers (1)
- power bi datasources (1)

- Transaction Splits (1)
- sales ledger (1)
- sales invoices (1)
- product sales (1)
- stock (1)
- suppliers (1)
- metadata (1)
- chart of accounts (1)
- Maps (1)
- nominal records (1)
- nominal budgets (1)
- outstanding (1)
- overdue (1)
- sales (1)
- Power BI Examples (1)
- Days Sales Outstanding (1)
- cash flow (1)
- Connect Power BI to Sage 50 Accounts (1)

Clarehall Shopping Centre,

Dublin,

Ireland