FIFO Inventory Costing in Excel, Step by Step

FIFO inventory costing in Excel takes one small table and two formulas. FIFO means first in, first out: when you sell, the oldest stock is assumed to go first, so each sale is costed at the oldest purchase prices. You list your purchase lots in date order, let MIN and MAX work out how many units each lot supplies, and SUMPRODUCT turns that into the cost of goods sold. This guide is for small business owners, bookkeeping students and anyone asked to build a FIFO sheet in a job test.

The figures below are examples to show the method. How you must value stock for your accounts or taxes depends on your country's rules, so check with an accountant before you rely on a method for reporting.

What FIFO inventory means#

FIFO is a rule for deciding which purchase price applies to the units you sold. Prices change over time, so the same product can cost 4.00 in January and 5.00 in March. FIFO says the units sold first come from the oldest lot.

Three terms come up again and again:

Under FIFO, the units left at the end are always the newest ones, so ending inventory is valued at the latest prices.

Set up the purchase lots table#

Put one row per purchase, oldest at the top. The order matters: every formula below reads the table from top to bottom.

Put the total units sold for the period in G2: 170. You can type it, or add it up from a sales log with SUMIF, for example =SUMIF(Sales!B:B,"Mugs",Sales!C:C). The inventory tracker guide shows how to keep that log.

Doing it by hand first helps. The first 100 units come from January, the next 50 from February, and the last 20 from March. That's 100 + 50 + 20 = 170.

How many units each lot supplies: MIN and MAX#

One formula, filled down column D, does that split for you. In D2, type:

=MIN(B2,MAX(0,$G$2-SUM(B$1:B1)))

Fill it down to D4 (Windows: Ctrl+D; Mac: ⌘D). Read it from the inside out:

1. SUM(B$1:B1) adds the quantities of all the lots above this one. In row 2 that range holds only the header, so the result is 0. In row 4 it becomes SUM(B$1:B3) = 150.
2. $G$2-SUM(...) is how many units are still left to cost after the earlier lots: 170 in row 2, 70 in row 3, 20 in row 4.
3. MAX(0, ...) stops the number going negative once the sales are used up.
4. MIN(B2, ...) caps it at the lot size, because a lot can't supply more units than it holds.

The $ signs are what make the fill work. $G$2 stays fixed on the units sold. B$1 keeps the start of the range on row 1, while the end moves down one row at a time. The MIN function guide explains how MIN picks the smaller of two values.

Cost of goods sold with SUMPRODUCT#

SUMPRODUCT multiplies two ranges row by row and adds the results, which is exactly the FIFO cost of goods sold. In G3:

=SUMPRODUCT(C2:C4,D2:D4)

It works out 4.00 × 100 + 4.50 × 50 + 5.00 × 20 = 400 + 225 + 100 = 725. Both ranges must be the same size, or you get #VALUE!. The SUMPRODUCT function guide and the longer guide to SUMPRODUCT cover the details.

The average FIFO cost per unit sold is =G3/G2, which gives about 4.26. For more on per-unit costs, see how to calculate cost per unit.

Ending inventory value#

Ending inventory is the total cost of everything you bought, minus the cost of what you sold:

=SUMPRODUCT(B2:B4,C2:C4)-G3

Total purchases are 400 + 225 + 400 = 1,025, so ending inventory is 1,025 − 725 = 300. Check it: 60 units are left, all from the March lot at 5.00, and 60 × 5.00 = 300. When the check matches, your formulas are right.

You can also add a "Left" column with =B2-D2 to see the units remaining in each lot: 0, 0 and 60.

FIFO vs weighted average cost#

Weighted average cost uses one price for every unit: total cost divided by total units. Here that's 1,025 ÷ 230 = about 4.46 per unit, so 170 units sold cost about 757.61.

When purchase prices rise, FIFO gives a lower cost of goods sold and a higher stock value, because the cheap old units are sold first. When prices fall, the opposite happens. The weighted average guide shows the SUMPRODUCT ÷ SUM formula, and the profit and loss statement guide shows where cost of goods sold goes in a report.

Keep the FIFO sheet working each month#

A FIFO table stays accurate if you follow a few rules:

• Add new lots at the bottom. The formulas assume date order. Inserting a lot out of order costs sales at the wrong price.
• Use cumulative sales. G2 should hold all units sold since the oldest lot still listed, not just this month's sales. To report one month, work out COGS for sales to date and subtract last month's figure. A running total column makes this easy.
• Watch for overselling. If G2 is bigger than the total of column B, the sheet costs only the units you have. Add a check: =IF(G2>SUM(B2:B4),"Sold more than bought","OK").
• Keep one table per product. FIFO is tracked item by item. A product code column and SUMIF can pull each product's sales into its own table.
• Round at the end. Keep full unit costs in column C and round only the final totals.

Practise it#

The FIFO sheet is mostly careful sums and one SUMPRODUCT, so the core skills are adding up the right rows and locking the right references. Practise a department budget in a live spreadsheet to drill totals and $ references, then practise SUMIF to pull units sold per product from a sales log. Each lesson checks your formula instantly. Then try the exercise on this page to cost the 170 units yourself.