Sage 100: Inventory Trial Balance does not match General Ledger
The standard Sage MAS 90 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. 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 MAS 90.
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. Sage Software Customer Support cannot review non-standard reports, nor help to reconcile to 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 is not 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 from any module that posts to Inventory or clearing the Daily Transaction Register postings can cause out of balance conditions. Postings are made to Inventory but not to the General Ledger.
3. 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.
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. The system allows this if the General Ledger period is still open.
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 does not balance to the General Ledger. Verify the quantity on hand on the Stock Status Report and make sure that it matches the Inventory Valuation Report. The IMZBL1 utility can also be run to search for any items with an out of balance condition.
Note: For additional details, search on “Stock Status does not match…“
7. Rounding issues, if the decimal precision is set to more than 2 characters for the unit cost.
For example, if an item is valued at Average Cost, the Valuation Report and the I/M Trial Balance report usually matches because they use the average cost rounded to the nearest decimal based on the moving average. The I/M Detail Transaction Report and the general ledger use specific costs for each individual transaction. The sum of these transactions when the total dollars are divided by the on hand quantity often result in exceeding the allowed number of decimal places.
For instance: .020554127 when rounded to two decimals would remain at .02 but when rounded to three decimals .021 and four decimals (the maximum allowed) .0206, each resulting in a different total cost calculation.
8. Use of the ‘Fix‘ button in 3.x versions.
9. Standard Cost – Trial Balance for Standard Cost items uses the current standard cost.
10. Use of the Cost Rollup Registers in Bill of Materials or Work Order changes the standard cost on parent items. If the parent is a standard cost item, the Trial Balance reflects the change but General Ledger does not.
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.
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 MAS 90 files to a test company and close the period in Inventory twice. Then, reconcile the General Ledger balance against the Inventory Trial Balance. If they are in balance, there are likely future dated transactions that are throwing the Trial Balance off.
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 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.
6. The Trial Balance is calculated as follows:
- Beginning Balance = IMM (this is written during period end)
- Ending Balance = (Current quantity on hand * Current Costs) – Future Dated Transactions
- Activity = Ending Balance – Beginning Balance
Support
If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.