Framework for Inventory to GL Reconciliation in Oracle Apps

Mr.Amar Sharma, CFO of Prime Agro Foods Limited (PAF) was a worried man.

PAF had implemented Oracle Applications in his company recently. Production Go Live was done with a lot of fanfare but post go live, the implementation was beset with a lot of problems. Most of the issues could be tracked to errors in the intake of opening balances and sub-optimal user training. 

Post Implementation, PAF requested my services for stabilizing their ERP Implementation. In my first meeting with Mr.Sharma, I asked him what is one area that I should focus on.

"One of my key requirements from ERP was that I should get the correct profitability figures from the system. This means that I will need the Product Group wise revenue and breakup of costs  component wise. ", he told me.

While I understood his expectations, I asked him to clarify what he meant by the term 'Breakup of Costs Component Wise'.

"The Cost of Good Sold will contain the following components. One is the cost of materials consumed. This is the cost of the materials consumed in the production process. Two, is the direct overheads. This is also known as Factory Overheads. This includes cost of machine usage, cost of direct labour used in the production process etc. Component three is the cost of indirect overheads, which include rent on premises, cost of cleaning and sanitation of premises, cost of electricity, water etc that is used in the factory. The cost of goods sold is the sum of the three components above, vis. COGS = Cost of Materials Sold + Direct Overheads + Indirect Overheads"

"What is the current status? Are you getting these information?" I asked Mr.Sharma

"No, currently I am neither getting the correct consumption figures nor is my inventory balances tallying with the GL Inventory Value. This means that I neither have a realistic number about my inventory nor do I get a correct figure of my consumption. I am not able to give a profitability number to my management, my MD is very irritated with me and the whole ERP Implementation."

I nodded my understanding. Sadly, this was the case in many of the implementations that I had opportunity to work on an advisory capacity. These two aspects, Inventory Reconciliation and Consumption Valuation are inter-related. Once you get one part right, the other part will automatically fall in place. 

Inventory to GL reconciliation is one of the most difficult challenges facing the ERP Implementation Consultant (during the implementation phase) and the Inventory Accountant post go live. There are three reasons why Inventory to GL Reconciliation is challenging.

One, the 'perpetual accounting method' that ERP use to account for Inventory Transactions. Traditionally inventory is accounted through a method called 'Periodic Accounting'. In this method, Purchase Account is debited at the time of purchase of an item. At the end of the month, the closing physical inventory is taken. The cost of materials sold is calculated by the formula, Cost of Materials Sold = Opening Inventory + Purchases - Closing Inventory. 

Why do the Organizations use 'Period Inventory Accounting'? This method is used because, the inventory transactions are large in number and are more frequent and is done a the store by a store clerk who is not an expert in accounting. Period Inventory accounting lets Organizations segregate inventory transactions from inventory accounting.

ERP Applications use 'Perpetual Inventory Accounting'. In this method, the inventory transactions and inventory accounting are tightly coupled. In this method, for every inventory transactions, Inventory GL Account is debited or credited (Debit for inventory receipt and credit for inventory issue). The only P&L impact is when the material is issued. For each material issue and expense account is debited and the inventory account (a balance sheet account) is credited.
Ideally this is the better approach, but it is fraught with many challenges. Configuration could be wrong-Some inventory transactions could be going to a different account than anticipated, unaccounted transactions, transaction errors, timing differences.. All could lead to reconciliation mismatches.

