Stratifying collection lag in days for Sales

You have been presented with a data file containing customer wise bills raised with bill number, bill date. bill due date, collection date and collection amount.

 

Your analytical requirement is to present the collection lag in days from the data file as a profiled report as below -

 

0 - 7 Days | No of Collections | Amounts Collected

 

7-30 Days | No of Collections | Amounts Collected

 

30- 60....60-90.....90-180 and 180 & above

 

This report requires two simple steps in IDEA.

 

a. Append a virtual numeric field titled 'Collection Lag in Days' with the criteria through the Equation Editor being @age(Collection_Date, Bill_ Due_Date)

 

b. Perform a stratification with fields to stratify being 'Collection Lag in Days' created in a. above and fields to total on being Collection Amount. Using interval in the stratification dialog box you would need to build stratum like 0-7, 7-30, 30-60....so on.

 

The stratification result will give you a report as desired plus you can convert the result into a graph of your choice to visually/pictorially show case delay in collections over 180 days.

 

Kind Regards

 

Jairam