How do I find why the inventory is out of balance running the Inventory Reconciliation report

Owned by Beth Blok (Unlicensed)
Last updated: Nov 21, 2022 by Mike SheenVersion comment

Problem

The inventory reconciliation report is out of balance i.e. the ledger balance does not agree to the stock on hand value.

Solution

There are a few reasons why the inventory may not balance.  It could be

  • transactions that have impacted the inventory control account but not updated stock records (e.g. creditor purchases); and/or 

  • non-physical inventory items posting to the inventory control account.  This will cease to be an issue in versions 7.00.175 and higher where the posting of non-physical items has changed (uses the Cost Of Goods Movement account to post purchases of these items)

  • physical inventory items linking the inventory value / delivered asset accounts to a P&L expense account

  • not all relevant inventory ledger accounts are included in the report.  The report looks at the Inventory Value and Delivered Asset accounts for each 'physical' inventory item and includes in the report each unique ledger account.  Where Warehouse Masking is applicable or new ledger accounts have been created since the system was setup, at least one physical inventory item needs the Inventory Value and Delivered Asset account(s) linked to the account 

Example - warehouse masking is set in the database

  • Three Inventory Value accounts - 6200-200-00, 6200-300-00, 6200-600-00

  • Inventory Classifications are all linked to 6200-200-00 and all physical items linked to 6200-200-00

  • One physical inventory item is to be linked to 6200-300-00

  • Another physical inventory item is to be linked to 6200-600-00

  • Report should now return all three ledger accounts 

A starting point is running off the General Ledger Listing with Source report for the inventory control account grouped by Source and see if there is something in there that provides the answer.

  1. Inventory > Configuration and check linking to the Inventory Value and Delivered Asset accounts (all Balance Sheet > Current Asset accounts)

  2. Inventory > Maintenance > Search window and select the filter Non-Physical (IN_Main.PhysicalItem = 0). Inventory Value and Cost of Sales should be expense accounts

  3. General Ledger Listing with Source report - select relevant period and ledger segments for the Inventory Value account(s) and Delivered Asset account (where different from the Inventory Value account)