COGS Formula in Excel: FIFO vs LIFO vs Average Cost, Worked Side by Side

Guide · Updated July 2026 · ~13 min read

Your gross margin came in at 42.8% this quarter against 47.3% last. Selling prices did not move, no supplier raised a price mid-quarter, and the stock count agreed to the warehouse sheet. Yet 4.5 points of margin — nearly 900 on 20,000 of revenue — has gone somewhere.

Nothing was miscounted. The units in closing stock were right; what changed was the cost attached to them. Cost of goods sold is not a number you observe, it is one you compute from a policy choice, and the same units bought at the same prices produce three legitimately different COGS figures depending on the policy. This post runs one small inventory dataset through FIFO, LIFO and weighted average cost in Excel — real formulas, real cells — so the three answers land side by side, then builds the monthly COGS schedule that feeds a P&L.

The formula everyone quotes has an unknown in it

The standard cost of goods sold formula:

COGS = Beginning inventory + Purchases − Ending inventory

In Excel, with beginning inventory in B2, purchases in B3 and ending inventory in B4:

=B2+B3-B4

Three of those four numbers are easy. Beginning inventory is last period's agreed closing figure; purchases come off the ledger. But ending inventory is not a fact you look up — it is units on hand multiplied by a cost per unit, and the cost per unit is exactly what FIFO, LIFO and average cost disagree about.

The middle line deserves its own name, because it is what the three methods split:

Goods available for sale = Beginning inventory + Purchases
COGS = Goods available for sale − Ending inventory

Goods available is fixed. Whatever you do not put in ending inventory, you have expensed — which is why COGS on the P&L and inventory on the balance sheet are the same pounds seen from two statements (balance sheet structure in Excel).

Before the methods, get the boundary right:

Purchase returns, rebates and settlement discounts reduce purchases rather than sitting in income. Freight-out is the one people argue about; either treatment survives review provided you apply it consistently. A service business has no inventory but still has a cost of sales line: direct staff time and subcontractors on billable work.

One dataset, three answers

One product — call it W-200 — over one quarter, in a sheet named Layers starting at A1:

Column E is =C2*D2 filled down; row 6 is =SUM(C2:C5) and =SUM(E2:E5). Goods available for sale is 15,800.00 across 1,150 units.

The trading facts are identical under all three methods. Units sold in H2, selling price in H3: 800 units sold, leaving 350 in closing stock (=C6-H2), at 25.00 each for revenue of 20,000.00 (=H2*H3).

Rising costs — 12, 13, 14, 15 — are what make the methods diverge. In a flat-price quarter all three agree, which is why the difference only shows up when you least want it to.

FIFO in Excel

First in, first out: the oldest layers sell first, so ending inventory carries the newest costs. A cumulative column and one MIN/MAX expression does it — no VBA, no manual layer-picking. In F2 a cumulative units column, in G2 the units consumed from each layer:

F2:  =SUM($C$2:C2)
G2:  =MAX(0,MIN(C2,$H$2-(F2-C2)))

Fill both down to row 5. F2-C2 is the units sitting above this layer, so $H$2-(F2-C2) is how many of the 800 remain unallocated when you reach it; MIN caps that at the layer size, MAX stops negatives once the 800 is used up.

The total is one SUMPRODUCT — =SUMPRODUCT(G2:G5,D2:D5).

FIFO COGS = 10,550.00. Ending inventory is the 350 units left in the newest layer: 350 × 15.00 = 5,250.00, and 15,800 − 10,550 = 5,250 confirms it. Always run that check — goods available less COGS must equal ending inventory, or an allocation column has slipped.

LIFO in Excel

Last in, first out: the newest layers go to COGS, so ending inventory carries the oldest costs. Same construction, cumulating upward — I2 a reverse-cumulative column, J2 the units consumed:

I2:  =SUM(C2:$C$5)
J2:  =MAX(0,MIN(C2,$H$2-(I2-C2)))

The anchoring flips: C2:$C$5 locks the bottom of the range, so the sum shrinks as you fill down.

LIFO COGS = 11,450.00, and ending inventory is the untouched 200 opening units at 12.00 plus 150 of the January layer at 13.00 = 2,400 + 1,950 = 4,350.00. Again 15,800 − 11,450 = 4,350.

Weighted average cost in Excel

One blended rate for everything, and the shortest formula on the page. SUMPRODUCT weights each unit cost by its layer size; divide by total units:

Average cost per unit:  =SUMPRODUCT(C2:C5,D2:D5)/SUM(C2:C5)
COGS:                   =$H$2*avg_cost
Ending inventory:       =(C6-$H$2)*avg_cost

15,800 ÷ 1,150 = 13.7391 per unit. COGS = 800 × 13.7391 = 10,991.30; ending inventory = 350 × 13.7391 = 4,808.70.

The mistake to avoid is =AVERAGE(D2:D5), which gives 13.50 — the simple mean of the four prices, ignoring that the 400-unit layer at 15.00 outweighs the 200-unit layer at 12.00. It produces a COGS of 10,800 that reconciles to nothing. Weighted average means weighted, and SUMPRODUCT does the weighting.

The three, side by side

Same units, same purchase invoices, same count sheet:

900.00 of gross profit and 4.5 percentage points of margin separate the outer columns, and no transaction differs between them. In a rising-cost period FIFO charges the oldest, cheapest units to COGS — flattering profit, leaving the balance sheet near replacement cost — while LIFO charges the newest, dearest units, depressing profit and carrying inventory at stale costs. Falling costs reverse both effects exactly. Weighted average sits between them by construction, which is precisely why many finance teams choose it.

Margin is profit over selling price, not over cost; the markup-versus-margin conversions are in percentage formulas for accountants.

Periodic vs perpetual: the second average

There is a fork inside the average method. The 13.7391 above is a periodic weighted average: one rate struck at period end over everything available. A perpetual (moving) average recalculates after every purchase, so sales made before a purchase are costed at the older, lower rate.

Same quarter, sales interleaved — 100 units sold on 05/01, 250 on 20/01, 200 on 25/02, 250 on 20/03:

10,721.35 against the periodic 10,991.30 — 269.95 apart, same method name, same 800 units. In Excel it is a running-balance layout rather than a layer table: units in C, unit cost in D, units out in E, running balances in F (units) and G (cost), COGS in H.

COGS on a sale row:  =IF(E3>0,E3*(G2/F2),0)
Units balance:       =F2+C3-E3
Cost balance:        =G2+C3*D3-H3

The G2/F2 deliberately reads the previous row's average — that is the whole mechanic. Say which variant you use in the accounting policy note; "weighted average cost" alone does not tell a reviewer which number to expect.

Which methods you are actually allowed to use

Before you pick the one that flatters this quarter. The summary below reflects the position as of 2026; standards and tax rules are amended, so confirm the current text of the standard you report under — and take the tax points to your own adviser rather than a blog post.

• IFRS (IAS 2) does not permit LIFO. FIFO and weighted average cost are the permitted cost formulas; specific identification is required for items not ordinarily interchangeable. Under IFRS the LIFO column above is an analytical exercise, not an option.
• US GAAP permits LIFO, used mainly because it reduces taxable income when costs rise. The trade-off is the conformity requirement: broadly, a US business using LIFO for tax is also expected to use it in its financial statements — no showing investors FIFO profits while paying tax on LIFO ones. The detail here is genuinely technical, so check it against current guidance before adopting or changing a method.
• Specific identification costs each unit individually — right for serialised, high-value stock, unworkable for 40,000 identical fasteners.
• Consistency. Your choice applies to that class of inventory every period. Switching is a change in accounting policy with disclosure and comparative restatement attached, not a quiet spreadsheet edit.

Why gross margin moves when the count did not

Four causes, in the order they actually turn up:

1. The method itself, as above — including an unannounced change of it. Test this first when margin swings with no operational explanation.
2. Ending inventory is a plug, so its errors are COGS errors, pound for pound. Because COGS is goods available less closing stock, understating closing stock by 5,000 overstates COGS by exactly 5,000 — and it reverses, because the next period opens with the understated figure and overstates profit there. One count error moves two periods in opposite directions.
3. Classification drift. Freight-in booked to distribution one month and purchases the next moves cost across the gross-profit line without touching the bottom line. So does a production supervisor's salary migrating between COGS and admin.
4. Cut-off. Goods received on the 31st but invoiced on the 2nd sit in the count with no matching purchase — inventory up, no cost recognised, margin flattered until the accrual catches it.

The disciplined answer to "why did it move" is a decomposition into price, volume, cost and mix effects rather than staring at two totals; the mechanics of that bridge are in variance analysis in Excel.

How HISAB 360 helps

The arithmetic above is easy. Assembling the inputs is not: purchases by month by account, goods-received cut-off, freight-in split from freight-out. Usually that means exporting a general ledger, a purchases listing and an inventory valuation, then stitching three files together before a single SUMPRODUCT gets written.

HISAB 360 is a paid Excel add-in that shortens the first half. It connects to QuickBooks Online, Xero, Zoho Books, Odoo, FreshBooks and Sage Accounting with read and write access, pulling bills, purchase invoices and GL activity straight into worksheets — so the purchases figure in your COGS schedule is a query you refresh, not a CSV someone re-exports each month. Its AI panel is docked inside Excel and reads the workbook you have open: ask for a twelve-month COGS schedule with beginning inventory linked to the prior month, SUMIFS pulling purchases from the ledger sheet by account and date, and a gross margin row underneath, and it writes those formulas into the live sheet. It will also build the FIFO allocation columns or a Power Query step that appends monthly purchase extracts.

Honest limits. Windows desktop Excel only — Microsoft 365 or 2016+, 64-bit — not Mac, not Excel on the web, and paid, with a 15-day trial including 50 AI credits and no card. It gives you the schedule and the ledger data; the method choice, the count and the cut-off judgement stay yours.

A monthly COGS tracker that feeds the P&L

One product is the teaching case; the working artefact is a twelve-column monthly schedule, months across the top and this down the side:

Three things make it trustworthy. Row 3 is a link, never a typed number — beginning inventory that does not equal last month's closing is the commonest break in a COGS schedule. Row 8 is the only manual input, so keep the valuation workings on a visible sheet beside it. And add a check row: =C7-C8-C9 must return zero across all twelve columns.

Building it on a ledger extract keeps it repeatable — the date-and-account SUMIFS pattern works against any tidy GL export, and the extraction routine is in getting a general ledger out of QuickBooks into Excel. Rows 9 and 11 then drop onto the trading section of the statement; layout and subtotals for that are in building a P&L in Excel, and the P&L template has the margin formulas already wired.

The formulas on one page

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.