account-reconciliation-differences-detection

Balancing Inventory with General Ledger ‘made easy’

Once upon a time… the inventory was out of balance

When I first started working with ERP systems as an application manager, over 20 years ago, I was asked why the inventory value did not balance with the General Ledger. If I could ‘take a quick look at it’. So, from that moment on, one of my tasks consisted of matching inventory balance with the General Ledger. And every time there were variances, and something was wrong somewhere. As we did not have the tools to export the data to Excel, this meant manually comparing reports (often many dozens of pages long). For a while on a daily basis, hoping to find the cause of the variance. And even then I only knew which article caused the out of balance situation, not the transaction that caused the issue… Fortunately, after several months of intensive digging through reports and transactions, most of the issues in the processes had been solved, although each month it remained to be seen whether the inventory balance would match the General Ledger balance.

The spreadsheet! – Reconciling with Excel

Another question I got from the Finance Department: “How can we specify the balance of the suspense account ‘Received not vouchered’ more easily, without having to start over and over again?”
Each month, it took one person multiple days to specify the account. And the Head Office had just decided that the month close procedure needed to be shortened by a day each year. The ERP software we where using at the time did not offer any functionality to speed up the process. So, we ended up using a spreadsheet. Creating an ODBC connection to the database, retrieving last month’s figures from the system, running various macros, manually creating journal entries for the rounding differences and exchange rate differences (and making mistakes in these corrections, resulting in a 1 cent difference ending up as a 2 cents difference), retrieving the data from the system again, re-running macros, manually fixing the last issues and we were done. And next month we had to make sure to start with the correct spreadsheet from last month of course.

2001… My discovery of JD Edwards

Switching to JD Edwards ERP was a relief on several fronts. No more mystery about where the system stored the data, export data to Excel (even for end users) and even the possibility to reconcile General Ledger transaction in the ledger itself, even though it was still a challenge sometimes and mostly manual. And balancing inventory with General Ledger was still a challenge.
After years of being involved in software development (additional JD Edwards modules for local government), I once again found myself working for companies with inventory and manufacturing. And of course…. Balancing inventory with General Ledger. That was still a challenge. DMAAIs, General Ledger classes, Product costing, manufacturing accounting, shipments, RMA, Sales update, Intercompany sales orders, Inventory receipts, adjustments. You Name it.
And how to determine the actual inventory value? By calculation Q * P of course, but which Q and which P? And how and when do you do that? A few days after the end of the period? By taking a snapshot of your current inventory position at midnight? And what if you’re in a 24/7 production environment? And last but not least… if the inventory is out of balance, how do you know where to look and how to solve it? Preferably so that you will not be confronted with old mistakes and subsequent corrections. And without resorting a ‘lump sum’ booking that you cannot explain to your accountant. And by inventory account.
Fortunately, the explanation of suspense accounts was much easier. Or at least for those companies that use the Account Reconciliation module from Cadran Consultancy.

account-inventory-reconciliation-jd-edwards-erp-oracle

Download our Brochure

Download the brochure and discover the key-features and the benefits of the Account & Inventory Reconciliation add-on. We dive into:

Account Reconciliation
Inventory Reconciliation
Inventory Reporting
Inventory Integrity

Download now

Inventory and integrity – the current toolbox

But what about inventory? There are integrity reports. But every month reviewing those reports is no fun. Certainly not if most of the reported issues are just rounding differences, which is not unusual in a production environment. And there is an ‘Item As-Of’ that ‘posts’ your inventory transactions so that you have a total inventory value per period. Fine, unless your processes result in different G/L dates between the Cardex (Item Ledger) and your General Ledger. And validating the inventory value with G/L mid period is no option. And often custom reports, ‘snapshots’ of the inventory balance at month end and BI dashboards do not deliver the desired result. Certainly not if the Item Balance (F41021) table is used for this.
Since a few years there is limited ‘Inventory reconciliation’ functionality in JD Edwards. Those who are familiar with this are probably already faced with the limitations. Especially if intercompany (SK / OK) sales orders are used, if you have to manually create and connect correction entries or when you have to deal with many rounding issues.
So, what is the solution? Let’s start at the beginning: What is the inventory value on a given date?

The ‘P’ and ‘the’ moment – the value of an item through time

Inventory value = Q * P. Sounds easy. But unfortunately, it is not that easy. Obviously, you must multiply the quantity (Q) of an item by the price (P) of that item. Or the price of the item in the relevant warehouse, or of the specific lot (and location). And of course, for all items, lots (if applicable) and warehouses. So far so good. But the value of an item changes over time. You need the value of the item at the end of the period. And that is not stored in the system. Or is it? Although the price per unit is not kept per date/time in JD Edwards, if the price per unit changes, a transaction is neatly recorded in the Cardex (the item ledger) that represents a change in value of the relevant inventory. Moreover, each inventory transaction is provided with a quantity and a value. And that mechanism ensures that the inventory value can still be determined over time. And that mechanism has proven to be pretty reliable.

