Reconciling stock movement with GL is not rocket science

One of the issues I come across most frequently among Epicor ERP users is reconciling General Ledger Stock values with the movements in Inventory. The biggest complaint is that there are no tools to trace the change to the GL back to the Inventory system. This is not true – I admit it’s sometimes difficult to understand how the system came up with the values it is using, but tracing the movement is easy.

Here’s how to do it:

First, run the Inventory/WIP Reconciliation Report for the month using the “Only Unposted to the G/L:” radio button. You should get an error saying “No Records Found”, meaning everything has been posted.

Now run the Inventory/WIP Reconciliation Report for the entire month using the “Only Posted in the G/L:” flag. Go to the Chart Tracker and look for the inventory account (or, for that matter, any account on the report) and look at the movements for the month. Nine times out of ten, this will match directly and you now have all the detail as to what movements were recorded.

If the movements don’t match, go into the Transactions Details on the Tracker, activate “Group By” on the grid and drag the Journal Code into the Group area. You will find that the IV group will match the movements from inventory and that any difference will come from another Journal Code, meaning that the account has been posted to either manually or through another process.

If it still does not reconcile, you need to check the Review Journal to see if a group is outstanding or if there has been a failure.

Once you’ve established the GL is in sync with the postings from Inventory, the more difficult task arises – reconciling the inventory value to the Stock Account. One thing you must do is run the Stock status report before you start to transact stock movements for the following month. This ensures there is no blending of the values, as the report will sometimes reflect movements after the cut-off date if they have taken place.

If you do this and you started with a reconciled value, it should be relatively easy to reconcile. I always start by making sure my opening balance is the same (auditor habit). If the difference is wildly out, the best thing to do is to break up the task into chunks, run the COS WIP report for each day and check it for oddities.  Some of the things I have found with Epicor ERP:

• Stock Adjustments where both sides of the transaction are posted to the stock account
• Products posted with no cost but showing up on the Stock Report (changed the value)
• Landed Cost transactions not updating the correct GL Account (but updating the stock value)
• Major variances in an average cost
• Jobs not closed completely

Always review the stock status report for items with zero cost or negative amounts. Depending upon your valuation method, these items can cause the value to fluctuate from the transactional value.

If there are transactions in the GL that look odd, make sure you check them out in the Tracker using the Transaction Detail/Cost Transaction tab or the Transaction Detail/Journal Detail/Tran GLC Detail tab.

And finally, if you’re struggling to reconcile every month, I would recommend going back to basics – run the COS WIP Report every day and review it for anomalies. This discipline often catches issues before they can develop into a crisis and should only take a few minutes. Often, it helps to identify the root cause of the issue and after a while the routine can be relaxed. You don’t have to capture every day, but reviewing the report can make a big difference.