Discrepancy Between Balance Sheet and Inventory Valuation

Scenario

Inventory Valuation is not equal to the Balance Sheet and/or General Ledger's Inventory Account balance.

Solution

An Inventory Valuation Report total may not equal to a Balance Sheets' Inventory Account balance for to the following reasons:

• Inventory Valuation Report is based on transactions affecting the item and its value.Inventory amount in the balance sheet is driven by GL account balance. Transactions that are not linked to an item such as Journal Entries, Bills, Checks and Deposits directly using the Inventory account may cause the discrepancy. The said transactions are reflected in the Balance Sheet as it hits the GL but it is not reflected in the Inventory Valuation Report.

This has been discussed in detail with SuiteAnswers ID: 12250

Another way to verify such discrepancy is to use a saved search which details the following:

1. Put Balance Sheet amounts against Inventory Valuation amounts
2. Shows transactions which uses the Inventory accounts but does not have any items set against
3. Show transactions that uses the Inventory accounts but which its item type is not an Inventory Item nor an Assembly Item

Here are the steps to create said saved search:

a. Go to Reports> Saved Searches> All Saved Searches> New
b. Choose Transactions
c. On the Criteria tab, set the following:
- Posting = is True
- Account = set your Inventory Accounts to be investigated
** Optional - Date = is on or before date to be compared
d. On the Results tab, set the following:
Field - Summary Type - Formula - Custom Label
-Account | Group
-Amount | Sum |    |     | Balance Sheet
-Formula (Numeric) |  Sum |  case when {item} is Null then {amount} else 0 end  | Txns with No Items (i.e. Journal Entries)
-Formula (Numeric) |  Sum |  case when {item.type} IN ('Inventory Item', 'Assembly') then {amount} else 0 end | Recognized in Inventory Valuation/Detail
-Formula (Numeric) |  Sum |  case when {item} is Not Null and {item.type} Not IN ('Inventory Item', 'Assembly') then {amount} else 0 end | Txns w/ Invty Acct but itemtype is not Invty Item/Assemly Item
-Document Number
-Type
-Date
e. Name Saved search and click 'Save and Run'.

Double click on the Account name to drill down to the transaction details.

Alternatively, use the following formulas, if the Item Type of the Assemblies is 'Assembly/Bill of Materials' and not just 'Assembly'
-case when {item.type} IN ('Inventory Item', 'Assembly/Bill of Materials') then {amount} else 0 end
-case when {item} is Not Null and {item.type} Not IN ('Inventory Item', 'Assembly/Bill of Materials') then {amount} else 0 end

It is also possible that there are Inventory Transactions that did not post on the Inventory Asset Account in the Balance Sheet. 

This happens when the asset account set on the Item Record are not an Inventory Asset or Other Current Assets. In addition to that, Non-Inventory Items that have Inventory Asset account set on their record for Expense Account and/or Income Account would also cause discrepancy.

To look for this, simply create an Item Search and expose the Accounts set on the Item Record.
a. Go to Reports> Saved Searches> All Saved Searches> New
b. Choose Items
c. On the Result tab, set the following:
-Asset Account
-Income Account
-Expense Account
d. Save and Run