Let's create a pivot table with the following dimensions and expressions:

The creation of the [A/R Invoice Days Overdue Bin] field in the script makes this report easy to create. In the script, we subtract the invoice due date by the date of the data snapshot and then use several nested if-statements to assign that result to a bin. As this field is relative to each snapshot's date and not today's date, we can analyze the aging report over time. The field is also a dual() data type where Current is 0, 0-30 is 1, 31-60 is 2, and so on. This feature allows us to sort the field more easily by selecting only Numeric Value option in the Sort tab.

How well a customer pays us, or their DSO is an important indicator of how important that customer is to our business. Let's continue the customer stratification exercise that we started in Chapter 2, Sales Perspective, and see how we use DSO to evaluate a customer's importance.

Let's create a bar chart with the following property options:

avg({$<_Periodicity={'Monthly'}
          ,[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'}
                    ,[AsOf Months Ago]={">0<=12"}>}
                 [A/R Invoice Balance]),[Year-Month],[Customer]))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Net Sales])*
365

We make slight changes to the aggr() function in the expression we used to calculate DSO in Exercise 5.2. calculated total DSO by year-month, We replace [AsOf Year-Month] with [Customer] as this is the dimension we use this chart. We also go as far as to use a second aggr() function to calculate each fractile as the fractile() function only works over a set of numbers. This second aggr() function creates a list that contains the DSO of every customer for the fractile() function.

Stratification by distribution divides the customers into nearly equal-sized bins. The bar belongs to the nearest quartile reference line above it. According to the previous chart, Divanoodle, Fanoodle, and Skipfire are in the first quartile. Every other quartile has two customers. As the best customers have a low DSO, we classify customers in the first quartile as A, in the second quartile as B, in the third quartile as C, and in the fourth quartile as D.

Let's add DSO to the customer stratification we started in the sales perspective:

We create the vExp_DSOcustomerStratification variable with a $1 parameter so that we can calculate different factiles using only one variable. In general, when we encounter several expression variables whose only difference is a number, we reduce them to one variable and add a parameter.

The Total Weighted stratification is calculated by first converting the letters A, B, C, and D of each individually stratified metric into the numbers 1, 2, 3, and 4, respectively. In this example, We use the match() function to efficiently turn the letters into numbers. We then multiple each number by a factor that allows us define how important each metric is to the final customer stratification. Other than the fact that the sum of the factors should be equal to one, they are completely arbitrary and depend on the business's strategy. For example, as we want to put more emphasis on the sales stratification, we multiply it by .6 and the DSO stratification by .4.

As the sum of factors is equal to one, the sum of the all the weighted stratifications is between one and four which makes it possible for us to convert it back to a letter format. In this example, we use the pick() function in Total Class to convert a rounded Total Weight back into letters. In this way, we can combine multiple customer stratifications into one. For many business users, such as sale representatives, this can help them more easily determine a customer's importance according to the business's strategy. Finally, we introduce a way to visualize how individual customer measures influence how they are classified.

Multivariate analysis leads to complex data visualization. The scatterplot matrix serves as a tool for more advanced analysts who want a rough idea of correlations and clustering between multiple variables at a glance. For example, we can observe that there is a stronger relationship between gross profit and sales than there is between DSO and either of these two metrics.

In the same way we use DSO for customer stratification, we can also use DSI and DPO for product and supplier stratification. This type of analysis helps us understand each working capital element within the context of other measurements. For example, a top-selling customer with a high DSO may be acceptable. However, we might lower the credit available to customers that buy little and have a high DSO.