Inventory, like payroll, is consistently one of the most problematic areas for clients. A rigid set of procedures, with everyone inputting data consistently and in a timely manner is crucial. This often opens an opportunity for training.
The fundamentals of inventory tracking should be reviewed initially. Here are three considerations:
- Evaluate whether the Inventory items in QuickBooks Desktop are set up properly. Clients often set up items with the item type of Inventory Part, when they would be better served using the Non-inventory Part item type. Non-inventory items do not keep a perpetual count or an average cost.
- Evaluate whether the total inventory value on the Inventory Valuation Summary report agrees to the inventory amount reported on the Balance Sheet for the same date. Disagreement between these two reports can be caused by several factors. The Client Data Review (CDR) tool, available to your clients’ External Accountant users, as well as those of you using QuickBooks Accountant Desktop and QuickBooks Enterprise (all editions), can assist with this problem.
- Review the Inventory Valuation Summary for negative quantities and inaccurate average costs.
For the purposes of this article, all future mentions of QuickBooks will refer to QuickBooks Desktop.
Common error: Incorrect and/or inconsistent inventory accounting
Symptoms
- Inventory Valuation Summary report shows negative quantities
- Inventory Valuation Summary does not agree to General Ledger
- Average cost and/or quantity on hand appear incorrect
CDR tool – Compare balance sheet and inventory valuation
The method used in QuickBooks for inventory valuation is weighted average costing (the exception is QuickBooks Enterprise, which, if Advanced Inventory is used, can be set for FIFO costing). An Inventory Valuation Summary Report summarizes the quantity, average cost, and extended value of each of the inventory quantities and provides a total inventory valuation. The total asset value on this report should match with the Balance Sheet value for Inventory; just ensure these two reports were created with the same time/date settings if other users are entering data in QuickBooks at the same time.
If journal entries or deposits (or other transactions using the Expenses tab rather than the Items tab) have been posted directly to the inventory account, the Inventory Valuation Summary will not agree with the Inventory Asset balance on your Balance Sheet. Unfortunately, there is no warning to stop or at least slow down someone entering transactions directly to the inventory account when they should be using inventory items and quantities.
Additionally, inactive inventory items with Quantity On Hand (and a corresponding asset value) will also cause a discrepancy between the Inventory Valuation Summary report and the totals for the inventory asset account on the Balance Sheet or Trial Balance. To correct this problem, re-activate the inventory item, create an inventory adjustment (as discussed below) to zero out that item’s quantity, and then inactivate the item again.
The Compare Balance Sheet and Inventory Valuation tool within CDR determines whether the inventory account on the Balance Sheet and the Inventory Valuation Summary match. The tool compares the inventory general ledger account with Inventory Valuation Summary and displays the results.
A green circle indicates that the inventory account in the general ledger agrees with the Inventory Valuation Summary.
A yellow warning triangle alerts that a discrepancy between the inventory general ledger account and the Inventory Valuation Summary exists. The major cause of this type of discrepancy is non-inventory accounts affecting inventory transactions due to the incorrect setup of accounts for inventory items.
The default date for the comparison is the last day of the review period specified in the CDR. The date in the As of field can be changed and then Refreshed to narrow down the date when the discrepancy occurred.