6. Managing Inventory

Inventory management requires adherence to a strict process to monitor the items being purchased into stock and later being sold. Failure to have these processes in place can wreak havoc on the company’s financials by misstating the value of the inventory or the associated costs for that inventory. This chapter provides details on how to use QuickBooks to properly manage your inventory activities.

Adjusting Inventory

For companies that track inventory, I find that inventory balances receive less attention than the company’s receivables and payables. You review your accounts receivable because you have customers that need to pay you. You keep up with your accounts payable because you have vendors that won’t supply you without first getting paid. Why exactly is it that inventory “reconciling” is often at the bottom of the list, yet it can have the greatest impact on a company’s financials?

Experience has taught me that most companies are unaware that QuickBooks can be used to properly review and audit their inventory balances. This section outlines specific reports and methods you can use in QuickBooks to make sure inventory balances are correct.

If you are using inventory in your QuickBooks data, it is recommended that you view your financial reports in accrual basis. Most companies that have inventory also report their tax financials on an accrual basis. However, a more important reason is that, with accrual basis reporting, QuickBooks matches the cost with the related sale. The exception is when using non-inventory items. These items, when purchased, do not increase an Inventory Asset account. Instead, they are recorded directly to the Cost of Goods Sold account or the Expense account that was assigned when the item was created.

Any company that manages inventory needs to minimize inventory errors. Business owners list any of the following reasons for having to correct their accounting inventory errors:

• Errors in the physical counted results

• Damaged goods

• Theft of inventory

• Open vendor item receipts or bills that are not due to the vendor

• Incorrect valuation given to the inventory at the startup of a data file

This section discusses the methods for correcting inventory errors you might find in your QuickBooks data.

Performing a Physical Inventory Count

If you have a good inventory count commitment from your team and manage the resulting information from the QuickBooks Inventory Valuation Summary report, you should see little to no data entry errors; instead, you probably will be adjusting inventory only for damage or theft.

All too often, here is where I find a complacent attitude about inventory management. I agree that performing a physical inventory count is a time-consuming task. However, if effort is put to this task, your overall financials will be more accurate.

To make recording the count easier, on the menu bar, click Reports, Inventory, Physical Inventory Worksheet. You cannot modify the report or filter it for specific dates (see Figure 6.1), so you should run the report at the moment you start to do your physical count and be certain that you have entered all your inventory-related transactions. If you want to keep a record of the original worksheet, you can export it to Excel or email it as a PDF attachment.

Figure 6.1. Create a physical inventory worksheet to record the actual inventory counts.

After completing a physical inventory count, you can confidently create an inventory adjustment so the accounting records match your actual physical inventory.

Quantity Adjustments

If you discovered quantity differences between your accounting records and your physical inventory account, you should record an inventory quantity adjustment to correct your accounting records.

To create an inventory quantity adjustment in your data, follow these steps:

1. On the Home page, in the Company section, click the Inventory Activities icon and then, from the menu, select Adjust Quantity/Value on Hand. From the Adjust Quantity/Value on Hand dialog box that displays, use the Find & Select Items button to add items to adjust (see Figure 6.2).

Figure 6.2. Use the Find & Select Items button to efficiently select multiple inventory items to adjust quantity.

2. From the Adjustment Type drop-down list, select Quantity.

3. Select an Adjustment Date and type an optional Ref. No.

4. Select the appropriate Adjustment Account. This account is usually a Cost of Goods Sold account or an Expense account named Inventory Overage/Shortage.

5. (Optional) Assign a Customer:Job and/or specify a Class if it’s being tracked. However, Job Profitability reports do not include inventory adjustment transactions; only the Profit & Loss by Job reports include the value of these adjustments.

6. To efficiently select multiple items at once, click the Find & Select Items button. Click with your cursor to place a checkmark (see Figure 6.2) next to the items you want to create a quantity adjustment for.

7. Click Add Selected Items to return to the Adjust Quantity/Value on Hand dialog box.

8. In the New Quantity column, enter your count from the completed physical inventory or, optionally, enter the change in the Qty Difference column.

9. Click Save & Close when finished.

QuickBooks provides details on the Item Info After Adjustment and summarizes the Total Value of the Adjustment and the Number of Item Adjustments.

