Inventory, like payroll, is consistently one of the most problematic areas for clients. A rigid set of procedures, with everyone inputting data consistently and in a timely manner is crucial. This often opens an opportunity for training.

The fundamentals should be reviewed initially. 

Second, evaluate whether the total inventory value on the inventory valuation summary report agrees to the inventory amount reported on the Balance Sheet. Disagreement in these two numbers can be caused by several factors. A Client Data Review (CDR) tool can assist with this problem.

Third, review the Inventory Valuation Summary for negative quantities and inaccurate average costs.

Common Error: Incorrect and/or inconsistent inventory procedures

Symptoms

• Inventory Valuation Summary report shows negative quantities
  

• Inventory Valuation Summary does not agree to General Ledger
  

• Average cost appears incorrect
  

• Asset value does not agree with the General Ledger
  

CDR Tool – Compare Balance Sheet and Inventory Valuation

The only method available in QuickBooks for inventory valuation is average costing. An Inventory Valuation Summary Report summarizes the quantity, average cost and extended value of each of the inventory quantities and provides a total inventory valuation. The total asset value on this report should match with the Balance Sheet value for Inventory; just ensure these two reports were created with the same time/date settings if other users are entering data in QuickBooks at the same time.

If Journal Entries have been posted to the inventory account, the Inventory Valuation Summary will not agree with the Inventory Asset balance on your Balance Sheet. Additionally, inventory items with Quantity On Hand that have been made inactive will also cause a discrepancy between the Inventory Valuation Summary report and the totals for the inventory asset account on the financial statements or trial balance. To correct this problem, re-activate the inventory item, create an inventory adjustment (as discussed below), then inactivate the item again.

The Compare Balance Sheet and Inventory Valuation tool within CDR determines whether the inventory account on the Balance Sheet and the Inventory Valuation Summary match. The tool compares the inventory general ledger account with Inventory Valuation Summary and displays the results.

A green circle indicates that the inventory account in the general ledger agrees with the Inventory Valuation Summary.

A yellow warning triangle alerts that a discrepancy between the inventory general ledger account and the Inventory Valuation Summary exists. Two major causes of discrepancies are non-inventory accounts affecting inventory transactions and the incorrect setup of the accounts for inventory items.

The default date for the comparison is the last day of the review period specified in the CDR. The date in the As of field can be changed and then Refreshed to narrow down the date when the discrepancy occurred.

If the tool shows a discrepancy, links are provided to items that can help resolve the problem.

Links to the Balance Sheet and Inventory Valuation Summary 

A link to a report that shows Transactions using an inventory asset account but not inventory items allows you to see a report that lists all transactions (such as a Journal Entry, Check or Bill) posted to the Inventory Asset account that did not affect an inventory item. For checks or bills, the transaction should be edited to include the inventory item on the Item tab and the transaction information on the expenses tab removed. If a journal entry posted to the inventory asset account, then the Journal Entry needs to be deleted or voided and an inventory adjustment made for the appropriate item.

There are also links to:

• The Add/Edit Multiple List Entries window to review the inventory setup
  

• Adjust Inventory Quantity/Value on Hand
  

• The Item List
  

CDR Tool—Troubleshoot Inventory

Improperly recording the purchase or sale of inventory items in QuickBooks can cause many problems. Having a negative quantity for an inventory item is also a problem, and can affect how QuickBooks calculates average cost. Identifying the specific item and/or transaction that caused the negative inventory situation can take a considerable amount of time.

The Troubleshoot Inventory tool will help find inventory errors. A column in the tool displays a Yes if a negative value occurred as of the end of the period or any time during the review period. Another column shows inactive items that have a quantity on hand and another column shows items with a percent of markup that is less than the amount specified.

• Tool Options – The item list is show in spreadsheet format. Filter options are displayed on the top left. The Columns to Display option is on the top right and a link to the Adjust inventory Quantity/Value on Hand adjustment window is located at the bottom of the window.
  
• Filter Options – Items can be filtered for all or only active items, Negative Quantity, Inactive items with Quantity on Hand, and a % of Markup less than a specified percentage.
  
• Marking Items as Inactive – Items can be marked as inactive directly from this window. Check mark which items you wish to mark as inactive and then click Make Selected Items Inactive.
  
