AR Days Sales Outstanding (DSO)

DSO i.e. Days Sales Outstanding is undoubtedly the most popular KPI in Accounts Receivable and has visibility right from CEO to operational managers. It can be seen as a “High-Impact” metric for monitoring, wherein a small deviation gets noticed along the entire management hierarchy. Incidentally, this KPI is also used by external investors to evaluate financial health of a company. DSO is similar to AR Turnover Ratio wherein it indicates average time taken for the company to collect payments from customers and expressed in number of days. 

DSO is derived partially from financial statements for a specific time period and it goes without saying that the measures are semi-additive in nature and time context is mandatory. The formula is very simple and all values are derived for a said period.

DSO = (Ending AR / Total Credit Sales) * Number of days

The key here is “Credit Sales”, which cannot be derived from financial statements, but has to be computed from sales orders booked with different credit payment terms. Numerator indicates the quantum of actual collections made against receivables and denominator indicates the quantum of receivables that were introduced.

For example, for the month ending March-2016, if the AR Balance is $15,000 and Sales was $10,000 the monthly DSO would be 46 days. Assume the quarterly credit sales was $35,000, then quarterly DSO would be 39 days. As you can see, the length of time context can have a considerable impact on the value of DSO and has to be handled carefully because reporting with partial context will convey wrong message to the user.

Since Days Sales Outstanding represents effectiveness and efficiency of Accounts Receivable function in a single KPI, it goes without saying that any AR dashboard is incomplete without DSO.

KPI Context – Sales or Revenue or Both

One often encounters the scenario above while developing dashboards or reports. It is easy to be confused because many times these two terms are used interchangeably but essentially mean two different things.

Only difference between a measure and Key Performance Indicator is the meaning & context attached to it.

Depending on the audience the KPI Sales and Revenue will mean two hold different scales of importance, for example a CEO is interested in Sales first and Revenue second whereas CFO looks at Revenue first and Sales later.

kpi_sales_revenue_differenceSales and Revenue are interlinked measures. Sales as a KPI indicates the amount of “Expected Revenue” in the forthcoming future whereas Revenue as a KPI indicates the actual amount of “Sales Realized”. For example, A sale made for $10K in Jan-2016 might be fulfilled shortly, but actual money collected i.e. realization might happen in Feb-2016 or later. Due to this time difference, while reporting both metrics together time context plays an important role.

Sales is precursor for Revenue, which is monitored closely but not disclosed to public whereas Revenue is actual money in the pocket and disclosed to public through financial statements.

Factless Fact Table

In Data Warehouse parlance a “Fact Table” is the central data repository for all transactions related to a subject area. From this perspective, the concept of a fact table without fact data sounds like an oxymoron, but this concept of “factless fact” has special place within BI context wherein the analytical scenarios vary slightly from traditional reporting.

As the title suggests, a factless fact table is a star schema model does not contain any measures. In reality, it is common to have a single measure that acts as a flag or value depending on actual data it contains. The most common examples for this model are the Attendance tracking system and Student Enrollment system.

In the Leave tracking example, a measure is not required becausefactlessfact1 a valid record is created only when an employee actually avails leave. In majority of cases a single measure with constant value as is updated. At times, having a single measure to act as flag can help in invalidating leave records, for example if an employee cancels a leave, a valid leave will be flagged as 1 and a cancellation will be flagged as -1.

Similarly, the student enrollment example too factlessfact2draws several analogies with the previous example. In order to identify which student has enrolled for which course, a simple factless fact would suffice. This model can be easily extended to track more than just enrollment with additional measures such as attendance, GPA etc, which is typically adopted during actual implementation.

Factless fact models are also applied in queue based transaction environments. A queue based transaction is similar to an activity that has to pass through multiple stages before completion. Process of interviewing a candidate for job opening is an example of this type. transactional_queue_exampleSomer examples are Insurance Claims processing, Customer Service Request Interaction, E-Commerce Order shipment tracking etc.

Although conceptually a factless fact table exists, but in real-world scenarios they are used rarely. A factless fact data model provides only COUNT aggregation method to be used, which is a big drawback whereas a robust data model can provide the much more analytical scenarios.

Two major reasons for factless fact data models to be ignored by data architects today are

  • Overhead of maintaining multiple models for analysis and its associated maintenence
  • Increase in processing power (i.e. query execution time) of systems today has made this redundant

AR Turnover Ratio

Accounts Receivable Turnover Ratio is a KPI used to ascertain overall health of a company both internally and externally. Standard Financial Reports i.e. Balance Sheet & Income Statement of a company provide data necessary o calculate AR Turnover Ratio. Internally, it is of high interest to Senior Management team due to its high degree of impact in cash flow.