The accounting effect of the transaction in Figure 6.3 is to adjust the quantity on hand for each of the items shown. The net effect of this adjustment is to increase the Inventory value on the books with the offset to the assigned Adjustment Account.

Figure 6.3. Use Inventory Adjustments to properly adjust your inventory in your accounting to match your physical counts.

Value Adjustments

Timing is important when doing a valuation adjustment. You want to carefully consider value adjustments, if appropriate, for their impact on the company’s resulting financials.

Value adjustments differ from quantity adjustments because they do not adjust the quantity—instead, they adjust the recorded value of the specific items in inventory.

To create a value-only inventory adjustment in your data, follow these steps:

1. From the menu bar, select Vendors, Inventory Activities, Adjust Quantity/Value on Hand. A dialog box with the same name (Adjust Quantity/Value on Hand) opens (see Figure 6.4).

Figure 6.4. Value adjustments are not used often; be sure to ask your accountant first if they are appropriate for your needs.

2. In the Adjustment Type drop-down list, select Total Value.

3. Select an Adjustment Date and type an optional Ref. No.

4. Select the appropriate Adjustment Account. This account is usually an Expense account named Inventory Overage/Shortage.

5. (Optional) Assign a Customer:Job and/or specify a Class if it’s being tracked. However, Job Profitability reports do not include inventory adjustment transactions; only the Profit & Loss by Job reports include the value of these adjustments.

6. To efficiently select multiple items at once, click the Find & Select Items button. Click with your cursor to place a checkmark next to the items you want to create a quantity adjustment for.

7. Click Add Selected Items. You return to the Adjust Quantity/Value on Hand dialog box.

8. In the New Value column, enter the new calculated total value you want assigned to the item.

9. Click Save & Close when finished.

The accounting result of this inventory value adjustment, shown in Figure 6.4, is no net change to inventory quantities, a decrease (credit) to your Inventory Asset account, and an increase (debit) to your Inventory Adjustments account (either a Cost of Goods Sold type or an Expense type). A new average cost will be computed based on the (Original Asset Value + or – the Value Difference)/Quantity on Hand, as recorded on the inventory value adjustment. You can view the newly assigned average cost in the lower left of the inventory adjustment.

The new average cost is recorded when a sales transaction uses this item on or after the date of the inventory adjustment.

Inventory Reporting

You have learned just how easy it is to buy, sell, and adjust your inventory. Good inventory management also includes frequent reviews of inventory-specific reports available in QuickBooks.

This section introduces you to some of the more common reports. However, take the time to review all available reports—you might have a specific reporting need in addition to those mentioned here.

Inventory Center

To access the Inventory Center, from the Home page, in the Company section, click the Inventory Activities icon; from the drop-down menu, select the Inventory Center (see Figure 6.6). Similar to the other “centers” in QuickBooks, the Inventory Center offers one convenient location to manage your inventory reporting and activities, including the following:

• New Inventory Item—Quick access to creating an individual new inventory item or to the Add/Edit Multiple List Entries functionality.

• New Transactions—Quick access to all inventory-related transactions.

• Print—Quick access to printing the item list, information, or transaction list.

• Excel—Exports to Excel the item or transaction list. Import the item list from Excel. In addition, you can add new items from Excel via import or by pasting.

Figure 6.6. Access the most common transactions and reports for your inventory from the Inventory Center.

Click with your cursor on a specific inventory item, and to the right you will see detailed information, transactions, and links to some of the more common reports for managing your inventory.

You can also attach a document to the inventory item using the free QuickBooks attachments feature. Click the Paperclip icon in the Inventory Center with a specific item selected, or from the menu bar, select Company, Documents.

Click the Edit button after you select an item in the Inventory Center to make changes, as Chapter 5, “Setting Up Inventory,” discussed previously in the section titled “Adding or Editing Inventory Items.”

Report Center

Use the Report Center to find the right report for your inventory management needs. See Figure 6.7.

Figure 6.7. The Report Center makes finding the right inventory management report simple.

To open the Report Center, follow these steps:

1. From the menu bar, select Reports, Report Center.

