Welcome to Excel Highway, your go-to resource for smart, powerful Excel solutions!
In today’s post, we’ll show you how to calculate Cost of Goods Sold (COGS) in Excel using either FIFO (First-In, First-Out) or LIFO (Last-In, First-Out), all without any VBA code. That’s right — this template works purely with formulas, so it’s easy to maintain, fast to use, and super flexible.

Let’s dive into how this Excel inventory management template works and how you can use it to track multiple products, match sales to purchase batches, and get real-time cost analysis using FIFO or LIFO methods.

🔧 What This Excel Template Includes

The Excel file is organized into three sheets:

1. Sales Sheet – Where you input your sales data.
2. Purchases Sheet – Where you input all inventory purchases.
3. Balance Sheet – Where all the matching, calculations, and COGS tracking happens.

What makes this file especially powerful is that it supports multiple products, so you don’t have to create separate tracking files for each SKU. Everything is handled in one dynamic Excel workbook.

🧾 The Sales Sheet – Easy Input, Smart Output

This sheet is where you record all sales transactions. The structure is simple:

• Blue columns are for manual input:
  • Order Date
  • Product
  • Quantity Sold
• Orange columns are calculated automatically:
  • Order Number (generated using ROW()-1)
  • Value (based on FIFO/LIFO logic)
  • Price Per Unit

The formula-driven approach ensures that all values are calculated instantly as you enter new sales. The Order Number is key — it’s used throughout the workbook to track each sale line by line.

🛒 The Purchases Sheet – Record All Your Inventory Batches

The Purchases tab is where you list every batch of inventory you’ve purchased:

• Batch Number (can be auto-generated using ROW())
• Date of Purchase
• Product Name or SKU
• Quantity Purchased
• Price Per Unit

This sheet acts as your inventory ledger, and the data from here is matched to sales in the balance sheet, depending on whether you select FIFO or LIFO logic.

📈 The Balance Sheet – Where the FIFO / LIFO Magic Happens

This is the heart of the workbook.

Here’s what the Balance sheet does:

• It matches each sales line from the Sales sheet to the appropriate purchase batch (based on FIFO or LIFO).
• It pulls the correct purchase date, quantity, and unit cost using a mix of INDEX, MATCH, and helper columns.
• It keeps track of how much of each batch has already been used.
• It calculates:
  • Quantity used per batch
  • Remaining inventory
  • Total value sold
  • Price per unit sold

This entire mechanism is driven by formulas only — no macros, no VBA, and no Power Query required.

🧠 How the Batch Matching Works

The logic is elegant but a bit complex:

• A helper column finds the “Nth” occurrence of a product in the purchase list.
• As orders come in, batches are depleted one by one.
• The formula identifies the next available batch and allocates as much as needed to fulfill each order.
• If a batch can’t fully cover the quantity sold, the next batch is used — automatically.

This works beautifully for both FIFO and LIFO, depending on how you structure your data (earliest purchases at the top = FIFO, latest purchases at the top = LIFO).

👥 Multi-Product Support

You can track multiple SKUs in parallel — the template filters and matches purchases and sales by product, ensuring inventory tracking is accurate even when juggling different product types.

⚠️ What Happens If There’s No Inventory?

If you try to sell a product that hasn’t been purchased yet, the system shows:

• Quantity sold: 0
• Value: 0
• Price per unit: blank

This makes it easy to spot missing inventory so you can update your purchases accordingly.

🧪 Test Example: FIFO vs LIFO in Action

Let’s say you sell 500 units of a product.

• FIFO logic: First 350 units come from a batch at $7, and the remaining 150 units come from a batch at $9.
  • Final COGS: (350×7 + 150×9) = $4,800 → Price per unit = $8.00
• Want to test LIFO? Just reverse the batch order, and the calculations adjust automatically!

🧮 Extend It As Needed

You can drag the formulas in the Balance sheet down to support more sales rows. All logic is dynamic, so once you extend the formulas, the sheet will continue to work as expected.

📁 Who Should Use This Template?

This Excel COGS calculator is perfect for:

• E-commerce store owners
• Inventory and supply chain managers
• Bookkeepers and accountants
• Anyone needing COGS tracking in Excel without complex tools

📥 Get the Template

Want to try it out?

📢 Final Thoughts

With this formula-based Excel solution, you can accurately calculate cost of goods sold, manage multiple products, and analyze FIFO vs LIFO — all without touching VBA or macros.

If you’re serious about using Excel for inventory management, this tool is a game-changer.