It has amazed me over the years how “creative” clients can get using QuickBooks inventory. Be it for urgency, ignorance, or out of frustration, clients will take shortcuts or unknowingly perform an action that ultimately undermines what they’re trying to accomplish. To help users avoid such problems, in this article, I’ll explain nine things you should NEVER do with QuickBooks inventory.
1) Select Inventory Asset from the Adjustment Account drop-down when performing an Adjust Quantity/Value On Hand transaction
QuickBooks will force the user to select an offset account when adjusting inventory using this window under the Vendors drop-down or Vendor Center. What the new user often doesn’t realize is that QuickBooks is saying, “If you want to adjust the quantities of your items up or down, the offset to the Inventory Asset account when we do this should be [fill in the blank].”
Figure 1: Incorrectly posting an inventory adjustment directly to the Inventory Asset account.
I’ve seen a lot of clients assume that QuickBooks wants the Inventory Asset account in this field. Even though QuickBooks will throw up a warning message about the account needing to be an income or expense account, clients will ignore it and leave it as an Other Current Asset–type of Inventory Asset account. This results in an adjustment of the quantity on the Inventory Valuation Summary report, but the entry has no effect on the Balance Sheet. Since one “side” of the entry using the Adjust Quantity/Value on Hand is already the Inventory Asset account, you need to select (most likely) an expense or Cost of Goods Sold account to reflect the change in value on the Profit & Loss report. Otherwise, you’re just “circling back” on yourself with this entry.
2) Merge items
One of the things I most enjoyed about QuickBooks when I began to learn it was how easy it was to merge lists, specifically customer and vendor lists. In the “old days” of accounting programs, when you had a duplicate name on a list, you had to manually back out each transaction from the duplicate and reenter the transaction in the correct vendor or customer list. QuickBooks made this correction easy by allowing the user to simply merge the names, and all the associated transactions transfer with them.
A lot of users will apply this same logic to merging items. What harm can there be if you simply rename an item and allow QuickBooks to merge the transactions? Well, potentially, a lot of harm can come of it.
As I’ll explain later with regard to “selling negative” as one of the nine things you don’t want to do with QuickBooks inventory, the order of transaction dates is extremely important to how QuickBooks posts costs. When you merge items, you can get unexpected results because of the dates of the transactions involved. For example, previously completed Build Assembly transactions may revert to “pending” status, quantities that were positive before may become negative, or links to purchase orders may be lost. In the worst case (and I’ve seen this), data corruption can occur as a result of QuickBooks trying to “line up” all the transactions in date order.
As an alternative, select the item you want to “retire” and perform an inventory adjustment to bring the quantity to zero and inactivate the old item. If you’re not concerned about changing prior-period financials, you can also try changing the individual documents to show the correct item. Once all of the documents have been changed, you can then inactivate or delete the old item.
3) Inactivate items that still have a quantity – negative or positive – on hand
You may choose to discontinue having items in your inventory that you no longer sell. That’s fine, but make sure you perform an Adjust Quantity/Value on Hand adjustment under the Inventory drop-down to zero out any quantity you may have remaining. This is also necessary for any items you know are out of stock but perhaps still have a negative quantity left on hand. All items have a value based on the quantity multiplied by the average cost. If you inactivate an item that still has a quantity, its associated value will drop off the Inventory Valuation Summary, but still will be reflected in the sum of the Inventory Asset account on the Balance Sheet. It’s best to zero out the quantities of items BEFORE you inactivate them.
4) Attempt to “split items” to allocate to multiple locations
This has been around since before QuickBooks added the Advanced Inventory module, and even now, people still try to do it. Let’s say a client has two warehouses – warehouse A and warehouse B. The client gets a widget in and wants to allocate it to both warehouses so the quantity on hand can be tracked. In order to do that, the client creates a widget A and a widget B. The problem begins with the Item Receipt. If the item is received at one location, does the receiving person “split”’ the receipt into the two items, and, if so, how does the person know how many to send to the alternate location? If the item is delivered to both locations, then each receiving person needs to know which widget he or she is receiving against. On the sales side, the person entering the sales receipt or invoice needs to know which location the item is being pulled from in order to enter the correct item on the sales document. Additionally, transfers will require another inventory adjustment, increasing one widget and decreasing the other one. Do this with hundreds of items, and you can see what a mess this could create.
Figure 2: “Splitting” items to assign to multiple warehouses never works.
Trying to split items up to assign them to different locations just doesn’t work in QuickBooks. I’ve probably seen every possible attempt to make it work, and it always fails or becomes incredibly tedious to maintain. Do NOT attempt this! If your company is using QuickBooks Enterprise, get the Advanced Inventory module that adequately addresses this issue.
5) Create one general ledger (GL) account for each item
I think one of the genius ideas of QuickBooks since the beginning has been the use of items for purchase and sales documents. This makes it easy for both management and the end users to ring up transactions with familiar-sounding names and let the accounting happen in the background. However, some users think all their items must appear on the Profit & Loss statement, and, therefore, they must have a separate general ledger account that’s then “connected” to each item. This usually results in a lengthy and cumbersome Chart of Accounts that doesn’t truly give management the kind of information it needs.
Instead, pick general-sounding names for “buckets” of items in the Income, Cost of Goods Sold, and even the Inventory Asset subaccounts, then post many of the items to these accounts. This helps the end user categorize and duplicate items easily, and it summarizes the item activity on the financial statement. If clients want detailed sales or purchasing reports, they can run these reports instead:
- Reports => Sales => Sales by Item Detail
- Reports => Jobs, Time & Mileage => Item Profitability
- Reports => Purchases => Purchases by Item Detail
6) Have an “empty” or one-item Bill of Materials
Clients will sometimes create an assembly with the intent to “build out’’ the components at a later time, so they leave the Bill of Materials (BOM) empty. Other times, they may be unsure whether to make an item an Inventory Part or an Inventory Assembly, so they create an assembly with just one component in the BOM. We frequently see situations where assemblies were created, but there are no components listed in the BOM or there’s only one item. Assemblies always have to be built (or the quantity adjusted in the adjustment window) in order to be put on sales documents. In the former case, there’s nothing to build, so no cost is carried over. If the assembly only has one item, then make it an Inventory Part so the item can just be received against, and it doesn’t have to be built to put it on a sales document.
Figure 3: Having a one-component BOM is the same as having an Inventory Part. BOMs with no components have no cost.
7) Post a purchase directly to the Inventory Asset account
An inexperienced QuickBooks user will sometimes not use the Items tab on a bill, check, or credit card transaction to list the items received or about to be paid for, but will instead use the Expenses tab to post directly to the Inventory Asset account on the Balance Sheet. This increases the amount on the Balance Sheet, but doesn’t impact the detail of the inventory listed on the Inventory Valuation Summary report.
Figure 4: Incorrectly posting directly to the Balance Sheet account on a bill or check.
One way to find these is to do a QuickReport on the Inventory Asset account filtered for the transaction type. Add Items to the display, and when running the report, look for any transactions that are missing items.
8) Post a journal entry to the Inventory Asset account
Similar to posting a bill, check, or credit card directly to the Inventory Asset account, a common mistake is using this account when posting a journal entry. Adding line elements using this account will increase or decrease the Balance Sheet account, but since journal entries don’t include item list elements, it won’t have any impact on the Inventory Valuation Summary report. (Note: An exception to this rule may be at year-end when an adjustment needs to be made to account for a cash-basis Balance Sheet, but even then, the journal entry should be reversed on the first day of the next period.)
Figure 5: Incorrectly posting directly to Inventory Asset within a journal entry.
9) Sell “negative” on-hand items
This is probably the most important one, and yet the one mistake most clients make. This refers to actions that cause the Quantity on Hand to become a negative value. It may be the result of a drop shipment where the client is invoiced right away, but the bill from the vendor is added at a later date. More commonly, it’s the result of clients entering invoices or sales receipts without the corresponding item receipts having been entered. QuickBooks needs the average cost of the item on file at the time of sale to match expense to revenue. It doesn’t look at later transactions to compute the cost if the items are posted after the sale. This causes a distortion in the timing of the Cost of Goods Sold, which can in turn impact the overall Profit & Loss report, as well as the Profit & Loss by Job report and the Profit & Loss by Class report.
Figure 6: Warning that appears when attempting to sell negative quantities on an invoice.
Negative quantities also affect the status of Build Assembly transactions. If a component of an item is received in QuickBooks after the item is actually built, it will revert to pending status because the components were not in stock at the time of the build.
Fortunately, QuickBooks does “heal” itself if you later redate the transactions to the prior order (assuming you can still post to closed periods). The easiest way to avoid these problems is to simply instruct users on the timing of performing their tasks, then enforce and monitor how the transactions are being put in.
Your primary guide to making sure the client’s inventory in QuickBooks is correct is by matching the Inventory Asset balances on the Balance Sheet with the Inventory Valuation Summary. By avoiding these nine don’ts, you’ll have a better chance of keeping inventory in balance.
This article was updated on 4/3/2014