GDPR Declaration !!

I am not collecting any personal information of any reader of or visitor to this blog. I am using Blogger, provided by Google to host this blog. I understand that Google is using cookies to collect personal information for its Analytics and Adsense applications.

Sep 14, 2018

ERP Detective: The mysterious affair of seesawing stocks (2001)

"We have a major problem in XYZ" informed my project manager.

It was in the year 2001

In the early 2000's, I was working as an ERP Consultant, implementing an ERP product called ABC. This was a Swedish Product and my employer was the only ABC Partner in India. This product was widely sought after by European companies who had set up shop in India

My company did a lot of ABC Business.

I had joined my employer in December 2000 and after about a month of training, was assigned to implement ABC in XYZ India Limited, a manufacturing company based out of Hosur in Tamil Nadu.

I implemented ABC in XYZ from January 2001 thorough November 2001. The implementation was a success and company was very happy with the implementation. I still  have the testimonials and the leather bag that the company gave as a memento after the successful implementation.

In the early 2002, I was assigned to implement ABC for STC Pumps Limited, a leading pump manufacturer based out of Chennai. Every weekend I used to travel to my home town of Bangalore. 

During one such visit to Bangalore, I went to my base office and met my PM. 

"We  have a major problem in XYZ", said my PM

Being my first ERP implementation, XYZ was always close to my heart. I was concerned about issues in what I thought was a very good and stable implementation. 

I waited for my PM to continue.

"As you might know", continued my PM, "XYZ was bought over by World's leading manufacturing company. The new company has KPMG as the auditors. When KPMG auditors audited the books, they found that the Inventory is not tallying with General Ledger (GL, the accounting book of the company). When they analyzed deeper, they found that the fluctuation was random and they are not able to detect a pattern. Due to the seriousness of the issue, they have recommended that XYZ move out of ABC application and migrate to SAP immediately. The MD of XYZ, Mr.Mukherjee, is furious because he just implemented ABC at a considerable cost and has informed us that Inventory issue is related to ABC and has asked us to prove that ABC application works correctly, else, he  has threatened to sue us" informed my worried PM.

"What has been our stance?" I asked

"We have told him that ABC application works fine in many organizations and the product is stable. But MD is not convinced. We are handicapped because we are not able to find the root cause of the issue" answered my PM. 

The issue was serious enough. In any company, Inventory, also called Stock in India, is the most difficult to control and most prone to misuse. A decrease in inventory could mean anything from lack of tracking all the way up to theft. An ERP Application like ABC brings in tighter control and integration within the organization by providing strict process control in the way the organization processes and consumes the inventory. In this case, it appeared that this control had been violated.

"Does it happen in case of all the items?" I asked. The objective was to see if any pattern existed.

"No, not for all items. It appears that this issue is happening only in cases where there is at least one purchase in a period", replied my PM.

That was a clue. Either the system was failing during the purchase process or there was some other explanation. It was for me to find out.

"How did they find out that there was an issue?" I queried

"This was identified when the audit team was reconciling the value of Inventory in GL (General Ledger, the account book of a company) Trial Balance report with Stock Valuation Report. They found huge difference between the values in these two reports Stock valuation report is a custom report that we had developed." replied my PM wearily. 

I had one final question.

"What have we done so far?"

"We have made multiple visits to XYZ. I have gone and analysed the issue myself. I also sent our consultant twice to check. Everything seems to be in order and system is working fine as far as we can see", answered my PM.

Any yet, we have a major issue in our hands, I thought to myself as I took leave of my PM.

Almost immediately I called Dennis, the Finance Manager of XYZ, and fixed up an appointment with him for the the next day morning. The poor chap was under such a tremendous pressure from both the auditors and his CFO that he seemed very happy to hear my voice.

On my way home, I reviewed the issue in my mind. The issue, as I understood, was that the inventory value in GL Trial Balance was different from the Inventory value as shown in the Inventory Valuation Report. This issue, usually called 'reconciliation issue' is one that normally keeps many a consultant awake for months. There could be many reasons for this to happen, but I felt that in this case there were mainly three possibilities.

1. Someone has created a direct entry in General Ledger without reflecting this transaction in the Stock Ledger (Inventory Valuation)

2. Some transactions entered in Stock Ledger are not transferred to General Ledger

3. Some other indeterminate issue.

I somehow felt that the root cause of the issue was lying in point 2 or 3 above.

Upon reaching my home, I started my analysis. The first step was to define the problem correctly. I knew that the problem definition was as follows.

1. User checks the Inventory value in the GL

2. User checks the Inventory value from the 'Stock Valuation report' 

3. User finds a difference between these values

One external element in the above process was the custom report. To run the custom report, you need the reporting tool. Since I did not have the tool, I created another report to get the Inventory Valuation.

I started analysing the problem methodically. First I checked if all the stock transactions were accounted correctly.

They were.

Then I checked if all the accounting entries were transferred to General Ledger.

They were.

If everything is OK, then stock value from my report and the Trial Balance should tally. I noted the inventory value from the Trial balance. Then I ran the Inventory Valuation Report (developed by me) and noted the value. 

Both values were matching exactly !!!

I couldn't believe what I saw. I seemed to have resolved the issue in my first attempt itself. To ensure that there was no mistake, I tried it multiple times, always with the same result. The values were matching in all the cases.

Armed with this information, I went to XYZ the next day. Before meeting Dennis, I had a meeting with Mr.Mukherjee, CEO of XYZ. He was also very worried and was under a lot of pressure. He wanted to have my explanation.

"These kind of mismatches cannot happen in ABC application", I told him, "this is an accounting application and only thing that we do here is comparing two views of the same information. The values have to match"

He had already heard this spiel from my PM. He was skeptical.

"What if they don't match, as it appears in this case?" he asked me.

"In that case, I  have to say that there is some problem with ABC application", I replied

This is what he wanted to hear from us. If I do not resolve this problem, then I just committed to him that the problem was with ABC appliction. And being the ABC partner in India, my company will have to take the responsibility for all the damages that XYZ has incurred, and will incur, due to this issue.

I was not even sure if I was authorized to make this commitment.

Anyways, I took leave of Mr.Mukherjee and sat down with Dennis in his office. I quickly explained the process to him and got his buy in. Having done that I demonstrated him the two reports. 

They showed a difference of Rupees 125. It seems that Dennis had created a transaction the previous day. He quickly reversed the above transaction and the values matched exactly. 

The problem was solved. It was almost too easy.

Fifteen minutes after we left him, we were back in Mr.Mukherjee's office. I told him that the issue was resolved. He couldn't believe his ears. He asked Dennis for confirmation.

Dennis confirmed.

I explained to him that the issue was related to the custom Stock Valuation Report It appeared that this report, instead of picking a single aspect of the purchase transaction, was picking two aspects of the same transaction and effectively doubling the purchase value. Since the items being purchased and the value of the purchases were different in different months, the Inventory value was behaving randomly and haphazardly. 

I did not face this issue since I did not use the above report. Instead I had developed a separate report of my own where this mismatch was not reflected.

I left XYZ office after some time and went back to my office. I went to meet my PM. He was ecstatic.

"I looks as if you have resolved the issue?" asked my PM

I confirmed that that was the case.

"Mr.Mukherjee was on the phone just now. He paid you the ultimate compliment." informed my PM.

"What did he say?", I was curious.

"He told me that 'Ramaswamy knows my business better than my people'"

That my friends, is the ultimate compliment that an ERP consultant can get from a customer.

No comments: