Hands On

BI in Accounts Receivables

Sanjay Shah presents a picture of how AR and related data can be analyzed in the Manufacturing domain

Indian companies are struggling to reduce their operational costs. One of the areas targeted by them is the investment being made in working capital. Accounts Receivable (AR) and Inventories are two areas which are receiving considerable attention.

This article gives you a look at how AR and related data can be analyzed in the domain of Manufacturing Industries.

General Scenario

“Cash today, Credit tomorrow” reads the sign outside my chai-wala shop. Even he has to control credit. Now imagine the plight of large companies, with huge geographical spread, multiple business units, and decentralized management and so on. How can one effectively control credit in such scenarios?

All ERP systems (like SAP, Oracle Apps, BAAN etc) contain, in some form or the other, a basic report showing the Open Invoices in some pre-defined aging buckets like 0-30, 30-60 etc. The head of credit control generally assigns the task of reporting of AR to his Executive Assistant (EA). The typical process of reporting of AR consists of downloading the report from their ERP, summarizing this in Excel by customer, or by collector, and then giving this report to the management.

Creative Executive Assistants may make a graph or so to this, or study this in a trend over time etc. But as these reports are flat and lack interactivity, every question raised by the management has the EA scurrying to his computer to generate a new report. Practically for every view a separate report is generated at a huge cost of time and effort. The worst part is that this process has to be repeated month on month, as there is no automation.

BI for AR

So how does one go about generating a reporting solution which is analytical and actionable? What should the report contain, so that the management can find what it is looking for, rather than keep searching through the myriad permutations that a typical BI report tends to give?

In my practice I had once designed an analytical report in the area of AR. I had used Excel Pivot tables as my reporting tool. The final report enabled the user to generate many different views and graphs. I thought that I had designed the best possible report in the world. I went (very proudly) and gave the presentation to the MD of my client company. I showed them the report and how they could easily navigate and create various views of data and drill down from the highest to the lowest detail with the click of a button. There was no response from the MD. I showed them various interactive graphs. Again there was no response from the MD. I started thinking that this man has an attitude and does not want to praise such a fantastic report generator created by me!

As I was about to pack and leave (dejectedly), the MD asked me whether I had heard of a game called the Rubik’s Cube. Of course I had heard about it. In that game one could keep turning and twisting the sides endlessly till the colors of each side were the same. “Your MIS”, he said, “is like the Rubik’s cube. One can generate a fascinating number of views using this. It would be good, if I had unlimited time. But in any business the costliest commodity is managerial time. Design your solution in such a way that I get reports which are actionable. Your reports should quickly tell me what I need to see, so that I can act fast and stay competitive.”

I felt like a punctured tire. What he said was one hundred percent correct. Then he sat and discussed various actionable information with me which he wanted to generate in this domain. I learnt a lot from him, and hope what I mention below helps you.

Intelligent Dimensions in AR

On thinking about what that MD told me, I realized that there were two types of dimensions to be put into any BI report. Type-1 dimensions are those which I call the ‘natural’ dimensions. Type 2 dimensions are what I call the ‘intelligent’ dimensions.

Natural dimensions are ones which come directly from the ERP databases. For e.g. customer code and name, customer group, customer vertical, year, month, week, etc.

Intelligent dimensions are the ones which can be generated by applying business rules. These dimensions help you to see ‘what you need to see’. They filter out the clutter and help you concentrate on what is important.

Intelligent dimensions cannot be standardized for all situations. They have to be built on a case to case basis. Even two companies in the same domain may require different dimensions. MIS has to ultimately match with the creativity of the entrepreneur, and that can never be standardized. In each case the management must specify what they want to see, and the BI designer must design accordingly. It would be great if the BI designer is able to add value based on his experience.

I will share some of the intelligent dimensions and reporting which we have done in our practice as follows:

Classify dues into Due Categories:

Generally dues are classified based on their age groups e.g. 00-30 days, 30-60 days, 60-90 days and > 90 days. This does not bring out the criticality of dues. For example in your business a 60 day overdue from a private company may be critical, but for the customer who is a government body it may be 180 days after due date to become critical. Therefore classify the customer dues as:

• Not Due
• Overdue
• Critical Due
• Unadjusted Credits
• Provisions

It would be further interesting to create day wise aging categories within each of the above categories, for example within critical_due category, you could see what is critical > 90 days, > 180 days etc.

Give Early Warning Indicators

Create dimensions like:

• Potential_Overdue
• Potential_Critical_Due
• Potential_Provision

The potential categories tell the user where a particular due will be classified if it is not collected by say the end of the reporting month.

