Note that the Sage 100 Trial Balance and Transaction Detail Reports in Inventory are date sensitive and use the same posting date as the General Ledger. The Trial Balance and Detail Transaction reports should be used for reconciling to the General Ledger. Other Inventory Reports reflect “up to the moment” balances and should not be used for balancing to the GL.
As with any reconciliation, you might find situations where the Trial Balance Report in Inventory does not balance to the corresponding account in the General Ledger.
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.
Inventory Detail Transaction Report uses the IM_ItemTransactionHistory table and provides detail records for the Trial Balance and Valuation by Period for the same period.
Factors that can cause these Inventory Reports to not match with the Inventory Account in General Ledger:
Turning off integration to General Ledger from any module that posts to Inventory.
Hardware errors or lost-connectivity during an 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 the wrong GL accounts.
Rounding issues if decimal precision for unit cost is set to more than 2 characters.
Restoring some files from backup, but not the entire MAS_XXX (XXX=Company Code) folder.
Editing of data files using Utilities.
Issues in data from prior versions, including: Not closing modules in a timely manner in versions prior to 4.40. and posting to a closed period in versions prior to 4.40, or use of the ‘Fix’ button in 3.x versions.
Checklist when reconciling the Inventory Reports to the corresponding GL account balances:
Look at all areas where posting accounts are required. Verify that the correct Inventory Account is being used wherever it is required, and incorrect accounts are not being posted to. Check the settings, including:
Inventory Product Line Maintenance – Accounts tab for all product lines.
Miscellaneous Item Maintenance via Sales Order setup or Common Information main menus Accounts tab.
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 Company Maintenance in Library Master to make a copy of your live data files to a test company. Then, reconcile the General Ledger balance against the Inventory Reports, noting the steps required.
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.