Inventory reports do not match and do not reconcile to the inventory accounts in General Ledger

Because the Stock Status and Valuation reports are perpetual inventory reports and not date-sensitive, you will not use these to reconcile to the general ledger or other date-specific reports such as the Trial Balance, Valuation by Period or Detail Transaction Reports.

If "they always balance" and this period-end they do not, preview the Inventory Detail Transaction Report using the selection criteria By Date. The starting date should be the day after the period-end day and the ending date should be 12/31/9999. For example, if you are running the Valuation by Period as of 12/31/2020, your starting date should be 01/01/2021.

This will show any "future" transactions which are included in the Valuation Report but, not in the Valuation by Period report.

The standard Sage 100 inventory reports are

• Inventory Valuation Report
• Inventory Stock Status Report
• Inventory Trial Balance Report
• Inventory Valuation Report by Period
• Inventory Detail Transaction Report

The Inventory Valuation and Stock Status reports are not date sensitive. These reports are perpetual inventory reports, as of the moment run, and should not be used to reconcile to the General Ledger.

The Inventory Trial Balance, Valuation by Period and Detail Transaction reports are date sensitive and use the same posting date as the General Ledger. These reports should be used for reconciling to the General Ledger.

As with any reconciliation, you may encounter situations where reports do not balance to the corresponding accounts in the General Ledger. Sage Software Support cannot review non-standard reports, nor help to reconcile to the General Ledger.

CAUTION: 

Report/Table features:

• Trial Balance and Valuation by Period reports use the IM_PeriodPostingHistory table.
  • This table contains the BeginningBalance, PeriodChangeQty and PeriodChangeDollarAmts.
  • This should match the Detail Transaction Report when run for the same period.
  • These reports should be run far into the future to capture any future period changes when comparing with the Stock Status, Valuation Reports and GL Inventory Accounts.
• Detail Transaction Report uses the IM_ItemTransactionHistory table and provides detail records for the Trial Balance and Valuation by Period for the same period.
• Valuation Report uses the IM_ItemCost table.
• Stock Status Report uses the IM_ItemWarehouse table.

Factors that can cause the Inventory Reports to not match each other and be out of balance with the Inventory Accounts in General Ledger.

• Turning off Integration to General Ledger from any module that posts to Inventory.
• Connectivity lost, errors and hardware malfunctions during update from a module register causing entries to be lost.
• Clearing the Daily Transaction Register postings before updating the Daily Transaction Register. Postings are made to Inventory during the subsidiary module update but not to the General Ledger history.
• Posting to wrong accounts.
  • Example: In Common Information, Main menu, Miscellaneous Item Maintenance, one or more Miscellaneous or Charge type item may have an "Inventory" account entered. However, reports in Inventory Management module do not track miscellaneous items or charges. Any "Inventory" accounts entered in Miscellaneous Item Maintenance should not be the same as those used for true Inventory Management Item Codes.
  • Example: In Inventory Management, Setup menu, Product Line Maintenance, one or more product lines may have had an inventory account number in the wrong field (such as putting in an Inventory account for the Cost of Goods Sold field). Or, a non-inventory account number was entered for the Inventory field.
  • Example: In Accounts Payable, Main menu, Invoice Data Entry, users may have posted a vendor invoice to an Inventory account.
    • Note: One way to check for this is to open General Ledger, Reports menu, General Ledger Detail Report and specify a Source Type of "Module Registers" and specify for Account Number an Inventory account, then Preview the report to see if any postings were made to Inventory from the Accounts Payable module.
• Rounding issues, if the decimal precision is set to more than 2 characters for the unit cost.
• Restoring some files and not the entire MAS_XXX (XXX=Company Code).
• Editing of data files using Utilities.
• Issues in data from prior versions
  • Not closing modules in a timely manner in versions prior to 4.40.
  • Posting to a closed period in versions prior to 4.40.
  • Use of the 'Fix' button in 3.x versions.

The following is a list of things to be aware of when reconciling the Inventory Reports to the corresponding GL Account(s):

• Look at all areas where posting accounts are required. Verify that the Inventory Account is being used wherever it is required, and incorrect accounts are being posted to.
• Verify the GL accounts setup for postings is not using the same GL account for the debit posting as the credit posting causing a zero entry.
• Use original hard copies of the register, journals, and the Daily Transaction Register to verify entries are not missing from the General Ledger.
• Use the Library Master, Main, Company Maintenance to make a copy of your live data files to a test company. Then, reconcile the General Ledger balance against the Inventory Reports.
• Use the G/L Detail By Source Report to reconcile. Verify each transaction posting back to each register. Verify that no postings to the Inventory Account in General Ledger resulted from an inappropriate subsidiary module or Journal Entry. If a transaction was posted that did not result from an appropriate subsidiary module, reverse the entry in the appropriate module and reenter the transaction in the appropriate module.

How to copy company data into another company in Sage 100
Inventory Trial Balance Report does not balance to the Inventory account in General Ledger