Rectify the Due Date

In many scenarios, the due date is calculated by the ERP system on the basis of the date of invoicing plus the credit period given to the customer. In reality however the customer calculates the due date on the basis of the receipt of material at his location. This can lead to a considerable amount of confusion. It would be much better to recalculate the Due Date on the basis of the actual receipt of goods by the customer. However for the purpose of a good audit trail show to the user the invoice number, invoice date, original due date, revised due date and the goods inwarding date of the customer.

Show Provisioning Information

Various professionally managed companies, especially multinational corporate, have strict rules of providing for AR balances. These rules generally depend on the nature of the sale, customer classification etc. Business rules should be written to identify which uncollected invoices would have to be provided for. The fact that some invoices have got classified in the ‘Provision’ category means that something somewhere is going terribly wrong. Once an invoice has got classified under Provision, it should show up as a separate category as shown in above under Classify into due categories.

Show Pareto analysis
(ABC Analysis)

Draw attention to the top nn debtors or the top nn% debtors. Or classify the debtors into buckets based on amounts. For e.g.

• 00 - 10 lakhs
• 10 – 20 lakhs
• 20 – 50 lakhs
• … and so on.

Be sure that the user is able to drill down from this to see the customers in each of these categories.

Show Relative Information

Calculate ratios which show the relative importance of various factors, so that such information does not get hidden in the big numbers. For e.g. show the following ratios:

• Overdues to Total Dues
• Critical Dues to Total Dues
• Daily Sales Outstanding based on Current AR
• Daily Sales Outstanding based on Rolling 12 months Average AR

Again classify these ratios into buckets so that the most critical ones surface.

A sample AR report

Show Cross Functional Information

Show a 360 degree view by showing for each customer, in a single report, e.g. show:

• Pending Orders
• Sales
• Collections
• AR Balances

This gives a full view of the relationship with the customer, especially if it is seen in a trend format e.g. rolling past 12 months. It helps you to take a proper decision with reference to the customer’s outstanding balances.

While this is complicated to do, it is what BI’s all about!

Calculate the actual Credit Period enjoyed

Go a bit behind the scenes and calculate the credit period actually enjoyed by the customer over the past one year. This is the difference between the receipt date and the invoice date. Compare this with the credit period as per the customers master and create a ratio of the same. This can show some very interesting trends.

Calculate Collection Efficiency

Create a mechanism of identifying the collections to be made by each responsible office (collector). Be sure to ensure that the collection target specifies from which age-bucket the collector has targeted his collections. For example collecting Rs. 1 crore from past dues is much more important and needs much more effort than collecting Rs. 1 crore from current dues.

Monitor daily collections against the collection plan and display the collection efficiency (on a dashboard). Make this as visible as the required-run rate to win the cricket match!

Remind the Customer

Out of the database which you will create for the BI reporting, create a very proactive and polite system for reminding the customer. Remind him about collections due next week, in the next month etc. This can work wonders.

Report according to Needs

Create Key Performance Indicators in this domain e.g. Overdues should be 40% of Total Dues etc.

Display summary information in a dashboard for senior management. However design the report in such a way that summarization does not hide lower level problems. The senior management should be able to see the big picture, but also get an indication of any problems down below.

Give analytical information to the analyst who can drill down to the lowest level of detail and can diagnose the problem. Show him the analysis of AR as on a date, and also a trend so that he can study a behavior pattern over time.

Don’t keep the reports to the boss and his EA. Distribute the interactive reports widely to all those who are responsible for collections, so that each person can analyze, review and monitor his own performance.

Calculate Potential Losses

Calculate potential interest loss due to delayed collection, both on open items as well as on collected items. It can indicate the magnitude of the problem of delayed collections.

Also calculate and show the potential exchange gain/loss of open invoices. This too can be an eye opener in these days of wildly fluctuating currency rates.

The BI solution described above will help you in not only controlling your dues, but will also give you the benefit of person-independence and load-independence in reporting.

Please don’t cut-paste this to your scenario. That would be pushing a solution to a problem. Each scenario is different, analyze yours and build suitable intelligent dimensions!

Sanjay Shah (B.Com, CA) is the CEO of Prosys Infotech Private Limited, a Pune, India based company specializing in developing BI solutions. Prosys has developed BI solutions for various companies like Honeywell Automation India Ltd., Alfa Laval India Limited, Tata Auto Components Ltd., Kirloskar Group Corporate Office, Kansai Nerolac Paints Limited etc. He can be contacted at sanjay@prosysinfotech.com.