Inventory Reconciliation Part I: Inventory Setup Basics

Introduction

The General Ledger should match the Inventory Subledger at all times, however there are instances when the two can get out of sync and need to be reconciled. In this demonstration we are going to review common causes of differences between the inventory account on the Chart of Accounts and the inventory value of the subledger. In order to do this, first we need to understand inventory setup and the entries NAV generates.

Inventory Setup

1.0

There are six fields that specifically influence the value of inventory on the Inventory Setup page:

Automatic Cost Posting

If this field is set to ‘Yes’, then NAV will automatically post to the inventory account, adjustment account and COGS account in the general ledger rather than waiting until the “Adjust Cost/Post Cost to G/L” functions are run. It is still recommended to run the “Adjust Cost/Post Cost to G/L” functions periodically even if this field is set to Yes. The benefit of setting Automatic Cost Posting to No is performance related. When NAV is adjusting cost it locks certain tables to preserve the integrity of the data and can interrupt daily business if a large amount of transactions are being processed, therefore it may be beneficial to set “Automatic Cost Posting” to ‘No’ and run the process every evening.

Expected Cost Posting to G/L

When inventory is received and not invoiced or shipped and not invoiced, NAV treats the value as “Expected Cost” since the cost can be changed before invoicing. If Expected Cost posting is set to ‘Yes’ then NAV will post these expected costs to interim accounts in the general ledger to get an estimate of the cost of items before invoicing the purchase or sales document.

Automatic Cost Adjustment

The Automatic Cost Adjustment specifies whether to, and when to, adjust cost changes automatically at the time inventory transactions are posted. Here’s how the options break down:

  • Never – Costs are not adjusted when posting
  • Day – Costs are adjusted if it is within one day of the work date
  • Week – Costs are adjusted if it is within one week of the work date
  • Month – Costs are adjusted if it is within one month of the work date
  • Quarter – Costs are adjusted if it is within one quarter of the work date
  • Year – Costs are adjusted if it is within one year of the work date
  • Always – Costs are always adjusted

If Automatic Cost Adjustment is set to Never, then the Adjust Cost/Post Cost to G/L functions will need to be ran in order to adjust costs to their proper values. It is typically recommended to set Automatic Cost Adjustment to either ‘Never’ and run it manually. The other options are to conduct a scheduled task every evening, or set it to ‘Always’ and have the system adjust in real time.

Average Cost Calc. Type

If an Item’s Costing Method is set to Average Cost this field determines how the Average Cost Calculation is broken down. It can be broken down per:

  • Item No.
  • Item/Location/Variant

Note: It is typically not recommended to change the “Average Cost Calc. Type” once entries have been posted because changing this option will recalculate the Average Cost on all items for all open periods.

Average Cost Period

If an Item’s Costing Method is set to Average Cost, this field determines the scope of the Average Cost Calculation. The average cost period can be per:

  • Day
  • Week
  • Month
  • Accounting Period

Prevent Negative Inventory

This field indicates the value in the Prevent Negative Inventory field on individual item cards. The values can be ‘Yes’ or ‘No’, which you can change. It is recommended to set Prevent Negative Inventory to ‘Yes’ since if inventory is sold to the negative, NAV cannot pair the outbound entry with an inbound cost layer.

Note: This option is not available in the North America versions of NAV prior to 2013

Posting Groups

Posting groups are used to determine which G/L Account(s) an entry should post to so that financial data is reported accurately.

Inventory Posting Setup

2.0

The inventory posting setup is per Location/Inventory Posting Group Combination and specifies which G/L Accounts transactions should post to.

Inventory Account

The G/L Account that holds actual inventory value for that location/inventory posting group combination.

Inventory Account (Interim)

The G/L Account that holds expected inventory value for that location/inventory posting group combination. Transactions posted to this G/L Account will reflect the expected cost of inventory received but not invoiced. When the inventory is invoiced the expected amount is reversed out and the actual amount is posted to the Inventory Account.

