SAGE 100 ERP TIPS: BALANCING INVENTORY TO THE GENERAL LEDGER
Clients sometimes call asking what to do when the Inventory Trial Balance Report or the Inventory Valuation Report does not balance to the Inventory account in General Ledger. The standard Sage 100 ERP reports are the Inventory Valuation Report, Inventory Stock Status Report, Trial Balance Report, and Detail Transaction Report from the Inventory Management Reports menu. The Inventory Valuation and Stock Status Reports are not date sensitive. Because Sage 100 ERP is a perpetual inventory system, these reports are “as of this moment”, and should not be used to reconcile to the General Ledger. The 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 are the reconciling reports provided by Sage 100 ERP.
As with any reconciliation, you may encounter situations where the Trial Balance Report in Inventory does not balance to the corresponding account in the General Ledger.
There are many factors that can cause the Inventory Trial Balance Report to be out of balance with the Inventory Account in General Ledger.
Some of the reasons are listed below:
1. The Trial Balance Report can only be run for the current period. Changing the period in the Inventory Setup Options to run the Trial Balance Report in a prior or future period will not be successful because the beginning balances on the report are calculated at period end. Running the report for a different period does not change the beginning balance on the report.
2. Turning off integration to General Ledger in Inventory Management Setup Options or clearing the Daily Transaction Register postings.
3. Posting to the wrong G/L accounts.
4. Not closing modules in a timely manner. The subsidiary modules should never post beyond the current period and one future period.
5. Posting to a closed period, which should never be done.
6. If there is an out of balance between the IM2 (Inventory Stock Status Report) and the IM3 (Inventory Valuation Report) the Inventory Trial Balance will not balance to the General Ledger. Check the quantity on hand on the Stock Status Report and make sure that it matches the Inventory Valuation Report. You can also run the IMZBL1 utility to check for any items with an out of balance condition. The IMZBL1, Inventory Quantity On Hand Balancing Utility is documented on your TRSG (Technical Reference and Support Guide) CD.
IMZBL1 can be used on Lot/Serial Items, but will not correct the out of balance condition if items using these valuation methods are Out Of Balance between warehouse detail (IM2) file, and cost detail/cost tier (IM3) file. Call DSD Sage 100 ERP Support for assistance in correcting this condition.
The following is a list of things to be aware of when reconciling the Inventory Trial Balance to the corresponding GL Account:
1. Look at all areas where posting accounts are required. Verify that the Inventory Account is being used wherever it is required, and no other accounts are being posted to. For example, double check the posting accounts in Product Line Maintenance (found on the Inventory setup menu).
2. Use original hard copies of the register, journals, and the Daily Transaction Register to verify entries are not missing from the General Ledger.
3. Use the SVDATA utility to copy your Sage 100 ERP files to a test company, and close the period in Inventory twice. Then, check the General Ledger balance against the Inventory Trial Balance.
4. Print the Detail Transaction Report for all transactions by blanking out the starting date and making the ending date some year out in the future. The sum of the Detail Transaction Report and the future year in General Ledger should agree when the report is run in this manner.
5. Use the G/L Detail By Source Report to reconcile. Verify each transaction is posting back to each register. Verify that no postings to the Inventory Account in General Ledger resulted from an inappropriate subsidiary module. If a transaction was posted that did not result from an appropriate subsidiary module, reverse the entry in the appropriate module and re-enter the transaction in the appropriate module.
The following relates to items whose valuation method is Average Cost:
The ending dollar amount on the IM Trial Balance Report for an average cost item will change based on future transactions. This makes it impossible to reconcile the Inventory module to the General Ledger accurately because the totals on the I/M Trial Balance report will no longer match the totals in the General Ledger for the Inventory Account. Therefore future receipt transactions can cause improper rounding when the ending balance of the trial balance is generated (because the ending dollar amount of the trial balance is not stored but rather calculated).