Two, lack of conceptual clarity (by the user) of the costing method and reconciliation concepts. The second reason is essentially a lack of understanding of 'What information to look for' and 'Where to look for it'. Material costing / valuation is concept oriented. Each costing method works in a different way and provide different output. For example, FIFO, FEFO, WAC, PMAC, Lot Costing and Standard Costing all work differently and provide different valuations for the same inventory. (If you do not know what each of them are, read (THIS). So a consultant, not only should know the costing method used, but also should know why that particular method is used in the first place.
When it comes to reconciliation concepts, there is internal reconciliation (within the sub-ledger) and then there is external (GL) reconciliation.

Three, large number of transactions. This is self-explanatory. Due to huge number of inventory transactions, identification of reconciliation issues is very challenging. One needs a structured approach to process the reconciliation and identify and resolve reconciliation issues.

"Sadly the problem that you are facing is not new. This is the case in many ERP Implementations that I have reviewed. It has come to a stage where I can review any implementation and ask a question, how are you reconciling inventory with GL and be responded with blank stares". I told Mr.Sharma

"So what is the solution to the issue? Are we to live with this mess for the rest of my tenure here? If this goes on, I can be sure that it is going to be a short one", mused Mr.Sharma bitterly.

"Don't you worry, you are in expert hands", I tried to pacify him, "I will help you resolve the issue. In my line of work, you can't become an expert without handling such challenges"

"How will you do it?", Mr.Sharma looked up, perhaps the first time, with hope in his eyes.

"First of all, I have to explain to you some concepts", I replied, "There are four data points to be considered while trying to reconcile Inventory to GL. Two of them are in GL and two of them are in Sub-ledger. The four data points are:

1. GL YTD Balances for Inventory group of accounts
2. GL Period balances for the Inventory group of accounts. 
3. Inventory Period End Balances in Inventory Sub-ledger
4. Inventory Transactions in Inventory Sub-ledger.

All of the four data points are linked together by the cost of inventory.

The four data points are shown in the diagram below.

Inventory closing balances should tally with GL YTD Balances and Inventory Transactions should tally with GL Period Balances", I concluded

"I am impressed", said Mr.Sharma. Coming from a CFO, it is a huge compliment. They are not normally given to expressing their emotions. Normally they are dour and acerbic and morose. (I am joking Mr.CFO, I am sure that there are pleasant CFOs out there, like Unicorns or something)

"Thank you Mr.Sharma", I replied graciously. "Coming to the reconciliation process, and here I am using reports in Oracle EBS as a reference, there are four reconciliation points that you have to consider. One could call them the 'Four Pillars of Reconciliation'. While one of them is the internal reconciliation (Within the sub-ledger), the remaining three are a part of external reconciliation (Sub-ledger to GL). These are:

1. Net Value of Inventory Transactions for the period = Inventory Valuation Report for the current period - Inventory Valuation Report for the previous period. (Internal Reconciliation)

2. Net Value Inventory Transactions for the period = GL Period Balances in Inventory Account for that period (GL Reconciliation 1)

3.  Closing Value of Inventory as per Inventory Valuation Report = GL Inventory YTD Balance in Inventory Account (GL Reconciliation 2)

4. COGS Value from OM Subledger = PTD Value of Material Consumption Account + PTD Value of Direct Overheads + PTD Value of Indirect Overheads (GL Reconciliation 3)

The above processes are shown in the diagram below.

"I see that in GL Reconciliation 3 above, you have an item called 'PTD Value of Material Consumption Account'. That should be my material consumption, correct?", asked Mr.Sharma

"Consumption is value of materials consumed in the process. Ideally it should be the PTD Value of Material Consumption Account as shown in point four above. However, in case you also have 'Miscellaneous Inventory Issue' transactions, you have to add value of those transactions to the above to get the consumption numbers.", I replied

"The formula is: Consumption for the Period = PTD Value of Material Consumption Account + PTD Value of Miscellaneous Issue Account", I continued


"You mentioned earlier about a structured approach to inventory reconciliation? What do you mean by that?", queried Mr.Sharma.

"Yes, I did mention 'structured approach'. In my opinion, there are various aspects to this. The approach starts at the time of solution design itself. User should have a clear view of various inventory transactions - Miscellaneous receipt, miscellaneous issue, PO receipt, Sales Issue, PO Returns, PO Corrections, Sales Returns, Physical Inventory Adjustment, Cycle Count Adjustments, Issue to Production, Production Yield, Material Transfer (to name a few) - how they should be accounted and how they are accounted. In addition user should be aware of additional cost generating transactions (without any material transactions) including cost adjustments, cost allocations and cost revaluations and how these impact Inventory Accounting. All these impacts should be tested thoroughly and documented clearly.

The next aspect is to build in adequate controls in the system so that Inventory account are not impacted by manual intervention. Most ERPs provide a whole host of system features to prevent manual interventions to the 'Control Accounts'. These controls should be tested, documented and implemented. In addition, the access to material transactions should be limited to Store Keepers and Store Managers. There are additional control features that the user can implement regarding the system configuration accounts.

Another aspect to the structured approach is to start reconciling from top down. This is something that I have found useful. Normally you should start reconciling at the global level and down through the individual warehouse level an through the transaction sources level. It is very easy to get overwhelmed by the volume of transactions and the hugeness of the effort required to reconcile the same.", I concluded.

"Wow ! that is a lot of information for me to handle", said Mr.Sharma. "I am very happy that you are here to support us. We SMEs do not have the capability to review the caliber of the consultants. We are fortunate that we found you. I am sure that with your expertise, we will be able to stabilize our ERP System much more quickly than normal". 

"Thank you sir. I will try my best. May I say that I may be a bit expensive, but I am the best insurance you have against delay in stabilization of your application and the delay in getting ROI on your ERP Investment.", I concluded.

Post Script: You can also read THIS POST

About me: I am a senior ERP Consultant / Expert / Advisor. I can be contacted at vkrama01@gmail.com or Twitter @vkrama01