2. On the Report Center’s Standard tab, select Sales.

3. Scroll down to the bottom of the page to find the Open Sales Orders—by Customer or by Item report, which is useful when managing inventory. This report is available only if you are using QuickBooks Premier, Accountant, or Enterprise Solutions.

4. On the Standard tab of the Report Center, select Purchases to view these inventory management reports:

• Purchases by Vendor—Summary or Detail

• Purchases by Item—Summary or Detail

• Open Purchase Orders—Summary, Detail or by Job

The Report Center provides a thumbnail image of how the data would look in the report.

5. Below each report, you can change the dates, click Run to prepare the report with your data, click Info to preview a larger thumbnail image, click Fave to include the report in the Favorites section of the Reports Center, or click Help for more details.

6. On the Standard tab of the Report Center, select Inventory to view these inventory management reports:

• Inventory Valuation—Summary or Detail. The next section has more details.

• Inventory Stock Status—By Item or Vendor.

• Physical Inventory Worksheet—Used for recording your physical inventory count totals.

• Pending Builds—For QuickBooks Premier, Accountant, or Enterprise users who have assembly inventory items.

7. If you are using the Manufacturing & Wholesale edition of QuickBooks Premier or Enterprise Solutions, click Mfg & Wholesale on the Standard tab of the Report Center for additional useful inventory management reports.

Inventory Valuation and Your Financials

Another equally important task in inventory management is to compare your Inventory Valuation Summary report to your Inventory Asset balance on your accrual basis Balance Sheet report.

What if the two balances do not match? The most common cause is entering a transaction that affects the Inventory Asset account but does not affect inventory items. The Inventory Summary report shows only the results of transactions that use inventory items. For example, if a General Journal has been used to adjust the Balance Sheet balance, those transactions do not affect the Inventory Valuation Summary report.

To find General Journal entries in your own data that might be causing the out-of-balance amount, follow these steps:

1. On your Balance Sheet report, double-click the Inventory Asset balance. QuickBooks creates the Transactions by Account report.

2. From the Dates drop-down list, scroll to the top and select All.

3. To locate the General Journal entries, from the Sort By drop-down list (in the upper-right corner of the report), select Type, as in Figure 6.9. QuickBooks now organizes the data by transaction type. Look for General Journal type transactions.

Figure 6.9. General Journal transactions should not be used to adjust inventory balances.

Do not modify, delete, or void these transactions, especially if they were used in accounting periods that have already had tax returns prepared using the current financial information. Continue with the remaining methods in the sections that follow before making any corrections.

Reviewing the Recorded Average Cost Valuation

As mentioned earlier in this chapter, QuickBooks uses the Average Cost method for valuing inventory. From the menu bar, select Reports, Inventory, Inventory Valuation Summary. Figure 6.10 shows the value assigned to your costs when you sell an inventory item.

Figure 6.10. Use the Inventory Valuation Summary report to verify the cost being recorded when an inventory item is sold.

If you are using QuickBooks Enterprise Solutions 14.0 with FIFO (First-In, First-Out) costing enabled, it might not be necessary to review this section because FIFO costing replaces average costing. Beginning with QuickBooks Enterprise 13.0, you can review your inventory valuation reports using either the Average Cost method discussed here or the First-In, First-Out (FIFO) method. FIFO costing in QuickBooks Enterprise is a preference setting that you can enable or disable.

→ To learn more about this and other inventory features, see Appendix C, “QuickBooks Enterprise Solutions Inventory Features,” p. xxx.

For example, assume you are selling an inventory product with the transactions shown in Table 6.1.

Table 6.1. Calculating Average Cost

Sales transactions that are dated on or between 12/15/17 and 12/24/17 for this item will record cost of goods sold at $10.00 per unit (assuming that no other replenishing purchases are made during that time).

The average cost of the 150 units will be $9.50 each if none are sold prior to 12/26. However, any quantity sold from the first order, prior to the purchase on 12/25, will change the weighting of the unit cost. This will result in a different average cost recorded when the second order is received.

To further explain how important it is to enter your inventory transactions daily (and not retroactively), Table 6.2 shows the resulting Average Cost calculations used when selling more inventory than is available.

