QuickBooks 2012 FIFO Inventory
One of the most common complaints I hear about QuickBooks inventory is that is calculates the cost of items by using an average costing method. Many inventory based businesses want to use another costing method, most commonly FIFO (first-in, first-out), but to get that you would have to use a third party add-on product that manages inventory entirely outside of QuickBooks. Until, that is, QuickBooks 2012. Now we have an option to add FIFO inventory in QuickBooks Enterprise Solutions if you subscribe to the Advanced Inventory feature.
Average costing is simple (for a detailed description see Understanding QuickBooks Inventory Cost). You buy an item at a cost, add the total value of that purchase to the total value of what you have on hand already, then divide that new total value by the quantity you have. That is the average cost. When you sell that item, the cost per unit that is posted to COGS is that average cost.
FIFO costing is also a simple concept, but a bit more difficult to implement. You buy an item at a cost and keep track of how many you bought at that cost. This creates “cost lots” of a given number of items and their cost, at a particular date. When you sell an item, you find the oldest “cost lot” that has some items left, and use those first.
Let’s run through a very simple example to show how this works, and how it changes costing in QuickBooks.
Inventory Costing Example
This is a simple example just to illustrate the difference of the two costing methods. This won’t cover all of the situations and issues that might come up.
I’ll start with an item, a “stapler”, where I have a zero balance on hand. I’ll enter four transactions:
- A bill (item receipt) for 5 units at $5.00 each on 9/1/2011
- A second bill (item receipt) for 5 units at $8.00 each on 9/2/2011
- An invoice for 4 units on 9/3/2011
- Another invoice for 4 units on 9/4/2011
We have added $65 to our inventory asset account through the purchases – the key will be how much we have left at the end of the sales.
If we perform these steps in QuickBooks when using average costing (the default mode), you can see that the average cost of the item is shown as $6.50. Note that the total value remaining in this case is $13.00 (two items at $6.50 each). The total posted to COGS is 8 units at $6.50 each, or $52.00.
Now, let’s take a look at the same situation but using the 2012 version of QuickBooks Enterprise Solutions with Advanced Inventory.
You can see a number of differences here. The key to look at is the Asset Value column. You can see that when we are done, we have $16.00 left in the inventory asset account, with $49.00 being posted to COGS. How was this calculated?
- The first sale consumed four of the items at $5.00 each ($20.00 to COGS).
- The second sale consumed one item at $5.00 and three items at $8.00 ($29.00 to COGS).
There is an avg cost column showing in the report. That is the new average cost based on what remains in inventory. In this case, 2 items and a total asset value of $16.00.
This is a very simple example – what is important to note here is that when we are done, each of the costing methods post different values to COGS, and leave you with different values remaining in the inventory asset account. However, if we had sold those last two remaining items (bringing inventory asset to zero in this case), the total amount posted to COGS would have been identical.
Using this option doesn’t create any additional work for you. You aren’t going to pick the “cost lots” to consume – all that happens behind the scenes.
Installing FIFO Inventory
Installation is easy. Note that if you have a large number of inventory transactions this conversion could take some time, so plan to do it at a time when you can afford to have everyone out of the system for several hours. This has to be done in single user mode.
- Remember, you must have QuickBooks Enterprise Solutions. This option is not available in Pro or Premier.
- You must purchase the Advanced Inventory feature from Intuit. This is an additional annual fee.
- Make a backup copy of your file before proceeding. You shouldn’t really have to, but I always make backups before making ANY change to my QuickBooks system that is going to have a significant effect on the transactions in the system.
- Select Preferences in the Edit menu, and choose the Items & Inventory preference.
- In the Items & Inventory preference select the Company Preferences tab, and click on the Advanced Inventory Settings button.
- At the bottom of the Advanced Inventory Settings window, check the box Use FIFO Starting on, and enter a conversion date in the date selector.
Selecting the proper starting date is very important. QuickBooks will take the average cost of each item on that date (using the standard average costing method) and the quantity on hand on that date and create the “first cost lot” for the item. One cost lot for each item, based on the information on that date. If you have any receipt transactions after that date, QuickBooks is going to use that information to create new “cost lots”, and it may recalculate your COGS postings for any sales after that date.
This is an important feature to be aware of. At the very least you should select a date that is at the beginning of your current fiscal year. If you select an older date you will be changing the calculations of COGS and your inventory asset account balance for older, closed periods, and that is something you normally would want to avoid. Don’t use a date older than the start of your current fiscal year.
Adjustments
(Additional material added after first publication)Based on a question (thanks, Murph), here is a bit of additional information. If you change a quantity using an inventory adjustment, how does this affect the “cost lots”? Here is a simple test. I have three cost lots to start with. I received 100 at $5.00, then 100 at $6.00, then 100 at $7.00.
First, an adjustment DOWN. I remove 50 items. The total cost removed is $250, which is $5.00 each. That comes from the first cost lot. That is what I would hope for.
Second, an adjustment UP, starting with the original three cost lots. The total cost added is $350, which is $7.00 each. That added to the last cost lot, and that again is what I would hope for.
These are very simple, controlled tests, but the indications are that things are working properly. Note that the date of the adjustment is key, as the adjustment has to use the cost lots available on the adjustment date.
Things to Think About
In general, I like this new feature if you need FIFO inventory valuation. However, it mind not be the best fit for everyone. I do think that there is room for improvement here, I hope that Intuit will continue to improve and refine the feature.
- As I said above, selecting the proper starting date is a key decision. Don’t use a date prior to the start of your current fiscal year.
- Changing to FIFO costing will alter the COGS and Inventory Asset postings for all existing transactions after that starting date.
- If you edit a bill (item receipt) from before the starting date then the cost/quantity in the “first cost lot” will be changed to accommodate any changes in the calculated average cost at that date. This will have a ripple effect through subsequent sales of that item as the lot calculations are changed.
- You can change your mind later and you can turn off FIFO costing. If you do, all of your COGS and inventory asset calculations will be changed to go back to the normal average costing method. I don’t recommend that you do this if you close a financial period and file tax reports based on FIFO, because going BACK to average costing will affect all transactions from that starting date.
- The “avg cost” column in the inventory reports is a bit misleading, although I don’t know of a better term. Note that this is not a number that you can use to compare the two methods of costing. This “avg cost” isn’t the same “avg cost” that you would get if you were not using FIFO.
My biggest concern about how Intuit has implemented this feature is that there is no “cost lots report”. You don’t have any way to actually see the “cost lots” to see how many items are in a particular lot, how many lots there are, what their cost is. To me, this is a key feature in any FIFO costing inventory system. You have to be able to see what the current lots are. It would also be a good idea to be able to see the specific “cost lot” information showing exactly what cost lots were used in a sale transaction. Without any of this we don’t have any way to audit QuickBooks. We have to assume that everything was done correctly. What if there is an error in the program? What if the file has been damaged and we have a data integrity issue? What if a user entered an incorrect value in some item receipt transaction in the past? The current implementation is too much of a “black box”, I want to be able to see what is going on. This is a key issue for me, and the lack of these reports is a point that you must consider before choosing to implement FIFO costing.
This is a great improvement to QuickBooks, and it seems to be working well in my preliminary testing.
Category: Manufacturing and Inventory, QuickBooks Tips/Tricks, Software Updates
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 (34)
Trackback URL | Comments RSS Feed
Sites That Link to this Post
- QuickBooks 2012 Inventory Lot Tracking : QuickBooks and Beyond | September 12, 2011
Anyone who elects this change and had not previously used FIFO will have to file Form 3115 with the IRS to obtain permission to change their method of accounting. Another drawback.
Thank you, Jay. An area I didn’t delve into, as I don’t deal with tax issues (other than for my own business). When dealing with changes like this with a client I always say “ask your financial and tax advisors” – I should have added that disclaimer to this article as well.
Thanks again Charlie for another great article. One thing about this was ‘how’ manual adjustments to inventory would be made; if for example you conduct a physical inventory and find that there are 3 fewer or 3 more which ‘cost lot’ adjustment is going to be applied. One would assume that if it is a reduction to inventory the oldest cost lot would be used, and if it is an increase in inventory that the newest cost lot would be used since the adjustment is date driven, but I never tested this in a small enough sample set to really see any difference. Do you have the scoop?
William “Bill” Murphy – Oklahoma City
Thanks for the question, Bill. The answer is “yes”, and the adjustments seem to work properly. I’ve modified the article to add a section on that, since I can’t put screen shots in comments.
A really well written and informative article Charlie.You could almost build a training course around this one.
Thank you, John, but who has the time? Lots of articles churning out here…
Charlie did you try a customer return after selling enough to have sold from two or more purchases? I am wondering if QB will track, as it should, which cost is being returned.
Does FIFO work with multiple u/m? Have you tried it where you have a case of 12 you order. A partial case remains from the first lot, and you sell two cases, what happens?
Two good points, Jim. By the way, you should get a “Gravatar” account (free), so we could get a picture of you in your comments…
Multiple UOM – no problem. If there isn’t enough in a cost lot to fulfill that case of 12, it finishes off the current (first) cost lot and then fills the rest out of the next cost lot.
Returns are an interesting issue. I’m not an accountant, so I’m not sure what the proper GAAP call is on this. How are you doing the “returns”? There are several ways. However, in general, returns entered as new transactions aren’t linked to the consumption transaction. If I have several cost lots, and sell some items, then issue a credit memo (for example), QuickBooks FIFO will return the item to the LAST or most current cost lot. I’m scratching my head on this one, I’m not sure what the accountants would want here…
We all know I am not an accountant, but if you expense the cost of the item sold at $25.00, and the return item is put back in inventory and pulls the most recent cost $33.00
Your COGS just went down, profit just went up, income taxes went up.
That in my view is a problem.
Well, Jim, the only thing we have truly established is that neither you nor I are accountants…
I’ve been reviewing some older software that I’ve worked with that used the “cost lots” method of FIFO inventory. They all worked the same way, if you did a credit memo kind of return. And you didn’t mention what method you were using for a return…
The general idea is that unless you have a very specific inventory system that is tracking each particular item, you have to use lots in some way. IN THIS particular case, the return is acting like a new purchase, in a way. The return is being added back to inventory in the MOST RECENT cost lot, so it is being returned at the most current purchasing cost that you have on record. I can see how that might be what is desired in some situations. Looking at the older inventory systems I can see how that really was the only reasonable and feasible way to do it. Creating another cost lot for that returned item, at the old cost, has a lot of ramifications in some situations.
Yes, it affects your financials. But again, I’m not sure what the proper accounting procedure is for this.
The way the system I use does this is that when you sell out of 2 cost lots, the COGS tracked with the sale becomes the average cost of the SKU sold. Items are still pulled from the system in a FIFO manner, but when they hit the sale, the aveage cost/item is recorded.
Then when an item is returned, it is returned at that cost. This is pretty much necessary since unless the item uses a serial number, there is no real way to know exactly which item was returned.
Thank you Charlie for another great article. Few comments:
Customer refunds: yes, the items are returned to the inventory at the last (as of the date of refund) acquisition cost. This is by design.
Adjustments: all comments are spot on for quantity adjustments. Even more interesting, though, is the value adjustment. In short, when adjusting value down, the most expensive lots are adjusted first. This is designed to accommodate the “Lower of Cost or Market” principle. Effectively, the highest lot price after such an adjustment is the Net Realizable Value. Adjusting value up works symmetrically, raising the cost of least expensive lots first.
Converting file to FIFO: “plan to do it at a time when you can afford to have everyone out of the system for several hours” is a bit of an exaggeration. As of R3, even a very large company files should convert in mere minutes.
Thanks again for exploring and explaining FIFO inventory in QuickBooks 2012!
Thank you, Yuliy. From your login I see that you are with Intuit, although I’m not sure we’ve met. Your post doesn’t say anything about that. Might I ask what your position is there? That might help other users understand the level of knowledge you have of the product.
The comment on FIFO was based on information provided to me by Intuit in the pre release (not sure if it was beta or release candidate). You reference R3, which is interesting, as that is the first public mention of R3 that I’ve seen. However, if R1 still has an issue with this, my statement stands, as R1 is all that is available to anyone at this point.
I’ll have to think about the comment on adjustments – my first thought is that I don’t like that, but it isn’t something that I’ve given a LOT of thought about. Interesting…
Charlie, I am one of the engineers who worked on FIFO implementation. All opinions expressed, however, are mine and not of Intuit.
Re: R3: correct, nothing is officially announced yet… but did anyone really expect that QuickBooks 2012 will stop at R1?
Conversion performance: in my tests a large (~1GB) company file converted to FIFO in ~20 minutes in R1. The next slipstream will do the same in ~45 seconds. In no release this particular conversion takes “hours”. Converting for Enhanced Inventory Receiving is another matter — it does indeed take longer. Perhaps that was the information provided to you.
Re: value adjustment: please feel free to contact me directly if you’d like to propose alternative implementation. I appreciate your input.
We need Scott B. to weigh in here on this valuation topic, but as I understand things, you can’t change valuation as you wish, you have to ask and receive permission from the IRS and it has to cover the entire year.
Lower of cost or market is a tax time valuation, but the requirement is to track inventory costs by an approved costing method. Then at tax time you can use lower of cost or market on an item by item basis assuming you have used that same valuation system last year or you filed a 3115 and received permission to change inventory valuation methods.
If you look at IRS Pub 538 page 17, it splits is up, Identifying cost is one section, and valuing inventory is another. Lower of cost or market is only in valuing, not in identifying.
Rustler, that’s exactly right. Switching from Average Cost to FIFO is a Form 3115 procedure and a one-time [or]deal for most businesses. Value adjustment, on the other hand, is a regular transaction, either at tax-time or as needed. We don’t enforce rules — from QuickBooks point of view the value of the inventory can be adjusted at any time, either up or down, on per-item basis. Under Average Cost value adjustment was trivial — just record the new value of the entire (item) inventory, and average cost changes appropriately. Under FIFO the question is what cost lots to adjust and by how much. Lower of Cost or Market was one approach that made sense — especially since it is the commonly accepted accounting principle for adjusting inventory e.g. for tax purposes. There are other ways, but we felt that we should support LCM as a bare minimum — so that’s how value adjustment under FIFO got implemented. Comments and suggestions are certainly welcome.
Yuliy: I commented on “R3″ because there has been no official public acknowledgement that there is an R3 in progress. I’ve speculated, but you are an Intuit representative and that is the first public statement I’ve seen about this. No surprise that there is an R3, but some people might wonder that R3 is being tested even before R1 has been released to anyone other than ProAdvisors (and that is another mess…). And, again, my statement on how long it would take was based on statements made by Intuit in the testing program. I’m glad that it doesn’t take a long time.
Yuri, I understand that intuit is not in the business of enforcing tax rules. LCM is a tax only valuation, it is not a costing system. In order to do LCM, you first have to have cost, and LCM is a tax time only method of valuation.
My concern is that users will start changing numbers to suit themselves, and one day an audit is going to hurt badly. Whether that bounces back at Intuit is anyone’s guess.
Value adjusting under average cost was only, should only I mean, be used to add extra costs to the base cost of an item. In that context it was needed, and in that context it most likely is still needed in 2012.
I only have 2012 premier, so I can’t play with the advanced features (I wouldn’t pay the subscription fee even if I had it, just not necessary for my business)and that is why I ask Charlie so many questions.
Thanks for chiming in, good to know someone from Intuit reads Charlies stuff too.
Charlie, I am happy to discuss how QuickBooks works on this blog — hopefully this is productive and helps your audience. We may need to find another forum to discuss how software is built — this is my work and my passion, and I can talk to it till your ears turn blue
, but this is hardly relevant here. Let’s just say that building software takes time, there is a sizable pipeline of multiple stages and there is a backlog of new features and improvements for every release including slipstreams. Could we leave it at this?
Returning to the original question, all I am trying to say is that the conversion time should be minutes for most users in R1, and seconds in the next slipstream release. (My numbers are for illustration and comparison only, since every computer setup and capabilities will be different. I can’t promise specific performance.) You may have been told “hours” either in reference to another feature, or out of abundance of caution. Which is OK, as I believe that most users who took it literally will be pleasantly surprised.
Hope this helps.
Yuily: I think that there is ’nuff said on this particular topic. And, just so you know, I understand software development, having been in that business since about 1977…
charlie, great job. I was just wondering why it took intuit forever, to make this FIFO option possible. It is quite disappointing that this is even only available in Enterprise, advance inventory not in Pro and Premier. if you compare QB to Tally, all the features below are basic and common across.
1. inventory valuation- average, FIFO, LIFO, Standard Costs
2. Duties + landed costs- it apportions the costs automatically either by value of items or qty.
All these features are available in all versions.
If QB wants to compete on the international market it needs to sit up and address basic but functional issues confronting users than spend time to decorate reports. Am just wondering how many small business can afford enterprise + advance inventory + annual fee. These make prospecting difficult for we ISPs.
Richard, I can only speculate on why this is only found in Enterprise with Advanced Inventory, I don’t have any inside info on that. Thanks for your comments!
Am a CPA in Kenya and currently using QB 2004 coz my employer refuses to buy advanced versions citing user friendliness in that he’s been used to this version and fear of losing data once we upgrade to a recent version. What facts do i need to convince him out of his prejudices.
Miri, I can’t give you details because I don’t know what the needs of the client are, and what the budget is. However, note that QuickBooks Enterprise has essentially the same mode of operation and user screens as you see in QuickBooks Pro and Premier. Moving from one to the other is easy to understand – they are the same product at their core. However, I will note that the 2013 product (for ANY of the versions) is going to look very different than the 2004 product. Lots of changes over the years.
My question is, if i run for example the 2012 version, will it prompt me to upgrade the current version? Will i be able to retrieve the current data or i will lose it? Thanks.
Miiri, in general, if you have the same national version of QB 2004 and QB 2013, when you open a 2004 file with the 2013 product it will allow you to open the file and it will try to convert the data. If your 2004 data is in good shape, it will convert. However, if there are any problems in the 2004 data it might not convert correctly. I don’t recall if the 2004 product has a “rebuild data” feature – but if it does then you would need to make a backup of your file, then do the “rebuild data”, and clean up any errors that might appear in the qbwin.log file, before converting. Also, the jump from 2004 to 2013 is a big one, most people who do these kinds of conversions will suggest doing it in smaller jumps – 2004 to 2006, then 2006 to 2013 (or maybe even smaller jumps). That avoids some problems that MIGHT occur. Of course, that works only if you have the 2006 version available. There are people who specialize in doing these kinds of conversions and cleanups, but of course they charge a fee.
It is simple to test this if you have a separate computer with 2013 installed – carry the 2004 file to that system and open it, see what happens. You might be lucky!
I have a question that I hope someone here can address. Let me preface it by stating that I have only the most passing familiarity with QB and accounting systems in general; I am performing some IT consulting for a client, and this question has come up so I have taken it upon myself to look into it.
The client inventories and then sells items, and the selling price for each instance of an item *must* be linked to their cost for that item. So if they buy 10 widgets at $5.00 and then another 10 at $8.00, and then they sell 12 widgets on one invoice, ten must be priced based on their $5.00/unit cost, and the remaining two must be priced based on the $8.00/unit cost.
Obviously, FIFO costing will take care of tracking the cost lots, but as far as I can tell, this only affects the *costing* of inventory, not the *pricing*, which is what this client needs. For further illumination, the client ia a non-profit, so selling price must be a 0% markup over cost.
I would be most appreciative if anyone has any insight into whether this sort of cost-linked pricing can be implemented in QB with AI. Thanks in advance.
There isn’t a pricing feature in QuickBooks that is going to automatically handle that. If you have a small number of items you can create a separate item for each “cost lost” that you have, but that creates a lot of extra work and other hassles.
Charlie -
Thank you for your swift reply. The number of items and volume of purchases and sales, along with the necessity for make creating invoices straightforward, makes it impractical to set up each cost lot as a separate item, unfortunately. I wish QB could have a pricing option to mark prices to the cost of the relieved item, based on a markup (percent, fixed amount, whatever). This would seem to be relatively easy to add; I hope Intuit is listening (Yuliy, are you there?) …
Meanwhile, I am open to any other suggestions as to how this price-linked-to-cost binding can be accomplished.
It does, to a degree, based on the “cost” field (Enterprise 13), but that isn’t the FIFO cost lot cost. Support for things based on FIFO is minimal.
Dear Charlie,
I am using QuickBooks 2013 Premier, what will I do to get the Quickbooks Enterprise Solution. And how much it cost us.
Thank you and I am waiting for your response.
Sincerely,
Sharon
Sharon, I don’t know what the arrangements are for getting Enterprise in Dubai. It isn’t something that I can help you with here. I don’t know if the Advanced Inventory feature would be available overseas.
Thank you Charlie!