Menu

Troubleshooting the inventory reconciliation

  • Updated

Below are items to review when the inventory value and general ledger inventory account do not reconcile.

  1. At go live - post correct inventory total to inventory G/L account?
    1. At the go live, inventory detail is converted to Inform to populate the inventory value or sub-ledger. A financial confirmation is signed by the customer with the total inventory balance converted. This is the balance that should be posted to the general ledger, not what is listed as the balance in your trial balance at the time of go live.
    2. If this balance is not entered as the beginning balance in Inform, inventory value will not reconcile to the general ledger right from the go live date.
  2. Review prior month end reports to determine when the variance first occurred.
    1. Review prior inventory reports in the EOM folder or in the attachment tab and create the inventory reconciliation by month to determine when the variance first occurred.
    2. The formula for inventory reconciliation is:
      1. G/L inventory value + Un invoiced PO report = Inventory value
  3. G/L control table should be set as follows:

    Application

    G/L Account

    Inventory

    Inventory

    Freight In

    Inventory (C3 Only)

    Direct ship inventory

    Direct Ship Inventory

    Current Asset

    Non Inventory Clearing

    Inventory Clearing Account

    Current Asset

    Inventory Change

    COS Account

    Damaged Goods

    COS Account

    Returned Goods Warehouse

    Current Asset (Version 17)

    Un Invoiced PO (option)

    Current Liability (Version 17)

    AP Receiving Variance

    COS Account (Version 17)
  4. Review the inventory G/L account in the G/L inquiry screen
    1. The only transactions that should post to this account are SJ, AP and IC transactions. Other transactions posted will only update the G/L and not the inventory value, causing a difference.
    2. Review the IC transactions - are there any large IC transactions?
  5. Review the Non Inventory Clearing G/L account
    1. This account should net to $0 every month. Any transactions posted to this account should be reviewed and adjusted out of this account.
    2. Accounts payable invoices are posted in this account when the AP invoice for an inventory vendor (inventory G/L account is in the vendor's master file as the expense G/L) is not linked to a stock receipt. To correct these transactions, either link the AP invoice with the stock receipt or make a journal entry to post the transaction to the correct account.
    3. For sales journal transactions in this account, a consumable or SP item with cost is credited to this account. The system makes an entry for the sale of these items to debit COGS and credit non inventory. To correct these transactions, a journal entry should be made to debit non inventory and credit COGS.
  6. Review the direct ship inventory G/L account and warehouse
    1. The direct ship inventory G/L account should be reviewed for activity at month end because it is a clearing account and should net to zero. If there are transactions in this account they should be reviewed and processed correctly.
    2. Run the inventory value report for the direct ship warehouse and tie to the general ledger. The warehouse should have $0 balance since all direct ship sales and AP invoices should be posted the same day.
  7. Review Misc codes (file >company> miscellaneous codes and run REPORT with G/L codes listed)
    1. Review codes to ensure proper posting.
    2. G/L should not be the Inventory G/L Account
  8. Review inventory adjustment reason codes (file>product>inventory reason codes and run REPORT with G/L codes listed)
    1. Review codes to ensure proper posting.
    2. G/L should not be the Inventory G/L Account.
  9. Consumables
    1. Consumable items have no on hand value (ex: labor charge or rental fee), yet when sold they may affect the inventory GL account. If consumables are not set up properly, when sold, the system treats it as an inventory item: Debit COGS and Credit Inventory
    2. Consumables need to be set up in one of the following ways:
      1. In the proper product lines (file>product>product line>general ledger tab). In the general ledger tab, use the non inventory clearing account in these sections: inventory, cost of goods sold, direct ship COG and inventory change. When sold, the debit and credit transaction will use the non inventory account, netting this transaction to $0 in the general ledger.
      2. General ledger cost (C2 or C3) should be set to $0 along with C1.
  10. Review product line G/L overrides (file> product>product line>general ledger tab)
    1. Settings will override G/L Control Table
      1. Inventory G/L account should only be used in inventory section.
  11. Review un-invoiced PO report
    1. Any old stock receipts that should have been linked?
      1. If so, create $0 AP invoice to remove from report.This does not affect the G/L, it only takes the receipts off the un invoiced PO report.
      2. Remember - the un invoiced PO balance is part of the reconciliation. The balance on this report represents the balance of all un invoiced PO's back to a year. When reviewing for old un invoiced PO's, go back a year.
  12. Review sales journals for large gross profit $ or %
    1. In the end of the month menu, run a sales journal by general ledger cost.
    2. Review the last 2 columns (GP$ and GP %) to determine if any invoices posted gross profits that were exceptionally high or low.
  13. Products with G/L cost at $0 but C1 has a cost
    1. If the G/L cost is $0 (C2 or C3), the system will look for C1 cost to post to the general ledger. This will cause an imbalance because the inventory value will not be affected but the general ledger will decrease.
    2. Run a report in the product master file to determine products with a G/L Cost of $0 but have a C1 Cost. Then change the Sales Order GP% Cost Column as needed.
  14. Vendor rebates should NOT be posted to inventory.
    1. Posting of vendor rebates will only affect the general ledger and not the inventory value.
    2. If vendor rebates are calculated in Inform and posted at the time of the sale to rebates receivable, then the check/credit from the vendor should be posted to the rebates receivable account.
    3. If vendor rebates are not posted as in (a) above, then the check/credit should be posted to an account on the income statement (rebate income, COGS, etc).
  15. Review inventory value report for negative inventory
    1. Run negative inventory value report (inventory value report menu) and determine why there is negative inventory.
    2. Be wary of adjusting to zero without:
      1. Checking corresponding quantities of similar items which may have been substituted.
      2. Determining why the inventory went negative.
  16. Review the returned good warehouse inventory value report
    1. Run the inventory value report for the returned warehouse and tie to the general ledger.
    2. If there are reconciling issues, it means that best practices were not followed when returning goods to the vendor.

Related articles