Dynamic monitoring of overdue accounts receivables

Today I want to tell you about the implementation of a flexible automated solution for tracking the dynamics of counterparties accounts receivable (CR), which I developed for one of my clients. In order to mitigate the risks of non-payments, a Dynamic Monitoring Model of CR was developed, which allows you to see the deterioration in debt service quality. Here, dynamic monitoring is understood as three equal periods, which are counted retrospectively from the current date for a total of six months, and for which the dynamics of CR for each period is compared.

This model was based on three indicators for comparison, namely:
1. Number of times of delays
2. Number of days overdue
3. Amount of delay

To summarize the information in total for the three above indicators for each counterparty, a scoring system with color indicators was chosen:
• green (there is no negative dynamics for the counterparty),
• yellow (there has been a negative trend for the counterparty),
• red (there is a significant negative trend in the counterparty).

This task as a whole turned out to be not trivial, since one Buyer can have several contracts and several shipments in each period under consideration. For each contract and shipment, there may be separate delays with different amounts, overdue days, and the number of times the delay was reached.

Additional tasks were minimization of manual labor, ease of unloading report generation and speed. It was important for the management to see the overall counterparty picture with a general indicator, as well as to be able to study all the source data in detail. Taking into account all the requirements, it was decided to implement the model using OLAP cubes in SQL Server Analysis Services (SSAS). Interactive analytic processing of OLAP cubes is a feature in Microsoft Service Manager that uses the existing data warehouse infrastructure to provide self-service business intelligence functionality to users. I also finalized the regulations for working with overdue debts, written and video instructions for the report were recorded.

By implementing this solution, the client achieved the following goals:
1. Implemented the ability to build predictive models of the debt service quality by customers with subsequent analysis by responsible departments;
2. Reducing the total amount and number of overdue receivables days and, as a result, the release of working capital;
3. Speed of response increasing in case of counterparty risk profile change.

Despite everything, I continue to publish articles about business development, incl. in digital and information technologies, and also continue to provide business consulting and implementation of the Vuvino board game project: https://vuvino.ru/. If you are interested in articles on these topics, then subscribe to my Telegram channel: https://t.me/biz_in. If you have a need for business consulting support, then I am waiting for you on my website: https://akonnov.ru/