Table 6.2. Calculating Average Cost When Selling Negative Inventory

In Table 6.2, the cost recorded on 12/20/17 is $10.00 per unit. However, after the replenishing transaction, the actual average cost is $8.50 per unit. The costs in 2017 will be overstated by $1.50 per unit, with the correcting entry to Cost of Goods Sold reported in the 2018 financials.

These details assume that no other transactions were recorded for this item, except as noted in the following transactions.

Average costing is a perfect fit for a business that sells a product that does not fluctuate significantly in cost from one period to the next. However, this is not always the case, so it becomes important that you verify the relative accuracy of the average cost QuickBooks has recorded on the Inventory Valuation Summary report.

In Figure 6.10, shown previously, the average cost for the Interior Wood Door is listed as $69.90. Compare this amount to a recent vendor bill. If the amount is significantly different, you should review the purchase details for the item.

From the menu bar, select Reports, Inventory, Inventory Valuation Detail. You might want to customize and filter the report for specific dates or items. Chapter 14, “Reporting in QuickBooks,” has more on working with reports.

In Figure 6.11, the Inventory Valuation Detail is shown for the Interior Wood Door. Reviewing the individual lines in the Average Cost column can help you determine whether an issue exists with the average cost. If the average cost changes dramatically, you might want to review the specific bill or check details recorded when the product was purchased.

Figure 6.11. Create an Inventory Valuation Detail report to research changes in average cost.

Reviewing and correcting the average cost of items is just as important as adjusting the quantity on hand.

Reviewing Aged Item Receipts

The “Purchasing, Receiving, and Entering the Vendor Bill” section of Chapter 5 provides details about the inventory process. One of the methods of receiving inventory is without the vendor’s bill.

The effect of receiving inventory without a bill is to increase your inventory asset (debit) and increase your accounts payable (credit). The unique feature of this method is that QuickBooks creates an item receipt transaction that will not show in the Pay Bills window. QuickBooks recognizes that because you did not receive the bill, you will not likely be paying it without the final bill details.

Often goods will arrive at your business without a bill. A vendor might ship the goods to your place of business without a final bill for a couple reasons:

• Shipping charges need to be added to the bill. Often the vendor does not know the shipping charges when the goods are initially shipped.

• Vendors do not want to disclose the value of the inventory so that those who handle the inventory during shipping will not know the value of the goods being shipped.

Item receipts age just like an outstanding accounts payable bill. To see whether you have open, outdated item receipts, go to the Vendor Center and click the Transactions tab (see Figure 6.12). Select the Item Receipts transaction type. Filter by All Dates and click the Date column to sort the transactions by date.

Figure 6.12. Use the Transactions tab of the Vendor Center to see all open item receipts.

If you find you have aged item receipts and you know you have paid the vendor, use the following steps to assign the check to the open item receipt. If you have paid your vendor with a credit card, you might also want to use these same instructions, substituting the credit card transaction for the check transaction:

1. From the Vendor Center, select the Item Receipts transaction type on the Transactions tab.

2. Unapplied item receipts display on the right. Click the header for the Date column to sort by date. You are looking for any old, outdated item receipts.

3. If you find an aged item receipt that you know was paid to the vendor, double-click the item receipt to open the Create Item Receipts transaction.

4. Select the Bill Received checkbox (top right), shown in Figure 6.13. The item receipt now becomes a vendor bill transaction type.

Figure 6.13. Item receipts do not show up with bills to be paid.

5. Click Save & Close, and then click Yes to acknowledge that you want to record the change.

6. Locate the check that was written to the vendor. From the Vendors tab on the Vendor Center, select the same vendor the item receipt was created for.

7. On the right, in the Show drop-down list, select Checks. (Optional) Select a Filter By and Date range.

8. After locating the check in the Vendor Center, double-click the check to open the Write Checks transaction. Modify the Account on the Expenses tab to be Accounts Payable, as in Figure 6.14.

Figure 6.14. Changing the Expenses account to Accounts Payable and adding the vendor name on the same line creates a vendor credit from the check.

9. Assign the Vendor name in the Customer:Job column. The effect of this updated transaction is to decrease (debit) Accounts Payable and decrease (credit) Cash.

