Finding the details behind Days Sales Outstanding
You can use the Collections Management Days Sales Outstanding window to calculate the average sales per day and average days outstanding in a rolling period. This information indicates how much you are selling, and how fast you are receiving payment for sales.
The window is found in Sales pane —> Inquiry —> Days Sales Outstanding:
Every so often we get a question on how these values are calculated, and that if you wanted to recreate them using the data in SQL, how to do so. The following script should help provide you this information:
DECLARE @begindate AS DATE DECLARE @enddate AS DATE DECLARE @numofdays AS INT DECLARE @SalesForPeriod AS NUMERIC(19, 5) DECLARE @SalesOutstanding AS NUMERIC(19, 5) DECLARE @DaysSalesOutstanding AS FLOAT DECLARE @AverageSalesPerDay AS FLOAT SET @begindate = '2017-12-1' --Set the Beginning Date SET @enddate = '2017-12-31' --Set the Ending Date SET @numofdays = Datediff(day, @begindate, @enddate) + 1 SET @SalesForPeriod = ( isnull((SELECT Sum(ortrxamt) FROM rm20101 WHERE ( docdate >= @begindate AND docdate <= @enddate AND rmdtypal IN ( 1, 3, 5 ) AND voidstts = 0 )),0) + isnull((SELECT ORTRXAMT=( Sum(ortrxamt) * ( -1 ) ) FROM rm20101 WHERE ( docdate >= @begindate AND docdate <= @enddate AND rmdtypal IN ( 7, 8 ) AND voidstts = 0 )),0) + isnull((SELECT Sum(ortrxamt) FROM rm30101 WHERE ( docdate >= @begindate AND docdate <= @enddate AND rmdtypal IN ( 1, 3, 5 ) AND voidstts = 0 )),0) + isnull((SELECT ORTRXAMT=( Sum(ortrxamt) * ( -1 ) ) FROM rm30101 WHERE ( docdate >= @begindate AND docdate <= @enddate AND rmdtypal IN ( 7, 8 ) AND voidstts = 0 )),0) ) SET @AverageSalesPerDay = @SalesForPeriod / @numofdays SET @SalesOutstanding =(SELECT Sum(custblnc) FROM rm00103) SET @DaysSalesOutstanding = @SalesOutstanding / @AverageSalesPerDay SELECT @numofdays AS 'Number Of Days' SELECT @SalesForPeriod AS 'Sales For Period' SELECT @AverageSalesPerDay AS 'Average Sales Per Day' SELECT @SalesOutstanding AS 'Sales Outstanding' SELECT @DaysSalesOutstanding AS 'Days Sales Outstanding'
To use it, simply copy the code into a query window opened against the company database, enter the starting and ending dates, and execute:
The first section calculates the Sales For Period, achieved by adding the Sales/Invoice, Debit Memo, and Service/Repair Document types in the RM20101 and RM30101 and subtracting from that value any Credit Memos and Returns.
Next, Average Sales Per Day is calculated by dividing the Sales For Period by number of days:
Sales Outstanding is generated by summing the Customer Balance from the RM00103:
Finally, Days Sales Outstanding is calculated by dividing the Sales Outstanding by the Average Sales per day:
This is a useful way to get the detail behind Days Sales Outstanding so that you can improve Collections turnaround time and get paid quicker.
The post Finding the details behind Days Sales Outstanding appeared first on Professional Advantage Blog.
*This post is locked for comments