5 Tips for Keeping Inventory Accurate in Real-Time

How to make ecommerce overselling a thing of the past and peace of mind your SOP.

When used correctly, QuickBooks can be a very powerful tool for tracking inventory. But the key to using QuickBooks to track inventory successfully is in appreciating how and why QuickBooks tracks all inventory activity at the item/product level.

1. Proper Item/Product Setup: It may be tempting to start recording your sales in QuickBooks and watch the revenue grow, but to get the most reliable financial reports, you’ll need to put in some time and effort up front. Here’s how to think through your inventory setup and list organization:READ: 5 Tips for Keeping #Ecommerce #Inventory Accurate in Real-Time. #Webgility Click To Tweet

• Do you want to use the SKUs from your website or other sales channel for your item/product names? If so, you can populate the “item name” with this info or choose to populate another data field with the SKU. Webgility mapping features help to streamline the matching of different SKUs between all sales channels.
• Decide how to organize your list. QuickBooks Online allows for product categories, but QuickBooks Desktop does not have the category feature. However, you can organize your QuickBooks Desktop lists by using the field: sub-item of.
• Consider adding a few income accounts within your chart of accounts for a clearer view of your income on the P&L. Once these accounts are set up, you can choose to which account each item/product’s revenue maps from the dropdown menu in your item/product screen. Also, if you are in QuickBooks Desktop, you can change your mappings in bulk: Lists > Add/Edit Multiple List Entries
• The cost field in the item/product setup is mostly just used to populate the default value on your purchase transactions (bills, purchase orders). This cost field is not directly used to calculate your inventory value—more on that below.

2. Accurate Starting Counts: Ideally, you want to get started with a physical inventory and accurate item/product counts. But realistically, it can be a challenge to get all of your inventory counted at once. If this is too overwhelming, start with a large even number, like 100 or 1,000 units for the inventory count of each item/product, and then adjust as accurate counts are done using a quantity adjustment. When recording the adjustment, Just be sure to use the date of the actual count.

3. Proper Inventory Workflow: Now that you have inventory set up, you need the proper workflow to maintain accurate counts and costs. The following transactional documents can add inventory quantities to QuickBooks:

• Bills (QuickBooks Desktop and QuickBooks Online)
• Credit Card charge (QuickBooks Desktop and QuickBooks Online)
• Item Receipt (QuickBooks Desktop only)
• Expense (QuickBooks Online only)

There are two critical data fields on these transactions:

• Date: When the items/products will be added to your on-hand quantities
• Cost: per item/product cost; it’s what feeds the calculation of your inventory asset value and upon sale, the cost of goods sold. This may be the same default cost from your item/product setup or it may be different due to vendor pricing, etc.

A purchase order (PO) acts like an FYI in QuickBooks, telling you that inventory is on its way. For tracking and planning purposes, a PO is the optimal place to start the inventory workflow. However, POs are not required in QuickBooks and POs do not have any financial impact. If you want, skip the PO and start with bill, or any of the transactions referenced above.

One powerful feature offered by Webgility is the auto-creation of POs based on rules at the item level. This feature is frequently used to generate POs for dropship orders or for items that are out-of-stock—yet another reason to keep accurate counts.   

4. Do Not Oversell: Once you have accurate on-hand quantities in QuickBooks, work to keep it that way! Try to avoid negative inventory counts which can lead to:

• Unforeseen consequences to the “cost of goods sold” amounts
• Unreliable quantities for order fulfillment
• Data corruption

If you do run into problems, two-way sync—Webgility view of inventory variances—can help correct and prevent any discrepancies. In Webgility, two-way sync implies the cyclic movement of inventory caused by removing inventory as orders are placed and shipped to the customer. That change is then shared in inventory to the master record (your accounting system) as well as with any other connected sales channels. As you sync sales from your channels to your accounting and inventory master system, inventory counts are automatically updated by adding items to a customers transaction and reducing them from your on-hand or available inventory. Other inventory changes, like accepting items from an item receipt, are also reflected in the master record. Webgility collects these changes and syncs them back out to all connected channels in order to keep quantities accurate from top to bottom.

5. Know Your Reports: Now that you have inventory properly flowing through QuickBooks, here are some key reports to keep you on track:

• Sales by Item/Product Summary: This shows quantity sold (units and dollars) as well as COGS and gross margin. This powerful report can help you make decisions on purchasing, marketing, and sales based on orders and margins for each item/product.
• Inventory Valuation Summary: This shows on-hand quantity of each item/product, plus average cost, asset value, and retail value. The total asset value on the inventory valuation summary should tie to the balance sheet. If you have a large catalog, refer to #1 above and consider organizing with categories.
• Inventory Stock Status by Item: This report, only available in QuickBooks Enterprise, is a great tool to help you determine when and how much you need to order more of an item/product. It not only shows you what inventory you currently have on hand, but also how much you have promised to customers through Sales Orders (QuickBooks Desktop only), how much you have on order from your vendors through POs and other statistics to show your true inventory position and sales velocity.
• Negative Inventory Listing: In a perfect world—if you have followed tips 1-4—this report should be blank. If not, then run an Item QuickReport (see below) to find out why and correct negative inventory quantities.
• Item QuickReport – Shows the transactional history for a specific item. You can run an Item QuickReport from the item list by right-clicking on an item/product and selecting “QuickReport: Item Name.”

Once your inventory is set up and you have a functioning workflow, it’s finally time to sync your online orders! And just like that, a proper QuickBooks workflow combined with Webgility’s order syncing feature offers a real-time view of your financials.

By guest contributor and Advanced QuickBooks ProAdvisor Susan Hawkins