Why Doesn't QuickBooks Desktop Inventory Valuation Match the Balance Sheet or the Warehouse Count?

Before you start

• Back up the company file and keep your earlier backups. Some fixes change dates and accounts on old transactions.
• Talk to your accountant before you edit past bills, invoices, journal entries or adjustments. Intuit asks you to confirm each change is legitimate.
• Be clear which three numbers you're comparing. They are the Balance Sheet's Inventory Asset balance, the inventory valuation report total, and what's on your shelves.
• Go to Window > Close All before you open the reports, so you start with a clean screen.

Why the numbers don't match

The Balance Sheet totals accounts, while the inventory valuation reports total items. A transaction that hits Inventory Asset with no inventory item shows up on the Balance Sheet only. Inactive items with value do the same, because Inventory Valuation Summary leaves inactive items out.

Step 1: Compare the Balance Sheet with Inventory Valuation Detail

1. 1Go to Window > Close All.
2. 2Open Reports > Inventory > Inventory Valuation Detail and set Dates to All.
3. 3Open Reports > Company & Financial > Balance Sheet Standard. Select Customize Report, set the dates to All and the basis to Accrual, then select OK.
4. 4Double-click the Inventory Asset amount. QuickBooks opens the transaction detail for that account.
5. 5In Total By, choose Item Detail. Then close the Balance Sheet.
6. 6Go to Window > Tile Vertically to put the two reports side by side.
7. 7Scroll to the No Item section at the bottom of the account detail. Those transactions are on the Balance Sheet but not in the valuation reports.

The two reports should otherwise match. If the difference isn't in No Item, compare them line by line until you find it.

Step 2: Fix transactions posted to Inventory Asset without an item

Bills, checks and credit card charges

1. 1Go to Reports > Custom Reports > Summary.
2. 2On the Display tab, set Dates to All and Display rows by to Item Type. Under Advanced, choose Non-zero rows.
3. 3On the Filters tab, set Account to Inventory Asset, then select OK.
4. 4If a No Item row appears, double-click its amount to open the transactions behind it.
5. 5Edit each one so the inventory line sits on the Items tab with the right item, not on the Expenses tab.

Journal entries and inventory adjustments

1. 1Go to Reports > Accountant & Taxes > Transaction Detail by Account. Select Customize Report and set Dates to All.
2. 2On the Filters tab, set Account to Inventory Asset and Transaction Type to Journal.
3. 3Intuit's fix is to delete those journal entries and enter each again as an inventory adjustment. Use Vendors > Inventory Activities > Adjust Quantity/Value on Hand, and agree on it with your accountant first.
4. 4Run the report again with Transaction Type set to Inventory Adjustment.
5. 5Open any adjustment that uses Inventory Asset as its adjustment account, and choose a different adjustment account.
6. 6Check for adjustments dated in the future. Extend the end date to include them, then rerun the inventory valuation reports.

Intuit suggests a separate Cost of Goods Sold account for adjustments. We set one up in How to adjust inventory quantity and value.

Step 3: Clear value from inactive items

1. 1Go to Lists > Item List and select Include inactive. Inactive items have an X in the first column.
2. 2Look for inactive items with a number in On Hand.
3. 3Decide with your accountant whether each one still exists on the shelf.

• If the stock is real, select the X to make the item active again. The valuation reports then include it.
• If it isn't, open Adjust Quantity/Value on Hand, enter 0 as the new quantity or new value, choose your adjustment account and save.

Step 4: Find and fix negative inventory

Selling an item before its purchase is in QuickBooks pushes quantity below zero. QuickBooks then costs the sale at the average cost of the stock you had, or at the Cost on the item. With neither, it uses $0. When the bill arrives at a different cost, the bill posts a correction to inventory and cost of goods sold. That's why cost of goods sold can appear on bills and vendor reports.

1. 1Go to Reports > Inventory > Inventory Valuation Detail and set Dates to All. Intuit calls this the only report that shows the full extent of negative inventory.
2. 2Look for negative numbers in the On Hand column.
3. 3If an item's first transaction is a sale, open its first bill and date it before the first invoice, if that date is legitimate.
4. 4If purchases went on the Expenses tab, edit the bills so those lines use the inventory item. Check with your accountant first, because this can change your inventory expenses.
5. 5Repeat for every item with a negative balance.

Adjusting today's quantity back to positive isn't enough. Intuit says each time stock went below zero has to be corrected. In Enterprise 15.0 and later, Reports > Inventory > Negative Item Listing shows what's negative today, but not past negatives. If negative inventory is widespread and hard to repair, Intuit says starting a new company file may be the better option.

Stop it from happening again

• Enter an opening quantity and value on every new item. If you have none on hand, enter a purchase before the first sale.
• When a customer orders stock you don't have yet, enter a sales order instead of an invoice. Convert it once the purchase is in QuickBooks.
• Or enter the invoice and choose Edit > Mark Invoice As Pending. After the purchase is entered, choose Edit > Mark Invoice As Final and set the invoice date to the ship date.
• In Enterprise, select Don't allow negative inventory quantities in Edit > Preferences > Items & Inventory > Company Preferences.
• Build assemblies before you invoice them. A late build carries the cost correction, and a build can't take a customer, job or class, so job and class reports miss it.

For the order side, see How to convert a sales order to an invoice and How to handle backorders and partial shipments.

Step 5: Match QuickBooks to the warehouse count

1. 1Finish Steps 1 to 4 first, so you count against corrected history.
2. 2Count your stock. In Enterprise, Reports > Inventory > Physical Inventory Worksheet gives you a count sheet.
3. 3Go to Vendors > Inventory Activities > Adjust Quantity/Value on Hand and choose Quantity as the Adjustment Type.
4. 4Date the adjustment on the count date, choose your adjustment account, and enter each counted number as the new quantity.
5. 5Check the quantity difference on each line, then select Save & Close.
6. 6Run Inventory Valuation Summary and Inventory Stock Status by Item to confirm the new numbers.

In Enterprise with Advanced Inventory, you can count part of the warehouse at a time. See How to run cycle counts in Advanced Inventory.

For accountants: Client Data Review

In QuickBooks Accountant and Enterprise, Client Data Review has a Troubleshoot Inventory tool. It can list items that went negative at any point in a date range, and inactive items that still show quantity on hand. Double-click a quantity to open that item's Inventory Valuation Detail.

Where QuickBooks Desktop leaves the work to you

• Someone has to compare the reports and trace each No Item line back to the transaction behind it.
• Changing dates or accounts on old transactions needs your accountant's sign-off.
• Every past occurrence of negative stock has to be fixed, not just today's balance.
• Sales entered before purchases keep creating new problems until the order process changes.
• An adjustment records the count difference, not its cause.

How Logistify helps

Logistify doesn't count stock or correct inventory value. One of Intuit's ways to avoid negative inventory is to record orders for out-of-stock items as sales orders, not invoices. The Sales Order Service enters orders that arrive by email, PDF, WhatsApp, SMS, voicemail or paper into QuickBooks Desktop as sales orders. A person reviews the exceptions. The Procurement Service follows up with suppliers on open purchase orders.