by Barbara Plagens for KCP Dynamics
Reconciling inventory general ledger account balances to the inventory balance in Microsoft Dynamics NAV can be a painful process; understanding the relationship between Item Ledger Entries, Value Entries, and General Ledger Entries can make it easier.
There are two basic pieces of information NAV uses to calculate inventory value – quantity and cost. Item Ledger Entries in NAV record changes in quantity. Every Item Ledger Entry has associated Value Entries containing the cost information. By segregating cost from quantity, NAV is able to provide functionality to support expected cost and landed cost (topics for another day).
When posting Sales or Purchase Orders in NAV, users select what type of activity to post. Purchase Order choices are Receive, Invoice, or Receive and Invoice; Sales Order options are Ship, Invoice, or Ship and Invoice.
The Receive and Ship posting options create Item Ledger Entries – and the initial Value Entry. The Item Ledger Entry records quantities received as an addition to inventory and quantities shipped as a reduction in inventory on hand. You can confirm this by checking the Inventory field on an Item Card before and after posting a Purchase Order receipt or Sales Order shipment.
To better understand the information included in Item Ledger Entries and Value Entries, the next two screenshots show the results of posting a Purchase Order receipt for a Quantity of 10 and a Direct Unit Cost of $100 per item.
The Item Ledger Entry above has the Quantity of 10 recorded; because this is a purchase receipt, the quantity represents an increase in the inventory quantity on hand. The amount fields, including Cost Amount (Expected) are flow fields that display the total of the amounts for the related Value Entries. Click either the Value Entries icon or the entry in any of the amount fields to view the Value Entries page as shown below.
Some of the important information on the Value Entry includes the amount (expected) versus the amount (actual). For this example, the $1,000 amount is recorded in Cost Amount (Expected) and the Cost Amount (Actual) and Cost Posted to G/L are zero. If this were my only item and the only entry, the general ledger inventory account balance would be zero. The Inventory Valuation report would show $1,000 in on hand inventory. The Inventory to G/L Reconcile report would explain the difference in the two amounts – essentially, there is $1,000 in expected value that is not yet posted.
NOTE: The information presented here is specific to the NA (North American) database. For example, the WW (World Wide) database uses Inventory Valuation report 1001; the NA database uses Inventory Valuation report 10139. An additional difference is the WW database does not have an Inventory to G/L Reconcile report.
If I now post the Purchase Order as invoiced, the Item Ledger Entry does not change; the flow fields reflect the new totals in the Value Entries. Below is the Item Ledger Entry screenshot. The Cost Amount (Actual) displays the invoiced amount.
It is important to understand that the timing of when the Value Entry data is posted to general ledger is determined by Inventory Setup. Based on Inventory Setup in this company, there still is not an amount in the Cost Posted to G/L field. In order for the cost to be recorded in general ledger, the final step is to run the Adjust Cost – Item Entries and then Post Cost to G/L. When those two tasks are completed, the Item Ledger Entry does not change, the Value Entries page is updated as shown below.
The Purchase Invoice Value Entry record is updated; the Cost Posted to G/L displays the amount posted to general ledger. In the screenshot, to make it easy to identify the invoice Value Entry, the Document Type field is added to the page.
Value Entries are the connection between Item Ledger Entries and General Ledger Entries.
Now that the Value Entry has an amount in the Cost Posted to G/L field, you can select the line and click Actions → Entry → General Ledger to view the entries posted to general ledger.
Alsoy, from the General Ledger Entries page, to navigate to the related Value Entries, users can click Navigate → Entry → Value Entries as shown below.
The next time you post Item transactions from Sales or Purchase Orders, take a few minutes to review the Item Ledger Entries and Value Entries created and updated – the ability to trace and understand the posted activity is key to successfully reconciling general ledger and inventory balances.
Let us know if you have questions on Value Entries and their relationship to General Ledger and Item Ledger Entries or on reconciling inventory.