At some point working with SAP Business One you are going to get a question about GL account balances not matching the inventory audit report. Generally this is related to the year-end-closing process. Unlike accounts receivable and accounts payable, inventory accounts are not considered control accounts. Because they are not considered control accounts, they are not controlled like control accounts. This article will detail how to get your inventory balances, how to run the inventory audit report and how to make the two match.
What is the Inventory Audit Report?
The inventory audit report is a report designed to give you all the details of the inventory transactions in your system for a given period of time. The inventory audit report is very detailed and contains every transaction in and out for all items including revaluations, inventory transfers, etc. It is designed to be a running ledger of the values of each item code and to provide detail accompanying something like a balance sheet or trial balance.
The audit report can be run for all inventory accounts at once or just specific ones. It can be run for specific warehouses, it can be run for specified item groups or even item codes. For the purpose of this tutorial, I'm going to assume you are just trying to match one inventory account trial balance to one inventory account on the inventory audit report.
Why Doesn't the Inventory Audit Report Balance to My Trial Balance?
I will list issues and common fixes for scenarios I have observed.
Manual Journal Entry to Linked Inventory Account
Issue: The most common issue you will find is manual journal entries done to inventory accounts linked to warehouses or item groups. You will find accountants who simply want to revalue the dollar amount of inventory at year-end to some arbitrary or calculated value. So they simply do a manual journal entry to adjust the values which is the quickest way to change the balances. Problem with this solution is that the dollar values assigned to each items cost have not changed proportionately. So that when you go to sell the item, the item value issued out to COGS will be generally understated (could be overstated).
Solution: The easiest way to fix this is to track down manual journal entries in the general ledger of the account in question. Simply go to Financials >> Financial Reports >> Accounting >> General Ledger and run the report for the specific account for the required period of time. Run through the transactions under the "Doc. No." column and look for items marked JE. When you add these up they should match the difference you are seeing in the inventory audit report.
Once you isolate the transactions you can reverse them one at a time. Simply reclassify the balances to an "Inventory Adjustment" type of account. If you do not see any journal entries in the same period you are looking, try running for a wider date range.
Item Group or Warehouse Inventory Account Changed with Stock
Issue: This is a very challenging issue to fix. It is also harder to diagnose since it's not as apparent as a manual journal entry. This happens when an unsuspecting administrator changes a GL account determination while stock is still in the system in that item group or warehouse. If you do not reduce the inventory value to $0.00 before swapping GL accounts you will be in trouble.
This is why there is a red bar warning if you change any inventory related GL account determination.
The easiest way to diagnose this is to run the audit report for an item group but group by account. Then output the results to Excel and cross-reference the item codes to see if one set of codes appears in multiple accounts. If this is the case you will need to manually fix each item that is
This can also occur if one specific item has a change of item group while stock exists. So you might be facing a single item code which is affected by this which is difficult to track down. In this case I would run the audit report for all items and all accounts then summarize by accounts. Pivot the results to item code totals by account and then do a count to see if there is an item code with more than one account.
Solution: If the item has balances in two accounts then you have to clear the existing balance first. My suggestion for this is a goods issue. Once the balance is at $0.00 for the currently assigned account, switch the settings or item group back to the old account. Then do the same thing with another goods issue. You will have to potentially use an inventory revaluation to clear the account properly if the moving average has been changed significantly. Simply compare the quantity and value left in the audit report once you switch the item back to the old account. Then you would use an inventory revaluation (Inventory >> Inventory Transactions >> Inventory Revaluation) to get the dollar amount perfect so that when you issue out the final stock it clears to zero.
Inventory Audit Report Discrepancy Prevention
My recommendation is to block manual journal entries (object type 30) to any inventory account linked to a warehouse or item group using the TransactionNotification StorProc system (more detail here). I install this on all my client databases to prevent this issue. Unfortunately to block the change of item group you need something like B1UP to make the field read-only.
Education of users is also key. Make sure the client is aware that they need to read the warning messages produced by changing inventory accounts. Also, understanding the whole cycle of inventory transactions is key to preventing these issues.
However, if this does occur then follow my steps to make the balances match again without too much trouble.