01 / 12The Margin Report Did Not Reconcile

Inventory CostingExcelSUMPRODUCT

FIFO Inventory Cost in Excel: Cost of Goods Sold by Lot

The Margin Report Costed October's Reels at the Average of Four Prices and Put 19,180.00 of Cost on 18,980.00 of Stock, Because Nobody Used Up the Lots in Order

4 Nov 202610 min read

UsesMIN, MAXSUMPRODUCTSUMAVERAGE

October's margin report for the ER-20 extension reel said 31.16%. The stock it was built on did not add up. Cost of sales and closing stock came to 19,180.00, and the business had only ever paid 18,980.00 for those reels.

ER-20 reel, October 2026Figure
Units available, four lots1,400
Paid for them18,980.00
Units sold950
Cost of sales as reported13,015.00
Cost of sales, FIFO12,460.00
Margin as reported31.16%
Margin, FIFO34.09%

Scope. SUM, SUMPRODUCT, MIN, MAX, AVERAGE, ROUND and SUMIFS work in every version of Excel since 2010, on Windows, Mac and the web. Nothing here needs dynamic arrays.

Throughout, the lots are in A1:H6: lot in A, date received in B, units in C, unit cost in D and lot cost in E. Columns F, G and H are the FIFO working. Units sold in October are in C8, and the method comparison sits in A10:H14.

01The Margin Report Did Not Reconcile

Corran Supply buys the ER-20 reel in lots, at whatever the factory charges that month. Its stock policy is first in, first out: the oldest reels are treated as sold first, and their cost leaves the books with them.

October opened with two lots left over, 400 reels bought at 12.50 and 300 at 13.20. Two more arrived during the month, 500 at 14.00 and 200 at 15.10. The company sold 950 reels at 19.90 each, for 18,905.00.

The analyst costed the month in one cell, =AVERAGE(D2:D5)*C8. That is 13.70 a reel and 13,015.00 of cost of sales. The 450 reels left were valued at the same 13.70, or 6,165.00.

Four Lots, One Sale, Four Ways to Cost It

Corran Supply's October costing for the ER-20 extension reel, in A1:H14. Rows 2 to 5 are the four lots in date order: units in C, unit cost in D, lot cost in E. F counts the units received before each lot, G the units FIFO takes from it and H their cost. C8 holds the 950 reels sold at 19.90, and rows 11 to 14 cost the same sale four ways, with cost of sales plus closing stock in G.

ABCDEFGH
1
Lot
Received
Units
Unit cost
Lot cost
Units before
Units used
FIFO cost
2
Lot 1
28/08/2026
400
12.5
5000
0
400
5000
3
Lot 2
16/09/2026
300
13.2
3960
400
300
3960
4
Lot 3
02/10/2026
500
14
7000
700
250
3500
5
Lot 4
21/10/2026
200
15.1
3020
1200
0
0
6
Total
1400
18980
950
12460
7
8
October sales
950
18905
9
10
Method
Units
Unit cost
Cost of sales
Closing stock
Cost + stock
Margin
11
AVERAGE of prices
950
13.7
13015
6165
19180
31.16%
12
Latest price
950
15.1
14345
6795
21140
24.12%
13
Weighted average
950
13.56
12879.29
6100.71
18980
31.87%
14
FIFO lots
950
13.12
12460
6520
18980
34.09%

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

The controller added the two figures before signing the pack. They came to 19,180.00, and the four lots had cost 18,980.00. The sheet had costed 200.00 of reels that nobody ever bought.

Scenario: Put the four lots in A2:E5 and 950 in C8. Enter =AVERAGE(D2:D5)*C8 in E11 and =AVERAGE(D2:D5)*(C6-C8) in F11. Check that =E11+F11 reads 19,180.00 while E6 reads 18,980.00.

Try it in the grid

02AVERAGE of Prices Ignores the Quantities

AVERAGE gives every lot the same vote. The 200 reels at 15.10 count as much as the 500 at 14.00, so the result leans towards whichever lots happen to be small and dear.

