Skip to content
logo Knowledgebase

Inventory reports don’t reconcile to the inventory accounts in General Ledger

Created on  | Last modified on 

Summary

How to Reconcile Inventory or Inventory Management to the General Ledger. What to do when the Valuation report doesn’t match the Valuation by Period report in Sage 100.

Description

  • Stock Status and Valuation reports aren’t date-sensitive.
    • Don’t use them to reconcile the general ledger or other date-specific reports.
  • The Inventory Trial Balance Valuation by Period, and Detail Transaction reports depend on specific dates. 
    • These reports use the same dates as the General Ledger.
    • Use these reports to reconcile with the General Ledger.
  • If reports usually balance but don’t balance this period, preview the Inventory Detail Transaction Report By Date.
    Set the start date after the period-end and the end date to 12/31/9999.
    For example, if you're running the Valuation by Period as of 12/31/2026, your starting date is 01/01/2027.
  • This shows future transactions 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

Resolution

Reports and Tables

  • Trial Balance and Valuation by Period reports. Use the IM_PeriodPostingHistory table.
    • This table contains the BeginningBalance, PeriodChangeQty, and PeriodChangeDollarAmts.
    • Matches the Detail Transaction Report when run for the same period.
    • Run the reports far into the future to capture any future period changes. Use them to compare Stock Status, Valuation Reports, and GL Inventory Accounts.
  • Detail Transaction Report uses the IM_ItemTransactionHistory table. Provides detailed records for the Trial Balance and Valuation by Period for the same period.
  • The valuation Report uses the IM_ItemCost table.
  • Stock Status Report uses the IM_ItemWarehouse table.

Investigate Common Causes of Mismatches

Factors can cause Inventory Reports to disagree with each other and fall out of balance with the Inventory Accounts in the General Ledger.

  • Turning off General Ledger integration in any module that posts to inventory causes mismatches.
  • Lost connectivity, errors, or hardware issues during updates can cause missing entries.
  • Rounding issues, if you set the decimal precision to more than two characters for the unit cost.
  • Restoring some files and not the entire MAS_XXX (XXX=Company Code).
  • Editing of data files using Utilities.
  • Clearing the Daily Transaction Register before updating it causes mismatched records.
    • The module posts to Inventory but not to General Ledger history.
  • Posting to the wrong accounts. 

Examples of posting to the wrong accounts

  • Example: In Common Information, Main menu, Miscellaneous Item Maintenance.
    • A misc or charge item uses an Inventory account.  But Inventory Management reports don’t track these items.
    • Don’t use the same Inventory accounts for misc items and true inventory Management Item codes.
  • Example: In the Inventory Management, Setup menu, Product Line Maintenance.
    • One or more product lines use an inventory account number in the wrong field.
    • such as putting in an Inventory account for the Cost of Goods Sold field.
    • Or, a user entered a non-inventory account number for the Inventory field.
  • Example: In Accounts Payable, Main menu, Invoice Data Entry.
    • Users posted a vendor invoice to an Inventory account.
    • One way to check for this is to open the General Ledger, Reports menu, General Ledger Detail Report.
    • Specify a Source Type of Module Registers.
    • For Account Number, specify an Inventory account.
    • Preview the report to see if you made any postings to Inventory from the Accounts Payable module.

Verification techniques 

Review these key points when you reconcile Inventory Reports to their matching GL accounts.

  • Check all places that require posting accounts. Make sure you use the Inventory Account correctly and no wrong accounts post.
  • Verify GL posting setup doesn’t use the same account for both debit and credit. A debit and credit creates a zero entry.
  • Check the General Ledger for missing entries. Use original register copies, journals, and the Daily Transaction Register to confirm everything posts.
  • Use Company Maintenance to copy your live data to a test company. Then reconcile the General Ledger balance with the Inventory Reports.
  • Use the G/L Detail By Source Report to reconcile each posting. Confirm no Inventory posting came from the wrong module or a manual entry. If one did, reverse it in the correct module and reenter it properly.

Need more help?

Chat now



Related Solutions

How to copy company data into another company in Sage 100
Inventory Trial Balance Report doesn’t balance to the Inventory account in General Ledger