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 NAV.
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:
Allow Direct Posting to Inventory Account on the G/L
This is a common one as well. This case usually happens when there’s a change in the accounting personnel, rules and procedures that was in place gets broken because of reasons.
This problem occurs when people posts directly into the inventory accounts. Here’s an example of what we found when we were analyzing why the inventory valuation didn’t match G/L entries (the amounts, document, etc has been changed to protect the innocent). The customer did not know why there was a huge variation between the inventory valuation report and what’s on the General Ledger.
Digging deeper into the ledgers, we found the entries that are causing the problem:
At some point, some within the company had allowed Direct Posting to the Inventory G/L accounts. Based on the Change Log, it was changed by their previous accounting manager for unknown reasons.
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:
- Go to the Chart of Accounts and bring up the G/L Entries for the Invenventory G/L Account
- Filter on the Source Code field with ‘<>INVTPCOST‘. You’ll probably need to show the column first.
The resulting entries you’ll need to determine what account you should reclass these entries to.
The INVTPCOST is basically the special source code that is used to mark the transactions that originated from the inventory sub ledger. If you have transactions that does not go through the inventory sub ledger, you’re inventory figure will be off.
Running the Wrong Reports
People will try to run the Inventory Valuation report and try to match it to the G/L.
The problem is, the standard Inventory Valuation report will include transactions not have hit your G/L yet (for example, items received/shipped not invoiced).
The better report to run is the Inventory to G/L Reconcile. This gives you a breakdown of what items are received/shipped not invoiced and include it with your on hand valuation.
In addition, you will need to run the Inventory Valuation – WIP report so it can match what’s in WIP to G/L.
Why is this important? Because your WIP account, and if you’ve turned on the Expected Cost Posting, will most likely be different G/L accounts. Make sure you’re matching all those up.
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 realize not everyone will become experts in NAV (nor should they need to be) and know what reports to run to close the month and go home. I’m still against turning this on, but I’m softening my stance on this.
I still believe people should truly understand the software 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 FINISHED 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 5-6 years later or if there’s a personnel change, this will have been forgotten.
Solution
With NAV 2018 and Dynamics 365 Business Central, they’ve taken steps to reduce this problem from occuring 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 would recommend hiring someone who knows what they’re doing to do the initial company setup for you. Why? Because most companies will only need to do this (hopefully) once in a lifetime. Learning and ins-and-outs of posting setup, especially when you only really need to set this up once, is not a good 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 everytime something is sold, it will hit the 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 problems down the road. This concept applies to Dynamics 365 Business Central aka Dynamics NAV 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’m not sure. 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/2018. 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/2018. However, the G/L transactions occurred on 4/30/2018.
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.
I recently had an issue: customer had activated CostAdjust on a regular basis.
But for some reason around 20 adjustment-entries (value entries) back in year 2014 had not been posted to G/L.
No one knows why.
But it may be reproduced by un-ticking “Post til G/L” in the CostAdjust-batch or (at the time that CostAdjust is ran) un-tick “Automatic Cost Posting” in Inventory Setup.
One way to catch issues like this is to run “Post Inventory Cost to G/L” or “Post Invt. Cost to G/L – Test” (reports 1002/1003).
And do this when CostAdjust is ran.
Another great solution is to use “Inventory Periods” and util “Close Period”.
Customers problem was – obviously – that fiscal year by now had been closed. And as NAV insists to use original Value Entry Posting Date as date in G/L, he had a problem.
My solutiuon: I modified the posting-dates on the Value Entries concerned!
And then ran that “Post Inventory Cost to G/L”.
What you could’ve done is to set the Allow Posting From on the General Ledger Setup. If you put a date into that field, the adjust cost process will not post back to 2014. It will use whatever date that’s on the Allow Posting From.
Hi There,
If All value entries are supposed to reflect that Adjustment is done.
I can see many entries in my value entry table with Adjustment flag as un ticked, however I did rant the Adjsut cost job for all as well with some specific items filter to test but no result, un adjusted value are showing varying costs that normal for ones those are adjusted, please help
Please reach out to your NAV partner and ask them to come take a look at this for you.
Wow, thanks Alex!
This list is a must-see list whenever I get the inventory problem 😛
Thank you for reading!
The client’s automatic cost adjustment got hampered in March automatically – God knows how…how to know that?
We are proposing to run Adjust Cost/Item batch and changing Inventory setup – adjustment to “Always” but that is impacting entries back to March-april and May – will restricting “allowed posting date from” help us here? will all adjustments be then posted in June only?
And if that happens, how the Inventory G/L in March and April be reconciled with Inventory valuation report?
That’s up to the client to decide. Basically they need to make a decision on what period they want their adjustments to be posted in. Setting the Allow Posting From will dictate when those adjustments will take place.