Every costing method has to pass one test: cost of sales plus closing stock equals what was bought. A plain average of prices fails it whenever the lots differ in size.

MethodUnit costCost of salesClosing stockCost + stock
AVERAGE of prices13.7013,015.006,165.0019,180.00
Latest price15.1014,345.006,795.0021,140.00
Weighted average13.5612,879.296,100.7118,980.00
FIFO lots13.1212,460.006,520.0018,980.00

Costing everything at the latest price fails worse, by 2,160.00. Two methods pass. Which of those two applies is a policy decision, and Corran's policy says FIFO.

Scenario: Fill in the four method rows in A11:G14, then enter =G11-$E$6 in I11 and fill it down to I14. Check that it reads 200.00 for the average of prices, 2,160.00 for the latest price and 0.00 for the other two.

Try it in the grid

03FIFO Uses Up the Oldest Lot First

First in, first out means the 950 reels sold are the 950 oldest reels, whatever was physically picked. Fill the sale from lot 1, then lot 2, and stop part of the way through whichever lot runs the order out.

LotUnitsUnit costUnits usedFIFO cost
Lot 140012.504005,000.00
Lot 230013.203003,960.00
Lot 350014.002503,500.00
Lot 420015.1000.00
Total1,40095012,460.00

The cost of sales is 12,460.00, or 13.12 a reel. The 450 left are the newest: 250 from lot 3 and all of lot 4, worth 6,520.00. Together that is 18,980.00, to the cent.

04Count the Units Ahead of Each Lot

Each lot needs one number before it can be costed: how many units sit ahead of it in the queue. That is a running total of column C that stops one row short:

=SUM($C$2:C2)-C2

In F2 it returns 0, because nothing came before lot 1. Filled down, it reads 400, 700 and 1,200. The anchored $C$2 keeps the start fixed while the end of the range moves down with the row.

Scenario: Enter =SUM($C$2:C2)-C2 in F2 and fill it down to F5. Check that F5 reads 1,200, the units in lots 1 to 3.

Try it in the grid

05Take Each Lot's Share With MAX and MIN

A lot supplies whatever is still unsold when its turn comes, but never more than it holds and never less than zero. MIN sets the ceiling and MAX sets the floor:

=MAX(0,MIN(C2,$C$8-F2))

$C$8-F2 is the demand still open when the lot's turn arrives. Lot 1 sees 950 and is capped at its 400. Lot 3 sees 250 and gives exactly that. Lot 4 sees −250, which MAX lifts to 0.

Then H2 is =G2*D2, filled down, and =SUM(H2:H5) is the FIFO cost of sales. Check that G6 equals C8 before you trust it. If the units used fall short of the units sold, the stock ran out.

Scenario: Enter =MAX(0,MIN(C2,$C$8-F2)) in G2 and =G2*D2 in H2, filled down to row 5. Check that H6 reads 12,460.00, then change C8 to 1,500 and watch G6 stop at 1,400.

Try it in the grid

06The Whole FIFO Cost in One Cell

If the sheet cannot spare working columns for G and H, SUMPRODUCT does the same job. MAX(0,x) does not work element by element on a range, but (x>0)*x does:

=SUMPRODUCT(D2:D5*((C8>F2:F5)*(C8-F2:F5)-(C8>F2:F5+C2:C5)*(C8-F2:F5-C2:C5)))

The first half is the demand still open at each lot. The second half is the demand still open once that lot is empty. The difference is the lot's share, and the formula returns 12,460.00.

This is the same shape as a marginal commission ladder. Each lot is a band, its unit cost is the rate, and the units sold play the part of the sales figure.

Keep the working columns if you can. The one-cell version still needs F2:F5. Columns F and G let a reviewer see the 250 units taken from lot 3; the one-cell formula shows only the answer.

07Prove It: Cost Plus Stock Equals Purchases

Closing stock under FIFO is what each lot has left, units minus units used, at the lot's own cost:

=SUMPRODUCT(C2:C5-G2:G5,D2:D5)

