Troubleshooting Inventory Reconciliation problems

The typical inventory reconciliation process involves first running the Inventory to G/L Reconcile report and comparing the totals to the related General Ledger Accounts to which they balance. Your process may be more complex than this, but it should describe at least one of your reconciling steps. If all goes as planned, these should tie out every month but sometimes they don’t!

Inventory issues are notoriously complex and typically mixed in with other aspects of the system like your Costing Method, Posting Dates, Inventory Periods and accounting-related stuff, so it can get really messy to untangle.

Where to start the “de-tangling” can feel overwhelming. We use a systematic methodology when digging into any inventory reconciling issue. This method typically reveals the problem or problems eventually or if nothing else, it eliminates what could be the issue. Here are the steps in order.

Run ‘Adjust Cost-Item Entries’

Never look at costing questions unless you run this routine first (even if this is set to ‘Always’ in the Inventory Setup, run it again). Many reconciling issues are resolved with this one step. Many an hour has been wasted looking for other causes when this simple step was all that was needed to bring the balances back into sync. Also run the ‘Post Inventory Cost to G/L’ for good measure. So, we repeat, never look at costing questions unless you run this routine first!

Costing Method(s)

What costing method or methods are being used? This will determine how the system is using the item’s cost and plays a part in deciphering what we are looking at when digging through Value Entries. It is a factor in determining what the system does with an Item Entry. Always keep the costing method in mind when looking at any inventory question.

Where Used?

We recommend dedicated accounts for Inventory related setups in the Inventory Posting Setup and inventory related General Posting Setups. What we mean by that is one GL Account has a single use in the Posting Setups at least for the ones relating to the Inventory accounts.

Not everyone agrees with that philosophy. Business Central requires more inventory accounts than what most standard systems require. Most users don’t want to add more accounts and we get that but resist the urge to re-purpose an account for multiple use, especially Inventory related accounts like WIP.

What we often find is that these setups are what we call ‘cross contaminated’. They have different types of posting setups hitting the same GL Account. They either didn’t understand how the account would be used or just didn’t want to create one more GL Account for the confusing matrix you are explaining to them when setting it up. Now there is a situation where to reconcile the account, you must run multiple reports, possibly with different filters and then add the report totals together to tie back to the GL Account balance. Sometimes it is just impossible to have reports that support these GL Accounts. And have your camera ready for the look your auditor gives you when you explain the 47 steps you do to reconcile inventory.

So, the first step we do is go to the Inventory related GL Accounts and look at the ‘Where Used?’ link for each one. This tells you what is in the Posting Setups directed to post to this account and what should be supporting this GL Account balance. It also reveals any ‘cross contamination’ like posting your COGS and COGS Interim to the same accounts. Resist the urge!

Also if you create WIP products, consider separating WIP (costs held during a production process before the production order is finished) from WIP (work in process completed inventory).

Another philosophy we follow is that if an account is used in any posting setup, never ever directly post a journal entry to that account (or use it on a line of an order). Remove the ‘Direct Posting’ flag on the GL Account to prevent this from happening. Shameless plug – we have a nifty App in Microsoft Appsource to help control what types of entries post to accounts, when direct posting is on.

System-Created Entry

You can filter on the General Ledger Entries using a field called ‘System-Created Entry’, Yes’ or ‘No’ are the options.

‘Yes’ means the entry was created for the General Ledger by the system. For example, a Sales Order posted a Shipment and the system created an entry for the General Ledger to debit COGS Interim and credit Inventory Interim.

‘No’ means the system did not generate the entry but it was a direct posting via a Journal Entry or included on a line of an order.

Go to the GL Accounts for the ones not reconciling, drill down to the entries and filter on ‘System-Created Entry = No’. The date range for the filter should cover your entire time in the system. This gives a list of directly posted Journal Entries or entered on an order line.

Once you have established your beginning balances and have gone live in the system, you should no longer be posting directly to the inventory accounts. Any entries after your go live date will cause reconciling problems. These entries should be reversed and done properly.

When did it last reconcile?

Sometimes the reconciling question comes from a person new to the position of making sure it reconciles. And day one, right out of the gate, it does not reconcile! This task is new to them and they are not sure what is causing the problem so they reach out for help.

The first question back to them is typically, when did it last reconcile? Often, they can’t answer that question. Remember, they are new to this task or maybe even the company. You want to know the specific day. You want to narrow down the transactions so You can find the exact moment the issue was introduced.

Here are the steps we recommend to find the day the wheels went wobbly:

• Take a clue from the prior task where you filter on any journal entries posted directly to any Inventory accounts after the go live date. Usually, the journal entry was made so the GL tied to the reports. If there are journal entries,  go to the period prior to the journal entry date to see if it reconciled for that period.
• If this date did not reconcile either, hop on back to day one. Did it reconcile on the go live date? If not, it’s not going to reconcile today either.
• If it did reconcile on day one, when did the issue start? Depending on how long you have been on the system, start comparing the Inventory to GL Reconcile report by year, then by month then by week until you narrow down to the day the wheels started wobbling!

Now that you know the day, you have a smaller segment of data to focus on which typically makes it easier. If you can find one item as an example of the issue, even better. Whatever happened to it, happened to the rest of them.

Clues to the problem

Now that you have narrowed down to one example, here are some of the clues to look for and what they mean.

• Item’s Costing Method is Average Cost
  
  • The setups related to how Average Cost is calculated were changed but did not run the ‘Adjust Cost-Item Entries’ routine afterwards.
  • In various scenarios, if an item goes negative and cost changes happen in the next period, after the Posting Date is closed, this could happen. It will even out once you include the next period but could look off in the prior period. If reconciling issues go away when you run the Inventory to G/L Reconcile report across accounting periods, this could be the problem.
• Item’s Costing Method is Standard Cost
  
  • Inventory on hand has costs other than the current Standard. This happens when someone changes the Standard Cost but does not revalue the items on hand to the new Standard cost. See our free Standard Cost History app for Business Central which helps prevent users updating the item card directly instead of using the standard cost worksheet.
• Prior Item Ledger Entries or Value Entries for an item posted to different GL Accounts than they do now.
  
  • This would indicate that someone changed the Posting Setup’s accounts. You can do this however things need to be evaluated first to see if there are other maintenance steps needed too. If there are additional steps and they are not done, this will cause reconciling issues.
• Prior Item Ledger Entries or Value Entries posted with different Posting Groups than they do now.
  
  • This means someone changed the Inventory Posting Group or the Gen. Prod. Posting Group on the Item Card setup after transactions started for this item. You can change these however things need to be evaluated first to see if there are other maintenance steps needed too.
• Dates and Month End
  
  • Be aware of Posting Dates and running inventory valuation with an ‘as of date’ at the end of the month. Production and output can be posted across 2 periods. This may look like a reconciliation/balancing issue but will work out if you adjust the as of date one date further.

This is in no way a comprehensive list of all the reconciling issues that can happen, but it is a good place to start your process. Hopefully it will help with your inventory “de-tangling” efforts.

Ternpoint Solutions is a Microsoft Dynamics 365 Partner. We would love to hear from you, reach out using our contact us page

‍