Reconciling the Dynamics NAV Inventory to G/L

Reconciling the Dynamics NAV inventory against the G/L can prove to be quite complicated. The number of elements influencing the inventory span across most of the application, and include:

  • Purchased items
  • Items received not invoiced
  • Items received using expected cost 
  • Item charges 
  • Revaluations 
  • Sold items
  • Items shipped not invoiced
  • Items shipped using expected cost
  • Inventory Adjustments
  • Inventory Accruals
  • Standard cost variances
  • Overhead

Not to mention the manufacturing elements:

  • Material variance
  • Capacity variance
  • Subcontracted variance 
  • Manufacturing Overhead Variance

One report can help up to reconcile; the Inventory - G/L Reconciliation report. It has existed for many years but it is undiscovered by many people. The Inventory - G/L Reconciliation report which - by-the-way - is no report but a matrix page consists of a number of elements and shows, which status they have and how much have been posted to the G/L:

To demonstrate the Inventory - G/L Reconciliation page, I am going to show you the consequences of each posting.

I have therefore created a new warehouse (BLACK) to separate my test from the existing CRONUS postings and I will use a special date (March 15th) to distinguish the G/L transactions.

I have run the “Adjust Cost – Item Entries”, the “Post Inventory to G/L” and I have Set the Inventory Setup to automatic cost adjustment and inventory posting.

The G/L Total and the Difference columns will only appear if the page is run with no filters. Which means that filtering for one or more locations, the two last columns disappear.

 For more information on the Inventory batch process please check my previous post:

https://dynamicsuser.net/nav/b/peik/posts/inventory-batch-posting-in-microsoft-dynamics-nav

 

Lastly, I have created a set of test items including a BOM structure and routings for the later manufacturing tests.

 

Inventory postings with FIFO Items

Scenario 1:

Purchasing two items (92001 & 92002) in two steps (receive and Invoice). The Item 92002 has an overhead rate of 1 and a unit cost of 11 whereas 92001 has no overhead rate and a price og 10:

After the purchase order has been received the Inventory - G/L Reconciliation page looks like this:

Since we haven’t activated posting of expected cost, the Inventory (interim) is only an information and is not reflected in the G/L.

Posting the purchase order moves the expected cost to the Direct cost applied account and applies the overhead cost.

Then if an extra charge later is applied, e.g. a freight invoice looking like this:

The Direct Cost Applied will be increased:

Any adjustments to items will also affect the Inventory - G/L Reconciliation:

In this case, a positive adjustment has added the last raw material to the inventory:

After the Inventory batch posting, the Inventory Adjustment is moved to the Direct Cost Applied:

Selling one of the purchased items in two steps (ship and invoice):

 

Will make an interim posting to the Inventory - G/L Reconciliation:

Which is then moved to the COGS account on posting the invoice:

Inventory postings with standard cost Items

Working with items running Standard Cost changes the posting of the item. Basically, it means that the items will be added to the inventory at a predefined price and that any deviation in purchase or production cost will be posted as a variance. In this case I have created an Item (92005) with a standard cost of 30. Posting the purchase order of ten items at a price of 32 will show up on the reconciliation like this after receipt:

After posting the invoice, the 320,00 is added to the Direct Cost Applied and the Purchase Variance holds the 20,00:

Manufacturing postings without expected costs and FIFO Items

Creating a production order consisting of FIFO items the production could look like this:

The subassembly item consisting of components:

1 pcs Item 92002 á 11

1 pcs Item 92003 á 12

And routings:

The work center 100 and 200 having an overhead rate of 0,10 each leaving a detailed calculation like this:

Posting the production order with an extra 10% capacity consumption only adds the component value to the WIP Inventory.

And after changing the production order to finished, the value of the output items is added:

The 1.350,90 consists of both the output items and the raw materials:

Checking the G/L Accounts for the inventory:

The 1596,14 + 3450,00 = 5046,14

Manufacturing postings with Standard Cost Items

Using the same example as above but now with 10 % extra consumption on both items and capacities then detailed calculation still looks the same and the Cost Shares Page looks like this:

Now, after creating a production order, I am posting the time, subcontractor and item consumption with 10% extra.

Posting the output on the last operation triggers the item to be expected to inventory at the standard cost. Changing the status of the production order will move the amount from interim to the inventory to WIP as well as updating the variance fields:

Wrapping it all up

So, to wrap everything up let’s take a look at the Inventory - G/L Reconciliation report for all locations and try to compare each part to the G/L:

Firstly, looking in the GL Accounts:

The total amounting 1.233.696,67

This can also be found in the Inventory Valuation. Here also printed to include Expected Cost:

The WIP can be found in the Inventory Valuation WIP report:

I hope this can help shed some light on the Inventory - G/L Reconciliation report

 

Anonymous
Related