FIFO vs LIFO vs Weighted Average: Inventory Valuation

How you value your inventory affects two things directly: your reported profit and your tax bill. These aren’t the same number, and the method you choose legally changes both. The good news: you can model all three in a spreadsheet before committing to one with your accountant.

Here’s what each method actually means, and how to calculate it.

The Three Methods Explained Without Jargon

FIFO (First In, First Out) assumes the oldest inventory you bought gets sold first. If you bought widgets for $10 in January and $12 in March, and you sell one in April, FIFO says you sold the $10 one. Your cost of goods sold (COGS) is $10, and the $12 widget remains in inventory.

LIFO (Last In, First Out) assumes the newest inventory gets sold first. Same scenario: you sell the $12 widget. COGS is $12, and the $10 widget stays in inventory. Higher COGS = lower profit = lower taxes. LIFO is not allowed under international accounting standards (IFRS) and cannot be used if you file internationally, but it’s permitted under US GAAP.

Weighted Average takes the average cost of all inventory and applies it to each sale. If you have 10 widgets at $10 and 10 widgets at $12, the weighted average cost is $11. Every sale costs $11 regardless of when you bought it.

Which Method Saves You the Most Tax?

In an inflationary environment (prices rising over time — which describes most of the last few decades), the ranking is:

1. LIFO saves the most tax — higher COGS, lower profit, lower taxes
2. Weighted Average is in the middle
3. FIFO pays the most tax — lower COGS, higher profit, higher taxes

In a deflationary environment (prices falling), the ranking flips. FIFO saves you more.

The catch: LIFO also makes your balance sheet look worse because your inventory is valued at older, lower prices. If you need to show strong assets for a loan application, FIFO makes your inventory look more valuable.

Talk to your accountant before choosing. Switching methods later requires IRS approval (Form 3115) and is a headache.

Setting Up FIFO in a Spreadsheet

Create a Purchase Ledger tab with columns:

• Date
• Units Purchased
• Cost Per Unit
• Total Cost

Then create an Inventory Layers section that tracks each purchase batch separately. When you record a sale, you consume the oldest layer first.

Date	Units In	Cost/Unit	Units Remaining	Layer Value
Jan 5	50	$10.00	50	$500
Mar 12	30	$12.00	30	$360

When you sell 60 units, FIFO logic is:

• Consume all 50 from Jan 5 layer: COGS = $500
• Consume 10 from Mar 12 layer: COGS = $120
• Total COGS for this sale: $620
• Remaining inventory: 20 units at $12 = $240

Formula to calculate remaining units in a layer: =MAX(0, UnitsInLayer - MAX(0, TotalUnitsSold - UnitsInPriorLayers))

This gets complex with many purchase batches. Most businesses using FIFO seriously should consider inventory software, but for small product counts, this works.

Setting Up Weighted Average in a Spreadsheet

Weighted average is simpler to maintain. You recalculate the average cost every time you make a new purchase.

Column	Formula
Units on Hand	Running total
Total Inventory Value	Running total cost
Weighted Avg Cost	=Total Inventory Value / Units on Hand

When you sell: COGS = Units Sold × Weighted Avg Cost.

When you purchase new inventory: add units and cost to the running totals, then recalculate the average. The new average blends old and new costs automatically.

Example:

• You have 100 units at $10 average ($1,000 total value)
• You buy 50 more at $14 ($700)
• New totals: 150 units, $1,700 value
• New weighted average: =$1,700 / 150 = $11.33

Every sale going forward costs $11.33 per unit until you buy more inventory.

A Simple Comparison Model

Before committing, build a comparison model for your last 12 months of activity:

1. List all purchases with dates and costs
2. List all sales with dates and quantities
3. Calculate COGS under each method
4. Calculate ending inventory value under each method
5. Compare resulting gross profit

The difference in gross profit is the difference in taxable income. Multiply by your marginal tax rate to see the actual dollar difference in taxes. This is the number that matters.

If the tax difference between methods is $500/year, don’t lose sleep over it. If it’s $8,000/year, that’s a conversation worth having with your accountant immediately.

Next Step

Build the comparison model described above using your last 12 months of purchase and sales data. Even a rough version with estimated averages will show you which direction to go. Then bring those numbers to your accountant and ask specifically: “What are the trade-offs for my situation, and would switching methods require IRS approval?”