Overview

At the end of every month, we will inevitably get some questions on why their inventory valuation figures does not match the G/L inventory account. We would investigate and find out why and provide solutions on how to fix them.

Some of the reasons are pretty common so I’d thought I compile a list of reasons why your inventory valuation does not match your general ledger, and how to fix them.

Note that this is not a comprehensive list, but it’s most common symptoms we’ve come across.

Did Not Run Adjust Cost – Item Entries Process

The windows version of Navision aka Dynamics NAV aka Dynamics 365 Business Central has been out since 1995, and I’m still amazed how many NAV partners do not emphasize how important this function is to the end users.

No, setting up the Automatic Cost Adjustment on the Inventory Setup is NOT enough.

I wrote an article covering this specific topic here. The article was written in 2012 and it’s still relevant today. When I went to the Navision training class back in 1999, this was one of things that they stressed on when using inventory function in Dynamics 365 Business Central.

Run Adjust Cost people! Do it before you run any inventory, costing, or financial statements!

Solution

What we do for our clients is to set it up on the Job Queue so it’s ran automatically DAILY. Don’t wait too long until this is ran or else it may take a long, long time to finish, which may lead to other problems if you’re running 24 hour shifts.

If you’re not setup to run this process automatically, run it manually! Just. Do. It.

You can read more about why this process is important on this link:

Why Your Inventory Cost is Wrong

Allow Direct Posting to Inventory Account on the G/L

This is another common issue often occurring is when there’s a change in accounting personnel. When that happens, established rules and procedures can break down for various reasons.

The problem typically arises when someone posts directly to the inventory accounts. Here’s an example we came across while analyzing a case where the inventory valuation didn’t match the G/L entries (note: amounts and document details have been changed to protect the innocent). The customer couldn’t explain why there was such a large discrepancy between the Inventory Valuation report and the General Ledger.

Digging deeper into the ledgers, we found the entries that are causing the problem:

At some point, someone within the company had allowed Direct Posting to the Inventory G/L accounts, probably because they couldn’t match the General Ledger Inventory account value to the Inventory Valuation report.

Solution

Set the Direct Posting on the G/L account for Inventory to OFF!

To look at and fix the entries that’s causing the problem, you’ll need to do the following:

1. Go to the Chart of Accounts and bring up the General Ledger Entries for the Inventory G/L Account.
2. Use the Personalization feature to show the Source Code field in the General Ledger Entries page.
3. Filter on the Source Code field with ‘<>INVTPCOST‘

You’ll need to identify the appropriate account to which these entries should be reclassified into.

The INVTPCOST source code is a special identifier used to mark transactions that originated from the inventory subledger. If you have transactions that bypass the inventory subledger, your inventory figures in the general ledger will be incorrect.

Running the Wrong Reports

People often try to match the Inventory Valuation report directly to the General Ledger. The problem is that the standard Inventory Valuation report does not include transactions that haven’t yet hit the G/L, such as items that have been received or shipped but not invoiced.

The better report to run is the Inventory to G/L Reconcile (not to be confused with the Inventory – G/L Reconciliation matrix page). This report gives you a breakdown of what items are received/shipped not invoiced and includes it with your inventory on hand valuation.

If you use manufacturing features such as production orders, you will also need to run the Inventory Valuation – WIP report so it can match what’s in WIP (or consumed) to the General Ledger.

Why is this important? Because your WIP (Work in Progress) General Ledger account and, if you’ve enabled Expected Cost Posting, the expected cost accounts are likely separate G/L accounts. You need to ensure that all of these accounts are being included and reconciled when comparing to the Inventory Valuation report.

Solution

Turning on the Expected Cost Posting on the Inventory Setup will mitigate the problems stated above. So you can run the Inventory Valuation and match it up against your regular inventory and the inventory Interim account.

In the past, I’ve argued against turning on the Expected Cost Posting. However, I’ve come to realize that not everyone using Dynamics 365 Business Central will become an expert—or should have to be one—just to run the right reports, close the month, and go home. While I’m still generally not in favor of turning it on, I’ve softened my stance, recognizing that in some cases, it can make life easier for the end users.

I still believe people should truly understand the system so they know what reports to run and how to reconcile properly.

Relying on consultants is okay. Being self-reliant is better.

Inventory Posting Setup is Incorrect

Take a look at the following screenshot for the Inventory Posting Setup.

The Inventory Account is set to expense account. This means whenever you purchase or sell an inventory item with this inventory posting group for that location, it will go to account 70500.

This may be done in accident or on purpose, but when people reconcile inventory they’re typically going to only look at your inventory G/L accounts. When you go to your typical inventory accounts, you’ll be missing transactions entries for RESALE Inventory Posting Group for the location GREEN.

Again, there may have been a reason this was setup in the first place. But I’m willing to guarantee that in 5-6 months or if there’s a personnel change, this will have been forgotten.

Solution

With Dynamics 365 Business Central, they’ve taken steps to reduce this problem from occurring by introducing the Account Category and Account Subcategory field on the G/L Account table. This means when you drill down to select an account, you can’t select a “wrong” account.

I recommend hiring someone who truly knows what they’re doing to handle the initial company setup.

Why? Because most companies only need to go through this process once. Hopefully in a lifetime. Learning the ins and outs of posting setup, especially when it’s something you’ll only do once, isn’t the best use of your time.

General Posting Setup is Incorrect

This scenario is similar to the above. Instead of setting the Inventory Posting Group (the balance sheet side) incorrectly, they set the General Posting Setup incorrectly.

This means every time something is sold, it will hit your inventory G/L account instead of the sales account.

Same as the previous point, there may be a special (and probably good) reason for doing this. But over time, this knowledge will be lost.

Make anything complicated or weird and you’re almost guaranteed to have problems down the road. This concept applies to all other areas in the setup of Dynamics 365 Business Central as well.

Solution

Same as above. Just pay someone who knows what they’re doing to do this setup for you. The chances are, you’ll only need to do this once.

“But Alex, how do we know who says they really knows and who really knows?”

I don’t know.

I have problems identifying those who talk a big game and those who can actually deliver as well. I’m sure that’s a separate article at some point in the future.

Abnormal Posting Date

Basically, when you post a receipt on a date later than when you post the invoice. For example, you posted the receipt on 5/6/2018, but you post the purchase invoice with the posting date of 4/30/2026. The primary reason companies do this is because they want certain purchase transactions to occur in certain periods.

When you do this, the inventory valuation will not pick up this particular transaction if you run the inventory valuation as of 4/30/2018. Why? Because the inventory transaction occurred on 5/6/2026. However, the G/L transactions occurred on 4/30/2026.

Solution

The subject of Abnormal Posting Dates has been covered in an article I posted. You can read the article here.

Basically, you will need to run a supplemental report to balance out the inventory.

Conclusion

There are probably other common reasons, but these are just from the top of my head.

Note that these symptoms usually comes with follow up questions on why the inventory costing is weird. But that’s a topic for another article.

The bottom line is as long as you can reasonably explain what happened to the auditors, you will be OK. The key is understanding and setting up the system so it can’t fail you.