Q and Q – Physical inventory vs Financial inventory

Let’s look at the Q. In primary units, because the ‘P’ is the value of one primary unit. We do not want to add pounds and yards. Conversion from one unit of measure to another must be set up correctly. And then we obviously do not want to know what is in stock now, but what was in stock at the end of the period. But which inventory transactions do we need? In JD Edwards the value of the inventory in the case of a shipment to a customer is only updated after invoicing. Until then, the value of the inventory will still be accounted for on the inventory account, even if the inventory is physically no longer present (but e.g. on route to the customer). And material issues on a work order are only processed to the G/L if the manufacturing accounting process is run. That could be the same day (night) but could also be a day (or more) later. Often as a result of incorrect work order statuses and ‘reserved’ work orders.
Until the value of the inventory is transferred to work in progress, it is part of the inventory value. This means that there is not one Q, but in fact two. An ‘on hand’ quantity, which indicates how much was physically in stock at the end of the period, and a ‘financial’ quantity, which indicates how much inventory end of the period was accounted for in the general ledger.

In and out – Inventory is constantly changing

Inventory is on the move. Goods are received, moved, consumed, produced, sold, shipped or issued. Purchase prices are revised, bills of materials for manufactured items change, hourly rates are adjusted, and cost prices are recalculated. Combine all these activities with the factor time and the two different ways to look at the inventory quantity, and it is not surprising that many organizations have some problems with balancing their inventory with G/L. Especially in a 24/7 environment, where the stock is constantly changing. Because of this complexity, custom reports that are not developed properly are often not correct and organizations end up making monthly lump sum bookings (often a month debit and the next month credit or vice versa) as a result of timing issues or is inventory posted to the wrong account as a result of incorrect setup or errors in master data management.

Find the 14.293 differences… and one needle – Relevant variances

Another relevant factor when it comes to inventory transactions: there are a lot of them. And that does not make it easier finding the problem. And if the problem cannot be found, it is often difficult, if not impossible, to determine the underlying cause of the problem and to improve the process to prevent future problems. Especially within a production environment, the number of differences is often substantial due to rounding differences. Nevertheless, provided that the inventory related processes are well organized, and procedures are followed, usually only a few transactions are responsible for most of the variance. The majority of transactions do not result in variances.

The right tools for the job – Inventory reporting

But how do you get the job done then? First, by comparing apples with apples. For the General Ledger this is already well arranged: the period totals. Determining the inventory and inventory value from the Item Ledger requires accurate inventory reporting (by inventory account to compare with G/L) that considers the ‘timing’ (which transaction belongs to which period) and distinguishes between the physical inventory value and the financial inventory value at a given date.

More excellent tools – Inventory reconciliation

With just an accurate inventory report you’re only halfway there. What if the inventory doesn’t balance with G/L. How do you find those transactions that cause the real problem? Then it will come down to checking the transactions: validating inventory transactions with G/L transactions and vice versa! And in order to find the incorrect transactions, we must eliminate the correct transactions, so we’re left with the incorrect transactions. In other words: we need to reconcile the inventory transactions with the General Ledger transactions. And we are not interested in small rounding differences, but we do want to know if we have found all the transactions that explain the total difference. And of course, we cannot do this manually. There are generally too many transactions for that. That process is very similar to Account Reconciliation, at least to the process supported by the Cadran Account Reconciliation module: combining transactions, matching and automatically account for differences within certain limits.

… and they lived happily ever after. – Inventory reconciliation made easy?

So, we decided to extend Cadran’s Account Reconciliation module to include inventory transactions. We included accurate inventory reporting with an online comparison with G/L balances to quickly validate the inventory balances. On top of that we included new requirements from our customers and improved the setup and user interaction. The result: a thoroughly revised module to reconcile inventory with G/L much easier and faster.
Now, inventory transactions are calculated daily, rounding differences are booked automatically and if there are differences between the new inventory report and the General Ledger, these are fully explained by the transactions that have not been reconciled. Daily. Allowing you to identify today which transactions went wrong yesterday. And know what needs to be corrected, investigated and improved.

Finally

Variances between the Item Ledger and the General Ledger are often indications of issues with processes and/or procedures and sometimes software-related. And if the inventory value in the General Ledger is not correct, it is likely the results that are reported are not correct as well.
Also, system integrity is of added value when analyzing the performance of the organization. This can become painfully obvious when Business Intelligence is introduced: various cross sections result in different results.