Saturday, March 20, 2010

Days sales Outstanding(DSO)

Days Sales Outstanding :
A measure of the average number of days that a company takes to collect revenue after a sale has been made. A low DSO number means that it takes a company fewer days to collect its accounts receivable. A high DSO number shows that a company is selling its product to customers on credit and taking longer to collect money.

Days sales outstanding is calculated as:

Accounts Receivables
= __________________ X Number of Days
Total Credit Sales

Conventional DSO = (Total outstanding receivables/Total sales for prior DSO days) * (DSO days)

OR
DSO FORMULA:
============


DSO = (TOTAL RECEIVABLES * System Option Value) / TOTAL SALES

Total outstanding receivables is calculated as sum of ACCTD_AMOUNT_DUE_REMAINING from payment schedule for classes 'INV', 'DM', 'CB','DEP' and 'BR'. The sum does NOT include ACCTD_AMOUNT_DUE_REMAINING for classes 'CM','GUAR' and 'PMT'..The same is true while calculating total sales for last DSO days.

The way you can use this column is to know how many days are in your period definition in System Options. If it is defined at 90 days, you as a collector should be able to divide this number by the DSO and know there has not been any purchases on the account for quite some time. Based uponthe functional balance due and the DSO column, they higher this number, the longer it's been since a sale was made to this customer.

This column is used as an indication of how delinquent a customer is and how frequent sales are made. The higher the number, the less sales in the number of DSO days you have set in the System Options. If this number is low, this is a indication the customer is a frequent buyer as well as current on payments.


Conventional Days Sales Outstanding (DSO):
Multiply the customers current A/R Balance by 30 and divide by prior period sales.
At a specific point-in-time, measure indicated how long it takes to convert receivables to cash. Interprets trends in receivable turnover. You must set your DSO calculations based on number of days in your accounting month - usually 28 or 30 days.

True DSO :The accurate and actual number of days credit sales are unpaid. This is a complicated formula as you have to tie every invoice back to net sales for the month in which the invoice originated.
Formula: True DSO per invoice = Number of days from invoice date to reporting date * (invoice amount / net credit sales for the month in which sale occurred)
The sum of True DSO for all open invoices = True DSO per total accounts receivable. 


Script for Days Sales Outstanding (DSO) :
==================================

In order to make sure that the value displayed is correct:

exec fnd_client_info.set_org_context(&org_id);

TOTAL OUTSTANDING AMOUNT:
=========================
select
SUM( DECODE(PS.CLASS, 'INV', 1,'DM',1, CB',1,'DEP',1, 'BR',1, 0)
* PS.ACCTD_AMOUNT_DUE_REMAINING ) * MAX(SP.CER_DSO_DAYS)
TOTAL_BALANCE_DUE,SUM( DECODE(PS.CLASS, 'INV', 1, 'DM', 1, 'CB', 1, DEP', 1, 'BR', 1, /* 10-MAR-2010 Implementation */ 0) * DECODE(SIGN (TRUNC(SYSDATE) - PS.TRX_DATE - SP.CER_DSO_DAYS), - 1, (PS.AMOUNT_DUE_ORIGINAL + NVL PS.AMOUNT_ADJUSTED,0)) * NVL( PS.EXCHANGE_RATE, 1 ), 0)) TOTAL_CREDIT_IN_DSO_DAYS, MAX(SP.CER_DSO_DAYS) DSO_DAYS

from
ar_payment_schedules ps,
ar_system_parameters sp
where

customer_id = &customer_id;

If the user was querying the Customer Accounts form based on customer and site then add the following where clause to the above sql :" and customer_site_use_id = &site_use_id".


The above script will give you the values for TOTAL_BALANCE_DUE and TOTAL_CREDIT_IN_DSO_DAYS.

To get the DSO value use the formula:

TOTAL_BALANCE_DUE / TOTAL_CREDIT_IN_DSO_DAYS

Another way of finding the DSO value is to run the following:



TOTAL RECEIVABLES
=================

select

sum(acctd_amount_due_remaining)
from

ar_payment_schedules_all
where

class in ('INV', 'DM', 'CB','DEP', 'BR')
and
customer_id = &customer_id;
TOTAL SALES
===========

select
sum(unit_selling_price*quantity_invoiced)
from
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl
where
rct.customer_trx_id = rctl.customer_trx_id
and
rct.bill_to_customer_id = &customer_id
and
trunc(trx_date) >=trunc(SYSDATE) -;

The System Option Value is the Number of Days defined in System Options (Miscellaneous tab) for the field Days in Days Sales Outstanding Calculation.



How is the DSO Calculated for Advanced Collections?

A code change was made to make the Days Sales Outstanding(DSO) in the Advanced Collections header match the DSO in Accounts Receivable(AR) Account Details and the Conventional DSO found in the metrics listed in the Profile tab?   In an effort to improve the performance of the Collections Agent form (IEXRCALL), the code change includes a new profile IEX: Show DSO in Header.   You may set this profile to Yes if you would like the DSO to appear in Advanced Collections, or if you are not using the DSO and would like improved performance of the form, you may set the profile to NO.

To set the profile for your User:

1)  Login to Collections Agent
2)  Navigate to Collections Agent/Collections. 
3)  Go to Edit>Preferences on the Menu bar at the top and query IEX: Show DSO in Header in the profile name field
4)  In the user value provide Yes and save it. 

When you query for a customer and you will see the DSO calculated and it will match the metric Conventional DSO value.     

DSO Formula:
DSO = (total outstanding receivables/total sales for last DSO days) * (DSO Days)
Note:  If Total Sales for DSO days is null or zero, then one(1) is substituted in the calculation.

No comments:

Post a Comment