The Inventory Posting Group is specified on the Invoicing Tab of the Item Card:

3.0

 

 

General Posting Groups

The General Posting Setup is per Gen. Bus. Posting Group and Gen. Prod. Posting Group combination and specifies which G/L Accounts transactions should post to.

The Gen. Bus. Posting Group is specified on either the Vendor or Customer card. The Gen. Prod. Posting Group is specified on the Item card.

4.0

Utilizing Accounts for Inventory Transactions

Here are a few examples of how some of the accounts are utilized for inventory transactions. Note that in some instances, named accounts are from different posting setups like the Inventory, Vendor, or Customer. All of the posting setups come into play with inventory transactions.

Example 1: A Purchase Order is created and 1 unit is Received into inventory at 5 dollars, then that unit is Invoiced at 6 dollars. The following entries are created in the General Ledger:

Note: This example assumes expected cost posting is turned on.

Purchase Receipt:

Inventory (Interim) 5
       Invt. Accrual Acc. (Interim) 5

 

Purchase Invoice:

Invt. Accrual Acc. (Interim) 5
       Inventory (Interim) 5
Purch. Account 6
       Payables Account 6
Inventory Account 6
       Direct Cost Applied 6

 

Example 2: A Sales Order is created and 1 unit is Shipped from inventory at 5 dollars, then that unit is Invoiced at 6 dollars. Unit Cost was 2 dollars. The following entries are created in the General Ledger:

Note: This example assumes expected cost posting is turned on.

Sales Shipment:

COGS Account (Interim) 2
       Inventory (Interim) 2

 

Purchase Invoice:

Inventory (Interim) 2
     Inventory Account 2
COGS Account 2
       COGS Account (Interim) 2
Receivables Account 6
     Sales Account 6

 

Item Cards

The Item Card is where all of the general setups come into play. When transactions are done in the system, NAV will reference the selections on the individual item in order to decide how to treat that item. Since this blog focuses on Inventory Reconciliation, we are going to focus on specific fields in the Invoicing FastTab.

Invoicing FastTab

5.0

Costing Method

The costing method specifies how the item’s cost is calculated. There are five costing methods available: FIFO, LIFO, Specific, Average, and Standard.

Cost is Adjusted

The Cost is Adjust field indicates whether the cost is adjusted for the costing method calculation. If the field is not checked the “Adjust/Post Cost to G/L” process needs to be run.

Cost is Posted to G/L

The Cost is Posted to G/L field indicates whether the cost for the item’s subledger has been posted to the G/L. If the field is not checked the “Adjust/Post Cost to G/L” process needs to be run.

Standard Cost

Specifies the unit cost that is used as a standard measure, this field is required if the Costing Method is set to Standard.

Unit Cost

Specifies the unit cost of a single item, how this field is calculated depends on the Costing Method.

If the costing method is Standard, the field is greyed out. If the costing method is FIFO, LIFO, Specific, or Average, then:

Unit cost = inventory value of items on hand / quantity on hand

Gen. Prod. Posting Group

This field specifies which Gen. Prod. Posting Group NAV will utilize when determining which G/L Accounts to post to for inventory transactions. The Gen. Bus. Posting Group on the Vendor/Customer Card will determine the second part of the matrix, if you remember the matrix combines both to determine which general ledger accounts to post to:

6.0

Inventory Posting Group

This field specifies which Inventory Posting Group NAV will utilize when determining which G/L Accounts to post to for inventory transactions. If you will remember, the inventory posting groups are setup with a Location/Posting Group Matrix, therefore, it is possible to break down your inventory into different G/L Accounts not only per an inventory classification, but an inventory classification per location:

7.0

Conclusion

This concludes Part I of the Inventory Reconciliation Series. In Part II we will break down inventory transactions to gain a better understanding of them before moving on to looking into what causes differences between the general ledger and inventory subledger and how to find them.

July 28, 2015