• Negative Inventory – Negative Inventory is indicated by a yellow warning sign and a Yes/No message in the far right column. Double click on the amount showing as negative and the Inventory Valuation report appears. Note: this tool does not have the ability to automatically fix negative inventory as the fix can vary from situation to situation. However, an adjustment can be made to the inventory items by clicking the Adjust Quantity/Value on Hand link on the bottom of the window. Negative inventory alerts can also be displayed by the As of date or Any time in date range by selecting the desired option in the bottom right corner of the window.
  
• Customizing Columns – Click on the Columns to Display link to customize the format of the spreadsheet. Note: by default all available columns are marked so the available columns are displayed and unwanted columns can be removed easily from this window. To remove a column, simply click on the check mark next to the column name and click Save.
  

INVENTORY FIXES

Items Overview

Within QuickBooks, an item can be anything bought or sold by the business. Think of an item as anything that you will purchase and then later sell. The item is the underlying link between invoices and bills or checks to the general ledger. Sometimes a multitude of items is needed for small variations amongst items sold, and sometimes one generic item will due when the item sold is actually slightly different. That determination is made based on the level of detail needed by management and the potential differences in cost of the items purchased and sold. For example, if the business sells pizzas, one item is all that is required. As each invoice is created, the description and price could be revised as needed. The other alternative would be to create items for each of the different kinds of pizzas sold. This approach would eliminate the need for revisions as the invoice is created since the description and price would come up automatically. More sophisticated sales reports would also be possible in the latter example.

There are many judgment calls and different ways to achieve accurate general ledger results. Review of the item list is critical to:

• Confirm that the items have been set up correctly and
  

• Confirm that the decisions made are appropriate for the business based on the flow of paperwork, accounting knowledge, QuickBooks experience, etc.
  

Item Types

When creating an item, several choices of item types are available. The type is also important for subtotals on reports and for some function options.

Below is a list of the types and the typical use for each:

• Service – most commonly used for items such as labor, consulting, hourly fees, etc. A service type item is required for use on timesheets.
  

• Inventory Part – items normally carried in stock until sold. This item type will keep a running balance of quantity on hand and moving average cost. This item type will record purchases into an Other Current Asset account when purchased and record a corresponding entry with each invoice to record the appropriate amount to cost of goods sold. Further detailed discussion of this type of item is beyond the scope of these materials.
  

• Inventory Assembly – items created from other inventory items (i.e., assembled) then carried in stock until sold. This item type will keep a running balance of quantity on hand and moving average cost. This item type will record purchases into an Other Current Asset account when purchased and reclassify the appropriate amount to cost of goods sold when an invoice or cash sale receipt is created. Further detailed discussion of this type of item is beyond the scope of these materials.
  

• Non-Inventory Part – items not kept in stock. This could include items such as custom or special orders. This item type is also used if the average cost method of valuing inventory is unacceptable or perpetual inventory counts are not necessary. As items are purchased, they are simply expensed.
  

• Other Charge – freight, service charge, fuel surcharge, gift-wrapping services or other expenses that are passed through to the customer are all examples of other charge type items.
  

• Subtotal – a special item type that permits a subtotal line on an invoice to accumulate the amount due of all lines entered previously.
  

• Group – this item type is used when several items are sold at the same time. The group item adds flexibility for printing all of the individual components on the customer’s copy of the invoice, or showing the detail only on the screen with one line on the printed copy of the invoice that merges the detail together. Note: Reports cannot be run on groups by themselves. For example, if an installed door set is sold that includes a door, the hardware, and labor, a sales report will show the appropriate amount of revenue for each component but will not show the revenue generated from the sale of the door set group itself, only how many components were sold.
  

• Discount – a reduction on the invoice that can be either a flat dollar amount or a percentage of the line immediately preceding it. If a discount is to be given on each item, the discount will be entered either after each item, or once after a subtotal of all items.
  

• Payment – if a payment is received at the time of the invoice, this item type can be used. If the Client uses statements, then the statement will only show the net of the transaction, not the detail. If it is important to show the payment on the statement, entering an invoice then creating a receive payment transaction may be preferred. The Payment item type can also be used for client’s whose customers prepay or make deposits.
  

