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 usually take shortcuts or unknowingly perform a process that ultimately undermines what they are trying to accomplish. To educate the readers here, and ultimately, the end-users, I’m including here nine things you should NEVER do with QuickBooks inventory.
Enter ‘Inventory Asset’ into the adjustment field on the ‘Adjust Quantity/Value On Hand’ transaction
QuickBooks will force the user to select an offset account when adjusting inventory using this window under the vendor dropdown or Vendor Center. What the new user doesn’t realize, usually, is that QuickBooks is asking, ‘OK, you want to adjust the quantities of your items up or down, and the offset to the Inventory Asset account when we do this should be [fill in the blank]’.
Incorrectly posting an inventory adjustment directly to the Inventory Asset account.
What I’ve seen a lot of clients do is that they will assume that QuickBooks wants the inventory asset account listed 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 Liability-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 Statement. Otherwise, you are just, ‘circling back’ on yourself with this entry.
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 the 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 re-enter 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 of the associated transactions would ‘transfer’ with them.
A lot of users will apply this same logic to merging items. What harm can there be if I simply re-name an item and allow QuickBooks to merge the transactions? Well, potentially, a lot of harm can come of it, actually.
As I’ll explain later with regard to, ‘selling negative’ as one of the nine things you don’t want to do at the end of this article, the order of transaction dates is very important in how QuickBooks posts costs. When you merge items, unexpected results can happen because of the dates of the transactions involved. Previously completed Build Assembly transactions may revert to ‘pending’ status. Quantities that were positive before may now become negative. Links to purchase orders may be lost. In the worst case, and we have seen this, data corruption can occur as a result of QuickBooks trying to ‘line up’ all of 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 are 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.
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 you have run out of stock for, but perhaps still have a negative quantity left on hand. All items have a value based upon the quantity times the average cost. If you inactivate an item that still has a quantity, its associated value will drop off of the Inventory Valuation Summary, but will still 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.
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. Say a client has two warehouses – warehouse A and warehouse B. They get a widget in, but want to allocate it to both warehouses so they can track the quantity on hand, so they create a ‘Widget A,’ and ‘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 do they 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 they are 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 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 causes.
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 seen probably every possible attempt at it, and they always fail or become incredibly tedious to maintain. Do not attempt this! If your company is using QB Enterprise, get the Advanced Inventory module which does adequately address this issue.
Create one 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 that all of their items must appear on the Profit & Loss statement, and thus must have a separate general ledger account which is then ‘connected’ to each item. This usually results in a very lengthy and cumbersome Chart of Accounts that doesn’t truly give management the kind of information they need.
Instead, pick general-sounding names for ‘buckets’ of items in the income, cost-of-goods-sold and even the Inventory Asset sub-accounts, then post many of the items to these accounts. This helps the end user categorize and duplicate items easily, and summarizes the item activity on the financial statement. If the client wants 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
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 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 still frequently see these situations were assemblies were created, but there are no components listed in the Bill of Materials, or there is only one item. Assemblies always have to be ‘built’ (or quantity adjusted in the adjustment window) in order to be put on sales documents. In the former case, there is 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.
Post a purchase directly to the Inventory Asset account
An inexperienced QuickBooks user will sometimes not use the Item 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 Expense tab to post directly to the Inventory Asset account on the Balance Sheet. This increases the account on the Balance Sheet, but does not impact the detail of the inventory listed on the Inventory Valuation Summary.
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.
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 do not include item list elements, it will not 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 JE should be reversed on the first day of the next period.)
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 is 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 the goods sold, which can in turn impact the overall Profit and Loss report, as well as the Profit & Loss by Job and by Class reporting.
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 re-date 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 the users on the timing of their tasks, then enforce and monitor how the transactions are being put in.
Conclusion
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 ‘don’ts,’ you’ll have a better chance at keeping the inventory in balance.
