Inventory Reconciliation Part II: Understanding Inventory Transactions

Introduction: Understanding Inventory Transactions

In Part I of this series, we reviewed the basic aspects of Inventory Setup which influences how NAV calculates inventory value in various scenarios. In Part II of this series, I will review what types of entries NAV posts when inventory transactions are processed. In order to determine the cost of an item there are three types on entries posted:

  1. Item Ledger Entries
  2. Value Entries
  3. General Ledger Entries

Their interactions can be briefly summed up in the chart below:
1.0

Image Source: https://msdn.microsoft.com/en-us/library/hh997354(v=nav.71).aspx

1. Item Ledger Entries

Each time there is an inventory transaction two transactions are posted in order to track it. The first transaction can be considered the quantity tracking. It is posted in the Item Ledger Entry table as a record of the inbound and outbound entries for a specific item. Item Ledger Entries are created at the time of the inventory movement not at the time of invoicing. For example, an Item Ledger Entry is created when a Purchase Order is received or a Sales Order is shipped. There is no Item Ledger Entry created when either document is invoiced.

2.0

For the purposes of inventory reconciliation, I would like to draw attention to certain fields on the Item Ledger Entry field page:

Entry Type

Shows which type of transaction the entry was created from:

  • Purchases and Positive Adjustments represent an increase in inventory; however, if undo receipt is used the Type of Purchase will be displayed for the inventory decrease and the “Correction” field will be checked
  • Sales and Negative Adjustments represent a decrease in inventory, however, if undo shipment is used the Type of Sale will be displayed for the inventory increase and the “Correction” field will be checked
  • Transfer represents transfers and reclassifications of inventory
  • Consumption and Output describe manufacturing processes
  • Assembly Consumption and Assembly Output describe assembly processes

Document Type

Describes the source of the entry.

Document No.

Describes the Document Number assigned to the document that generated the entry. It is important to note that when an item is received the document number of the receipt is assigned to the item ledger entry, however the document number assigned in the Inventory Account of the General Ledger will be that of the Invoice. This is because the inventory movement takes place on receipt which is what is represented on the Item Ledger.

Item No.

The item number the entry influences.

Quantity

The number of units in the item entry

Quantity Invoiced

Specifies how many units of the item have been invoiced. For example, if you receive 5 units and invoice 2 the Quantity field will display 5 and the Quantity Invoiced field will display 2.

Quantity Remaining

Specifies how many units of the item associated with the Item Ledger Entry are remaining to be applied. For example, if 5 were purchased and 3 sold the Remaining Quantity would be 2.

Sales Amount (Actual)

Displays the amount the cost layer has sold for, the sales amount is determined in detail through the Value Entries. You can right click and drill down on the field in order to see how NAV determined the number:

3.0

Cost Amount (Actual)

Displays the actual cost realized of the Item Ledger Entry. Costs are considered actual costs when they are invoiced. Like the Sales Amount (Actual) field, the Cost Amount (Actual) field is determined through the Value Entries and you can see the entries which determine the cost by right clicking and drilling down on the Cost Amount (Actual) field.

Cost Amount (Expected)

Displays the expected cost of the Item Ledger Entry. Costs are considered expected costs when they are received or shipped. Like the Sales Amount (Actual) field, the Cost Amount (Expected) field is determined through the Value Entries and you can see the entries which determine the expected cost by right clicking and drilling down on the Cost Amount (Actual) field.

Open

Specifies whether the cost layer has been fully applied. NAV applies inbound and outbound entries against each other for example, if you purchase 1 unit of Item A for $5 and sell 1 unit of Item A for $7 NAV has functionality to tie the two item ledger entries together so that the cost can be appropriately applied and posted to the General Ledger’s Cost of Goods Sold Account.

Completely Invoiced

Shows if the entry has been fully invoiced.

Entry No.

