ERP Detective: The Dhaka Chronicles.... (2003)

One of the advantages of being an ERP Consultant is that you get to travel to many new places, meet new people and learn about their culture. As a person who loves travelling, I look forward to these opportunities. They rejuvenate me. Customer takes care of you really well because you are the 'Consultant', you see.

A look at my passport show that I spent 10 days in Dhaka from 7th April 2003 to 17th April 2003. 

Memory takes me back....

Sometime in March of that year, my manager called me.

"Ram, you need to travel to Dhaka", he told me. "We implemented ERP application at a company called GAFCO, headquartered in Dhaka. They are facing serious issues after go live"

"What is the issue?", I asked

"Inventory quantities are increasing at a rapid rate. No one is sure why this is happening. Even our team is not clear of the root cause of the issue. We have done a lot of analysis, but are unable to resolve it. The issue has been escalated at a very high level in the company and they are threatening legal action", replied my manager. He is usually chilled out and calm, but today he was stressed out.

Visa process took about a month. I was supposed to fly from Bangalore to Kolkata and board a 'Biman Bangladesh (BB)' flight to Dhaka.

In the morning of 7th I flew down to Kolkata. I cleared the emigration and reached the waiting area of the gate from where flight to Dhaka was posted.

Travelling to Dhaka is a short, funny and surreal experience. The fun starts at the Kolkata airport itself.

While waiting for the flight, I saw a crowd of people around a stall selling alcohol.

"Why is there a crowd there?", I enquired to a bystander.

"Funny story", that guy replied in a thick Bengali accent, "in Dhaka you can't buy beer. Since the flying time between Dhaka and Kolkata is only 30 minutes, many Bangladeshis take the early morning BB flight, reach Kolkata airport, drink as much beer as they want and take the afternoon flight back to Dhaka. This is the crowd mostly of Bangladeshis having their beer while waiting for their return flight", he told me

If you had a mental picture of a flight attendant as young, nubile and sophisticated, Biman Bangladesh will prove you wrong.

The typical flight attendant is a middle aged, saree clad lady, looking all bossy and maternal, her main task being to steer into the aircraft, the unruly mob of beer drinking men who will do anything to dodge her so as to cram that last can of beer before they board the aircraft. It is fascinating to watch the dance of the hunter and the hunted.

"Cholun dada, cholun (Come, come), fight is announced, please board the flight", she implores a shifty looking passenger.

"Didi (Madam) give me one minute, let me go to the rest room", saying this he turns towards the beer stand.

She is smarter, "Dada, restroom is on the other side", she reminds him and effectively pushes him to the restroom forcing him to spent time there that could have been fruitfully used in tucking in another can of beer. After all, once in flight, you can use the in-flight toilet, no.

(Many people do that. Once the flight is airborne and the 'fasten seat belt' sign is off, men will get up from different seats in the plane, like Bamboo Shoots sprouting in spring time, and make a beeline to the two toilets in the aircraft. This is the largest ever known migration of men to the aircraft toilet in a single flight.)

After 15 minutes of shouting, cajoling and pushing, the lady has managed to get the reluctant beer drinkers to board the aircraft.

Next comes the on board announcement

"Welcome to the Biman Bangladesh flight from Kolkata to Dhaka. The flight time is 40 minutes. Now the time is 11.30 AM. Insha Alla we will land in Dhaka at 12.10 PM"

What the.... !!!!

From then, till the flight lands in Dhaka, you are freaking out, wondering what 'Allah ka Insha' (will of god) is...

Hot, humid and sultry, Dhaka city is no different from any of the many crowded cities in North India. I was accommodated in a 5 star hotel in a quite Dhaka neighbourhood.

I took rest on that day and reported to the customer on the next day refreshed and full of energy. My first meeting was with Mr.Jalal Ahmad Khan, the CIO of GAFCO. Normally an affable man with a pleasant, smiling demeanour, he was none of those things when I met him. His brows were furrowed and his eyes had none of the mischievous gleam. If any, he looked tense and worried.

