In many ways, Intuit makes QuickBooks so simple and easy to use: enter vendor bills, complete customer invoices, write checks, etcetera, that people rush in to use the product. While that might be fine for some transactions, incorporating inventory into your data file is more like building a house: it takes planning and more of a step-by-step approach to implementation.
Why is the inventory module so much more involved and difficult than, say, reconciling the bank account? I can think of two reasons.
One, inventory affects ALL functional areas of the business, from sales to purchasing to accounting to production to shipping. How someone handles a transaction in their department will most likely result in what someone else does in another area. We call that the workflow process.
Second, more lists come into play as inventory (and manufacturing) move through the transactional pipeline, thus affecting the database. Transactions for items will draw upon vendor and customer information. Items themselves may have anywhere from 10-40 attributes (average cost, price, unit of measure, etc.). The items will go through several transactional stages. In acquisition, for example, items will go from Purchase Orders to Item Receipts to vendor Bills. On the sales side, they go from Estimate => Sales Order => Pick Ticket => Packing List => Invoice. Each one of these pulls in more lists (i.e., customer message), adding to the connectivity of the transactions.
Many new users to the inventory module in QuickBooks rush in just as they would with simple data entry, and only recognize later the problems they have caused. Here is a more systematic approach to setting up inventory for the newcomer.
Lists Are the Foundation
Before even thinking about bringing in the Item List, there are other lists you have to have prepared: the Chart of Accounts, the vendor list, the Units of Measure, and, if using the Advanced Inventory module, the locations and tracking lists. The beauty of QuickBooks is that items are tied to the financial accounts, freeing the end user from the knots of accounting. So, you start with the accounts first, and then connect the items to them in turn.
There are three accounts on the Chart of Accounts (COA) that many inventory items will tie to:
An Inventory Asset account on the Balance Sheet,
An Income account on the Profit & Loss Statement (a.k.a., Income Statement), and
A Cost of Goods Sold (COGS) account, also on the Profit & Loss Statement.
Choosing which of these three accounts to attach to your items is critical for getting effective reporting and accurate financial results.
You turn on the inventory functionality in QuickBooks by the Administrator going to Edit => Preferences => Items & Inventory => Company Preferences tab and clicking on Inventory and purchase orders are active (Figure 1).
When this preference is activated, QuickBooks automatically creates the Inventory Asset account as an Other Current Asset type of account on the Balance Sheet. Note that the type is grayed out (Figure 2), meaning it is a default account, and the type cannot be changed.
Sometimes, additional Inventory Asset accounts might be created depending on how the company needs to track the value of their inventory. For most wholesalers and distributors, one default account is fine (see Figure 3). For a manufacturer, who needs to track Work-in-Progress, multiple accounts may be needed (Figure 4: default account renamed to Materials Inventory).
The Inventory Valuation Summary (IVS) report, which lists all of the inventory part and assembly items in stock, serves as the detail report for the balance(s) in the Inventory Asset account(s). The total of the IVS report and the Inventory Asset accounts serves as a checks and balances that items are being recorded properly on transactions, and should always match.
Next are the Sales or Income accounts. New users frequently make the mistake of creating too many income accounts, sometimes for every product they sell! It is better to create buckets of sales accounts or groups, depending upon your business. Usually just two or three accounts will suffice for product lines. For tracking profitability, see the Item Profitability report under Reports => Jobs, Time & Mileage. Realize that items only tie or connect to one account, so if you sell to multiple channels (i.e., wholesalers and retailers), and you want to track the sales separately, consider using Classes in QuickBooks (see Help in the product on this functionality).
The third account on the COA to set up properly is the COGS account. Again, when you turn on the inventory function in QuickBooks, a default COGS account is created automatically. Like the Sales accounts, you may have reason to create more COGS accounts, such as Freight, but ask yourself why you need the additional accounts? Only do it if you have additional reporting requirements, and remember that each item will only connect to one of these accounts.
The Units of Measure (UOM) list gets more users in trouble than most other lists. This is where users decide what package they are going to receive, stock, or sell their goods in (called Multiple Units of Measure, versus a single unit that is purchased and sold in the same unit). This function is also turned on under Preferences => Items & Inventory. There are several issues to consider regarding setting up the UOM:
The base unit of an item is usually the smallest unit received, stocked, or sold. Purchases and sales of the product are usually multiples of the base unit, eliminating the need for fractions of another unit (i.e., case).
QuickBooks will only display the base unit on Inventory Stock Status report. QuickBooks cannot track multiple units in stock (i.e., a case and by each) on quantity on hand (QOH) reports.
You can set up cross-type units of measure, such as weight and volume, as long as the quantities of these units do not change depending upon the product involved.
The other imperative list to consider before importing items is the vendor list. Most companies already have this compiled and imported into their QuickBooks file. Having the ability to auto-create purchase orders later saves time, but this requires a default preferred vendor for the product. By including an up-to-date vendor list, you save the time of specifying the vendor later.
If you or the client (if you are a bookkeeper) are using QuickBooks Enterprise with the Advanced Inventory function turned on, then two other lists need to be prepared: locations and serial or lot number tracking lists. The locations, or sites, can be prepared ahead of time and added from the Lists dropdown. This is a two-level list where the parent is the Site and underneath the site are bin locations, which can be rows, shelfs, aisles, etc. I will discuss the serial and lot number lists later regarding importing inventory quantities.
The Item List – Types of Items
Once you’ve completed the ancillary lists, you are ready to tackle the item list. Let’s briefly go over the various item types:
Service – Typically used for labor purchased from a vendor or included on an invoice to a customer. Usually only requires an income account if just used for customers.
Inventory part – Usually raw material or items for resale where the user wants to track the quantity in stock. The value of these items is stored in the Inventory Asset account until sold on an invoice, when the cost is recognized.
Inventory assemblies – A good that is the result of an assembly of components or from a manufacturing process (raw materials). Assemblies include a Bill of Materials (BOM), which is the “recipe” for the finished good. A Build Assembly transaction must take place to remove the components and build the final good which is stocked. In a manufacturing environment, the value of the goods may flow on the Balance Sheet from the materials account, to WIP, to the Finished Goods account. The value of these items is stored in the Inventory Asset account until sold on an invoice, when the cost is recognized.
Non-inventory parts – These are usually items that can be expensed at the time purchase, and where the manager doesn’t necessarily track the quantity at any given time. I like to use the example of a bucket of bolts. You might care about how many buckets of bolts you have, but not about how many individual bolts you have. This item can be included on purchase and sales documents.
Other Charge – These are not really, inventory items but might be related to inventory, like freight or handling charges. You cannot specify a unit of measure with an “other charge” item.
Group items – These are a basket of components that are sold as one item. Only at the time of sale does QuickBooks reduce the quantity of the components. The group item is not kept in stock, thus no quantities of the group items are tracked.
The other item types, sales tax, discounts, and payments do not apply directly to inventory.
You must first decide which inventory type your items fall into. Inventory Parts and Assemblies require all three of the accounts mentioned earlier. Service, non-inventory, and Other Charge items may use a purchasing account, a sales account, or both – depending upon whether the item is bought, sold, or both. Group items do not require accounts since the posting is a result of the accounts tied to the components pulled.
Structure of the Item List
To make easier both finding items on transactions and reporting on item categories, we strongly encourage users to set up a hierarchy of their item list. We also encourage you to use names, wherever possible, instead of just numbers. This helps you compartmentalize your thousands of items into groups that people recognize. You might do it by product category or vendor – it’s up to you. QuickBooks allows up to five levels of categories of items. In this case, the first level is usually the primary Parent account (or header), and the last level is the item itself (see Figure 5 for an example of a two-level structure).
By categorizing the items, you make it easier on the QuickBooks user who is trying to find items. When he or she selects an item for a purchase order, bill, sales order, or invoice, they need only type the first few characters of the name to find it quickly. In the example of Figure 5, the user could type an “H” for the parent account, or a “B” to quickly find the item, Brass hinges.
Preparing the Item List
Start with a simple spreadsheet to begin building a database table of the items. There are up to 40 additional fields, including custom fields that can be associated with an item. However, only a few are required. Here is a list of the most common fields:
Item name/number – (Required). This can be an alphanumeric (numbers, letters, most special characters) label with up to 31 characters. We prefer names because they are easier to remember, but use numbers when necessary. Try NOT to use the manufacturer’s part number as your number, as you may later add additional suppliers of that part.
Item Description – (Not required, but highly recommended). This is the internal database description of the object, and also serves as the sales description. The limit is 4,095 characters. You can include another field in the database for Purchase Description if it is different.
Item Type – (Required.) You are limited to: Inventory Part, Inventory Assembly, Non-Inventory Part, and Other Charge items for the naming conventions. For assemblies, once you have the name or “shell” of the assembly in, you will add the BOM to it at a later time.
Preferred Vendor – This is where having your vendor list already in the QuickBooks file comes in handy. List the vendor you are most likely to create a purchase order to. This will come in handy when creating auto-POs.
Manufacturer’s Part Number (MPN) – If you purchase this item from just one vendor, and that supplier has a part number that you want to include on purchase orders, include the number here.
Cost – This is a very important field for two reasons: one, this is the default cost that will typically show up on the purchase order when adding the item. It can be changed on the form, but it’s convenient to have it pre-filled. More importantly, when you later import the quantity of items you are adding, QuickBooks must have a cost in place to calculate the value of the inventory on hand (cost x quantity = value). If the field is left blank, or with a zero amount, then the value of the items on hand will be zero.
Price – This is only required for items you are going to sell. It’s not required for raw materials. This amount will be pre-filled on sales documents as you enter the items.
Unit of Measure – This is the base unit, usually the stocking unit. This is the typical unit you will either keep on the “shelf,” or build in an assembly.
Reorder Point – Although this is not required, we highly recommend it to our clients so they don’t have to go back to add it later. This number is the quantity of an item that once it is reached, you want QuickBooks to alert you that it is time to place a PO to reorder that item.
Asset Account – Balance Sheet account to track inventory value as items are bought and sold.
Cost-of-Goods Sold (COGS) Account – Designated account for tracking the cost associated with the item.
Income Account – Simply listed as “Account” in the Item Edit screen and on reports, this is the revenue account that will be posted to when the finished good is sold.
Custom Fields – You can have up to 14 custom fields related to the item.
Notes about item setup:
1: QuickBooks requires an Income account for all item setups, except Groups, even though the part may be something only purchased or used in assemblies. In this case, specify an income account on the item edit screen, even though you will probably never use it.
2: By default, new Service, Non-Inventory Part, and Other Charge items will display only the sales side of the new item. When the edit screen shows the Account field, it means the Sales account, not the purchase account. You must check the box, This item [or service] is used… to open the cost side of the transaction.
From this screen (see Figure 6), you can also add the group or category the item falls under, the manufacturer’s part number (if there is only one), and a base unit of measure set.
In Part II of this article, we’ll talk about how to enter the list data and update the quantities.