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 costfield, 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. I’ll address this in an upcoming posting.
Let me know if this is clear, and if you need more information!
Brian Sweat | Jan 14, 2009 | Reply
Thanks for the excellent explanation! I’ve found that many QuickBooks users don’t really understand how COGS is calculated and many just “give up” on trying to maintain an accurate COGS.
I’m the product manager for ACCTivate!, one of the “third party addon program that manages inventory outside of QuickBooks”. Some of our new customers will say, “we use a FIFO costing method”, which lets me know they didn’t understand how QuickBooks COGS works
Emelinda C. Palattao | Feb 9, 2009 | Reply
You have explained it excellently. But I need help in calculating the cost in manufacturing my product when I have many styles and sizes to apply the cost. Also I need help on how to use quickbook in itemizing my products so that I can have finished goods inventory
Charlie | Feb 9, 2009 | Reply
Emelinda: You have several options, it is hard to give you a direct answer via this blog/comment. It depends on how you want to cost things, and control things. In very general terms, the more detail you capture, the more accurate your costing and reports, and the more work it takes. You have to find the balance that fits your situation. If you create an inventory part for every kind of item you have, it takes more work, but you have a more accurate picture. If you use items one time and never again, you don’t need that level of detail, usually.
Heidi | Feb 20, 2009 | Reply
Hey, Charlie.
Thanks for this website, I have looked up a few things on here before, and find it very helpful.
I have a question for you — the way our inventory is set up, when we bring items in we end up paying a third party for freight costs. We use a separate freight item which collects into COGS accounts. Then, I manually adjust the ‘Cost’ box to show our true cost for each item. However, of course, the ‘Avg. Cost’ box still just shows what each item cost from the initial vendor.
This is causing trouble in our balance sheet — I have no way to reliably offset the two costs to see how much freight should still be accounted for in COGS, versus what should go away because we’ve sold those items.
I was using an ‘Item Listing’ report, and introducing the ‘Cost’ column that shows up in there into an ‘Inventory Valuation Detail’ report which showed only ‘Avg. Cost,’ and that seemed to be working. However I’ve now run into another problem. I need to have these reports generated for the end of last year. Instead, if any items have been deactivated since the end of the year, they don’t show up on an Inventory Valuation Detail dated at the end of the year! So all my numbers are off. We’ve clearanced and sold quite a bit of materials since the end of the year, so now I don’t know how I’m going to get this information. Do you have any suggestions I can look into?
Charlie | Feb 20, 2009 | Reply
Heidi, you’ve mixed in a couple of questions here. Let me note first – I am not a CPA or bookkeeper, so I tend to dance around accounting related issues a bit. Sorry about that.
For the “item listing” report you can modify the report to turn off the filter that excludes inactive items (I assume that this is what you mean by “deactivated”). You can’t do that for the Inventory Valuation reports, however. You could get this kind of information out from the ODBC driver and a reporting tool, but it is a bit complicated. Or a custom developer could write a report for you.
As for COGS, that is a bit complicated. Since you are paying a third party for shipping, lots of your options are not available. You can play around with paying the shipping cost to a “holding” expense account, then doing inventory adjustments against that holding account to move the value back into the inventory, but that might not be what you want (and again, I don’t know if this accomplishes what you need from an accounting or tax purpose).
Heidi | Feb 20, 2009 | Reply
Yeah, I’m afraid it is a muddle. I think I may have worked something out this time, but we’ll see what happens. There is a button in the modify report dialogue that allows you to make advanced changes to the inventory valuation report, which looks to show deactivated items, so I am working with that to see if my numbers will come out close.
Thanks for your answer!
JES | Feb 23, 2009 | Reply
How do I delete an erroneously posted Sales Receipt. I cannot get it to go away!
Charlie | Feb 24, 2009 | Reply
JES: If you are in Windows – when looking at the sales receipt in the “enter sales receipt” window, press ctrl-D to delete it. If you have already recorded the deposit for that receipt it won’t let you – you first have to delete the deposit (or remove this value from that deposit).
Heidi | Feb 25, 2009 | Reply
Another question, Charlie — and thanks very much for your help!
When we pull a balance sheet as of the end of a period, and then try to back up that information by pulling an inventory valuation summary, the numbers are not the same. We can’t figure out why – it doesn’t seem to be pulling from different cost boxes. Do you have any hints as to what might be throwing this off? Would deactivation of inventory items since the end of the period have affected reports we are trying to pull now?
Charlie | Feb 25, 2009 | Reply
Again, make sure the dates match (the same ending date). These can be hard to find – you may have journal entries that would affect the balance sheet but not the inventory valuation summary. The balance sheet is the sum of all transactions to that account, the inventory report only reflects transactions that affect individual inventory items. Inactive items shouldn’t affect either.
Heidi | Feb 26, 2009 | Reply
Yeah, I’ve gone over the dates several times to make sure they’re all for the same date (December 31, 2008); perhaps it is journal entries that are an issue, but I think the accountant who’s currently working on it had already checked into that. I’ll see if I can find anything, though.
Donna | May 5, 2009 | Reply
when receiving in items there is somtimes freight , I do not charge this to my customer because when we Bid on a job we always add extra on the Bid amount to cover in freight cost to us , when reading your post regarding freight, Iam now wondering what that is doing in my QB , I pay the vendor for freight charged, When adding this to my bill from Vendor i use Freight from the Expense side (not Item), If i know cost of freight when placing order to vendor I use an item set up as freight under items ( are these the same)
Charlie | May 5, 2009 | Reply
Expensing freight is the simplest method, and very common. You need to talk to your CPA though, and follow the advice they give. The IRS usually wants you to incorporate the freight cost into inventory value rather than expense it, but there are many variations and exceptions. I don’ t know enough about your business to be able to guide you on this, nor am I a CPA.
Ditto Pietrzak | May 5, 2009 | Reply
Charlie –
Do you know where I can get an item listing with the cost, avg cost and price on it? I have tried to modify the item listing report but that does not have the avg cost field. I have tried to modify a inventory valuation but that does not have the cost and it includes the transactions for that item – I am looking for just an item lists.
Ditto
Charlie | May 6, 2009 | Reply
Ditto, that is a tough one, because any of the editable item reports won’t let you add the average cost (at least none that I’ve found). The Excel export doesn’t include it either.
You can get the ODBC driver (if you have Enterprise you already have it) and you can get the info that way (takes a bit of work to get used to). Or you can purchase one of several data extraction tools to extract the item list and then generate the report.
My company has a product that would generate that report for you, but it hasn’t been released to the general public yet (it is being field tested). If you wish to talk about that, send me an email to the address in the “about” page of this blog.
Lisa Sandifer | May 12, 2009 | Reply
Is there a way to clear the inventory average cost?
Charlie | May 13, 2009 | Reply
Lisa, the average cost is calculated by the receipt costs of all purchases for the item, and any inventory value adjustments. If you think that the cost is not correct, you can try correcting the cost of any receipt transactions that are in error. You can try creating a new item so you don’t have the “history” of costs from the old item. You can use the inventory adjustments feature to do a “value” adjustment and reset the cost, but that requires that you post a value to an adjustment account to take care of the different in valuation. I can’t say what exactly is the best approach without knowing why you want to change the average cost of an item…
Irfan Sheikh | Jun 17, 2009 | Reply
Hi,
I have some questions about Costing Inventory Items in QuickBooks.
When we add products in QuickBooks, is it mandatory to fill out the field “Cost”?
Can we leave the field “Cost” empty?
Is the field “Cost” is updated by QuickBooks?
Will we have to change the field “Cost” whenever the costs of inventory items change?
Please give more information about this.
Charlie | Jun 17, 2009 | Reply
Irfan: Why do you not want to enter anything in the “Cost” field?
If you are adding a new item, and AT THAT TIME you enter a quantity (I don’t recommend doing this), then the “cost” field is used to place a value on that initial transaction. A common mistake is to not have a value in that field.
When you purchase an item on a PO, the “cost” field is what is usually used as the default cost for the purchased item. You can enter any value you want to override it then.
The “Cost” field is only edited when you receive an item at a different cost, and only if you have your parameters set to update the cost.
The “Cost” field is used in the display of the total BOM cost of an assembly item.
You don’t need to update this value, in all cases other than the initial quantity entry it doesn’t affect your actual inventory valuation.
Aaron | Jun 24, 2009 | Reply
Hi Charlie,
I am new to QuickBooks and have successfully imported my item list with current on hand quantities into the program. However, short of manually receiving each of my 800+ on-hand items, I can’t figure out how to attach an average cost to them. any ideas?
Charlie | Jun 24, 2009 | Reply
Aaron, average cost is calculated by “transactions” applied to your inventory. Here are your options as I see them:
If you can take a step back to a point in time BEFORE you imported your list, you can update the average cost when you import the list. This only works if you are ADDING new items, not if you are UPDATING existing items. You have to map out the quantity and total value – which are used to calculate the average cost.
You can try to use the IIF file format to import transactions of some sort that would adjust inventory. That is complicated. I’d suggest looking at http://www.bigredconsulting.com to find a tool to help with that (and, test a demo copy to see if it works like you expect, and make a backup copy of your files BEFORE testing).
Get the transaction importer from http://www.baystateconsulting.com, and try to come up with a transaction that would work for you. A more expensive program (not much more) than Big Red, but perhaps a bit easier to work with in some ways.
Enter the adjustments by hand in an inventory adjustment (a pain to deal with) or an inventory receipt.
It is hard for me to say what works best for you without knowing a lot more about your situation. In any case, the only way to get average cost updated is by a transaction – either importing one, or if you do the Excel import with NEW items and the proper fields that becomes a transaction too.
Josie | Jul 16, 2009 | Reply
Hi Charlie,
Thanks for the great website. I have a question regarding QBES Manufacturing – our avg cost has been terribly corrupted and I’m looking for a way to fix it.
We have items that should cost $100 and show up at $900 (not actual numbers just example). The changes seem to occur when multiple of the same item are sold, the value of the individual items then takes on the value of the total sales price.
Any thoughts on how to fix this?
Charlie | Jul 16, 2009 | Reply
Josie, glad you like it!
It can be hard to figure out whey costs go strange. Sometimes you find that running a “rebuild” straightens things out (but unlikely). A very strong possibility is if you allow inventory balances to go negative – such as selling more items than you have on hand in QB on a certain date. The average cost calculation used by QuickBooks goes “bonkers” (that is a technical term…) if you have negative inventory balances. In essence, it tries to compensate for a situation that physically is impossible, and it gives you strange results. Average cost valuations can be very bizarre. Usually it is corrected when you bring your inventory balance back up to zero (or higher), but not always.
Beyond that, I can’t say much without actually seeing your data. You can look at a “Quick Report” for an item to see all the transactions that affect it, which might help. Sometimes you may find a transaction that was entered incorrectly…
Al | Aug 12, 2009 | Reply
Hi Charlie
Thanks for the valuable information you have here
Basically we have couple issues need sorting out
As some of the items cost price change at least once a month, is the average cost in QB ideal for our purposes, if not what’s the alternative
Also should we want to discard historic data in general, what’s the options to do this correctly.. Is it setting up a new company or adjusting the trail balance with journals or something else!
Charlie | Aug 12, 2009 | Reply
Al: In QuickBooks, “average cost” is the only method of costing that the program offers. If you want to value your inventory another way you either have to track inventory costs outside of QB on your own (complicated) and make adustments to your balance sheet and P&L, or you have to buy a third party inventory add-on that manages inventory totally outside of QuickBooks (like Fishbowl Inventory).
If you want to “prune” your data file and get rid of historical info, you can either start a new company and do some balance forward postings, or purchase one of the utilities from Karl Irvin that can handle all of this for you. Go to http://www.q2q.us and see what he has. I HIGHLY recommend his products.
Pat | Aug 24, 2009 | Reply
I am a Business Systems Consultant and have a client that has all their items showing negative inventory. How can they change the inventory to reflect good numbers and not affect costing etc..? My first idea would be to do a physical inventory count.
Thanks in advance for your help.
Charlie | Aug 24, 2009 | Reply
Pat: I can’t give you a detailed answer as I don’t know all of the issues with your client. If they have negative inventory, then they are selling more than they are receiving. I’d first determine WHY they have negative inventory cropping up (not entering receipts? Not entering accurate numbers?) and first fix that issue. You might find that by correcting those problems the negative inventory goes away. If not, you can do a physical and use inventory adjustments, but you are going to have to post that value to some account, and you need to decide what account to use. Again, that comes back to “WHY”
Connie | Aug 31, 2009 | Reply
My husband and I own a butcher shop. I am trying to set up our inventory, but I am running into trouble. Basically, we purchase cattle carcass as a whole, then at the shop, we section it off and sell some as ground beef, ribs, steaks and so on… How do I set up my inventory so that when I sell my individual items, it subtracts it from the whole cattle carcass item? Hope that makes sense. Also, we purchase the cattle from different vendors and the our costs always flucuates, is this going to be a problem with QB’s? Should I just set up everything as non-inventory?
Connie | Aug 31, 2009 | Reply
btw thanks in advance for your help
Charlie | Sep 1, 2009 | Reply
Connie: The decision to make this an inventory part vs a non-inventory part is something that has a big impact on your financial statements, and it is something that you should talk to your accountant about (I’m not a CPA or accountant myself). I don’t know if there are specific rules or regulations that apply to your kind of business.
Assuming that there aren’t any special tax regulations – how long do you hold the carcass? I would assume that you don’t hold it for a long time. You purchase the carcass, divide it, sell the items. If the amount of time that this takes is small, then I would guess that you can treat them as non-inventory items. Check the box that says “this item is used in assemblies…” so that you make it a two sided item. Then you expense it when you purchase it. And then the component items are income when you sell them. You don’t have any tracking of how many carcasses you have on hand, and so forth, so that may be a problem.
If you hold the items for a period of time then this might not be acceptable. In that case, you have a lot of work to do to handle them as inventory parts. You have to do inventory adjustments every time you section it off. That can be tedious.
As for cost fluctuating, that isn’t going to be a problem.
Connie | Sep 4, 2009 | Reply
Thanks for your reply… We buy the carcass and use it within a couple of days. So I guess non-inventory it is. Thanks for the assemblies tip. Someone else suggested I expense it to COGS and use non-inventory for income. But I think your way sounds better. As long as I can have a report at the end of the month as to how much I spent and how much I received, I believe that is all we need for tax purposes (will double check with Accountant). Will the way you suggested provide me with such reports? I believe it will, but let me know if not. Again thanks for your reply!
Cory | Sep 10, 2009 | Reply
I’m having difficulties with changing the ‘cost’ of an item. I was able to edit it but it’s not showing up on reports nor is it ‘averaging’ the cost.
HELP????
Thank you!
Charlie | Sep 10, 2009 | Reply
Cory, you don’t mention which report. Is the report showing the “cost” or the “average cost” field?
The “average cost” of an item is adjusted when you receive an item (the cost of receipt) or build an item (for an assembly, the cost of the component items). Or when you do an inventory value adjustment.
Dave | Nov 8, 2009 | Reply
I have just joined the company. They have rapidly grown from garage to plant and the accounting controls have not grown with the sales. Among the several “issues” I am addressing is their inventory conrol. They have the negative number situation you have described in several of your blogs and I am using your input to correct. My question is more of best process… we receive a truckload of product that is used to create several different items, all from the same raw material, typically just by cutting different size or shape. The pieces are then packaged and sold, large size, medium size, small size. Additionally scraps are collected and used in another product. We sell to both retail and wholesale. While I do not expect a full blown solution… can you point me in the best general direction using Quickbooks? Many Thanks for any input.
Charlie | Nov 9, 2009 | Reply
Dave, it is difficult to give you a good answer without knowing a lot more about your business process, as well as what QB product you are using. QB doesn’t have a good process for handling scrap, and how you manage your raw materials depends on how you measure and count it, what the cost per unit is, and many different factors. Your best bet is to find a ProAdvisor who is familiar with inventory and your kind of manufacturing and sit down with them to discuss your options. It is much too complicated for a quick note in a blog comment like this.
Dawn | Dec 28, 2009 | Reply
Is it possible to print out a list of the goods that we purchase by vendor that shows part numbers and pricing?
Charlie | Dec 28, 2009 | Reply
Dawn, you can play with the “Transaction List by Vendor”, adding the “item” column, changing the filter to show detail transactions and only bills…
Paige | Jan 13, 2010 | Reply
Charlie,
I’m having some issues with QB Pro 2010. We just started using it a couple months ago, but when we uploaded our inventory from Excel, for some reason, some quantities and costs were jumbled between items, so I had to correct them individually. Now the average costs are way off, which is affecting our inventory value. Is there any way to fix this?
Charlie | Jan 13, 2010 | Reply
Paige, it is hard to give you SPECIFIC recommendations without knowing a lot more about the situation. However, in general terms, you can either edit the original transactions from the import, or you can make a value adjustment to set things to the value you want.
To adjust the original transaction, select the item in the item list and right click, select the QuickReport. Set the date range to “all”. Look for the very first transaction – it should be an inventory adjustment. That is what is created when you import a value when adding the item from Excel. Double click on it to open the actual transaction – it should be a posting to Opening Balance Equity. You can then change the quantity and value to be the correct one that you wanted to import.
Or, just make a value adjustment. You have to decide what account to post that to. See my article on this kind of adjustment at http://qbblog.ccrsoftware.info/2009/06/quickbooks-inventory-quantity-and-value-adjustments/
Paige | Jan 14, 2010 | Reply
Thank you for getting back to me so quickly! Ok, I understand what you’re saying and how to adjust that, but if I want to fix the average cost, should I change those values from the inventory adjustment to zero? I don’t want the erroneous average costs to be used for the inventory valuation, just the current price.
Charlie | Jan 14, 2010 | Reply
Paige, I can’t give you a full recommendation without having my hands on the file. If you make a Value adjustment with today’s date, you set the total quantity and the total value as of that date. This would set the average cost to be whatever value you enter (you enter quantity and total value, it divides that to get average cost). That would be the cost used from this point forward, and would not affect prior dates, but the adjustment amount is going to be osted to whatever account you select…
Debbie | Feb 4, 2010 | Reply
In the item list I have my cost filled in but when I process an invoice the cost is posted to my cost of goods account but sometimes the cost is zero – any ideas as to why the cost is appearing as zero to my cost of goods.
Charlie | Feb 4, 2010 | Reply
Debbie, for inventory parts COGS comes from the “avg cost” field, not from the “cost” field.
Ranjit Charles | Feb 10, 2010 | Reply
Charlie,
In one of your earlier discussions, you had mentioned that you have some information on the Total Bill of Material Cost. Do you have this posted somewhere?
We have a manufacturing add-in (Small Business Manufacturing for QuickBooks) and are completing our integration into QB. We have the need to update the average cost (used in valuation) as we report receipts from work orders from manufacturing side. Any ideas on how this can done?
Charlie | Feb 10, 2010 | Reply
Ranjit: I’ve not posted anything on that yet, my apologies. Essentially, that cost is the sum of the “cost” values of the components that are displayed at the time. It has no true accounting value in QuickBooks. Note that if you are using multiple level assemblies then the total bill of materials cost that is shown may be misleading, since the “cost” value of subassemblies is most likely not up to date (it isn’t changed in “build” transactions). You can use my CCRQBOM addon product to resolve that issue, though.
Bonnie | Feb 22, 2010 | Reply
First thank you for a great site! Lots of good info!
We have a photography business where prints & notecards are made to order. Sometimes printing is outsourced for larger prints.
In the past I’ve used a rather cumbersome process which includes inventory items (envelopes, notecard stock, paper, ink, packaging), and entering the finished products as inventory items (notecard sets, prints) which then are sold (sales receipt) and expensed (COGs). In a way its like this parts -> whole product -> sale -> COGs. This process has been used for several years now.
I also keep a XLS file that details our printing processes (calibrations, tests, samples & sales), images or notecard sets printed (which photo/series that is) and determines the cost/item sold to aid in pricing decision-making among other things. This XLS basically is where I determine what QBks inventory adjustments (value) need to be made for materials used & products created.
Perhaps obviously, I’m looking for a simpler way of dealing with the whole thing on the QBks side (that keep QBks B/S and P&L accurate) and how to transition to it from this probably “way too detailed” method we currently use (inventory items, inventory adjustments).
We enter all our purchases & sales via QBks (Pro 2007) so the data is there.
Any suggestions for a more sane approach?
Thanks for your help!
Charlie | Feb 22, 2010 | Reply
Bonnie, I’m sorry, I don’t know that I have an answer for you. There is a lot more that I would need to know. If you are making things to order, every sale is different. Off the top of my head I would think you might want to explore “group” items, as you can modify the content of the item when you sell it, but I can’t say for sure.
Kim | Mar 8, 2010 | Reply
Quickbooks actually uses a Perpetual Average Inventory valuation.
From the AccountingCoach.com:
Under the perpetual system the Inventory account is constantly (or perpetually) changing. When a retailer purchases merchandise, the costs are debited to its Inventory account; when the retailer sells the merchandise to its customers the Inventory account is credited and the Cost of Goods Sold account is debited for the cost of the goods sold. Rather than staying dormant as it does with the periodic method, the Inventory account balance under the perpetual average is changing whenever a purchase or sale occurs.
Under the perpetual system, two sets of entries are made whenever merchandise is sold: (1) the sales amount is debited to Accounts Receivable or Cash and is credited to Sales, and (2) the cost of the merchandise sold is debited to Cost of Goods Sold and is credited to Inventory. (Note: Under the periodic system the second entry is not made.)
Under the perpetual system, “average” means the average cost of the items in inventory as of the date of the sale. This average cost is multiplied by the number of units sold and is removed from the Inventory account and debited to the Cost of Goods Sold account. We use the average as of the time of the sale because this is a perpetual method. (Note: Under the periodic system we wait until the year is over before computing the average cost.)