In Part 1 of this article, I explained why lists are the foundation for setting up and using QuickBooks inventory in a systematic way. We saw that prior to bringing in the Item List, you need to prepare several other lists. I then reviewed the various item types on the Item List, the structure of the Item List, and how to prepare it. In this second article we will tackle getting the Item List into QuickBooks, along with how to add item quantities, for advanced inventory users.
Getting the Item List into QuickBooks
There are several ways to get the Item List into QuickBooks. I’ve detailed some of the methods in a prior article. The old-fashioned way is to manually enter each item. This is done by going to the Item List, under the Lists dropdown, and then selecting New at the bottom. When the screen opens up, you select the type, then enter the other fields.
Probably the easiest way to get items into QuickBooks is through the, Add/Edit Multiple List Entries screen under the Lists dropdown. (You must be in single-user mode to use this screen because of the account assignment). This screen allows you to copy and paste from the spreadsheet into this “list grabber,” which will, in turn, populate the item list. You do have to enter service items, inventory parts, non-inventory parts and assemblies separately, but it is a quick way to get these lists into QuickBooks.
Quickbooks Inventory List Entries screen
The beauty of this method of adding items is that it is quick and easy. The peril of this method is that it is quick and easy. What I mean by that is, just as when copying lists from one worksheet in a spreadsheet program to another, you need to make sure the lists “line up” in this window. If you copy over one column, say the Item Name, then copy over the next column of data, but are off by even one line, then you have pasted incorrect data for the entire table. Once you save it, there is no Undo button in QuickBooks! So be careful!
If you are using the Advanced Inventory module, and have turned on locations and bins, then two additional fields will show up on the Add/Edit Multiple List Entities screen: Default Purchase Bin and Default Sales Bin. These are useful for entering items on Item Receipt and sales transactions. The items will be immediately identified for the “put-away” location and the sales “pull from” location without requiring the user to stop and pick the bins.
In the List Entities window, you will also specify the Subitem Of or parent account for this item. This is where the idea of creating a hierarchy or product category list of the items comes in. If you know ahead of time how you want the list to be structured, then assigning the items to the parent accounts becomes easy.
You can select Inventory Assemblies in the entity dropdown list of the Add/Edit Multiple List Entities screen, and add assemblies, just like you do parts. However, this will only add the “shell” of the assembly (name, UOM, cost, price, accounts). You cannot add the Bill of Materials from this screen.
There are basically only three ways to add the BOMs to assemblies. One way is by manually editing each Inventory Assembly and filling in the components in the Inventory Assembly edit screen:
You can add the parts (inventory and non-parts), labor (service items), and even other sub-assemblies. Enter the quantity of each component that it takes to create one of the finished assembly (depending upon the unit of measure specified for the assembly). Also enter the UOM of that component that will go into the assembly.
Two other methods of importing the BOMs, first through an Intuit Interchange Format (IIF) import and second by using an outside product (Transaction Pro Importer), are detailed in the link to the article on importing data mentioned earlier. The IIF method takes some technical skill and trial and error. The TPI product is easier to use for novices, but costs $199 and requires some understanding before it can be used successfully.
‘Back up the Truck’
By now, you should have your items, vendors, and other lists imported and ready to go in QuickBooks. All of the optional fields for items should also be filled in and complete.
If you’re starting a brand new file, without the Advanced Inventory module available in QuickBooks Enterprise, and you have not purchased any inventory yet, then you are ready to go!
If you’re like a lot of companies, you already have some stock on hand. You may have been tracking your items in a separate spreadsheet, or you may have just started using QuickBooks. Either way, you now need to get the quantities of items you have into QuickBooks. I will discuss first the process of adding quantities into QuickBooks, then talk about the special cases surrounding multiple locations and serial/lot number tracking found only in the QuickBooks Enterprise Advanced Inventory module.
The most direct way to add the quantities on hand (QOH) is by performing an inventory adjustment in QuickBooks using the Adjust Quantity/Value on Hand screen found under either the Inventory, or Vendors => Inventory Activities drop down depending on your version of QuickBooks. This window allows you to adjust the quantity, value, or both quantity and Total Value of the items (Figure 4).
Use the Find & Select Items button to choose which items you want to add. If you don’t already have one, create a COGS account called something like “Inventory Adjustment” to offset the value that will be added to the Inventory Asset account once this transaction is saved. If you’re not sure, seek out a QuickBooks Pro Advisor or CPA to assist with the account selection.
When you save the adjustment, QuickBooks will look at the Cost field you set up for the item to calculate the value of the goods that are going into the Inventory Asset account.
You can also use the third-party program, Transaction Pro Importer, mentioned earlier, to perform an Inventory Adjustment transaction taken from a spreadsheet.
Adding Item Quantities – Advanced Inventory Users
There are usually two further constraints on QuickBooks AI users – dealing with multiple locations and serial/lot numbers associated with adding items. If the inventory sites and bins have been added to the QuickBooks file, then you can refine the quantity adjustments down to the bin level using the same Adjust Quantity/Value on Hand window. This time, you would select each location separately and enter the quantities for each location or bin (Figure 5).
Note, however, that a quantity adjustment is different from changing the lot or serial numbers of items. To adjust the serial numbers of items in stock, select the Serial Number adjustment type in this window. Additional fields will populate, allowing you to add or remove the tracking numbers (see Figure 6). The same is true if you are using lot numbers instead of serial numbers.
You can also import these lot and serial numbers, as well as locations, from a spreadsheet and into the adjustment screen using a third-party product called Lot Mop. This is a small program pulls in the data very quickly without the manual entry.
Ready for Business
Once the lists have all been prepared, and the quantities and tracking numbers of items have been imported, you are ready to start moving goods through your warehouse or shop floor. The next step is to follow inventory workflow processes found on the Sleeter Report and other professional QuickBooks service provider sites.