"I am worried Mr.Ram", he said as soon as we met, without so much as a cursory greeting. "My CFO is very agitated and is threatening to dump the ERP and go back to the pre-ERP days. This is a serious crisis"

*********************************************************************************

Till now, I did not have a clear idea of the issue. I had a vague idea that the inventory value is changing randomly.

*********************************************************************************

Before I proceed, let me explain to you the concern regarding changing inventory value and why it is a big issue. You can imagine the Financial Statements of a company as divided into four quadrants. 

Refer the video below.

As shown in the above video, the top left quadrant is the Costs, top right is Revenue, bottom left is Liabilities and the bottom right quadrant is the Assets quadrant. Costs and Revenue form the Profit and Loss group and Assets and Liabilities form the Balance Sheet group. The four quadrants work as a single integrated system. Any change in the value in one quadrant has to reflect in a corresponding change in value in any one (or more!) of the four quadrants.

'Inventory' is one of the elements in the 'Assets' quadrant. In normal operations, business raises a sales order, based on which material is shipped. As the material is shipped, three events happen. One, revenue of the company increases due to the sales. Two, inventory decreases as material is consumed. And three, the Costs go up to reflect the fall in inventory (Inventory is converted to cost). The difference between revenue and costs is the profit for the company.

Asset value is reduced. It is converted as an increase in the costs. The balance in the system is restored.

What happens with an abnormal increase in inventory? Ideally the reverse of above must happen. The costs should come down. But that will not happen since cost is an exogenous variable. Something physical has to happen for the costs to change. Since there is no physical change in this case, costs will not increase.

So what gives?

The notional revenue goes up as shown in the video. Since there is no impact on the costs, the incremental increase in inventory reflects as profit for the company. It is as if god has gifted you with an eternal flow of inventory and eternal profits without you having to spend any effort.

Cornucopia, right?

It would have been ecstasy if it were not for those pesky people called auditors. They are the killjoys of the business world. These hawk eyed pessimists are always suspicious of anything abnormal. They ask questions. Like where did the inventory come from? how was it paid for? who approved the receipt of the inventory? where is it stored? (these spoilsports even would want to see the inventory !!!). Did the company pay tax on excess profit?

Questions, questions and more questions...

In the absence of satisfactory responses, they will jump to the conclusion that the managers have an ulterior motive in abnormally increasing the profits. They will present their report to the authorities and before you know the 'C' suit guys in the company (Chairman, CEO,CIO, CFO..) will be cooling their heels in the bull pen, looking at 8 to 10...

*********************************************************************************

Mr.Jalal put me in touch with Mr.Javed, the CFO of GAFCO. Mr.Shadab Shaikh, the IT team member in charge of supply chain also attended the meeting. 

"We observed the issue from day one of go live about two months ago. For some items, the inventory quantity increases randomly. We observed very closely, but we could not find any pattern. For instance, in the evening, at the close of the day, we note down the inventory quantity. Let us say it is 15. We do nothing through the night. Next day morning, we check the inventory for that item and it has gone up. We do not do any transaction in the morning and when we check in the afternoon, the inventory quantity has gone up again", Mr.Javed summed up the issue succinctly

"We are totally at a loss", said the weary CFO.

I asked Shadab Shaikh if he has observed anything.

"I feel that some internal process is triggering these random increases in inventory. We did some analysis but could not find any process that could cause this", he informed

This happens all the time at the customer site. You ask the IT team members for their observations and they come back with their conclusions.

"Mr.Javed mentioned that this problem is happening for some items. Have you been able to identify any patterns?", I asked. Of course I knew the answer would be 'no'. Once you identify a pattern it is easy to narrow down the issue. As of now, the problem at GAFCO was wide open.

"No", he replied as if on cue, "we have not been able to identify any pattern. Initially we thought that the item quantity was doubling. That turned out to be not the case. We are stuck."

"You mentioned that the quantity of inventory was increasing. Has there been cases of abnormal decrease in inventory quantities?", I asked looking at both of them.

"None", Mr.Javed replied quickly

That was it.  There was nothing further to be asked.

*********************************************************************************

