Balancing Inventory with General Ledger ‘made easy’

Once upon a time… the inventory was out of balance

More than 20 years ago, when I had just started working in ERP systems, I was given a challenge as an application manager: why did the inventory never fully reconcile with the general ledger? Could I “just” figure it out? What followed was an intense process of manual searching, combing through page after page of reports—without the luxury of Excel to analyze data. I even went through this daily for a time, hoping to pinpoint the source of the discrepancy. After months of investigation, we managed to seal most “gaps” in the processes, but every month remained a bit of a gamble as to whether the numbers would align.

The spreadsheet! – Reconciling with Excel

Then came another question: how can we better specify the balance of the “invoices to be received” account without starting over every month? During month-end closing, this task could take days. Headquarters had just decided that we would have one day less each year to deliver monthly figures. Since the ERP system didn’t offer a solution, we resorted to a spreadsheet. With a data link between the database and the spreadsheet, we could pull last month’s numbers from the system, automate various steps to speed up the process, manually create journal entries for rounding and exchange rate differences, and then rerun the numbers. The catch? Each month, we had to remember to continue with the correct spreadsheet from the previous month.

2001… My discovery of JD Edwards

Switching to JD Edwards ERP was a relief in many ways. No more mysteries about where the system stored data, data exports to Excel—for end-users, no less—and even a feature to reconcile general ledger transactions, though it was still often a manual task. Inventory reconciliation remained challenging. Years later, after focusing on software development (additional JD Edwards modules for local governments), I returned to logistics and production environments. Sure enough… reconciling inventory was still a challenge. From DMAAIs (settings that determine GL accounts) and G/L classes to cost calculations, production accounting, shipments, returns, and intercompany sales orders, inventory valuation was anything but simple. And what is “the” value of inventory, anyway? Sure, Q*P (quantity * price), but which Q and which P? This question became particularly pressing a few days after period-end in a 24/7 production environment. If discrepancies arise, how do you identify the root cause and resolve it in a way that avoids recurring errors?

Reconciling clearing accounts had become much simpler, especially for companies using the Account Reconciliation module from Cadran.

Inventory and integrity – the current toolbox

Then there’s inventory… Yes, there are integrity reports. But plowing through PDFs each month, especially when dealing with common rounding differences in production environments, is no fun. There’s also an “Item As-Of” report that tracks inventory transactions, providing an overview of total inventory value by period. However, processes can create date mismatches between the Cardex (inventory transaction list) and the general ledger, making mid-period checks impossible. Custom reports, month-end inventory “snapshots,” and BI dashboards often don’t provide the desired insights, especially when they rely on the inventory balance table (Item Balance Table or F41021). This table shows current inventory positions but has limitations when it comes to complex valuations or tracing transactions across periods.

JD Edwards has recently added a limited option for inventory reconciliation. However, users often encounter limitations, particularly when dealing with intercompany sales orders, manual corrections, or frequent rounding differences. So how should it be done? Let’s start at the beginning: What is the inventory value at a given point in time?

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

Inventory value = Q * P. Simple, right? Unfortunately, it’s not quite that straightforward. Naturally, you must multiply the quantity (Q) by the price (P) of each item. But, for each warehouse or specific lot. However, item values fluctuate over time. You need the item value at period-end. And that’s not readily available in the system. Or is it? Although JD Edwards doesn’t track price per unit over time, any price change does generate a transaction in the Cardex, reflecting the value change in the corresponding inventory. This system ensures inventory value can still be determined over time—and it’s quite reliable.

Q and Q – Physical inventory vs Financial inventory

Then there’s Q. Obviously in the primary unit, as P is the value per primary unit. We don’t want to add units and kilos together. But which inventory are we counting? JD Edwards only updates inventory value upon customer invoice processing, meaning unsold, shipped inventory still counts as inventory value. Material used in a work order is only accounted for once processed in production accounting, often overnight but sometimes with delays. Until the value is transferred to work-in-progress, it counts as inventory value. So, there are actually two Qs: a physical quantity and a “financial” quantity, representing period-end value.

In and out – Inventory is constantly changing

Inventory is often in constant motion: receiving, moving, consuming, producing, selling, shipping, or adjusting. Purchase prices are revised, assemblies get new bills of materials, labor rates change, and cost prices are recalculated. Combine these factors with time and the two different ways of viewing inventory quantities, and it’s not surprising that many organizations struggle to reconcile inventory with the general ledger regularly, especially in a 24/7 environment.

Find the 14.293 differences… and one needle – Relevant variances

Inventory transactions usually add up to a large volume, complicating the search for discrepancies. When an error can’t be found, it’s difficult or even impossible to identify the underlying cause and improve the process to prevent future errors. Fortunately, well-configured inventory processes and procedures typically mean that only a handful of transactions cause genuine issues.

The right tools for the job – Inventory reporting

So, how should it be done? Start by comparing like for like. GL-side reporting is well-defined: what belongs to each period. Determining inventory and value from the inventory administration requires precise reporting per inventory account, accounting for timing and distinguishing between physical and financial inventory.

More excellent tools – Inventory reconciliation

But good inventory reporting alone isn’t enough. If it doesn’t balance, where’s the issue? To find problem-causing transactions, they need to be reconciled with general ledger transactions, a process known as “reconciliation,” until the source transactions are identified. We’re not interested in minor rounding differences but in pinpointing any transactions that fully account for the discrepancy. This process closely resembles the GL reconciliation supported by Cadran Consultancy’s General Ledger & Inventory Reconciliation module: transaction matching, reconciliation, and automatic minor corrections within set thresholds.

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

I adapted Cadran’s General Ledger Reconciliation module to allow for easy reconciliation of inventory transactions alongside GL transactions, adding precise inventory reporting with general ledger comparison. The result? Inventory transactions are reconciled daily, rounding differences are automatically corrected, and any discrepancies between the new inventory report and the general ledger are fully explained by unreconciled transactions—daily. This lets you know which transactions failed yesterday, what to correct, investigate, and improve.

Finally

Inventory and GL discrepancies often signal process, procedural, or software-related issues. If inventory in the general ledger is incorrect, reported results are likely also incorrect. A well-integrated system is invaluable for analyzing organizational performance. This becomes apparent with Business Intelligence: different views yield different outcomes.

Bart Dix Managing Partner

Bart Dix

Managing Partner