Shows the entry number that the program has given the entry. Every item ledger entry has a unique entry number, which is assigned when it is posted. This can be useful when trying to determine if entries have been backdated. For example if you have an item ledger entry with a posting date of 1/1/2015 and an Entry No. of 112 and you have an item ledger entry with a posting date of 12/15/2014 and an Entry No. of 816 you can determine that the entry with a posting date of 1/1/2015 was posted first because it has a smaller entry number.

2. Value Entries

Each time there is an inventory transaction two transactions are posted in order to track it. The second transaction can be considered the value tracking. It is posted in the Value Entry Table as a record of cost and adjustments to a specific item ledger entry. There can be multiple Value Entries per Item Ledger Entry. It can be considered a subledger for each Item Ledger Entry. NAV utilizes Value Entries in order to keep records in a simple easy to read format.

Value Entries can be accessed by Navigating on Documents or Drilling Down on the Cost Amount/Sales Amount fields on an Item Ledger Entry. General Ledger entries are created on the basis of Value Entries, therefore, Value Entries are important tools to utilize when reconciling inventory.

At the time of receipt or shipment, Value Entries are created for Expected Cost. These entries are created with a Document No. to match the receipt or shipment number. Note that Value Entries for Expected Cost are always created regardless of whether “Expected Cost Posting to G/L” is checked in Inventory Setup. That option only controls whether General Ledger entries will be created based on the Expected Cost Value Entries.

At the time of invoice, Value Entries are created to reverse Expected Cost and to record Actual Cost. These entries are created with a Document No. to match the invoice number.

4.0

Item Ledger Entry Type

The Entry Type that is specified on the related Item Ledger Entry.

Entry Type

Describes the type of Value Entry:

  • Direct Cost
  • Revaluation
  • Rounding
  • Indirect Cost
  • Variance (only posted for items using the standard costing method)

Adjustment

A checkmark is placed in this field automatically by NAV when an adjustment is made to an item’s value during the Adjust Cost – Item Entries batch job. An example of an adjustment would be when adjusting an item’s cost to average if the average costing method is used or if an item’s cost was changed after a Sales Order was Invoiced.

Sales Amount (Actual)

Displays the amount the associated Item Ledger Entry has sold for.

Cost Amount (Expected)

Displays the calculation for the amount the underlying item cost layer has cost. Expected costs are those that have been received or shipped but not invoiced.

Cost Amount (Actual)

Displays the calculation for the amount the underlying item cost layer has cost. Actual costs are those that have been invoiced.

Cost Posted to G/L

Specifies the amount of the actual cost which has been posted the general ledger, expected costs posted to the General Ledger are not reflected in this field.

Item Ledger Entry Quantity

Specifies the quantity from the Item Ledger Entry associated with the Value Entry.

Valued Quantity

Specifies the quantity valued on the specific value entry line.

Invoiced Quantity

Specifies how many units of the item are invoiced on the invoice that the Value Entry line represents. Each partial invoice posting is represented by one Value Entry line.

Cost per Unit

Contains the cost for one base unit of the item in the entry. Cost per Unit is calculated as Cost Amount (Actual) divided by Valued Quantity.

Item No.

The item number the entry influences.

Gen. Bus. Posting Group / Gen. Prod. Posting Group

Specifies which matrix in the General Posting Setup the entries utilized to determine which General Ledger Account to post to.

Source Code

Specifies where the entry was posted. In order to view the Source Code Setup, you can go to Departments / Administration / Application Setup / Financial Management / Trail Codes and click Source Codes.

Entry No.

Shows the entry number that the program has given the entry. Every Value Entry has a unique entry number, which is assigned when it is posted.

Item Ledger Entry No.

Shows the assigned Item Ledger Entry No. and can be used to tie the Value Entries to the Item Ledger Entries.

3. General Ledger Entries

When inventory transactions are posted the quantity and value changes to the inventory are recorded in the Item Ledger Entries and the Value Entries, respectively. The next step is to post the inventory values to the inventory accounts in the general ledger. There are two ways to do this:

  • Manually, by running the “Post Inventory Cost to G/L” batch job.
  • Automatically, every time that you post an inventory transaction.

