There has been a substantial ‘chatter’ in a variety of forums about inventory valuation irregularities that occur when converting a historical file from QuickBooks Desktop, using Average Cost, to QuickBooks Online, using FIFO (First In, First Out). This article is NOT based upon research into any such specific problems; rather, it examines the concepts of how both cost methodologies are applied in inventory systems using the different methods, and explores how valuation differences, and COGS variations can occur when converting between methods. It further looks at steps that might be taken to force financial statements to balance during conversion from one system to another, and why such steps may then result in a scenario where the underlying ‘item based’ transnational records do not match the general ledger values being reported on the financials.
While one might think that QuickBooks (desktop) calculates the value of Inventory (current) Asset account value as the sum of each inventory item’s average cost multiplied by the quantity on hand in your company file at the time of the computation, it actually is just the opposite. QuickBooks maintains a count of the quantity of each item purchased, but it also maintains the value of each item purchased. When it comes time to calculate average cost, QuickBooks calculates average cost by dividing asset value by quantity on hand. QuickBooks then uses the ‘computed’ average cost to determine the COGS applied for the quantity of each item appearing on a sale, or consume (as in the case of an ‘Assembly Build.’)
FIFO cost is entirely different approach than the Average Cost methodology QuickBooks (desktop) uses. FIFO does not use the average cost of an item to determine COGS; instead, it assumes that the units you sell in a particular sale are the ones that you acquired earliest and that are still in stock – thus, first in, first out. The ones you buy first are the first ones out the door. If the price that your suppliers charges you for inventory is constant, and doesn't vary as time goes by, then there is no difference between computing either Inventory Valuation or COGS using average cost or using FIFO. But when your acquisition costs change, as is typical, perhaps even over short periods of time, there can be dramatic differences between average cost and FIFO.
Let’s look at a comparative example involving the two inventory valuation methodologies:
- You purchase six (6) Heavy-duty pumps with motors at a cost of $1500.00 each ($9,000 total); and then a month later you purchase three (3) additional Heavy-duty pumps with motors at a cost of $1700.00 each ($5,100.00). Since you haven’t yet had any sales the total value of this inventory is $14,100.00.
- QuickBooks would tell you that the present Average Cost of the Heavy-duty pumps with motors is $1566.66667 [$9,000.00 + 5,100.00 / 9].
- After all 9 units have been received, you sell 7 of the units. QuickBooks would tell you that the Cost-of-Goods-Sold value for the sale would be equal to $10,966.66669 (7 X $1566.66667].
- On the other hand, FIFO would tell you that you sold 6 units at $1500.00 and 1 unit at $1700.00 so the COGS for the sale would be $10,700.00 [(6X $1500) + (1X $1700).
- Your Inventory valuation for Heavy duty pumps with motors would be $3400.00 using FIFO and $3133.33334 using Average Cost.
Effects of Conversion from Average Cost to FIFO:
Now let’s assume that these three transactions, the two purchases of Heavy-duty pumps with motors, and the one sale of Heavy-duty pumps with motors, are the only 3 transactions in a file being converted from an ‘average cost’ environment to a FIFO environment.
- When we convert the first purchase no problem arises. Our historical record shows that the Inventory value of the purchase was $9000.00 based upon the fact that the average cost for these 6 items was the same as the actual cost of $1500.00 each.
- But when we convert the 2nd purchase a problem does arise resulting from the two methodologies. When we have 6 units in stock at $1500 each, a change occurs in average cost when the 3 additional units are purchased at $1700 each. The total value of the asset is $14,100.00 but the average cost has become $1566.66667. While FIFO also records the asset value of the inventory at $14,000.00 it preserves the ‘cost buckets’ as 6 at $1500 and 3 at $1700.00
- And when we convert the sale then the variation begins to mount up. Using Average Cost, the 7 units sold had a COGS of $10,966.66667; but using FIFO the 7 units now have a COGS of $10,700.00. The COGS of this transaction decreased under FIFO by $266.6667. The Profit & Loss for the sale (and the period in question) has just changed.
- And when we look at the remaining balance of the Inventory, and its valuation we also find a problem associated with the conversion. Using Average Cost, the 2 remaining units are valued at $3133.33334, but under FIFO the remaining inventory is valued at $3400.00. The Inventory Asset valuation increased by a difference of $266.66667. The Balance Sheet has just changed.
We only had 3 transactions in our history, and we already have differences. Now just imagine what happens when there are thousands, or tens of thousands of transactions that must be converted, and thousands of items and their cost histories also involved. The variations can be significant.
In order to avoid 'financial statement' re-statement, some conversions may attempt to force the new financials and the prior financials to ‘match’, using of some ‘phantom’ transaction(s) (not existing in the prior financial record) posted into the new accounting system to produce the balance between Inventory Asset and COGS. There is just one very important factor to consider. We cannot post such a transaction which actually impacts the ‘item’ itself. If we post the transaction on an item level basis then we are either forcing FIFO to be out of balance (to FIFO methodology) on an item computation basis (the cost buckets get extra dollars to balance), or we create a new cost bucket that will have the long term effect of changing the true FIFO values. But if the phantom adjustment is made at the ‘general ledger’ level then we do not impact the item values, but there is also problem with this approach, the Item valuation will no longer match the Balance Sheet and the cumulative balance of the COGS account will no longer match the sum of the historical transaction COGS.
In either case above the historical COGS on each transnational basis will remain either out-of-balance in comparison to the prior historical record, or the true FIFO computational methodology. If we apply an 'adjustment' at the historical level by posting an additional 'behind the scenes' transaction between Inventory Assets and COGS, we may get both the transaction record and general ledger to match, but the applicable item-level and total item-based inventory valuation will still be in out-of-balance with the general ledger.
Different accounting and inventory systems may handle the problems of conversion differently, and it will sometimes be difficult to research as to 'how' any adjustments are being made if in fact the two records appear to match after conversion. In no realistic scenario should the two values of Inventory Asset and COGS ever match after converting raw data data from Average Cost to FIFO.
Switching from Average Cost to FIFO can have a significant impact on all financial statements. Every business switching will need to consider whether it needs to restate its financial data for prior years to reflect the new method or only apply the new method to the current and future years. However, the business will normally report the gain or loss as a line item within the financials, and should always disclose the change in the footnotes to the financial statements.
Furthermore, you should be aware that you cannot simply convert from Average Cost to FIFO just because you want to, or because you change computer software. You must obtain permission from the Internal Revenue Service to change inventory valuation methods whether you are going from Average Cost to FIFO, FIFO to LIFO, LIFO to Average Cost, or any other methodology change. In many such cases you must specific report the effects, or potential effects, upon your financial periods for the period of change and any such changes that are carried forward into the current or future periods. This is an issue that can be easily overlooked by ‘typical software users’ making changes in accounting or inventory software on their own.
As Trusted Advisors to our clients, changes in Inventory Valuation methodologies is an issue that you need to be keenly aware of, counsel your client regarding, and be prepared to assist your clients in not only the physical process of making such change, but also the potential accounting restatements and required disclosures and reporting.