Inventory tracking in QuickBooks appears simple on the surface, and it can be if it is entered correctly. It is important to understand how it works and how QuickBooks expects you to be entering the transactions involved, as there are pitfalls and side effects if your day to day workflow ignores those expectations.
What Do I Need to Understand about Standard Inventory Workflows in QuickBooks?
1. Common Misconceptions About QuickBooks Inventory Tracking
The most common inventory misunderstanding involves the use of inventory items on invoices. To inventory tracking within QuickBooks, entering an invoice means that the inventory items left your possession and now belong to the customer. This happens on the date entered for the invoice. The difficulty is that many companies create an invoice to receive payment, and then ship the items on a later date after the inventory is received. In life, the workflow makes sense. In QuickBooks, the difference can cause issues.
The described workflow can cause the situation called “negative inventory,” where the effective quantity on hand of an item is negative because we have sold the item before we have received it. This causes QuickBooks to have to make a guess at the “cost of goods sold” (COGS) value, and then correct it later – if the cost turns out to have changed.
QuickBooks does provide workflow options that avoid the issue by using the non-posting “Sales Order” transaction.
- Because it is not a posting transaction, it does not actually affect inventory.
- It will show as quantity needed on inventory stock status reports to help order items which are not in stock or need to have stock replenished soon.
- It is possible to have a template for sales orders that calls them Invoices when printed, so customer perception is unaffected, and they submit the desired payment.
- There are options for how to enter the prepayment without having an invoice created yet, with the specifics depending on your accounting needs.
- Once the items are ordered from a manufacturer or distributor, received and shipped, the actual invoice in QuickBooks is entered, taking the items out of inventory.
Another very common misperception involves “Item Receipt” vs “Bill” transactions. In the inventory workflow in QuickBooks, it is common to enter an Item Receipt when the items arrive, and then enter a bill when that is received.
The challenge is when the dates for items and bill are different because there is only one actual transaction, rather than two. This means that if the date is changed during the bill entry, it effectively changes the date the inventory was received. This can cause an unexpected negative inventory situation by changing what was recorded as being on hand when later transactions take place.
2. How to Properly Track Inventory Stock
About the Inventory Stock Status by Item Report
Let’s look at the Inventory Stock Status by Item report and how each transaction in the inventory workflow affects your inventory.
This report shows your list of inventory items with the preferred vendor and minimum reorder point set in the item.
- The “On Hand” column shows the quantity of the item on hand at the end date of the report.
- It has the amounts on any unfulfilled “Sales Orders”, which is subtracted from the “On Hand” quantity to show what is “Available” to be sold.
- The “Order” column works with the reorder point and is checked if the item should be ordered to bring the “Available” or “On Hand” quantity above the reorder point.
- There is a checkbox at the top to select using “Available”, and a default preference as well. If the quantity shown in “On PO” would bring the applicable comparison above the reorder point, it will also show it does not need to be ordered.
- “Sales/Week” is the only column that uses the specified date range for the report instead of being as of the end date.
3. How to Properly Track Inventory Purchases
The inventory process often starts with the Purchase Order transaction for one or more items, which represents an order placed with a vendor which you have not received yet.
It is non-posting, so it does not affect the “On Hand” quantity. It does show on the report and setting the expected delivery dates on your PO transactions will show in the “Next Deliv” date column.
Receiving Against a Purchase Order
Once the ordered items are received, an Item Receipt or Bill is entered. If the items are received without a bill, it would be entered as an Item Receipt; if the items are received with a bill, then we would directly enter it as a Bill.
As we discussed before, these are different states of the same transaction.
If you enter an Item Receipt and look at your AP Aging reports, they will show the Item Receipt, because you have received value from the vendor, and still owe them payment.
Once the “Bill Received” checkbox is checked, any terms are applied, and due dates entered, it becomes a Bill.
Bills and Item Receipts & Quantity On Hand
At this point, the received inventory is part of the “On Hand” quantity because the Item Receipt or Bill is a posting transaction.
The “On PO” amount is reduced because the PO has been at least partially fulfilled. QuickBooks knows how the quantity received and the cost paid for the items affects your Inventory Asset and average cost of the item, so when it is later sold, it knows what to use for the COGS value.
If the “On Hand” quantity was previously negative for the item because more had been invoiced than received, we would likely also see the Transaction Journal for the bill showing the adjustment between COGS and Inventory Asset – as QuickBooks corrects its previous COGS guess, as well as the usual AP to Inventory Asset part of the transaction.
Your workflow may skip the PO and/or Bill entirely because you had someone go pick up the items you needed and just pay with a credit/debit card, check, or even cash.
Depending on the details, you can put the inventory items on the Check or Credit Card Charge transaction directly.
This type of transaction also puts the inventory into the “On Hand” column which we want to always keep at or above zero.
4. Accurate Tracking of Inventory Sales & Quantity on Hand (QoH)
Now, let’s look at the sales side of the inventory flow. Which transaction comes first, and how it relates to the purchase transactions we already talked about depends on your business.
- If you keep inventory on stock and customers pick the items up from you, you may just use Invoice or Sales Receipt transactions directly. These transaction types take quantity out of the “On Hand” number, so we need to make sure we have the inventory in stock when entering either type.
- For businesses which take orders and fulfill them, tracking order and shipment in QuickBooks gets a bit more complicated. The concept still applies if you use another application, and sync your orders or invoices over to QuickBooks, so look at the combined workflow with the effect on “On Hand” quantity in mind.
When Starting With a Sales Order is Best For Your QuickBooks Desktop Inventory Workflow
It is best to enter the order as a Sales Order until the shipment is made if the order tracking is in QuickBooks.
- This is a non-posting transaction, so it does not affect your “On Hand” quantity.
- It does affect the “Available” quantity.
- It is also shows in the “On Sales Order” column, so that the user(s) handling purchasing can see what is needed.
This is particularly important if the item being sold is not one you normally have in stock, so it must be purchased or made before it can be sent to the customer.
- Because of this, a common chain of transactions starts with a customer submitting an order, which becomes a Sales Order transaction.
- Because the item is not in stock, and our “On Hand” quantity is zero, we enter a Purchase Order for a vendor to purchase the item.
- Once the item is received, we enter an Item Receipt or Bill transaction as discussed to increase the “On Hand” quantity for the item.
- At this point, we have the item in our stock, and an unfulfilled Sales Order for the item.
- We ship it and enter the Invoice transaction to show the item has been removed from our “On Hand” inventory.
5. The Importance of Transaction Entry Order
Order of transaction entry on a day can be important in this sequence of transactions. In the physical world, we can ship the received item right after it comes in and before the Item Receipt or Bill has been entered in QuickBooks.
If the Invoice for the shipment is entered before the transaction for the items being received is entered, then we will have negative quantity on hand happen briefly in QuickBooks – even though we did have the item in our physical stock.
This type of sequence can be seen using the Inventory Valuation Detail report for an item.
If necessary, it is possible to move a transaction to the end of the day’s sequence by deleting it and then entering it again; this may or may not be practical to do, such as needing to move a bunch of invoices after a single bill.
It is best to avoid the need by making sure the transaction for the received items is entered before the newly received items are shipped and Invoices are entered. If the invoices are for a later date than the transaction which brings the inventory in, then the date-driven nature of QuickBooks takes care of it. It is the transactions which are dated the same that can cause the issue.
What Must I Know About Cost of Goods Sold (COGS)?
How QuickBooks Desktop Calculates Cost of Goods Sold
The standard method in QuickBooks for determining Cost of Goods Sold, or COGS, is Average Costing. It is common for the purchase cost of any inventory part to vary over time, and there are multiple methods used by businesses to calculate the COGS value for an individual sale.
- Average Costing uses the average cost of inventory in stock at the time of the sale and is the most commonly used method.
First In, First Out, or FIFO, uses the cost of the earliest purchased item in inventory when the sale is made, and sells each item in that purchase order. This option is available as part of QuickBooks Advanced Inventory, and inventory applications such as Fishbowl also support it. - Last In, First Out, or LIFO assumes the most recent purchase is the first sold. This method requires using another inventory application such as Fishbowl.
- Standard Cost uses a predetermined cost of the item, and then records the variances from the actual costs. This method also requires using another inventory application such as Fishbowl.
We’ll go over Average Costing in a moment. FIFO will be covered with “Advanced Inventory.” If your business uses the less common LIFO or Standard Cost, we can talk over the details of the available options with you.
How Average Costing Works
When you receive inventory items, the quantity is added to your stock. The cost of those additions is added to your Inventory Asset account value. When the items are sold, the Average Cost used is the total value for the item in inventory divided by the quantity of the item on hand.
Les Step Through Some Transactions
- We purchase 5 parts at $10.00 each, so we have 5 on hand and the total value is $50.00.
- We sell one of the parts, so the COGS value is 50/5 = $10.00, leaving us with 4 on hand at $40.00.
- We purchase 3 more at $11.00 each, so our quantity on hand becomes 7, and our total value is $73.00.
- We sell two more of the parts, so the COGS for each is 73/7 = $10.43, leaving us with 5 on hand with a total value of $52.14.
QuickBooks makes it easy to see the shifts in “Quantity on Hand,” “Average Cost,” and “Total Value” – transaction by transaction – using the “Inventory Valuation Detail” report.
Each of those three columns shows the resulting value from the transaction on the line of the report. It can be helpful to filter the report for the item you are looking at, as the default is all inventory and assembly items.
COGS, Negative Inventory, and Sales Transactions
When the quantity on hand of an item in QuickBooks drops below zero due to the order of transactions, how does Average Costing work?
In a nutshell, when you sell items you have not received yet, QuickBooks must guess what the COGS value will be.
1. It uses one of two values for this guess and continues to use this guess until the “On Hand” quantity is above zero again.
2. The usual value is the average cost of the item from the last time the quantity on hand was above zero.
Please note: it is not sufficient to bring the quantity on hand up to zero. It must become higher than zero to reset the average cost used for the guess.
3. If the item has never been purchased in a file, and there was no initial inventory quantity and value entered, QuickBooks will fall back on the cost value entered in the item at the time the invoice is created.
4. If this value is zero at the time of the sale, it may retroactively change the guess if the item is then given a default non-zero cost.
COGS, Negative Inventory, and Purchase Transactions
Now that we understand how it makes the guess at the time of the sales transaction, what happens when we get around to entering the purchase transaction for the items?
1. If the cost on that purchase is different from the COGS guess that QuickBooks previously made, it adds a behind the scenes correction to the guess as part of the purchase transaction.
2. The current purchase cost affects the AP and Inventory Asset accounts as usual, and there are additional entries made to reflect the correction for the COGS and Inventory Asset accounts.
3. If the guess was too high, then the difference in value flows from COGS back to Inventory Asset.
4. If the guess was too low, then the difference in value shifts from Inventory Asset to COGS.
This correction is most commonly noticed when reviewing the COGS accounts used for inventory parts and unexpectedly seeing Bill transactions there. It is also possible to have them showing because Inventory Asset was incorrectly placed on the Expense tab of the bill. This brings us to another common inventory valuation issue.
How are Inventory Valuation Reports and the Balance Sheet Affected?
The Expected Results in QuickBooks Reporting
It is expected when using inventory parts in QuickBooks that the total asset value on the “Inventory Valuation Summary” report will match the balance of the “Inventory Asset” account on the “Balance Sheet” report for the same date.
Both reports work on an As Of date basis, where the values reflect all transactions for the item or account from the start of the file to the date specified.
Because the two reports function a little differently, entering transactions in ways that QuickBooks does not expect to be used can cause differences in those totals. Let’s look at why.
- The Balance Sheet report looks at all transactions affecting the Inventory Asset account when generating the report.
- The Inventory Valuation Summary report looks at all transactions with an active inventory item on them.
If inventory items are set up correctly, and used on all inventory transactions, then the balances should always match. The differences come into play when we put the Inventory Asset account on a transaction without using an Inventory item.
How Do We Find and Correct the Differences?
There are several common transaction entries which can be involved, so let’s look at them and the type of entry to make to avoid the issue.
1. Bills/Credits, Checks, or Credit Card Charges/Credits which use the Inventory Asset account on the Expense tab instead of the inventory part on the Item tab. The fix is simple, remove the Expense tab amount and use the item instead.
2. Inventory Adjustments which use Inventory Asset as the Adjustment Account.This effectively loops the value out of Inventory Asset with the item tag and then back into the account without an item associated with it.In this case, the adjustment account should normally be an income or expense account unless entering the opening inventory counts for a file, which would use the Opening Balance Equity clearing account.
Some accountants use an expense account for reductions in inventory, and an income account for increases in inventory. Some choose to put all adjustments to one or the other, which can result in the account balance becoming negative.
3. Journal Entries using Inventory Asset. Because journals cannot have an item on them, we should use an Inventory Adjustment instead.
The above transactions can be easily found by double-clicking on the Inventory Asset balance on the Balance Sheet, and then changing the report to total by item detail instead of the account detail.
The transactions causing the difference will be in the No Item section at the bottom of the report. It may be necessary to increase the date range to include everything prior to the date you are looking at.
There are a few situations potentially trickier to deal with.
Tricky Mismatch #1
The first interacts with the above transaction types. It happens when you start off tracking purchases with Inventory Asset as an expense, then enter sales with a non-inventory part, then change the type of that item to be an inventory part instead.
This is only possible in earlier versions of QuickBooks, as the more recent versions removed the ability to make that type change due to the chaos it can create.
Adding a new inventory item and entering current quantities instead of converting the item type is a much better way to make the change, as it does not affect the historical transactions which were not entered with inventory tracking in mind.
Tricky Mismatch #2
The second and less obvious issue is when an inventory part is made inactive without zeroing out the quantity and value for the item with an adjustment. The value is still in the Balance Sheet account value, but the Inventory Valuation reports ignore inactive items, causing the difference. The solution is to make the item active, adjust it out, and then make it inactive again.
Note: If yore running QuickBooks Desktop 2019, the Inventory Valuation Reports include a toggle to show inactive inventory items, which may further help with this type of mismatch.
Tricky Mismatch #3
The third situation is when the mismatch is caused by using unexpected accounts in the item settings. If a different type of account is used for the asset account on the inventory item for example, it will contribute to the mismatch of expected amounts.
While QuickBooks will currently let you use another account type here such as a liability or COGS account, doing so will likely result in unexpected amounts in your accounts.
Other unexpected results can happen by putting different account types in for the income or COGS accounts in an inventory part. If the accounts make sense for what the item represents, then it is probably not an inventory part, and should be given a different item type.
What Other Inventory Options in QuickBooks Do I Have For Tracking Inventory?
QuickBooks Advanced Inventory Options
There are more options for tracking inventory detail beyond just quantity on hand and the value of those items. These options can also involve adding The “Advanced Inventory” features to your QuickBooks Enterprise subscription and support plan, which makes it possible to track the following aspects of Inventory:
- Location or Inventory Site
- Serial or Lot Numbers
- Calculate Cost of Goods Sold using other methods beyond Average Cost
- The enabling of barcode scanning for efficiency and accuracy
Need Even More Inventory Tracking Detail?
A possibly required add-on application such as Fishbowl to provide additional functionality.
QuickBooks Enhanced Inventory Receiving – Another Way to Track How Inventory is Received
There is also an Enterprise option to make item receipts and bills into separate transactions using “Enhanced Inventory Receiving,” which is useful in very niche situations where the dates of those events are separated enough for most purchase transactions and the different workflow involved does not conflict with other needs.
We would be glad to discuss these needs with you and recommend solutions, as well as any questions you have about the standard inventory workflows discussed above.
