Inventory
How to Calculate Inventory Value and COGS in Excel
⏱ 9 min read · Inventory · COGS · Costing
Home›Blog› How to Calculate Inventory Value and COGS in Excel
Knowing how much your inventory is worth is fundamental to running a business — it affects your balance sheet, your tax return, and your decision about what to order next. Excel can handle inventory valuation using two common methods: weighted average cost and FIFO.
Method 1 — Weighted Average Cost (Simplest)
This is the easiest method and works well for most small and medium businesses.
Average Unit Cost = Total Cost of All Units / Total Units on Hand
Total Inventory Value = Average Unit Cost × Qty on Hand
In Excel:
=SUMPRODUCT(QtyReceived, UnitCost) / SUM(QtyReceived)
=SUMPRODUCT(C2:C100, D2:D100) / SUM(C2:C100)
Method 2 — FIFO (First In, First Out)
FIFO assumes you sell the oldest inventory first. This means your remaining inventory is valued at the most recent (usually higher) prices.
| Date Received | Qty Received | Unit Cost | Total Cost |
|---|
| Jan 1 | 100 | $4.00 | $400 |
| Feb 1 | 100 | $4.50 | $450 |
| Mar 1 | 100 | $5.00 | $500 |
If you have 150 units on hand under FIFO, they consist of the 100 units from Feb ($450) plus 50 of the Mar units (50 × $5 = $250). Total value = $700.
Calculating COGS in Excel
COGS = Beginning Inventory + Purchases - Ending Inventory
=BeginningInventoryValue + PurchasesThisPeriod - EndingInventoryValue
Total Inventory Value Formula
=SUMPRODUCT(QtyOnHand, UnitCost)
=SUMPRODUCT(E2:E100, F2:F100)
This multiplies each item's quantity by its cost and sums everything up — giving you total inventory value in one formula.
Which Method Should You Use?
| Method | Best For | Complexity |
|---|
| Weighted Average | Most businesses, interchangeable goods | Low |
| FIFO | Perishables, fashion, electronics | Medium |
💡 Consistency matters: Pick one method and stick with it. Switching methods requires IRS approval in the US and can affect your taxes significantly.
FAQ
Does the costing method affect my taxes? ▼
Yes significantly. FIFO typically results in higher inventory values and lower COGS during inflationary periods, meaning higher taxable profit. Weighted average smooths this out. Consult your accountant before choosing a method.
How do I handle items with different costs from different suppliers? ▼
Track each receipt separately with its own cost, then use weighted average: total cost of all receipts divided by total units received. This gives you a blended cost that reflects your actual purchase prices.
Related Articles