The value of AR Turnover Ratio indicates the number of times receivables was collected in full for the time period under consideration. AR Turnover Ratio is calculated for long time periods, either Yearly or Quarterly. Small time frames such as Monthly or Weekly will not yield the right picture due to lack of data volume. Two key measures are needed to derive this KPI

AR_TurnoverRatio

  • Net Credit Sales for a given period can be inferred from Income Statement
  • Average Accounts Receivable is derived from Balance sheet for two consecutive time periods and calculated

The example below has taken Microsoft Inc (MSFT) data for illustration purposes. MSFT_IS_ARTurnoverRatioPeriod under scrutiny is for the whole year that ended Jun-2016.  (Note: For sake of simplicity and illustration purposes, Revenue and Sales are assumed to be the same, ideally sales and revenue are different and Sales figures are not standard component of a P&L Statement). The numerator in this example is $82.5b.

In order to calculate the denominator, the average of Net Receivables for MSFT_BS_ARTurnoverRatioJun-2016 ($18.2b) and Jun-2015 ($17.9b). Average Accounts Receivable for the time period under scrutiny is 18.05 (i.e. 18.2 + 17.9 / 2) in denominator. Standard average cannot be computed because Net Receivables is a semi-additive fact.

With above two measures, the AR Turnover Ratio is computed as 4.57 for the year starting from 1-Jul-2015 to 30-Jun-2016. It implies that Microsoft was able to fully collect all receivables 4.57 times in the year. An alternative interpretation shows that MSFT took around 80 (365/4.57) days in the year to complete one cycle of collecting all receivables.

A KPI similar in meaning with respect to AR Turnover Ratio is AR Average Realization Time. Both KPI’s share some characteristics, but are not synonymous because source data as well as calculation methodology vary largely. One cannot be seen as replacement for another because the values will be different for the same time period.

Customer Value (RFM – Recency, Frequency, Monetary)

A famous quote by Sam Walton

There is only one boss. The customer. And he can fire everybody in the company from the chairman on down, simply by spending his money somewhere else.

Determining the value of a customer is a holy grail in CRM Anaytics. Organizations adopt different methods to rank a customer in terms of value they bring to the organization. Marketing efforts are designed an spent on customers based on value rank.

Two popular methods of determining customer value are

  • RFM (Recency, Frequency, Monetary)
    • This is a simple and easy to implement solution
    • Performs ranking only and parameters can be dynamically adjusted
    • It requires basic data about a customer’s history of transactions with the organization
  • CLTV (Customer Life Time Value)
    • Complex approach and implementing takes more time
    • Requires more data for analysis to run
    • Mathematical model does not provide room for changes
    • Tries to predict future prospects of a customer

Organizations adopt RFM based analysis for customer ranking first and then graduate to CLTV based analysis. RFM analysis deals with three parameters and all three parameters are independent of each other, but are tied together by time context. A common time context should be used or else the final rank will be skewed.

Recency Recency refers to the latest time frame of a customer order. (e.g. For the year 2016, Customer A last order was on 17-Jun-2016 as of 31-Jul-2016)
Frequency Frequency refers to the quantum of orders within the referred time frame. (e.g. For the year 2016, Customer A made on average 1.5 orders per month until 31-Jul-2016)
Monetary Monetary refers to the value of all customer orders with the referred time frame. (e.g. For the year 2016, Customer A total order value is $12,500 until 31-Jul-2016)

Firstly, historical customer data is used to determine the different scores for R, F &  M. Method for determining scores depends on the business policies, which are adjusted based on different parameters. In the example above, assume that a three value scale is implemented i.e. Low = 1, Medium = 2 and High = 3

  • Recency: If customer had purchased in last 30 days, then “High” or else if purchases were made in last 90 days then “Medium” or else “Low”. In this case, score for Recency is 2.
  • Frequency: If the customer had purchased in last 30 days 5 or more times, then “High” or else if purchases were made in last 90 days 3 or more times then “Medium” or else “Low”. In this case, score for Frequency is 2.
  • Monetary: If the customer had purchased in last 30 days value of $5000 or more, then “High” or else if purchases were made in last 90 days value of $10000 or more times then “Medium” or else “Low”. In this case, score for Monetary is 2.

The above example illustrates a simple scoring model for RFM analysis. Based on the scores arrived, a simple rank  or weighted rank could be obtained as the product of three parameters as follows.

Simple Rank = 2 * 2 * 2 = 6 out of 9

Weighted Rank = [(50% * 2) + (30% * 2) + (20% * 2)]/100 = 66%

Rank thus determined is used for further analysis and the process is tweaked over period of time whenever there are changes to internal and external factors. In real-time scenarios the scoring models are complex and when used in conjunction with predictive analysis can really give deep insight into customer behavior.