Microsoft Dynamics NAV and Business Central
If you find that your inventory and general ledger accounts don’t match up, there are six potential reasons this has happened. Ninety percent of the time, it can be explained by one of three situations. While the other three reasons are less likely we will also cover them in this post.
The six possible reasons your inventory and general ledger (G/L) accounts don’t reconcile:
You didn’t run Adjust Cost- Item Entries and/or you haven’t selected Post to G/L
You have Direct Posting set to “Yes” on a G/L account with a subledger
You’re simply comparing the wrong reports
The setups for General Posting Setup or Inventory Setup are incorrect
Someone changed the Gen. Prod. Posting Group on an item
You have an abnormal posting date
We’ll explore each of these in further detail below.
1. You didn’t run Adjust Cost- Item Entries / Haven’t selected Post to G/L
If you have the Automatic Cost posting turned on in your inventory setup and haven’t run the Adjust Cost-Item Entry report, your inventory and G/L won’t match up.
Even with Automatic Cost posting turned on, you can have the scenario where you’ve received some inventory that you’ve purchased and haven’t invoiced it yet. However, you’ve created a sales order for it that uses up that inventory and you post it. When you come along later to invoice this, the vendor may have changed the price (but, of course, you couldn’t have known this would occur).
In this situation, if you haven’t run the Adjust Cost-Item Entry, you haven’t matched up the cost between these inbound entries and outbound entries. You’ll need to run the Adjust Cost-Item Entry report to ensure that all of the costs have been posted to the general ledger so that your sub-ledger and the G/L are in sync with one another and can therefore be balanced.
The second piece of this involves the Post Cost to the G/L checkbox. Maybe you ran the Adjust Cost-Item Entry report but failed to check the Post Cost to the G/L box. Whenever you’re going to do your inventory reconciliation for the month, you should always run that Adjust Cost-Item Entry, and make sure that Post Cost to the G/L is run. These are foundational steps that must be taken before you attempt to reconcile anything.
To identify if this step hasn’t been done (and thus to verify that this is your problem), you can go to the item card and filter using “Cost is adjusted: No” or “Cost is posted to G/L: No.” This will validate that activity has taken place on this item, but the Adjust Cost item on this entry hasn’t been run to post it to the general ledger.
Best practice for running Adjust Cost-Item Entry:
While it depends upon how much activity you have, it’s recommended that you have your ERP partner set up the Adjust Cost-Item Entry to run from your job queue at night. It can be a time-consuming process, so you don’t necessarily want to run it during the day.
2. You have Direct Posting set to “Yes” on a G/L account with a subledger
The second potential reason why your inventory and your G/L accounts don’t match is as follows. In your chart of accounts (within one of your G/L accounts associated with your inventory subledger), you have direct postings that are set to “Yes” on the G/L account. Once you’re up and running, that should always be set to “No” because you should never make an entry to the G/L account directly. If you post directly to your general ledger, the entry would have no way of hitting the subledger.
These erroneous entries can be identified by examining your general ledger inventory account, specifically using the source code field.
If you use <>INVTPCOST to filter the list (INVTPCOST being the source code for when the subledger is making entries to the general ledger and <> denoting “not”), you’ll find any entry directly posted to the G/L. In this case, these entries will need to be reversed in order to reconcile your inventory with your general ledger.
3. You’re comparing the wrong reports
A third reason why your general ledger and your inventory don’t match is that you’re looking at the wrong reports. You might be looking at your Inventory Valuation report or the Inventory Valuation Cost Specification report instead of the Inventory to G/L Reconcile report. The Inventory Valuation report or the Inventory Valuation Cost Specification report shows only the subledger, not the G/L, so the Inventory to G/L Reconcile will show you any differences.
Likewise, if you’re doing production, the Inventory Valuation-WIP or the WIP to G/L reports will show you any differences there.
These three situations are the most common reasons why your inventory and your general ledger accounts have a discrepancy. Again, most of the time you encounter this issue because you’ve either posted something directly, didn’t run the adjust cost, or you failed to forward any of the additional costs to the general ledger.
Let’s look at the following uncommon reasons:
4. Your setups for General Posting Setup or Inventory Setup are incorrect
If you’re on a newer version of NAV or Business Central and your G/L accounts use new fields as part of account schedule creation (“account category” and “account subcategory”), and you don’t have those set properly, you might not have the ability to select that account when you go to do your inventory setup or your general posting setup.
When you’re setting up your inventory and your general posting, it’s imperative that you first take a close look at your chart of accounts setup. You want to ensure your account categories and subcategories are correct, because the wrong selection can lead you down the wrong path. Any time you create to the G/L account (or when you’re first getting started), make sure that you have the account category and subcategory properly set up.
It’s strongly recommended, therefore, that you work with your Microsoft partner as you set up your inventory and general posting. If you prefer to set things up yourself, have your partner review it before you begin creating entries. It’ll save you in the long run.
5. Someone changed the “General Product Posting Group” on an item
In NAV and Business Central, there’s something called “Change Log” which eliminates the whole “he said/she said” argument. The change log allows you to track insertions, modifications, and deletions of records in the system.
If you turn it on for your G/L account and your item, then you can identify who put the record in, who put the wrong setup in, and who changed something on an item card/general ledger account/direct posting for the General Product Posting Group.
Those are the fields within those tables that you’d want to be able to know about any alterations. This is because if you don’t have the change log turned on, you have no way to ensure this doesn’t happen again.
The change log lets you go back and see who made those changes and if they’re modifying what’s preexisting with the setup, what the old value was, and what the new value they changed it to was so you have full visibility into exactly what occurred.
6. You have an abnormal posting date
The last reason that you might have a problem with your inventory and G/L matching is also the least likely. Microsoft NAV and Business Central will allow you, when doing a purchase order, to come in and do a receipt date for today (let’s say November 15) but then invoice that purchase order on an earlier date (say, November 1).
Here, your invoice date is before your receipt date. When you’re looking at those subledger entries, it doesn’t occur until Nov. 15 but if you’re looking at the general ledger it supposedly occurred on Nov. 1.
In this case, there’s really no fix for it. You’ll have to do a reversing entry to make the G/L match or sync with that subledger. What’s more, identifying it is a bear. Fortunately, there’s a report available (written by NAV consultant Alex Chow) that can help identify if this is a problem. Otherwise, you have to manually run reports using guesswork to find the last time the G/L and the subledger matched. Then you’d have to do a manual journal entry to manipulate it.
To ensure that your inventory and G/L accounts match at the end of the month, prevent and validate. Turn on your change log, double-check who’s allowed to edit items, verify that your general ledger account categories and subcategories are correct, and keep a close eye on your posting dates.
If you run into a problem, reach out to our team. We’re used to assisting with these complications and have the experience to identify and correct them.
About the Author:
Meghan Parisi is a NAV Support Specialist for Advanced Business Systems. Meghan has more than 14 years of experience supporting customers with Microsoft Dynamics NAV/ BC starting with NAV version 2.6.