• Sales Tax – based on the customer being taxable, and the item being taxable, this item will calculate the tax liability due based on the tax percentage entered.
  

• Sales Tax Group – a group of Sales Tax items that allows the business to show one tax rate, comprised of a state, county and/or local sales tax item, as on item on an invoice or sales receipt. Currently, a Sales Tax Group report is not available within QuickBooks. The absence of this report makes this type item not a preferred method for tracking sales tax.
  

Inventory versus Non-inventory Type Parts

QuickBooks only reports inventory based on average cost. QuickBooks tracks inventory well when set up correctly and proper procedures are followed consistently for buying, selling and adjusting the inventory item balances. The premise behind this is properly setting up inventory versus non-inventory types.

The item types were defined above; however, a description of best practices is warranted.

With inventory, one item is coded to an asset, a cost of goods sold account, and an income account. As the items are purchased, they are recorded on the items tab of a bill, check or credit card charge) and the inventory balance is increased for the quantity and cost of the item. When the item is sold, the average cost is deducted from the inventory asset account and recorded in cost of goods sold. At the time of the purchase, the inventory account is debited and the bank account, accounts payable or credit card account is credited. At the time of the sale, the journal entry that takes place is a debit to accounts receivable and a credit to sales, but also, a debit to cost of goods sold and a credit to inventory at the average cost computed by QuickBooks.

With non-inventory parts, only one account is typically used both for the purchases and sales. The item can be edited and the check the box in the middle of the item marked. This opens two sections of information versus the prior one section. Now information for both the purchases and the sales accounts can be entered. With non-inventory type parts, at the time of the transaction the purchase is expensed and the sale is recorded as income. The inventory account is not involved.

Items can be changed for coding corrections, or to change a non-inventory part to inventory. Items cannot be changed from inventory to non-inventory. Make any changes carefully as these changes cannot be undone and could affect prior period balances.

The most important aspect of using inventory in QuickBooks is to always ensure an item is purchased before it is sold. The client should understand the significance of negative inventory quantities. Warning messages about negative inventory should never be ignored.

Inventory Purchases versus Sales

Perpetual inventory systems require adherence to proper procedures for accurate inventory control. Consistency in entering key transactions associated with the inventory items is key. An Item QuickReport is a quick way to confirm the item has only been sold when positive quantities on hand were showing.

Item Receipts

If an item has been received, but the bill has not arrived, an item receipt should be created. An item receipt will not age in Accounts Payable until the Item Receipt form is edited to indicate that the bill has been received. The item receipt will increase the Inventory and Accounts Payable balances. This Item Receipt can also be linked to a Purchase Order either when the vendor name is entered, or from the Select PO button at the bottom of the Items tab.

If no Purchase Order exists, simply enter the information manually on the Items tab of the bill.

NOTE: When the bill is received, click on the box in the upper right hand corner and the terms and due date fields will appear. This will activate proper aging and permit bill payment procedures. This step is important, if a new bill is entered rather than the item receipt being edited, the Inventory and Accounts Payable balances will be inflated. You can also achieve these same steps by entering a bill for items already received from the home page. An error message helps warn when a bill is entered but an Item Receipt already exists.

Inventory Quantity or Value Adjustments

To review the details of inventory values, select the Reports menu > Inventory, choose Inventory Valuation Summary. This report provides information about the quantity on hand as well as the value it is assigned in Inventory.

QuickBooks uses average costing for the inventory value. The total asset value on this report should agree on the same date and time with the Balance Sheet value for Inventory. These numbers can differ if users create Journal Entries and post them directly to the Inventory account. The inventory adjustments function should always be used to adjust inventory values rather than through a Journal Entry.

Create the following report to identify transactions that have been posted to the Inventory account via Journal Entries. Chart of Accounts > Inventory, select the Reports tab at the bottom of the screen, and then select QuickReport > Modify Reports, then All for the date range and filter the Transaction Type to Journal Entry.

The correct process is to make a change to Inventory for quantity changes or value changes from Vendors > Inventory Activities > adjust Quantity/Value on Hand.

The quantity by which you desire to change the amount is marked in the Quantity difference column. How the change will affect the inventory is shown in the lower left corner.