Understanding QuickBooks Inventory Cost
If you are using QuickBooks to manage your inventory, you need to understand how QuickBooks deals with the cost of inventory items. I’ve been answering a lot of questions about this in the Intuit Community Forums lately, so here is a quick rundown of how things work.
Cost Fields in QuickBooks
If you look at an Inventory Part item, you will see that there are two cost fields.
The cost field, on the left, is a “reference” field. That is, it doesn’t have any direct bearing on the valuation of your inventory, the cost of your inventory in your inventory asset account. I wish they had another name, because it is confusing to talk about it. I refer to this as the “last purchased cost”, although that isn’t always exactly right. If you purchase an item and receive a bill for it, the cost that you receive the item at will usually be stored here (but not always, that depends on how your company file is set up). You can edit this cost directly in this window, it doesn’t have a direct effect on your inventory valuation.
The avg cost field, bottom center, is the field that is used in the calculation of the value of your inventory. This is calculated by QuickBooks based on the cost of receipt (and adjustment) transactions. You cannot directly edit this in the window here.
Inventory Valuation
QuickBooks values your inventory using an average costing calculation, as opposed to other types you may be familiar with, such as LIFO, FIFO, or specific costing. If you need another costing method, you will have to use a third party addon program that manages inventory outside of QuickBooks.
This can be a complicated subject – I am only going to go into this lightly. Let’s look at a simple example.
- If start with an item with no quantity, no value, and receive a quantity of 10 at $1.00 each, you will see that the cost is $1.00, and the avg cost is also $1.00. You have $10.00 of inventory in your inventory asset account.
- If I then receive another 10 items, but at a unit cost of $2.00, you will usually see the cost value set to be $2.00. However, the avg cost of your inventory will show as $1.50. We started with 10 items and a value of $10.00, we added another 10 items at a value of $20.00, so we have 20 items with a value of $30.00. That gives us an average cost of $1.50.
If you sell one of these items in an invoice, the COGS account is incremented by the average cost of the item at the time of the sale.
This is a simple example. There are long arguments about the costing calculation that QuickBooks uses – relating to the more complicated situations when you have many added transactions, and other complicated situations.
One thing that I will note, briefly – if you sell all your inventory, and then continue to sell the item so that you go to a negative quantity, the costing calculation runs into problems. It can’t accurately account for a negative balance, and you can see some very odd figures show up in the average cost field, and your inventory valuation reports. Once you bring the balances back to positive these figures should resolve themselves, but it is always a good idea to not allow inventory balances to go negative.
Manufacturing Cost
When you are working with an Inventory Assembly item you have an additional cost field – the Total Bill of Materials Cost. See my article on Understanding the Total Bill Of Materials Cost.
Category: Manufacturing and Inventory, Working with QuickBooks
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He's been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had a one-floppy disk drive system, loading the program from one floppy and then replacing that with the other floppy to hold the data. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise. He also is a Xero Certified Partner. Visit his CCRSoftware web site for information about his QuickBooks add-on products. Charlie can be reached at charlie.russell@sleeter.com He is also the author of the California Wildflower Hikes blog and a regular blog contributor to the Intuit Inner Circle. Connect with Charlie at GoogleSubscribe
If you enjoyed this article, subscribe to receive more just like it.
Comments (83)
Trackback URL | Comments RSS Feed
Sites That Link to this Post
- QuickBooks 2012 FIFO Inventory : QuickBooks and Beyond | September 9, 2011
Thanks for explaining how to use QuickBooks to manage inventory.I haven’t been able to find other information on the web that is as comprehensive as this explanation.
I am helping a friend in the wholesale fish businss setup his QB items. He buys whole fish and sells as both whole and fillets. How do you account for the loss in yield when purchasing a whole fish item that will be sold in fillets with say a 30% loss in yield. How do you set up an item that is purchased as a whole but sold both ways.
Thanks
John, there are a whole bunch of issues here that can’t easily be addressed in a blog comment. And many ways of dealing with them that depends on a number of factors you haven’t expressed.
You could, for example, have inventory assembly items for the fillets, and “process” them by building them. But that may be more work than needed.
You could just not have these be inventory part items, since they would not be held in “inventory” for long, and just handle the purchases as expenses and the sales as income, and work out profitability in the financial statements. Some pluses to that, some negatives.
It depends on what you are trying to get out of the system, what your financial requirements are, and how much work you want to put into running the program.
And we won’t even start to talk about the whole “catch weight” issue, which is a major thorn in this kind of business process.
John, I have a w/ avg cost issue that I cannot seem to resolve. I keep all my avg costs @ $0 as I do not keep an inventory in QB. So all of my items have negative inventory balance. This has been working. Unfortunately I created a couple of items with avg cost amts and now I have a neg balance on my balance sheet in the Inventory Asset acct. I would like to set the avg cost to these items to $0 to avoid the implications to the Inv Asset acct. Suggestions on how to do this? I generally create new items off the item list. Initial purchase transactions do not exist for these items. So I am not sure where the avg cost amt came from. Thanks.
Please disregard this post. I found my answer on the Intuit Community Forum with a post by Charlie. Thanks Charlie.
Greg, why do you use “inventory part” items if you are not keeping inventory? It sounds like you are creating a lot of work for yourself – why not just use “non-inventory parts”?
If you entered a quantity for the item when you created the new item, that creates a receipt and it will use the “cost” value that you enter at that time…
Charlie, so now that I understand it. How do I correct it? My averages were way off…which appears to effect net income reports, customer income summary reports, etc. I went in to inventory adjustment values and “adjusted” the values back to what they should be…then chose inventory asset as the account to log the adjustment (instead of an expense account which would incorrectly change net income). Assuming that this was what I should have done (which I doubt), I then go back and I still have customer income showing negative numbers (which was the result of average cost being high and out of line). My little adjustment didn’t seem to carry back into history… not sure how to make that adjustment so that I can accurately show income/loss. Thanks in advance for any light you can shed here.
Kevin: Fixing this kind of thing is something that is really difficult to help you with through blog comments. There are many variables here, and without actually seeing what you have done in the past, and what you have changed, I can’t really give you good advice here. I strongly recommend that you work with a local advisor who can look at your files and give you sound advice based on what they see. You can find a Sleeter-Certified consultant by using the search box at the top of the right hand column here.
Inventory adjustments have a date, and the date is when the changes take effect. If you entered the current date, that might not fix historical problems correctly.
In many cases, depending on what the original problem was, you may be better off changing the original adjustments rather than posting new adjustments. However, that isn’t always the best approach, depending on the volume of posts AND the timeframe you are working with.
Posting to inventory asset is usually not a good thing to do, it creates other issues that you may have problems with.
In any case, this is a situation where you need to work with a consultant who can straighten things out for you. Someone with the Accountant’s Edition (and who knows how to use it) will have additional tools that can help straighten out inventory issues.
Thank You. This was very helpful.
Hi there,
I hope you can help me. I’m busy with financial year end and the accountants are querying the stock. My Stock Asset account and my Stock Valuation Report differs. I’ve check that all the items are set up correctly ie Asset Account is Stock Acc and COGS account. Any idea why these two will be different?
Helie, there can be many reasons. I recommend that you start by looking at an older article I wrote in another blog (which I need to update and transfer to here): http://qbblog.ccrsoftware.info/2009/04/reconciling-quickbooks-inventory-balances/
Hi Charlie,
It seems I may have caused QB to double count my COGS. For example, on my PnL, COGS for a product states $27,000. When I double click that number, the Transaction Detail By Account report comes up. In that report there are Invoices and Bills. I dont believe there should be bills in there…? Reason being, when we charge a customer in an invoice, it is taken out of COGS automoatically based on the average cost. Ok makes sense. But, why would it also show the bill to that vendor?
I do create PO’s and receive items & enter bills against them.
I asked my accountant and she is stumped. So am I. Please help
Erin
Erin, I can’t say anything for sure without looking at your files. My recommendation would be to find an experienced QuickBooks consultant who can help – there are many simple things that can be checked for, and a ProAdvisor would have tools that you might not have available to you. If you use the “find a consultant” widget in the upper right column of this page you can find a qualified person in your area who could help. The first thing I would look at would be to see what “type” of items you have. For instance, if you have “non-inventory items” and are purchasing them, you need to have them set up as “double sided” items. In any case, lots of reasons why things would show this way, and a quallified person would most likely be able to pin this down quickly once they have access to your files.
Greetings, I just started a job and have come across an entry that the previous staff were doing to COGS sold. Me and the office manager have went through all of this and it doesn’t really make sense to us. We came across your blog and we are hoping that you can help!! I have already read all the information posted on inventory costing. The reports I have printed are the detail to the material account. This is how the journal appears.
DR A/R
CR Inventory
DR Materials for inventory cost
CR Materials for the sale price
How does the variance of the inventory cost and the inventory sale price hit income?
The previous person that was in this job did a journal entry monthly and did the following entry.
DR Materials for the net of cost and sales price
CR Income for the net of the cost and sales price
Sorry for the length of this, but we are baffled as to why this entry would have been made at all.
Thank you for your time!
Nicole
Nicole, I’m not an accountant or bookkeeper, so I generally stay away from commenting on that kind of thing. And “forensic bookkeeping” is another thing altogether. My only comment here would be to say that if your system is set up properly AND you were using all of the features provided in QuickBooks, there typically is no need for regular adjustments like that. My recommendation is to work with a consultant who understands QuickBooks very well, and who has accountant/bookkeeping resources available. You can use the “find a consultant” link at the upper right of this page.
how can we block the issuing invoice to customers when the stock quantity falls short, the negative quantity will mess up our COGS.
thanks.
Short answer on that one, Lisa – “you can’t”. QuickBooks doesn’t have a way to put a hard limit on this. You just have to heed the warnings when it tells you that you don’t have enough items on hand.
Is there a resource that you know of that explains in detail how to adjust accounts receivable to cash basis when inventory assets are booked?
Thanks!
Ok here is my situation, I import goods, so my inventory cost includes duties and taxes. My problem is how do I put all these cost in my inventory without having to entering all these invoices into inventory which will overstate my inventory?
Cornwall: “Overstate” as far as quantity, or as far as cost? I would think that normally these additional costs should be a part of the cost of the item, but that is something for you to discuss with your financial advisor.
Take a look at the discussion in this article: http://qbblog.ccrsoftware.info/2009/03/shipping-costs-and-quickbooks-inventory/
Hi
i have a problem i want to make an accountant copy to my accountant to use but i don’t want him to see the cost of items i bought , the only thing i want him to do is issue invoices and collect money , is there a way to prevent him from seeing the cost of items in inventory other reports
Abdulaziz, the intent of the Accountant’s Copy is to give a FULL copy of your file to the accountant. So you won’t use that feature for what you are looking for. Also, unfortunately, in QuickBooks, if someone can sell an item they can also see the cost of the item, there isn’t a way to separate those functions with just QuickBooks by itself. You would have to go to some third party software solution that would work with QB.
Charlie,
I have a relatively simple question about average COGS. Do you know if the average COGS in QB is calculated on a time period basis, and what that basis is? I have inventory items which we previously paid for but are now free and I’d like to know if in the next fiscal year the avg cost for that item will go down to zero.
Kathy, it is essentially the ENTIRE history of the item. So it would depend on how your balances run. So if you buy some, sell some, buy some more, sell some more, the average cost fluctuates. However, if you sell out ALL of the items, so you have a zero balance on hand, and then buy some more, the average cost will be based just on those latest purchases. It is like you started all over, essentially, from that point.
Hi Charlie,
I bought a business with existing inventory 3 years ago. When my Quickbooks advisors set up my account, she didn’t enter the inventory I purchased as inventory so I have “negative” inventory. I need to print an accurate balance sheet. How do I correct this inventory issue without increasing my expenses?
Thanks,
Sarah
hey charlie,
i have no stock option in my item list and am therefore unable to add stock only non stock or services…
could you tell me the cause for this???
James, I’m guessing that you are using the UK version, so things are a bit different there than in the US version I work with.
However, I suspect that you haven’t enabled inventory. In the US version you would select “Edit” then “Preferences” and select the “Items & Inventory” option on the left. In the “Company Preferences” tab you would check the box “Inventory and purchase orders are active”, and then you’ll see inventory part items being available as an item type.
Charlie,
We carry inventory by sku purchased at various costs from different vendors. We have found that our COGS Expense & Inventory Asset balances are changing after the period has closed. The Trial Balance debit & credit totals remain the same, but the individual account balances changed by the same offsetting amounts. I’ve run audit reports, but I cannot see any adjustments, the original documents are actually changing, I assume because of the average costing issues mentioned above. Is this “normal” for Quickbooks, to change closed periods?
Thank you.
Debbie, the first thing to consider is if you ever allow your quantity on hand to go negative. If you have a negative quantity on hand at some point then odd things happen with COGS, because QuickBooks doesn’t have a good way of calculating an average cost for a negative quantity. You get one calculated COGS value when inventory goes negative, and later on if you bring the quantity back to zero or positive, QuickBooks will adjust COGS for this. How it adjusts it depends on several factors. But that is the first thing to look for.
After that, you look for situations where someone is changing a date on a receipt or adjustment, or going back to change a quantity, or something of that order.
we are using qb and we are buying parts under one part # and sellig them under another. Would building an assembly for these parts be the correct way to do this?
Maybe, maybe not. Why different part numbers? And are you doing some sort of processing to them?
If you are just buying with one number and selling under another, with no processing or “manufacturing” going on, then I would consider using a “Group” item instead. That is just like an alias or alternate name, no “building” or other processing needed. But the best answer does require some more information about your business and what you are doing, and how many items you have, etc.
Is there a report that I can run which will determine my COGS for the previous period (month)? Although I can determine the sales each day or month, I can’t find where I can show my COGS to help determine our monthly profit/loss. I did find where I can show my inventory valuation at a specific day/date but with the selling of inventory as well as the replenishment of same that number does not help. Thanks so much!
Karen, have you double clicked on the “Cost of Goods Sold” account in your chart of accounts? Assuming that is what you are using for COGS for all items. That would open the QuickReport, and you can set a date range there…
Hi, can you tell me what the best way is to track inventory when we send out samples that may or may not be invoiced at a later time. Some times the item is not invoiced until sixty days later if the customer likes it. Other times we get the item back and it goes back into inventory. We dont want to mess up the cogs by invoiving if it’s not a complete sale but we also want to be able to track how much stock we have to complete other job while we have samples out. I hope that makes sense.
There are several ways to deal with this, but it does depend on what QuickBooks version you have.
If you have Enterprise with Advanced Inventory then you can make a “site” for each of the locations you send things out to. Or, if there are a lot of sites, just create a site for all samples out.
If you have Premier then one way is to use sales orders. Create a sales order for the place that the samples go out to. They are “allocated” but not sold, so this doesn’t affect your COGS or financials. If they are returned, delete them from the sales order. If they are sold, use the sales order to invoice function to sell them.
Hi, Thank You, This is helpful information.
I’ve few questions here which average cost method QuickBook use, Weighted Average OR Moving-Average? Is there any way we choose one from these two ? And which one would you think is more commonly use/accurate?
Khurram, I’m not sure how you want to distinguish “weighted average” from “moving average”. Different people will define those in different ways. However, you can’t change how QuickBooks does it’s calculations. There aren’t any choices. EXCEPT if you want to move to Entprise and use the Advanced Inventory feature (which is an additional fee) and then you have an option for FIFO costing.
I have a client who uses QuickBooks Enterprise Solutions 2011. They currently caring inventory, and are tracking inventory in QBs; however, they are not actively using inventory. Meaning that they are purchasing items and posting them to the Inventory Asset account, but they are not removing the items from inventory when they are used in the manufacture of a product. In addition, because the parts are not being pulled from inventory in QBs and thus charged to the job, the PNL per job do not show the accurate COGS. I have suggested that they change each inventory item “Inventory Information” field “Asset Account” from “Inventory Asset” to “COGS:Materials” so that the Job Costs are tracked properly. They are concerned that if we do that, all historical data purchase under that part would also be moved to the COGS account. They have already created an AJE to move the 2011 and prior Asset Inventory used to COGS. So if we change the account at this point will it only change any new items we enter or will it change all parts ordered under that part number? Please advise. Thank you.
Katrina, that is more complicated than I really should go into in a blog comment like this.
Changing the asset account WILL affect existing transactions, so that probabl isn’t what you wan tto do.
There are all kinds of things going on here – the best idea may be to see if you can get them to use proper procedures as they move forward. If they are using “inventory parts” but not consuming them, the quantity is building up and that means it is worthless to do that work. Get them to “build” the items to pull the components properly. Or if it makes sense in this situation, create new items that are “non inventory” parts so that you don’t have to worry about quantities and such.
Charlie,
Thanks for your time and great replies, Need some problem solving.
I just ran a report as to see my profit on some sales I did, and realized that some items I make 100% profit, when I went in to those Items the cost is filled up, but the avg. cost is empty, after reading some help and the community I realized that the report derives the information from the avg. cost field is there any way I can modify that field so my Profit and income are accurate ?
Thanks for your time and appreciate your help
Joseph
Joseph, it is hard to give a specific answer without having hands on the file. If you are using inventory parts, and your average cost is zero, then you aren’t handling the purchase/receipts transactions correctly. That is where the average cost will normally come from. I can’t tell you how to fix that going backwards without knowing how long you have been using the system. Moving forward, you need to fix your processes so that these values are being captured correctly.
Hi Charlie,
I have item X cost 2 USD – sometime my salesman is sell it at 1 Usd ( negative margin ), so i check that every end of month but too late.
Please any way to block the sales of any items sell it ( UNDER – COST )
Thanks
Rany, QuickBooks doesn’t have a way to prevent that from happening.