10. Click Save & Close.

11. From the Home page, click the Pay Bills icon. The Pay Bills dialog box opens.

12. In the Filter By drop-down list, select the vendor you are correcting the records for.

13. Place a checkmark next to the bill (see Figure 6.15).

Figure 6.15. After the bill is selected, the Set Credits option is available.

14. Click the Set Credits button to assign the modified check transaction from step 7 to the open vendor bill (see Figure 6.16).

Figure 6.16. QuickBooks automatically matches a credit with a bill of the same amount, or you can select the credit and modify the amount assigned.

15. Click Done to return to the Pay Bills dialog box. If your credit is the same amount as the open vendor bill the Amt. To Pay column will be zero.

16. Click Pay Selected Bills. Click Done to close the Payment Summary dialog box.

Reviewing Aged Accounts Payable

At the same time you review your open item receipts, you should review your accounts payable unpaid bills that have aged more than 60 days.

To review your accounts payable, from the menu bar, select Reports, Vendors & Payables, Unpaid Bills Detail. If you see open vendor bills you are sure you have paid, it might be because you used the Write Checks transaction to pay your vendor bills instead of using the Pay Bills dialog box (accessed from the Vendors, Pay Bills menu). Having both an open vendor bill and a check paying for the same purchase overstates your costs or inventory.

QuickBooks tries to prevent you from doing this by providing a warning message when you try to write a check to a vendor for which you have an unpaid bill (see Figure 6.17).

Figure 6.17. A warning displays if you try to use the Write Checks transaction to pay a vendor instead of creating a bill payment from the Pay Bills dialog box.

If you did use the Write Checks transaction to issue a vendor payment for a currently open vendor bill, you can resolve this problem by reassigning it. To do so, follow the steps outlined in the “Reviewing Aged Item Receipts,” section, starting with step 7.

After completing the steps, you have successfully assigned the check you wrote using the Write Checks transaction to the open vendor bill that should have been paid by a check in the Pay Bills dialog box. This correction has now removed the expected overstatement in your costs and removed the bill from being included in your open accounts payable balances.

How QuickBooks Handles Negative Inventory

QuickBooks enables you to oversell your inventory, which results in negative inventory. This means that you can include an inventory item on a customer invoice or sales receipt before you have recorded the item receipt or bill for the purchase of the item into your inventory.

When a transaction will cause you to have a negative inventory balance, you will see the warning message in Figure 6.18. The warning does not prevent the user from completing the intended transaction, but it should be an indication that the user should research why QuickBooks does not have enough stock of that item in inventory to record the sale.

Figure 6.18. Not enough quantity warning indicates an issue with not following the recommended processes for proper inventory management.

Although ignoring the warning message can be useful for getting the invoice to the customer, it can create issues with your company’s financials. The following sections detail how QuickBooks handles the costing of the inventory behind the scenes when you sell negative inventory and provide information on how to avoid or minimize the negative effect it can have on your company’s financials.

When Inventory Has an Average Cost from Prior Purchase Transactions

If you review the Inventory Valuation Detail report (select Reports, Inventory, Inventory Valuation Detail from the menu bar) for the item(s) that have negative values, and there are previously recorded average cost amounts, QuickBooks assigns the most recent average cost dated on or before the invoice date that created negative inventory. When the purchase transaction is later recorded, QuickBooks adjusts the Cost of Goods Sold or Expense type for any difference.

→ For more information, see Appendix A, p. xxx.

Figure 6.19 shows that the average cost on the date of the invoice as $150.00 per unit. QuickBooks records a decrease (credit) to inventory for the two units of $300.00 total and an increase (debit) to Cost of Goods Sold of $300.00 for the two units sold.

Figure 6.19. QuickBooks records the cost of goods sold for negative inventory items at the last known average cost.

To show how important the date of the replenishing purchase transaction is, review the details provided next. This example assumes that no additional transactions were recorded except as noted.

The new inventory asset value is calculated as follows:

Figure 6.20. When you replenish inventory shortages, QuickBooks automatically makes an adjustment to agree with what the average cost would have been.

Figure 6.21. The Inventory Valuation Detail report shows the actual inventory value after recording the replenishment transaction.

