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 (79)
Trackback URL | Comments RSS Feed
Sites That Link to this Post
- QuickBooks 2012 FIFO Inventory : QuickBooks and Beyond | September 9, 2011
Hi Charlie,
I am looking at my company file that has has been selling invertory and not booking any receipt of items. All the inventory is current showing negative balances. In the P&L the COGS is 4 time the sales figure. I am trying to record the receipt of itmes for the first time. I am not an account so forgive if I am using the wrong wording.
If I record all my billes for inventory purchased, would this fix the problem in the P&L?
If that wont work, do I need to recreat the inventory items so that a more accurate average cost figure would affect the P&L and BS?
How do I fix my problem?
Barry, it is hard to give a specific answer without seeing your file, and knowing a lot more about your situation. In a very general sense, if you went back and entered all the purchases/receipts, that should resolve things. However, it does depend on how long a time this has been going on, how much work that entails, what you did as far as entering the expenditures for those item purchases (assuming you have been entering the checks you wrote?), and more. You may want to work with a qualified consultant who can look at your situation and work out a plan with you. And, then, you have to work out a solution that will keep this from happening again as you move forward.
Charlie -
I am having the same inventory problem with a client as Barry is having.
Their system was set up before I got to them, and we have been trying to make it work for the purposes they need. They mainly wanted to be sure that everything (office furniture) that they were buying was getting invoiced when sold so that nothing was falling thru the cracks. These were big items and were only supposed to be used once – from PO to bill to invoice everything was supposed to flow through the system. Then things got crazy and the items were being re-used without being “purchased” back into stock. QB was average costing and then the P&L looked like Barry’s. I noticed in the item listing that I had some negative “quantities on hand” since we were selling and not purchasing, so I did an inventory adjustment to COGS as a test and zeroed out those negatives on the items that I knew were complete. Will this give a more accurate reading in the COGS on the P&L?
Cheryl, hard to say without having hands on the file. COGS postings going forward are going to be based on the average cost you have for the items at the time of the sale. If you did just a “quantity” adjustment, that doesn’t change the average cost, and I can’t tell if the average cost going forward is going to be accurate or not. If you did a “quantity and value” adjustment, then you have reset the average cost to some value – if that is a correct value, then you are OK going forward (but I can’t tell if your COGS valuation will be right in the past). There are a bunch of things interacting here so it is not possible for me to say if you have it set up right or not.
And, going forward, it is only accurate if the client changes their ways and moves materials through the system correctly. If they continue to make the same mistakes as before, the problem comes back.
Is there anyway for it to stop using and average cost. I would like to adjust manually on a monthly basis per the inventory count.
Tanner, QuickBooks is an average cost system, you can’t change that in most cases. The only alternative costing method is FIFO if you are using Enterprise 12 and purchase the Advanced Inventory option.
Alternately, you can purchase one of the add-on inventory systems that takes inventory out of QB and just uses it for financials. But that is expensive, and I don’t know if you will get what you want from one of those.
You can turn your items into non-inventory items and then just use journal entries to adjust your financials. But you lose the ability to track balances on hand (etc), and this creates a lot of extra work. Usually not the best way to go.
You can just make monthly Value adjustments to set the inventory value to what you want, but that might not be the correct thing to do financially. Best to talk this over with a financial advisor who also understands QuickBooks.
Hi Charlie,
Am I correct in understanding that if an Item qty never goes back to zero, then the average cost will be the average cost of the Item since the transactions started occuring in that item? e.g. an Item was first used in 2005 and has been used continually without having a zero (0) quantity, then the average cost will be the average of ALL transactions since 2005?? As opposed to the actual average cost of the current stock on hand qty which may have only been received in the last month with all previous stock being sold/used, etc.?
Thanks
Stuart, yes, more or less. The average cost would be influenced by earlier purchases, and not just reflect the most current purchases.
Hi Sir,
I have one question, for one of our client they took laon from their relative for the business,they have not show in the books. But the repayment is making through business. So now this loan account is showing debits balance. can you please advice how solve this issue.
Second Question is – One of our client having retail business but in the books they have not recorded the purchase entry, but the inventory is directly updated to the opening stock. Some of the purchase made this year and those are also updated in the opening stock.So can you advice me how to adjust purchases for this year.
Shivakumar, my apologies, I don’t comment on that kind of accounting issue in blog comments (the loan issue). That is an issue for an accounting professional.
For inventory, I’m not clear what you mean by “the inventory is directly updated to the opening stock”. Are you saying that you are entering the balance on hand when you add an item to the item list? If so, that isn’t usually the best way to do it. You should always try to use item receipts (or checks, or credit card purchases) and not enter the value through the “Add Item” window. When you do it through “add item” you have a posting to “opening balance equity”, and then you have to make a journal entry to distribute that to the right place.
Hi, On the P&L the GOGS are being duplicated. When I go to the detail the customer invoice cost as well as the supplier invoice coist are being calculated. When payment to suppliers are made it’s being done through the cheque payment function under COGS. Please help as this is giving me the wrong P&L profit/loss.
Regards
Marius
Marius, it is hard to give you an answer without being able to see your QB file. It sounds to me like you don’t have the proper accounts set up for the inventory items (assuming that these invoices are for “inventory part” items). What accounts do you have set up for the three accounts in the inventory parts?
firstly, thx for this valuable informations. but i cant get it , i mean the average cost …. lets say that we will setup anew inventory part and we will put in cost window its first cost price right (1) dollar for example, what will happend when i 2ndly purchases with (2)dollar price .. i mean it will be done automaticly ???
Hany, yes, QuickBooks will maintain the average cost for you as you purchase new items. You want to use the item receipt functions rather than doing inventory adjustments directly.
Hi Charlie,
I’m setting up QB in a very basic way for the first time in our business and am in the process of entering 11 months of historical data for 2012. I’m using the register to just enter a single aggregate entry for each of my expense and income categories for each month into a dummy bank-type account that I set up specifically for this purpose – to capture all of my transactions regardless of which of my credit cards or checkbooks I used to pay.
I have about 20 expense categories like, Advertising, Freight-In, Rent, Office Expense, and my income category of Sales and I’m adding up all my expenditures for each category and entering it as a single transaction – just so I can get some data into the system.
My problem is with CoGS and Inventory. I do not want to use QB to track my myriad individual stocked items – I don’t have time to enter all that data. I just want to be able to know the total dollar value of my inventory based on the $ I spend to buy it, and doing some type of adjustment each month based on a physical inventory. I have a 1/1/12 beginning inventory and monthly physical inventories. How do I input purchases of merchandise so that the value of my inventory increases? How do I adjust my inventory at the end of each month to account for increases or decreases in physical stock? How do I do all of this so that my inventory value and CoGS reflects something close to reality?
The CoGS accounts I set up are Purchases-Merchandise, Purchases-Packaging, Purchases-Shipping Supplies, (these all relate to the stock I actually have on hand), and I have a Purchases-Dropshipping account too which is not related to inventory (though I’m not sure it’s set up right).
I’m not an accountant and I’m relatively new to QB so I appreciate you taking that into consideration with your answer!
Thank you very much in advance for any help you can offer!
BJ, I strongly recommend that you find an accountant to work with, or an experienced QuickBooks professional (try the “find a consultant” box on the right of this page), as this is a bit complicated to answer in short blog comments. And a lot depends on the details of how you are operating your business.
Thanks for replying.
Hi Charlie,
Can you please advise what would be the proper way to reflect the specific cost of an item sold instead of the average cost that QB generates. I’m using right now a journal entry to update the difference, but I will like to apply the adjustment direct to the item under the inventory account. Many times when these adjustment needs to be done, they are retroactive and I’m not sure of the repercutions.
Thanks for your assistance.
Frank, why are you trying to do this? QuickBooks is an average cost system, if you start playing around with making adjustments you’ll have to find an account to post the difference to.
On my company’s qb, cogs is recorded 20 times more than normal on certain day after recording receipt of bill-credit for an item so avg cost also 20 times more. Making weekly report for company, i realize jan. 24th the item(fd000601)’s cogs is about $3,000($150 on jan 23th). I record the bill credit with the item to return on that day. Based on my assumption, the avg cost should have become just little higher than the avg cost like before recording the credit.
Only on Jan 24 th, the avg cost is 20 times more and from Jan 25 the cost is the same as before. please tell me the reason.
Without being able to examine your file I can’t give a specific answer. Note that if your quantity on hand goes negative, very odd things show up in COGS as QuickBooks struggles to deal with the concept of negative inventory. That is the first thing that I look for when evaluating these kinds of issues.
I would recommend that you get in touch with a qualified accounting professional who has experience with QuickBooks and have them examine the file. QuickBooks ProAdvisors have access to the Accountant edition, which has a number of tools that can aid in understanding these kinds of issues.
Dear Mr. Russell,
You seem to be one of the most helpful guys I’ve found out there dealing with QuickBooks. So first I would like to say thank you for that.
My question is this….
After entering my order and then receiving with additional “Expenses” such as freight and trucking etc., the added cost did not average into the cost of the item purchased. I then manually costed and entered the landed cost for each of the almost 600 items before receiving the order with invoice.
Is there a way to have the costs associated with the multiple items on an order be allocated to their cost proportionately?
Also, my biggest issue now is that my average cost is showing for all items but I cannot create an accurate price list as it does not allow use of the average cost but only the cost as it is specified in the item set up. So if my cost has increased on an item that has been sold out or stock, my new stock is being priced for sale using the old cost that was entered at the time the item was created in the system.
Please advise if you are aware of this limitation and if there is a way that I can work around it without having to manually go to each of my 2,000 items and change the present cost prices in order that my pricing levels are accurate. Is there a way to create a price list from the average cost? Is there a way to have the new purchase order and invoice cost replace the old data?
I am starting to feel that QB is posing more challenges than it may be worth.
Would really appreciate your assistance if you are able.
Best regards,
M.E.
Mark, you are correct. QuickBooks doesn’t calculate the “landed cost” of the item for you. It either has to be calculated manually, or you can move up to a more comprehensive inventory system that works with QuickBooks, like ACCTivate.
As far as the price list, I would normally consider adjusting that based on the current purchase cost, rather than average cost. If you have QuickBooks set to update the “cost” field each time you purchase an item, then the “change item prices” function would update things based on that most recent cost. If you want to update based on average cost, send me an email (my address is in the blog author page) as I can provide you with information on a utility that will handle that kind of update easily.
Thanks so much for your response. I do not think I am set up with the update cost field. How do I do that? I would use the current cost price if I knew how to get it to register from the entered invoice received.
I will send you an email.
That depends on what version/year of QuickBooks you are using. If you have Premier, or a version of Enterprise that is older than V12: When you receive an item at a cost that is different than the item’s “cost” field, you should get a popup that asks you if you want to update the cost. I usually recommend that you do (there are times where you might not want to, but usually you do). If that isn’t showing up, that means that you turned the question off. If the cost isn’t updating, then when you turned it off you told it to not update. The way to get that back is to open your “preferences”, and in the desktop preferences there is an option to “bring back the one-time messages”. You check that box and close the window. Then, the next receipt, it will ask you (on this user account, it is set per user) if you want to update the cost if it is different. There is a box in that question “don’t ask me this again”, and if you check that it won’t ask you again, AND it will remember what you said. So you want to say “yes” – it is up to you if you want to tell it to not ask again. Hope that makes sense…
Thank you for your help with this issue. I have a client with years of allowing their hundreds of inventory items go negative. For 2012 we went through the Inventory Valuation Detail and updated all the bills to dates prior to sales invoices to correct negative amounts. This seemed to work great at getting COGS to a reasonable percentage, but when I made a final adjusting journal entry to inventory/COGS, it made the Accrual Inventory lose $100,000 in value on a $25,000 entry. COGS recorded correctly.
Have you seen the inventory just disappear like that? We are using the Inventory Valuation final total as our inventory value.
Leanne, I can’t say what is going on without having a lot more detail – and probably hands on. I don’t know what adjustments you made in detail. You should be able to look at the registers/ QuickReports and see what is going on.
Our controller is telling me I’ve done something wrong with a couple of our items. Our average cost is in the trillions or billions and it makes a few of the items go crazy. For this reason I haven’t been allowed to keep inventory totals in our qb 12. We’ve had to drop certain items and make almost identical item#s for them.
I know our controller tries to adjust certain issues using the adjust qty/value on hand. But after it ends up incorrectly she tells me I did something wrong. When all I believe I’m doing is doing a sales order, billing it a week or 2 later and when the lady sends it through billing ‘the average cost and amount’ end up in the trillions.
I don’t think there could be too may factors causing this
and rather than me say all the things I think could have caused this, maybe I could answer any questions you have that could answer this.. I’m just at a lost to explain this but it is up to me to do so with our team apparently.
John, you probably should sit down with a knowledgeable QuickBooks advisor who can look at what is going on and help. Either someone is adjusting something incorrectly, or you have some damage in your data file, or you have an inventory part that you are allowing to go to a negative on-hand balance. Do you have a positive (or zero) on hand balance for this item?
Most of the on hand balances are negative between -10 – -5,000 or so. I was going through and updating them intermittently as the items would be restocked.
But when I came into this position they had switched over from passport and no one ever set up an accurate on-hand balance. So about 3 or 4 months ago I decided I would update the items I manage as they were restocked, knowing it would take a few months for them all to become accurate – Rather than have someone in the warehouse do a full physical inventory(I wanted to wait until I’ve been here awhile before requesting that).
So slowly I started updating the on hand quantities and then after about a month or so I was told to basically stop. I don’t think what I was doing was necessarily wrong. But I know there are only certain ‘stock items’ we keep the exact qty on. I was also told our CPA couldn’t figure out what was going on with it either, that information is probably inaccurate too. I just think certain things get ‘adjusted’ daily so that when items are invoiced this doesn’t happen.
I’m not saying there is any illegal activity going on. Just a continual tolerance of inaccurate data that I can no longer tolerate.
John, negative quantity on hand is a MAJOR problem. It destroys your COGS calculations.
QuickBooks handles COGS on an average cost basis (unless you use Advanced Inventory in Enterprise, with FIFO enabled). There isn’t a way to calculate an average cost that is accurate when you have a negative quantity. QuickBooks comes up with a number, but it can vary wildly.
In the short term, if you have an item that you sell before you receive it (sell when negative), and in a short while you receive the item and bring the balance to zero or positive, QB will adjust the COGS value to an appropriate number. However, if a long period of time (which isn’t defined) passes between the sale and the receipt, QuickBooks will make an adjustment to COGS to bring it to a proper balance, and this adjustment drives bookkeepers/accountants crazy. It is hard to audit.
However, what is worse, if you do NOT bring your quantity back to zero or better, QB makes some very odd calculations for COGS, and they are not corrected. I’ve seen some bizarre numbers be posted. You won’t be able to make sense of it. Again, because you can’t calculate a true average cost of items when there is a negative quantity on hand.
The Accountant edition of QuickBooks has a feature that tests for these kinds of issues. Any CPA who is a Certified ProAdvisor should know about this, and be able to use it to find problems.
FIXING the problem is tough. One step is to bring balances back to proper values (quantity on hand) and ALWAYS keep them up to date. Making adjustments to handle this can be very complicated, and it is hard to work out things in your financial statements. And this won’t do any good unless you can keep your inventory records accurate. ALternately, if you can’t keep accurate balances, why do you have these as inventory parts? There is no value in that if you don’t have accurate quantities. In that case, I would make the inventory parts inactive (after adjusting their quantity to a zero balance on hand) and create new parts to replace them, as non-inventory parts.
This is a very general description, there are a lot of details to attend to. You need to work with someone who understands inventory and these issues in QuickBooks, or you’ll get off into some other potential problems.
Thanks for all your help Charlie.
I am one from the younger generation working at this business – And many I work with become overwhelmed easily with new software. Whereas I look at it and need to know why it tells me I’m right when I’m wrong.
But maybe I can start an inactive movement here until we get this sorted out. So Thanks again!
I am a very simple non profit organization, People donate children’s clothing to me. I gave each item a $1.00 cost in the item list.
When I give this item out to a chocolate, I invoice the School using the inventory part. This will give me HOW MNY ITEMS I gave out to each school on my reports.
I have set up Donations In = Vendors
I have set up Donations Out = Customers.
I have worked with G/L accounts most of my life, but find QB 2013 for nonprofit just puts inventory into G/L accounts automatically.
I am guilty of putting in neg items, but just did a physical inventory, hoping this will correct all of the crazy -$ amounts in inventory Assets and Cogs. I entered all items in an Inventory Adj. transaction. This just doubled my COGS.
Please tell me the basic procedure of bringing in Donated items. – if I go through all of the Vendor steps, I will end up with MONEY TO DEPOSIT. which I do not have ANY Money- just items.
Same with Invoices to Schools. – if I go though al of the processes it mess up my G/L accounts.
Bottom line. I do not need inventory items – just need the count of each piece out linked to School receiving it.
Please help me.
Mary, I don’t work with non-profits, so I’m not sure of all the ins and outs of that kind of operation. However, unless it is really important to know how many of a particular item you have on hand at any time, it seems to me that you don’t want to use an “inventory part” item. You can use a “non-inventory part” item, for example. No quantity on hand to worry about. You can STILL see sales quantities of these items in the “sales by item summary” report, for example, for these kinds of items.
That would simplify your process, probably.
However, you need to find a QuickBooks advisor who can sit down with you and go through your process and help you work out these details. It isn’t something that I can do for you through blog comments.
Sorry, auto correct is a pain. Should read ” When I give this item to a SCHOOL” not chocolate.