Jesse Lawrence
Microsoft Dynamics NAV® maintains an accounting ledger for recording all financial transactions and a separate ledger to record the stock and movements of inventory items, together with their inventory value. This should of course reconcile to the corresponding general ledger accounts but how do you ensure that it does? This blog explains the process you use to do this. The blog assumes you have some knowledge of Microsoft Dynamics NAV. Please click on the images to enlarge them.
Microsoft Dynamics NAV® maintains an accounting ledger for recording all financial transactions and a separate ledger to record the stock and movements of inventory items, together with their inventory value. This should of course reconcile to the corresponding general ledger accounts but how do you ensure that it does? This blog explains the process you use to do this. The blog assumes you have some knowledge of Microsoft Dynamics NAV. Please click on the images to enlarge them.
G/L Accounts to Reconcile
The G/L accounts that will contain the balances you need to reconcile to the Inventory Valuation will be the Inventory Account and the Inventory Account (Interim) accounts shown in your Inventory Posting Setup screen as in the example.
Inventory Valuation report
The total on the Inventory Valuation report that you need to reconcile is the Expected Cost included Total at the foot of the report as in the example below. You need to ensure that the Include Expected Cost flag is ticked when running the report. This should match the total of the balances of your inventory G/L accounts. You need to ensure you reconcile the very last column of the report (Cost Posted to G/L).
If the the two totals do not reconcile, there are a number of checks you can carry out to identify the differences.
Value Entries not Posted to G/L
If the last two columns of the Inventory Valuation report don’t agree to each other this means that you have inventory costs that haven’t been posted to G/L. To identify these, run the Post Inventory Cost to G/L report (don’t tick the Post to G/L option). The total in the Inventory column of the report should be the difference between the two columns in the Valuation report as in the example.
Once you have run this report with the Post option ticked, the two columns should show the same figures.
If your Inventory Setup does not have the Automatic Cost Posting option ticked it’s important to regularly run both the Adjust Cost-Item Entries report and then this report to keep your Inventory Valuation and G/L accounts in balance. Also, if you do not have the Expected Cost Posting option on the Inventory Setup page ticked, the Expected Costs of your items will show on the Inventory Valuation but not in the G/L balances.
Manual Postings to G/L
All the General Ledger Entries posted to inventory accounts should have Source Codes of either “INVTPCOST” for inventory cost posting or “INVTADJMT” for adjustments. Any other source codes e.g. “GENJNL” for general journals will make the G/L and inventory balances different from each other because they will be inluded in the G/L balances but not the inventory balances. An example of the entries you might see is shown in the screenshot.
You can prevent this from happening by ensuring that the Direct Posting flag on each G/L account in the Chart of Accounts is not ticked as in the example
Apart from the obvious timing difference that will arise if you run your G/L and Inventory Valuation as at different dates, you will be unlikely to have timing differences where postings to the inventory and G/L ledgers have different dates. This can happen however where you are running older versions of NAV and the Automatic Cost Posting option on the Inventory Setup page is not set or the Automatic Cost Adjustment option is not set to Always.
If you find that the Inventory Valuation reconciles to G/L at your current date but doesn’t at dates in the past, you will need to download the Value Entries for the period and compare them to the corresponding G/L Entries to identify any differences.
Inventory Posting Setup Changes
If you change either the G/L accounts on the Inventory Posting Setup page or the Inventory Posting Group on an individual item, the balances on the Inventory Valuation report for specific Inventory Posting Groups will not reconcile to their corresponding G/L accounts. For example, you run an Inventory Valuation report in a CRONUS database for the FINISHED Inventory Posting Group (with the Show Expected Cost option not ticked). This gives a total of £88,151.56 as in the screenshot.
This reconciles with the corresponding G/L account balance (G/L account 2120). If you then change the Inventory Posting Group for item 766BC-C and re-run the report, the balance it would show would be as per the example.
As you can see, the total has now changed to £86,922.96 because item 766BC-C is no longer included in the report but the corresponding G/L account would not have changed and so will no longer reconcile to the Inventory valuation report. The overall inventory totals will still agree though.
More about NAV from the Experts
Dynamics Consultants very much prides itself on the level of expertise of the staff. Personally I am a fully qualified accountant, we have warehousing and manufacturing experts as well. If you would like to find out more, one way is through training, either from one-on-one with your business, or through scheduled training courses at our offices.
Your ERP system can be a powerful tool to streamline your business' manufacturing processes. By integrating diverse functions such as inventory management, production planning and quality control, an ERP system helps reduce operational costs and enhance productivity.
An enterprise resource planning (ERP) system is a powerful tool that can streamline your entire supply chain by automating business processes and improving visibility. Integrating an ERP solution allows you to optimise core functions and create a more responsive chain of suppliers. This integration not only increases efficiency but also ensures you remain competitive.
In a world where data security is paramount, you need a system that offers robust protection. Dynamics 365 Business Central is renowned as the most secure ERP platform due to the cloud-based infrastructure wrapped in Microsoft's advanced security features. We explore those features.