When QuickBooks creates an adjustment to reflect what the average cost should have been, the adjustment is not assigned to customers or jobs—making it even more important to not sell negative inventory.

When Inventory Does Not Have a Prior Average Cost

Sometimes when you stock a new item, you add it to a customer invoice before recording any purchase activity for the item. If this happens, you should at least record a default cost on the New or Edit Item dialog box.

When an Inventory Item Has a Default Cost

If you have assigned an inventory item that you have not yet purchased (that is, the quantity on hand is zero) to a customer invoice, and if you did define a default cost when you first created the inventory item, QuickBooks uses this default cost as the suggested per-unit cost when the invoice is recorded.

Suppose you stock a new inventory item for a Wood Door with Glass. When creating the item, you record a default cost of $150.00, as shown in Figure 6.22.

Figure 6.22. This new stock item was created in QuickBooks with a default cost of $150.00.

Before any purchase is recorded for this item, it is sold on a customer invoice. When you create an invoice for which not enough quantity is on hand, QuickBooks provides a warning, as Figure 6.18 showed earlier.

QuickBooks has to estimate the cost of the item and, in this example, uses the cost assigned to the item in the New or Edit Item dialog box (see Figure 6.23). When you save this invoice, QuickBooks creates the entry in Table 6.3.

Figure 6.23. QuickBooks uses the cost assigned in the New or Edit Item dialog box when you sell inventory that you have not recorded any purchase transactions for.

Table 6.3. Accounting When Inventory Is Sold

To view this report, with the transaction displayed, on the Reports tab of the transaction ribbon, choose Transaction Journal.

This impact to your financials (positive or negative) can be significant if the actual purchase price differs from the recorded default cost on the New or Edit Item dialog box.

When an Inventory Item Does Not Have a Default Cost

If you have assigned an inventory item you have not yet purchased (that is, the quantity on hand is zero) to a customer invoice, and did not define a default cost when you first created the inventory item, QuickBooks uses -0- as the default cost per unit when the invoice is recorded, showing a 100% profit margin for your financials and for that customer.

Now, using the same previous example, only not recording a default cost when setting up the item, as in Figure 6.24, QuickBooks does not calculate any cost or inventory reduction with the sale of the inventory (see Figure 6.25).

Figure 6.24. Setting up an item without including a default cost is not recommended.

Figure 6.25. No inventory decrease or cost is recorded when selling an item you do not have in inventory, have not purchased, and did not record a default cost for.

Imagine the impact this can have on your financials and the profitability reports you might review for your business or clients. Simply heeding the warnings QuickBooks provides about the errors of selling negative inventory can prevent this from happening in your data file.

When a Replenishing Purchase Transaction Is Created

Exactly what happens when you do record purchase transactions? The date recorded on the purchase transaction has a significant impact on how QuickBooks handles this transaction.

For example, if a sale resulting in negative inventory was dated in October and the replenishing transaction (vendor bill) was dated in November, QuickBooks records the revenue in the month of October and the records cost in the month of November. From month to month, it might not be noticeable, but if the transactions cross years, revenue would be overstated in one year and costs in another. Additionally, the revenue is tracked by the customer assigned to the invoice, but the cost is not tracked by the customer because the cost is recorded on the date of the purchase transactions.

To limit the negative impact, date your replenishing purchase transactions before the date of the customer invoice that caused the negative inventory. QuickBooks recalculates the average cost of all sales transactions dated after this “replenishing” purchase transaction. Of course, it is simply best not to sell negative inventory at all.

How to Avoid Incorrect Inventory Valuation

Troubleshooting negative inventory can be an eye opener to show how important proper inventory management is. To help with this task, rely on the Inventory Valuation Detail report, shown previously in Figure 6.21.

You can avoid these issues if the purchase transactions or inventory adjustments are dated on or before the date of the sales transactions creating the negative inventory. Backdating inventory adjustment transactions can be a powerful solution for correcting months of misstated financials, so use it where appropriate after discussing it with your accountant.

Using solid inventory management processes and being current with your data entry can avoid recording negative inventory. If you do have negative inventory, be sure to correct it at the end of your tax year, or your tax return information will potentially be incorrect.