FIFO, LIFO and weighted average: three ways to cost the same inventory

A business that buys the same goods several times a year at different prices has a question to answer at year end: which of those prices belong to the units still on the shelf, and which belong to the units it sold? FIFO, LIFO and weighted average are three answers. This page explains each one in plain English, works through one set of numbers under all three, and shows how to build the schedule in a spreadsheet so the formulas do the arithmetic.

What a cost flow assumption is

Picture a shop that sells glass jars by the carton. It starts the year with some cartons, then buys more in March, June and October. Each delivery costs a little more per carton than the last. By the end of the year the jars are all mixed together in the stockroom. Nobody wrote the purchase price on each carton.

The shop still has to split its costs in two. The cost of goods available for sale is everything it had to sell during the year: beginning inventory plus all purchases. Part of that cost belongs to the cartons still on hand, which is ending inventory, an asset on the balance sheet. The rest belongs to the cartons sold, which is cost of goods sold, an expense on the income statement.

A cost flow assumption is the rule for making that split. It does not have to match how the goods physically move. A shop can sell its oldest jars first and still use LIFO for its accounts. What matters is that it picks one method and uses it consistently.

Each batch of goods bought at one price is called a layer. Beginning inventory is the first layer, and each purchase adds another.

• FIFO (first in, first out): the oldest layers are treated as sold first. Ending inventory is made of the newest layers.
• LIFO (last in, first out): the newest layers are treated as sold first. Ending inventory is made of the oldest layers.
• Weighted average: every unit carries the same cost, the total cost of goods available divided by the total units available.

All three methods start from the same cost of goods available, and all three split it completely. Ending inventory plus cost of goods sold always equals cost of goods available. Only the split changes.

Worked example: one year of glass jars

Copperleaf Studio sells cartons of glass jars and uses a periodic inventory system, so it works out ending inventory and cost of goods sold at year end from a count. Its layers for the year were:

It sold 320 cartons during the year, so 568 − 320 = 248 cartons are left in ending inventory.

FIFO

Under FIFO the oldest cartons are sold first, so the 248 left are the newest. Start with the last purchase and work backwards until you have 248 units:

October layer, all of it: 45 × $31.50 = $1,417.50
June layer, the remaining 203: 203 × $30.50 = $6,191.50
FIFO ending inventory: $1,417.50 + $6,191.50 = $7,609.00

Cost of goods sold is what is left of the cost available: $16,944.00 − $7,609.00 = $9,335.00.

LIFO

Under LIFO the newest cartons are sold first, so the 248 left are the oldest. Start with beginning inventory and work forwards:

Beginning layer, all of it: 142 × $28.50 = $4,047.00
March layer, the remaining 106: 106 × $29.50 = $3,127.00
LIFO ending inventory: $4,047.00 + $3,127.00 = $7,174.00

Cost of goods sold: $16,944.00 − $7,174.00 = $9,770.00.

Weighted average

Under weighted average every carton costs the same:

Average unit cost: $16,944.00 ÷ 568 = $29.8310 (to four places)
Ending inventory: 248 × $29.8310 = $7,398.08
Cost of goods sold: 320 × $29.8310 = $9,545.92

The two results add back to $16,944.00. Keep the unit cost unrounded in the calculation; round only the dollar results.

The three methods side by side

Unit costs rose all year, from $28.50 to $31.50. FIFO leaves the newest, dearest costs on the balance sheet, so it gives the highest ending inventory and the lowest cost of goods sold. LIFO does the opposite. Weighted average lands in between. If prices were falling, FIFO and LIFO would swap places. When prices are steady, the three methods give almost the same answer.

How to lay it out in a spreadsheet

A cost flow schedule fits on one small sheet. Put the layers in rows 4 to 7: the layer name in column A, units in column B and unit cost in column C. Put units sold in B9. Then the results go below.

Here is what each formula does:

• B11 adds the units in all four layers and subtracts the units sold. The answer is the 248 cartons that should match the year-end count.
• B12 uses SUMPRODUCT, which multiplies each pair of cells (units × unit cost for each row) and adds the four products. It saves a helper column of row totals.
• B13 costs the whole October layer (B7 × C7), then costs the rest of the ending units, B11 − B7, at the June price in C6. This works because the ending units in this exercise are more than the last purchase but fewer than the last two purchases together.
• B14 takes ending inventory away from cost available. Working cost of goods sold out this way guarantees the two halves add back to the total.

For LIFO, change only the ending inventory formula so it starts at the top instead of the bottom: =B4*C4+(B11-B4)*C5 costs the whole beginning layer, then the rest of the units at the March price.

For weighted average, add a unit cost cell, =B12/SUM(B4:B7), then multiply: ending inventory is =B11*B13 and cost of goods sold is =B9*B13, where B13 holds the average. Leave the unit cost unrounded so the two results add up exactly.

To compare all three, put FIFO, LIFO and weighted average in columns B, C and D of the same row. Each cost of goods sold cell can then subtract its column's ending inventory from a fixed total: =$B$12-B14, filled right. The dollar signs keep the reference to the cost available cell from moving as the formula is copied.

Common mistakes and how to avoid them

These are the mistakes our exercises check for.

Using the other method's layers

The names are easy to mix up because they describe what is sold, not what is left. FIFO sells the oldest units, so its ending inventory is the newest layers. LIFO sells the newest units, so its ending inventory is the oldest layers. In the example, using the oldest layers for FIFO gives $7,174.00, which is the LIFO answer. When you compare methods in one sheet, the same slip shows up as FIFO and LIFO swapped between columns.

Costing every ending unit at one layer's price

There are 248 cartons left, but the October purchase holds only 45. Pricing all 248 at $31.50 gives $7,812.00 under FIFO, which overstates ending inventory. Under LIFO, pricing all 248 at the beginning cost of $28.50 gives $7,068.00, which understates it. Use up the first layer in full, then take the remaining units from the next layer.

Forgetting to subtract the units sold

Units in ending inventory are units available minus units sold. If B11 is just =SUM(B4:B7), it says all 568 cartons are still on hand, and the FIFO ending inventory comes out at $17,369.00, more than the $16,944.00 that was available. Cost of goods sold then goes negative, which is a clear sign something is wrong.

Taking a simple average of the unit costs

The four unit costs average $30.00, but the layers are different sizes. The June layer has 240 units and the October layer only 45, so June's price should count for much more. A simple average gives ending inventory of $7,440.00 instead of $7,398.08. The weighted average is total cost available divided by total units available, $16,944.00 ÷ 568. A quick check: with a simple average, ending inventory plus cost of goods sold no longer equals the cost available.

Which method should a business use?

That is a policy decision for the business and its accountant, and it depends on the accounting standards it reports under. Some standards do not allow LIFO at all. Whichever method is chosen, it should be applied the same way from year to year, so the numbers can be compared. FIFO often feels the most natural, because it matches the way stock is usually rotated on the shelf.

The method affects the numbers on both statements. Ending inventory sits in current assets on the balance sheet. Cost of goods sold sits on the income statement and drives gross profit. A method that gives a higher cost of goods sold gives a lower gross profit in the same year. For the year-end entry that brings these figures into the accounts, see periodic inventory and cost of goods sold.

Periodic and perpetual versions

The example on this page is periodic: the split is worked out once, at year end, from all the layers together. A perpetual system applies the cost flow at every sale instead, removing cost from the inventory record as each order ships. FIFO gives the same answer either way. LIFO and weighted average can give slightly different answers in a perpetual system, because each sale can only draw on the layers that had arrived by that date. To see how a perpetual system records each purchase and sale, read perpetual inventory journal entries.