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:

DaysSalesOutstanding

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.

Write A Comment