Average cost or last purchase cost? Understanding where the gap comes from

Two costs, two separate questions

Weighted average cost answers this question: if I write down what the stock I currently hold cost me in total, per unit, what comes out? That is a question about the past, and it is accounting's question. Stock valuation, cost of goods sold and closing inventory value are all built on it.

Last purchase cost answers a different question: if I resupplied this product today, what would it cost me? That is a question about the future, and it belongs to pricing and buying. When you print a new price ticket, the fact that the old batch in your hand was cheap is not your concern; the replacement cost is.

When the two numbers diverge, people usually argue about which is correct. That is the wrong argument. The right one is whether the gap between them can be explained. If it can, the system is healthy; if it cannot, there is a grain or a definition problem in the data model.

The arithmetic of the gap: three batches, two numbers

Take a single product code in a grocery chain: a 2-litre bottle of extra virgin olive oil. There are 200 units in total across the warehouse and the branches, and those 200 units came from three separate purchase batches.

On this table the weighted average cost works out as (100 × 380 + 60 × 428 + 40 × 468) / 200 = 82,400 / 200 = ₺412. Last purchase cost is the unit price on the most recent invoice: ₺468. The ₺56 between them comes entirely from the three batches having entered at different prices. There is no error here, only time.

₺56 can look small. With a selling price of ₺560 excluding VAT, gross margin comes out at 26.4 per cent on average cost and 16.4 per cent on last purchase cost. Same product, same day, 10 points apart. When a category manager sees those two figures on two different screens, they lose trust in the system — when the only thing that needed doing was to write on each screen which cost definition it uses.

• Batch A — January purchase, 100 units, ₺380 each
• Batch B — March purchase, 60 units, ₺428 each
• Batch C — June purchase, 40 units, ₺468 each

The hidden items that move the average

The invoice unit price is not the whole cost. The weighted average has to absorb the items added to the invoice later, and those items are usually the real source of the gap.

What these items have in common is that they arrive after the goods have entered the warehouse. The goods land on 12 June, the freight invoice comes on 3 July. If the costing routine only works forwards, the cost of units sold between 12 and 30 June stays understated. If it corrects retroactively, last month's margin changes overnight and nobody is told.

The right behaviour is neither of those; it is to record both. Cost corrections are written as a separate movement type, with the period the correction belongs to and the date it was processed held in two different date fields. That way, when someone asks why a report changed, the answer sits inside the data.

• Allocated purchase expenses such as freight, customs and insurance
• Price-difference invoices and supplier rebates that arrive later
• Currency differences and payment-term charges on imports
• Returns: at which cost they come back is undefined in most systems
• Whose account shrinkage, waste and count variances are written to

Where cost lives in the data model

Trying to hold cost in one place in a star-schema warehouse is the most common mistake. Cost has two different lives at two different grains.

The first is the movement grain. One row of the stock movement fact table corresponds to a single movement line: which branch, which product, which date, which movement type, how many units, the unit cost in force at that moment and the average cost in force at that moment. Cost is written into this table frozen at the moment the movement is recorded. It is not calculated afterwards, because a cost calculated afterwards changes the past.

The second is the daily grain. One row of the cost snapshot fact table corresponds to branch, product and date, and carries the closing average cost and last purchase cost for that day side by side. Stock value reports read from this table; they do not scan the movement table.

A single stored procedure should feed both tables. The business logic sits in the database, not in an ORM layer. The reason is practical rather than ideological: the costing routine runs sequentially over a set of hundreds of millions of rows, and when the business rule and the data are in the same place it is both faster and auditable in one place.

• DimProduct, DimBranch, DimDate, DimSupplier dimensions
• FactStockMovement — grain: a single movement line
• FactCostDaily — grain: branch x product x date
• Cost corrections as a separate movement type, with their own accounting date

Which decision wants which cost

Use last purchase cost when setting prices. Pricing below replacement cost means the margin turns negative once the stock runs out. In periods of sharp currency movement especially, designing a discount campaign against average cost turns yesterday's cheapness into today's loss.

Use weighted average when reporting period profitability, stock value and cost of goods sold. Here the aim is to describe the past accurately, not to predict the future.

In supplier negotiations, use both. The gap between average and last purchase is a summary of the price increases that supplier has applied over the last twelve months. The ₺56 between ₺412 and ₺468 is a number you can put on the table.

For allocation and transfer decisions, average cost is preferred, because valuing one product two different ways when moving it between branches creates a leak in stock value.

What to do

Write the definition first, then build the screen. For every cost field, keep a one-line definition, the name of the procedure that calculates it and the decision it belongs to, in one dictionary. A cost field without a definition should not reach a screen.

Show both costs side by side on screen and put the gap in its own column. Hiding the gap does not increase trust, it reduces it. Making the batch breakdown open when the gap column is clicked turns a months-long argument into a one-minute answer.

Never post cost corrections backwards silently. Make the correction a movement, keep its period and its processing time apart, and make sure that when the corrected period's report is re-run, the reason for the difference is visible line by line.

In Mindpera the Mind Cost module sets this separation up by default: average and last purchase cost are separate fields in the same fact table, both calculated in a stored procedure, and the gap breakdown can be opened down to batch level. The AI layer does not recalculate these numbers; it only turns into a sentence which batch and which expense line the gap came from.