Later that day Shadab Shaikh showed me the issue in the system. First he opened and excel sheet..

"I have taken these three items for example. The excel sheet shows the quantity that was available for these items when I checked just before our meeting. Two of these items are problem items.", he said

He logged on to the system. Sure enough the quantity had changed for the first two items and not for the third item. I looked at the initial and current quantities of these two items and tried to see if any obvious patterns existed. Normally I check if the quantity has doubled, increased by a constant factor etc. The data did not fit any pattern.

I also looked at the transaction register. It showed that the last transaction on these two items was done the previous evening. 

No transactions, but stock quantity had randomly increased.

I was excited. This was not a simple issue. As an ERP detective, I like to go after such challenges. The quest will not only resolve the issue in the end, but also will improve my knowledge and is intellectually stimulating as well. 

Over the next four to five days, I and Shadab Shaikh did a battery of tests covering different inventory processes in the hope of identifying the root cause of the issue. We did procurement, warehouse transfer, production issue, production receipt, sales consumption, miscellaneous receipt, miscellaneous consumption, physical inventory and cycle counting. The objective was to identify if any specific transaction caused the issue.

Everything was working well. 

My return ticket to Bangalore was booked for 17th April. Date was fast approaching and we were still clueless. All the stakeholders including my manager, Mr.Jalal and the CFO were becoming jittery as the return date approached.

I did not allow it to affect my work. I continued painstakingly testing the various scenarios and documenting results. 

One clue kept haunting me. Some items were not impacted. What was the difference between those items and the others where the problem was occurring?

I and Shadab Shaikh worked on the weekends as well. We toggled between front end and backend. We wrote various queries and did detailed analysis.

Finally on the 15th of April, we got a break.

We found that all the items that had problems were received in a warehouse other than the 'Primary Warehouse'. Those items that were received in Primary warehouse did not show any quantity issues.

The company used to receive materials in five different warehouses, one of which was configured as 'Primary Warehouse' in the application. By default, materials are received in the 'Primary Warehouse'. For receiving material in any other warehouse, the user was overriding the primary warehouse at the time of material receipt.

This was a key finding. However, two questions remained. At what point during the 24 hours was the quantity changing? What was triggering this random change in inventory quantities?

We continued testing. With hardly a day to spare, Shadab Shaikh was losing confidence in the consultant (that is me) and by extension on the ERP application.

I didn't give up. I don't give up.

Finally on the morning of 16th of April, we hit pay-dirt.

Shadab Shaikh and I were analysing a new item that we configured. We created a purchase order for that item and received 7 units of the material in a non-primary warehouse. We checked the inventory quantity and it was updated as 7. We did some more analysis and came back and checked the quantity. It was showing 14.!

We were baffled. 

It was a new test item and no other person had access to creating transactions on that item. So how did the quantity jump to 14, the amount which was double the original receipt quantity?

I asked Shadab Shaikh check the inventory quantity in the application. Just check. Open the application and query the item quantity. That is all.

As he did it, I refreshed my query.

The quantity was updated as 21 !!!

The very act of checking the item quantity in the application led to increase in quantity equal to the original receipt quantity !!

It was like the story of Atlas and Hercules. Every time Hercules threw Atlas on the ground, he rose back with renewed strength. Just as the act of touching the ground increased the strength of Atlas, the act of checking the inventory quantity led to their quantity increasing !!!

(Technically it is called 'After Select Trigger', but that is not important)

We had identified the root cause just in time. Once the root cause was identified, it was simple to define a new process to always receive the material in Primary Warehouse and then transfer the same to the other warehouses where material was needed. 

Me and Shadab Shaikh met Mr.Jalal in his office. My manager was also present. 

"The root cause of the issue has been identified and resolved. The solution calls for a minor process change which I will coordinate", Shadab Shaikh informed them.

Mr.Jalal looked at me. "How confident are you that the issue won't recur?", he asked

"100%", I replied confidently.

That was what he wanted to hear. That was the confidence that he wanted to see in the consultant.

For the first time in 10 days, I saw Mr.Jalal smiling that beautiful smile of his.

*********************************THE END**************************************