Upon receiving the file, I ran the usual Verify Data routine. No problems found. I checked the program information window (press F2) and looked to see if there were a lot of data fragments. That number wasn’t too bad. I tried creating a portable file and restoring it. I ran a Rebuild. I ran the Verify again, and still no problems.
Then, I recalled her saying, when discussing how she used QuickBooks, that she was using the option in Advanced Inventory for FIFO. Could that be her issue? I started poking around her file and realized that it was probably the way she was looking at the reporting that was causing her confusion.
QuickBooks FIFO Demystified
Before I get to the FIFO reports, let me explain the difference between how QuickBooks manages ‘average costing’ versus how ‘FIFO costing’ works. QuickBooks desktop products normally track inventory costing via ‘average’ cost, or by averaging the costs of all of the quantities that are in stock divided by the total cost of those purchases. QuickBooks takes the last purchase of on-hand stock, and any prior purchases, in order until all quantities are accounted for. This ‘average’ cost is then posted when the item is sold. It doesn’t change until a new purchase, at a different cost, is made.
You have the option of switching to FIFO costing when using the optional Advanced Inventory module that is available for QuickBooks Enterprise.
Note that by default, users of QuickBooks Online are using FIFO costing, not average costing.
With FIFO costing, each purchase become a ‘costing layer,’ where QuickBooks tracks the quantity purchased and the unit cost of that purchase. As you consume items in an invoice the COGS value that is posted will be the cost of the oldest ‘costing layer’ that remains in the system, until that batch is completely used up. The cost of the sale of the item doesn’t depend on the total value of the items in stock, but instead on the oldest remaining costing layer for that item.
To illustrate the difference between ‘average’ costing, and FIFO costing, consider the case of an electronics distributor that sells computers. They buy the computers from several suppliers:
Purchase Date |
Quantity |
Cost |
Extended Cost |
Oct 15 |
100 |
$250.00 |
$25,000.00 |
Nov 15 |
150 |
$240.00 |
$36,000.00 |
Dec 15 |
200 |
$220.00 |
$44,000.00 |
By December 15th the distributor has received 450 units in stock at an average cost of $233.33 each ($105,000/450 units), assuming that there were no sales in that period. If he sells half of his stock after that date, using average costing he will have a cost-of-goods-sold of $52,499.25.
Under FIFO, however, the costs are pulled as items are sold from the oldest purchase date, until that costing layer is exhausted, before moving on to the next. In this scenario, the distributor’s COGS would come out to $55,000 ((100 X $250) + (125 X $240)).
While that difference might not seem significant (about $2,500), when you apply this over thousands of products over millions of dollars of goods, this can greatly change the cost showing up on the books.
What happens when you turn on FIFO in QuickBooks?
When you turn on FIFO costing in the Advanced Inventory module in QuickBooks Enterprise, an additional report becomes available: the FIFO Cost Lot History by Item report. This report breaks down each item by transaction and attempts to show the cost postings being made each time. This can be a difficult report to follow, and was certainly tripping up my client when comparing it to other reports.
Figure 1: FIFO Cost Lot History by Item Report. Vendor and Customer name have been left out.
To create an example, I used one of the Enterprise sample files. The current date setting of the file was Dec. 15th, 2018. Although the file had prior year activity, I turned on the FIFO costing as of 7/1/18. This causes the new report to be generated (see Fig. 1). I have left off the vendor and customer names to save room on the report and to simplify the example.
Note that the report only includes balances and transactions from the date of when FIFO began. Any existing balances of items on hand at the date that FIFO is enabled are placed into one costing layer.
The invoices are the ‘driving force’ in this report. As each batch of items is being sold, sections of each of the cost layers are being pulled. The bills are listed multiple times because more than one invoice can ‘pull’ from a purchase. The second through fifth invoices all pulled from the same vendor bill.
As products are consumed or ‘used up,’ the ‘Acq Date’ and ‘Acq Trans Type’ fields show the impact on the purchase transactions and their correlation to the invoices in the ‘Dist Trans Type’ fields. The columns highlighted in red show the units costs, quantities and extended amounts involved in these item receipts and bills (inventory quantity adjustments can also play a part). The invoices, along with distribution dates and sales amounts, are listed on the right.
While the FIFO Cost Lot History by Item report is the only designated QuickBooks FIFO report, it isn’t really the only report impacted by FIFO. This was the reason why our client was confused. Figure 2 is an extract of the history of the item using a different report, the Inventory Valuation Detail:
Figure 2: Transaction history of the part, ‘Interior Wood Doors’ on the Inventory Valuation Detail report.
In the report above (fig. 2), the green line represents the separation in the costing history of this item, based on the date that we changed to FIFO. Above the line, the item was recorded at average cost, and below the line using FIFO costing. The area in the blue box represents the progression of the ‘calculated average’ cost field of the item and the item’s Asset Value. This is where it really gets interesting.
Follow the numbers in the blue box with me as I go through the transactions.
- On May 12th, the client had 4 units at approximately $57.22 for a total value of $228.90 (slight rounding difference).
- On Oct. 25th, he purchased 16 units for $960, or $60 per piece. The new, total asset value of $1,180.90 divided by the new quantity of 20 produced an average cost of $59.45. This sounds right. On Oct. 27th, he added four more, slightly more expensive doors for a total asset value of $1,848.90, and now had a quantity of 24 at an average cost of $77.04 (again, slight rounding).
- Then, on Oct. 30th, he sells four doors, and his calculated average actually goes UP to $81.00! How is that possible? Because at this point, they are using ‘first-in, first-out’ costing. When he sold the four doors, he was not selling four doors that cost him $77.04 a piece, but the four doors that cost him $57.22 left over from the first ‘layer’ that was available in his inventory.
The change in the Asset Value column shows this, but isn’t immediately clear. The item’s asset value before the sale, $1,848.90 minus the value after the sale, $1,620, is the cost of the four items in inventory at the beginning of the costing: $288.90. So, while the ‘Calculated Average’ column continues to calculate average costing, the ‘Asset Value’ column takes into account the value of the assets remaining after FIFO costing is taken out. This is where users get confused on what QuickBooks is telling them. Think of the Calculated Average field as an estimate, and the Asset Value field of the total of the remaining inventory on a FIFO basis.
The confusion doesn’t end with the Inventory Valuation Detail report. Even after FIFO is turned on, the individual Item Edit screens will continue to reflect the average cost of the item.
Fig. 3: Item Edit screen of Interior Wood Door
It’s About the Layers, Not the Lots
One thing to note about turning on FIFO costing in QuickBooks is that the costing will be accounted for separately from the tracking of ‘lots’, another feature that is available in the Advanced Inventory module. This feature allows users to track items down to specific lots received from suppliers, or created in the manufacturing environment. When the user enters an item on a sales document, he or she can select from any lot available in the list. However, QuickBooks will still continue to pull the oldest item from the inventory, whether that’s from the picked lot or not.
Don’t Forget the IRS When Making the Switch
Companies are required to report to the Internal Revenue Service any change to the inventory costing method. Look up and file IRS Form 3115, Application for Change in Accounting Method, to notify the IRS of the change (or contact your
What If You Want to Stay on FIFO But Drop Advanced Inventory?
Switching to FIFO-costing also creates an interesting conundrum for the QuickBooks Enterprise user. Average costing is the default for all desktop copies of QuickBooks, including Enterprise. The FIFO-costing option is only available with the Advanced Inventory module. If the user decides to switch to a lower level of Enterprise that doesn’t have Advanced Inventory, then all of the costing data reverts back to average costing. Since the value of inventory becomes ‘re-costed,’ the timing of when costs apply to sales, even of prior periods, will change. What might have cost $10 under FIFO, might become $9 under average costing, even for items sold years earlier. This can in turn, affect financial reporting and taxes, even for prior
I have also heard from other colleagues that switching from average to FIFO and back to average doesn’t affect the Inventory Asset and COGS numbers showing up on the Balance Sheet and Profit & Loss reports under the original average cost method. However, this has not been my experience! I have seen the numbers for these two accounts change when this done, albeit slightly. I haven’t researched this enough to know exactly what got changed, but I have definitely seen it happen.
Is It Worth Going to FIFO?
Many wholesalers and distributors prefer FIFO costing because it tends to match how their products flow through the warehouse, making matching cost and sales price more accurate. And, in some cases, there may be tax advantages to using this method. Understand what you are getting into when switching to FIFO costing, however.