If you are using QuickBooks Online to manage inventory, one of the key issues is how you set up your inventory items initially. There are two fields that are required that will have a huge impact on the way you can use QuickBooks Online inventory. Let’s talk about the Initial Quantity On Hand and As Of Date.
QuickBooks Online Inventory
This article is the second in my series on QuickBooks Online inventory. I talked about how to enable quantity tracking in QuickBooks Online, and the basics of setting up new inventory items, in my first article in this series. Note that we are talking about QuickBooks Online Plus, the only version that provides inventory quantity tracking. Here’s a list of the articles I’ll have on this topic:
- QuickBooks Online Inventory basics
- QuickBooks Online Inventory initial setup (this article)
- QuickBooks Online Inventory adjustments
- QuickBooks Online Inventory FIFO costing
- QuickBooks Online Inventory reports
- Importing inventory records to QuickBooks Online
- Converting QuickBooks desktop inventory to QuickBooks Online
Initial Quantity On Hand and As Of Date
When you want to set up an item to track the quantity on hand you will check the Track Quantity on Hand box. This adds some new fields, Initial Quantity On Hand and As Of Date. These fields seem simple to understand – you probably already have some of this item in stock when you start using QuickBooks Online inventory, so why not enter that initial quantity here? The problem is, these settings can have a huge impact on how you can use this inventory item.
When you enter an “initial quantity on hand” value for an item, QuickBooks Online will create an Inventory Quantity Adjustment transaction. This posts the value of these items into the selected Inventory Asset account, making the balancing entry in the Opening Balance Equity account. You cannot select the particular balancing account to post this value to, which means that you will need to clear it out of Opening Balance Equity later.
What is the “value” that is posted there? It will be the Initial Quantity On Handmultiplied by the value in the Cost field. It is annoying to me that the Cost field is shown much further down in this window, it really should be something that you enter before you enter this quantity. The value isn’t posted until you close this window, but it is too easy to miss the Cost field and not have the correct value posted.
The main problem that I see people running into is the As Of Date. You arerequired to enter a date here. This is a major issue that you need to take care with! What date do you choose? Most people tend to just pick the current date without realizing what the impact will be.
Not only does this set the date on which this value hits your balance sheet, it also prevents you from posting transactions for this item before that date. I’ve heard many complaints from people who entered the current date and then found that they couldn’t enter a Bill for this item that was dated earlier. If, after adding a new inventory item, you want to enter outstanding purchase orders or older invoices, you could run into an error.
Another issue can come up if you are enabling quantity tracking in an existingitem record. You probably already have some sales or purchasing transactions in the database for this item, when it wasn’t set up to track quantity. In this case you cannot pick an “as of date” earlier or the same as that of any existing transaction.
So if I have a purchase order for this item on September 1, 2015, when it was a regular item without quantity tracking, I cannot pick a starting date that is on or older than September 1, 2015. This is good, in a way – it simplifies what is done with the accounting of the product. Remember that the flow of value through your accounts will change when you start tracking quantity for an item (as I describe in the first article in this series). This date limitation means that existing transactions don’t have to be converted from one way of accounting to the other, which is good (but it can be frustrating). The new accounts that you have set up (inventory asset account, possibly a change to the expense account) will only take place after the existing transactions.
So, what date should you pick? Do you want to just avoid the whole problem? Well, it isn’t easy. Note that these are required fields, you cannot turn quantity tracking on for an item but leave these fields blank!
OK, so what if I entered a date that causes problems, that I didn’t realize was not a good date. Can’t I just go to the Inventory Asset Register and edit that transaction, like I can in QuickBooks Desktop? Unfortunately, once entered, you cannot change that date!
Here’s the inventory Asset account register for an inventory item where I added 100 items at a unit cost of $0.50 at the time I created the item. I’ve customized the report to show a single line per transaction.
I find these registers annoying because you cannot customize them to show allof the information about this transaction. You can see some information by selecting the transaction and then clicking the Edit option. That shows the quantity, but not the value of the transaction (very annoying).
Trying to change the date of the transaction seems like a good idea, except thatit won’t usually work. At this time, if I try to edit the date directly in the Asset Register by clicking on the date I usually get an error message like you see below. Other times it just won’t let me make the change. Note that it is possible that Intuit has a fix on the way, so you may not see this same problem.
If I click the Edit button for the selected transaction, instead of editing the date directly in the register, I just get a display of the transaction details without the ability to change anything. Edit doesn’t work for this kind of transaction.
How about deleting the transaction? The program lets me do that! Problem solved? Unfortunately, that doesn’t change the As of Date in the item record. This date isn’t controlled by the adjustment transaction, it is a part of the item record itself, and there is no way to edit this date!
Clicking the Update button in this window lets me add another adjustment transaction, rather than edit the existing date. I’ll go into more detail on inventory adjustments in another article.
Since you can’t edit the initial as of date, the only options remaining are to live with it or “delete” this item and add a new one to take its place, this time entering the proper date. Note that “delete” really means “make inactive,” as the item is still there – you can’t truly get rid of it in QuickBooks Online.
As a side note, in some cases it would be really nice to be able to add this “as of date” to a report like the Product/Service List. There isn’t any way that I’ve found at this point to add this to any of the reports.
I was just talking to a company that has an add-on product that imports orders into QuickBooks Online from your web store. They have a major problem with some customers who have improperly set the “as of” date. You can’t import orders that have a date older than this date! That creates problems – do they bring the order in on the wrong date (whatever is allowed), or do they create new items to accommodate this transaction? There isn’t a good solution for them due to this limitation in the item list.
The moral of this story is, be careful and pay attention to the details when enabling quantity tracking for an item.
- You must be sure that this is an item you want to track quantity for. Once you turn this feature on you cannot turn it off. In many businesses you don’t really need to track the quantity of every inventory item – just the ones that are critical or have enough value that it warrants the extra work.
- Be sure to enter the proper unit cost if you are entering any value other than zero for the initial quantity on hand.
- Pay attention to the as of date to make sure that it is set properly – not so old that it will create problems in your accounting system (if you are entering a quantity and cost), not so current as to prevent you from entering transactions that may have a date older than this one.
- Consider a policy of always entering a zero quantity here, so that you don’t post a value to opening balance equity. That is my “best practice” recommendation in QuickBooks Desktop, but as we’ll see later in the section on inventory adjustments, the alternative isn’t much better. I don’t like these kind of “behind the scenes” transactions when you are creating records in a list, as it makes it hard to audit if there is an error.
What date should we pick for the as of date? In general, for a new item I recommend that you pick the oldest date in your current accounting period. That gives you some room to add existing transactions, and so won’t create problems with closed accounting periods. For an existing item you will have to adjust that date if you have existing transactions, in order for the item to be the oldest in the current period that you are allowed to use. In fact, some people recommend that you don’t enable quantity tracking for items that you have used, but instead create a new item for tracking.
In the next article in this series I’ll go into detail on QuickBooks Online inventory adjustment transactions.