That gives 6,520.00. Put the identity beside it so the sheet checks itself every month:

=ROUND(H6+J2-E6,2)=0

TRUE means every cent bought is either in cost of sales or still on the shelf. The ROUND stops a binary rounding difference from turning a correct sheet FALSE.

Scenario: Enter =SUMPRODUCT(C2:C5-G2:G5,D2:D5) in J2 and =ROUND(H6+J2-E6,2)=0 in J3. Check that J2 reads 6,520.00 and J3 reads TRUE.

Try it in the grid

08Weighted Average Is a Method; AVERAGE Is Not

There is a legitimate average cost method. It divides the money by the units, not the sum of the prices by the number of lots:

=E6/C6

That is 13.557 a reel. Cost of sales is 12,879.29 and closing stock 6,100.71, and the two add back to 18,980.00. The plain AVERAGE of 13.70 is the same idea with the quantities thrown away.

With rising prices the two valid methods differ by 419.29 this month. FIFO charges the old, cheap reels to sales and leaves the new, dear ones in stock, so it reports the higher margin.

Use the method your accounts use, and keep it. IFRS (IAS 2) allows FIFO or weighted average cost and does not allow LIFO; US GAAP also allows LIFO. Switching method between months moves the margin without a single reel being sold differently.

This weighted average is periodic: one rate for the whole month. A perpetual system recalculates after every delivery, so its figure differs slightly when sales fall between deliveries.

Scenario: Enter =E6/C6*C8 in E13 and =E6/C6*(C6-C8) in F13. Check that they read 12,879.29 and 6,100.71, and that they add up to E6.

Try it in the grid

09Cost Each Sale From Units Sold to Date

October's 950 reels left in two orders: 600 on 10 October and 350 on 25 October. Cost each sale as the FIFO cost of units sold to date, minus the FIFO cost of the units sold before it.

SaleUnitsSold to dateFIFO cost to dateCost of this sale
10/10/20266006007,640.007,640.00
25/10/202635095012,460.004,820.00

The second sale takes the last 100 of lot 2 at 13.20 and 250 of lot 3 at 14.00. That is 1,320.00 plus 3,500.00, the same 4,820.00 the subtraction gave.

In the sheet, point the $C$8 in the share formula at a sold-to-date cell, so one calculation serves every sale. Then check that every sale had stock to draw on.

The formula does not read the dates. A sale made before lot 4 arrived could still be costed from lot 4 if the units to date reach it.

Compare units sold to date with =SUMIFS($C$2:$C$5,$B$2:$B$5,"<="&A20), the units received on or before the sale date in A20. Sold to date must never be the larger.

Scenario: Change C8 to 600 and note H6. Change it back to 950 and subtract the first figure from the new H6. Check that the difference is 4,820.00, the cost of the 25 October sale.

Try it in the grid

10Five Traps

  1. AVERAGE of unit prices. It weights a lot of 200 the same as a lot of 500.
  2. Costing everything at the latest price. In a rising market it overstates cost of sales and stock together, here by 2,160.00.
  3. A share with no floor. Without MAX(0,…), lot 4 takes −250 units and knocks 3,775.00 off the cost.
  4. Lots out of date order. FIFO reads the rows top to bottom, so sort the lots by date received first.
  5. Selling stock you did not have. If G6 is short of C8, cost of sales is missing the extra units.

11Four Exercises

  1. Reproduce it. Cost the month with AVERAGE and confirm cost plus stock reads 19,180.00.
  2. Fix it. Build columns F, G and H and confirm the FIFO cost of sales is 12,460.00.
  3. Prove it. Add the closing stock and the identity check, and confirm J3 reads TRUE.
  4. Split it. Cost the 10 and 25 October sales separately and confirm 7,640.00 and 4,820.00.

What to Take Away

Every price on Corran's sheet was right, and so was every unit count. One AVERAGE threw the units away, and the books carried 200.00 of reels nobody paid for.

Whatever method your policy names, test it the same way: cost of sales plus closing stock must equal what you bought. FIFO passes that test by construction, one lot at a time.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge