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.
(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.
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 firstname.lastname@example.org 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 Google
Sites That Link to this Post
- QuickBooks 2012 Inventory Lot Tracking : QuickBooks and Beyond | September 12, 2011