Introduction: Identifying Differences
In Part I and II of this series we reviewed the basic aspects of Inventory Setup and Inventory Transactions. In Part III, I will review select inventory reports which can be useful for analyzing inventory, how to identify when your inventory needs to be reconciled and the steps to take to begin processing an inventory reconciliation.
Inventory Valuation Report
The Inventory Valuation Report can be used to determine the value of your inventory as per the subledger, therefore it is a useful tool when comparing the subledger to the general ledger. It includes in its calculation Actual Cost (invoiced cost) + Expected Cost (shipped not invoiced/received not invoiced cost), therefore if you have your Inventory and Inventory (Interim) separated, you will need to take the total of those two accounts in order to compare your general ledger to your subledger. Before running this report, you should run the Adjust Cost/Post Cost to G/L functions in order to ensure the Inventory Valuation Report is up-to-date. The Inventory Valuation Report can be found under Departments/Financial Management/Inventory/Reports
Inventory to G/L Reconcile Report
The Inventory to G/L Reconcile Report can be used to reconcile the inventory printed in the Inventory Valuation report to the amounts posted to the general ledger in the balance sheet inventory accounts. This report is especially useful if the inventory account and inventory interim account are set to different accounts or if the “Expected Cost Posting to G/L” is turned off. Since the Inventory Valuation report includes Expected Cost but the Inventory Account does not it is necessary to back Expected Cost out of the Inventory Valuation to tie it to the General Ledger Inventory Account. The report has the following columns:
The inventory value calculated on the Inventory Valuation Report
Received Not Invoiced
The Cost of Inventory that has been Received not Invoiced on Purchase Orders and Sales Return Orders
Shipped Not Invoiced
The Cost of Inventory that has been Shipped not Invoiced on Sales Orders and Purchase Return Orders
Total Expected Cost
The sum of inventory that has been Received not Invoiced and Shipped not Invoiced
Rec. Not Inv. Posted to G/L
If “Expected Cost Posting to G/L” is turned on, the amount that has been Received not Invoiced will be posted to the Inventory Interim Account and balance against the Invt. Accrual Acc. (Interim) Account. This column should tie to the Invt. Accrual Acc. (Interim) Account. If “Expected Cost Posting to G/L” is turned off, then Expected Cost is not posted to the G/L and this column should be 0.
Shp. Not Inv Posted to G/L
If “Expected Cost Posting to G/L” is turned on, the amount that has been Shipped not Invoiced will be posted to the Inventory Interim Account and balance against the COGS (Interim) Account. This column should tie to COGS (Interim) Account. If “Expected Cost Posting to G/L” is turned off, then Expected Cost is not posted to the G/L and this column should be 0.
Expected Cost Posted to GL
The sum of the Rec. Not Inv. Posted to G/L and Shp. Not Inv. Posted to G/L, this should tie to the Inventory (Interim) Account
Expected Cost to be Posted
If there is an amount here then the Adjust Cost/Post Cost to G/L functions needs to be run. This is because there are amounts have been received/shipped not invoiced which have not been posted to the G/L. In order to have subledger costs in this report tie to the G/L Accounts, the Adjust Cost/Post Cost to G/L functions need to be run so that the Expected Cost to be Posted is zero.
If the Adjust Cost/Post Cost to G/L functions needs to be run, this field will be set to Yes
The Cost of Inventory that has been Invoiced, this includes costs that have not yet been posted to the G/L
Inv. Value Posted to G/L
The Cost of Inventory that has been Invoiced, this should tie to your Inventory Account
Inv. Value to be Posted
If there is an amount here then the Adjust Cost/Post Cost to G/L functions needs to be run. This is because there will be amounts that have been invoiced which have not been posted to the G/L. In order to have subledger costs in this report tie to the G/L Accounts, the Adjust Cost/Post Cost to G/L functions need to be run so that the Inv. Value to be Posted is zero.
The Inventory to G/L Reconcile Report can be found under Departments/Financial Management/Inventory/Reports
Does My Inventory Need to be Reconciled?
The inventory subledger and general ledger should be reconciled on a monthly basis to ensure that they are in synch. If any differences are found, corrections should be made to bring them back into synch. You can determine if there is a difference between your subledger and general ledger by running the Inventory to G/L Reconcile Report and comparing the Inventory Value Posted to G/L total to the Inventory G/L Account.
Inventory to G/L Reconcile end totals:
The balance at the date amount from the Chart of Accounts is $10,000 different than the “Inv. Value Posted to G/L” amount on the report, therefore there is 10,000 dollars that has been posted to the general ledger which was not posted to the subledger. Please note if your Inventory (Interim) Account is the same Account as your Inventory Account you will need to combine the Expected Cost Posted to G/L and Inv. Value Posted to G/L columns from the Inventory to G/L Reconcile Report before comparing to the balance of the Inventory Account on the G/L.
Checking Source Codes in the G/L
Typically when there is a difference between the subledger and general ledger it is because a user made a direct entry through a general journal. There is a simple way to check if direct entries have been posted.
If you open the G/L Account Card and view the Ledger Entries, you can filter for Source Code is <> INVTPCOST.
If an entry with a Source Code of GENJNL appears, then you have found the entry created from the general journal:
Please note that beginning balances are often brought in through the general journal and beginning balance entries can be ignored and should not be reversed because the subledger and general ledger balances are generally brought in separately during conversion. Typically, beginning balance entries have:
- An obvious description or document number
- A small entry number
- A posting date that matches your conversion date
There is a built in functionality to reverse entries made from the general journal. If you select the entry that needs to be reversed, you can click the Reverse Transaction button on the NAV Ribbon:
The Reverse Entries Page will open and Selecting Reverse or Reverse and print will reverse the transaction:
Now an entry with a Source Code of reversal will be posted with the opposite sign:
The Reversed by Entry No. and Reversed Entry No. will reference the Entry No. in the General Ledger Entries so you can track if that entry has been reversed.
It is recommended to turn off Direct Posting on all G/L Accounts that tie to a subledger, this is a control to disallow postings that would circumvent the subledger:
Posting Documents Directly to the General Ledger
Another common cause of differences between the G/L and subledger is purchasing or selling directly to the Inventory G/L Account rather than through items:
When a Purchase Line is posted directly to a G/L Account, it posts with a Source Code of PURCHASE rather than INVTPCOST.
In this case the Source Code is not the best way to identify the lines. The best way to identify the lines would be to run the following tables and filter by Type: G/L Account and the Account Number for your Inventory G/L Account:
- Purch. Inv. Line Table
- Purch. Cr. Memo Line Table
- Sales Invoice Line Table
- Sales Cr. Memo Line Table
If you do not have access to run tables, a developer can easily add the pages for these tables to the Menu Suite for you. These pages come in native NAV however are not natively available on the user interface.
In order to correct a document posted directly to the inventory general ledger account you will need to post the opposite type of document for the same amount. For example, if you purchased directly to the G/L Account you will need to create a Purchase Credit Memo to the G/L Account for the same amount as the purchase. In order to simplify the process you can use the Copy Document functionality to copy the original Purchase Document:
It is important to keep track of which documents have been corrected since this information is not easily accessible from the General Ledger. Not tracking corrections can result in two corrections for the same document which will throw the general ledger out of balance in the opposite direction.
Changing Inventory Posting Groups
The final common difference between the General Ledger and subledger for inventory accounts is when there are multiple Inventory General Ledger Accounts and an Item’s Inventory Posting Setup is changed after transactions have been posted:
NAV will allow users to change the Inventory Posting Groups even when there is a quantity on hand. NAV will not retroactively update posted entries to a new posting group. For example if an item had been accidentally setup with the RAW MAT Inventory Posting Group and after inventory was bought the Item Card updated to the FINISHED Inventory Posting Group. The result will be the Item was bought to the Raw Materials G/L Account and sold out of the Finished Goods G/L Account overstating Raw Materials and understating Finished Goods. When the two G/L Accounts are combined the inventory value is correct, however since the item really should have been bought to Finished Goods the individual accounts are incorrect.
In order to identify items which have had their setups changed you will need the Value Entries for all transactions. The Value Entries will need to be exported from the Table because the Inventory Posting Group field is not natively available on the Page. Please make sure you are exporting the following fields to excel:
- Posting Date
- Item No.
- Document No.
- Cost Amount (Expected)
- Cost Amount (Actual)
- Inventory Posting Group
In Excel create the following Pivot Table:
- Item No.
- Inventory Posting Group
- Sum of Cost Amount (Expected)
- Sum of Cost Amount (Actual)
The items with two Inventory Posting Groups listed have had their Inventory Posting Groups changed. If there is an amount in Cost Amount (Expected) for an incorrect posting group you will need to temporarily change the setup back to the old posting setup (when users are not posting in the system) and either complete the Purchase Document/Sales Return Order or undo Receipt on the document and update the Item Card back to the correct Inventory Posting Group. This is because NAV will recognize the change in posting group when the cost is updated from Expected to Actual and will not allow the posting routine to complete.
In order to correct inventory balances where the inventory posting group setups have changed a direct entry will need to be made to take the inventory balance out of the old inventory account and place it into the new inventory account.
In the example above, item number 1000’s setup was changed from the Raw Materials G/L Account to the Finished Goods G/L Account. The amount off for this item is 2,103.56. Therefore you will need to make the following entry from a General Journal:
Corrections posted should be kept documented so that entries are not accidentally corrected twice. Additionally, since this correction involves posting directly to the general ledger the description for why it is being posted will need to be very clear. This is because these entries will be red flags for future inventory reconciliations and the person performing the reconciliation should be able to determine by the description that the entries were corrections which do not need to be reversed.
Identifying System Issues
If none of the scenarios described above resolve the differences between the general ledger and subledger, then it is time to look for a system issue. A system issue is when there is an issue with the code that is causing the general ledger and subledger to become out of sync. System issues are often difficult and time consuming to identify.
In order to identify a system issue, you must determine what action is causing the discrepancy. The best way to begin is to determine when the discrepancies began.
First, you should run the Inventory to G/L Reconcile report as of the day you were implemented to see if your Inventory Account matched as of conversion. If not, then the beginning balances of inventory may no been have balanced during conversion.
If your inventory balanced as of conversion, then I would recommend running for every year since conversion to figure out what year your inventory first went out of balance. Note, this is one reason we always recommend reconciling inventory on a monthly basis so if any issue occurs it can be caught and identified immediately.
Then, run the report for every month until you found out what month you first went out of balance.
Then, run the report for every week until you find the week that went out of balance.
Finally, run the report until you find the day you went out of balance.
Doing this narrows down the transactions you have to sift through to find out what transaction started the discrepancies.
Once you have figured out what day the discrepancy started it is time to compare the General Ledger and the Value Entries in order to find a discrepancy between the actual cost on the value entries and the total for the document number in the inventory account:
Please note that the Item Ledger Entry is posted with the document number of the receipt or shipment and the entries posted to the inventory account in the general ledger are posted with the document number of the invoice. Since the expected cost of the receipt or shipment is reversed out with the invoice if your Inventory and Inventory (Interim) accounts are the same, you will need to include the document numbers of all receipts and all invoices for one Purchase Order when determining the amount in the inventory account for that Purchase Order. You can export all the Value Entries to Excel and all the G/L Entries for the Inventory Account to Excel and pivot on Document No. and use an Excel formula to find the documents that caused a difference. A developer can write a report to calculate this for you since this can be a very time consuming process.
It is important to note that if you run the Post Cost Process per Posting Group this method will not work because the entries posted to the G/L will have the Document No. which was specified when the Post Cost to G/L Process was run and the Value Entries will have the original Document No. We recommend always running the Post Cost to G/L Process per entry so that the document number of the entry being adjusted is carried over. If you do post per Posting Group, a developer can write a report to identify the original document number for the G/L entry by writing similar functionality for the Item Ledger Relation on the G/L Register:
Once a difference is identified, it needs to be replicated in a test environment. You will need to try and post the document in the same manner to determine at what point and what function is causing the issue. Were there multiple receipts on the PO? Was Undo Receipt done on the receipts? Was the receipt and invoice posted on the same day? These are all questions to consider when trying to replicate the discrepancy.
Once you have determined the underlying issue for the discrepancy you should review Microsoft’s list of Hotfixes to see if your situation is described, here’s an example of a situation with a Microsoft Hotfix:
A developer will be able to implement the hotfix as a go-forward fix. A retroactive fix needs to be developed and tested thoroughly and will vary per situation, it may be as simple as a one-time direct entry to the inventory account to bring it back in balance or it may be as complicated as fixing every entry through a processing report.
If you cannot find your situation described in the Microsoft Hotfixes, you need to consider any customizations that have been made to your system. If a customization is causing the discrepancy it will need to be re-designed and a retroactive fix recommended by your Microsoft Partner. If you cannot find a Microsoft Hotfix and the situation can be replicated in a clean demonstration environment your Microsoft Partner can report the issue to Microsoft to have them review and create a hotfix.
This concludes the Inventory Reconciliation series you should have taken away an understanding of Inventory Setup and Inventory Transactions as well as how to identify when your inventory needs to be reconciled and the steps to take in order to bring the General Ledger and Subledger back into balance.