Inventory transactions in the General Ledger will post with a Source Code of INVTPCOST, therefore, in order to see where the item transaction was generated, you will actually need to Navigate and view the Value Entries.

Please find a chart below with information on what accounts are hit for what type of value entry transaction. Please note the general ledger accounts described can be found in the Inventory Posting Groups and Gen. Prod. Posting Group setup described in Part I:

Inventory Ledger

Source: https://msdn.microsoft.com/en-us/library/hh997400(v=nav.70).aspx

Bringing the Entries Together: An Example

In the following example, we are going to use an item that utilizes FIFO and take it through its life cycle while examining the transactions that NAV creates. For this example, I have created a new item with the following information on the Invoicing Tab:

5.0

Purchase Entries

Since this is a new item, I am going to create a purchase order and receive 1 unit of the item for $4.13. I can see which entries are created by using the Navigate tool on the Purchase Receipt:

6.0

Two types of entries are generated in order to track the item’s quantity and value. The item ledger entry:

7.0

The value entry:

8.0

Please note that the cost is currently considered expected and the Cost Posted to G/L is 0, however the Expected Cost Posted to G/L is $4.13.

Despite the Cost Posted to G/L being 0, an interim cost was still posted. The Cost Posted to G/L field is representative of the actual cost in the value entries. If expected cost posting is turned on, the interim entries posted to the G/L are a debit to the Inventory (Interim) Account and a credit to the Invt. Accrual Acc. (Interim):

9.0

Please note that if Expected Cost Posting is not turned on, no G/L Entries would have posted.

Now, I am going to invoice the unit at $4.96. No Item Ledger Entry is created because the Item Ledger Entry to track the quantity was generated at the time of the receipt; however, the value entries from before have additional entries to track the change in value of the item:

10.0

Notice, the entry for the Invoice reverses the Cost Amount (Expected) of the receipt and records the actual cost. Now, there is a Cost Posted to G/L.

The entries generated in the General Ledger are as follows:

11.0

The 4.13 is reversed out of the interim accounts and the 4.96 posted to the inventory and payable accounts. NAV chose which General Ledger Accounts to post to via the Gen. Bus. Posting Group from the Vendor and the Gen. Prod. Posting Group from the Item Card.

Sales Entries

Now, I will sell the item we just bought from a Sales Order. These are the entries generated when shipping the item with a Unit Price of $12.37:

The Item Ledger Entry created:

12.0

Notice the Sales Amount (Expected) field is 12.37, the Cost Amount (Expected) is -4.96 and the item ledger entry is not open. Because the costing method in this example is FIFO, the Cost Amount (Expected) is based on the cost layer of the item purchased in the previous example which was applied to this outbound cost layer. In order to see what has been applied to closed entries you can click the Applied Entries button on the NAV Ribbon:

13.0

The Value Entry created:

14.0

Notice that like on the Item Ledger Entry there is a Sales Amount (Expected) and a Cost Amount (Expected) for the shipment.

If expected cost posting is set to Yes then the following General Ledger Entries are created:

15.0

The Interim Inventory Account is reduced and the Interim Cost of Goods Sold Account is increased.

When the item is invoiced for 12.37, no item ledger entries are generated because one was created to track the quantity when the item was shipped. However, a value entry is created to reverse out the expected Sales Amount and expected Cost Amount and record the actual Sales Amount, actual Cost Amount and the Costed Posted to G/L is now updated:

16.0

The G/L Entries created reversed the amounts posted the interim accounts and posted the amounts to the inventory and customer accounts:

17.0

Conclusion

This concludes Part II of the Inventory Reconciliation Series, in Part III we will determine if your inventory needs to be reconciled and determine common causes of inventory discrepancies.

February 24, 2017

Questions?

Email [email protected] with any questions you have pertaining to this course.

New CPE Accredited Courses Now Available for Dynamics AX, GP, and NAVEARN CREDITS TODAY