Four Ways to Model Inventory in a Financial Model (and What Switching Costs You)

Inventory gets about ten minutes of thought in most models.

You are building a three-statement forecast, you reach the working capital schedule, and you need a number for inventory. You pick the method you have always used, you anchor the assumption on last year, you move on to the debt schedule. Nobody ever comes back to it.

That is usually fine. Then one day the model is used to size a revolver, or a buyer's diligence team asks why inventory is growing faster than sales, and the ten-minute decision turns out to have been worth several hundred thousand euros of cash.

Here is what the four standard methods actually do, run side by side on the same business, and what the choice costs.

The four methods

Method A: a percent of COGS.

Inventory = COGS × ratio

One assumption, set from history. Inventory grows exactly in line with cost of sales, forever.

Method B: days inventory outstanding.

Inventory = DIO / 365 × COGS

Mathematically the same shape as method A (a ratio is just DIO divided by 365), with one difference that matters: the assumption is expressed in days, so it is a number you can benchmark against a peer, negotiate with an operations team, and improve over time.

Method C: a unit build.

Inventory = units on hand × unit cost
         = units sold / 12 × months of cover × unit cost

You are no longer forecasting a balance, you are forecasting how much stock the business physically holds and what it costs. Two drivers, both operational.

Method D: ageing from flows.

Inventory = opening + purchases − COGS − write-off

You stop forecasting the stock entirely. You forecast the buying, and the stock is whatever is left. Almost nobody builds this one, and it is the only one that can tell you how old the stock is.

One practitioner note before the numbers. If you model monthly, feed the ratio methods with COGS over the trailing twelve months, never a single month. Otherwise inventory swings with the season instead of with the business, and every ratio you read back is meaningless.

Methods A, B and C infer the stock from COGS. Method D derives it from the flows. Most professional three-statement models use method B: the assumption is defensible and it degrades gracefully, because if you get the days roughly right the balance is roughly right.

They agree, right up until they don't

I built all four into one model, on the same business: a products company going from 40.0M to 64.6M of revenue, monthly over six years, gross margin around 42%, DSO 55 days, DPO 45 days. Same P&L, same everything, four parallel inventory builds and a switch that decides which one drives the balance sheet.

In the first full year the ratio methods land within 0.4% of each other:

That agreement is not a coincidence, it is the calibration. Every one of them was anchored on the same starting position, which is exactly what you do when you set up a model. At this point the choice looks like it does not matter.

Now run it five years, with one operational assumption: the business gets better at inventory. Days go from 68 to 62, cover goes from 2.24 months to 1.95. That is a modest improvement, the kind an operations team would call a good but unremarkable five years.

865,398 of spread on the same business. And notice what happened to method A: its implied days never moved. It could not. A fixed ratio has no way to express an efficiency gain, so it silently assumes the business never improves. That is not a modelling error, it is a modelling assumption, and it is one almost nobody makes on purpose.

The P&L will never tell you

Here is the part that makes this hard to catch.

Run the same model on methods A, B and C and the P&L is identical to the euro. Revenue 64,606,080, EBITDA 8,398,790, net income 4,874,093 in 2029, whichever of the three you picked. Inventory is a balance sheet stock. It does not touch the income statement.

The entire difference lands in cash. Since nothing else in the model changes, the cash gap is exactly the inventory gap, euro for euro:

Every euro that method A parks in the warehouse is a euro that does not reach the bank account.

So if you review a model by reading the P&L, which is what most people do under deadline, the inventory method is invisible. You have to look at the cash line, and you have to know what to compare it against.

Method D is the exception: it is the only one of the four where over-stocking can reach the income statement, through the write-off line. Which brings us to the interesting part.

The fourth method sees something the other three cannot

Methods A, B and C derive the stock from COGS. By construction, they have no idea how old it is. Ask any of them "how much of this warehouse has been sitting there for two months" and there is no answer to give, because age was never an input.

Method D forecasts purchases and lets the balance fall out. That changes what you can read. Under FIFO, what is left in the warehouse is always your most recent purchases, so age is a subtraction rather than a layer-by-layer simulation:

Stock older than 60 days  = MAX(0, inventory − purchases of the last 2 months)
Write-off (over 180 days) = MAX(0, stock before write-off − purchases of the last 6 months)

Two lines. No cohort table, no array formula.

What makes the ageing exist at all is the gap between when you buy and when you sell. In the model the purchase timing index is the sales seasonality index shifted two months earlier, because you buy for the peak before you sell it. Both indices sum to 12.00 per year, so they redistribute a year without changing its total.

Here is what that gives on a normal buying policy, buying 2% more than you sell:

The write-off never fires, and the stock ages anyway. Not once in 72 months.

That is the finding, not a modelling failure. A business that turns its stock in 60 days never holds anything for 180, so the accounting alarm stays silent for years while a quarter of the warehouse quietly gets old. Raise the purchase policy from buying 2% more than you sell to 8% more and two thirds of the stock is over 60 days, with the write-off still at zero.

Method D also starts from a different place: 3,509,000 at the end of 2025, against roughly 4.32M for the other three. It is not calibrated on a target ratio, so its opening balance is whatever the buying policy leaves. By 2029 it lands at 6,051,723, an implied 60 days, right between the days method and the unit build. The path there is the point, not the endpoint.

One honest limit: this is one inventory pool, no SKU, no seasonality inside the pool. If your question is ageing per SKU, this gives you the frame, not the answer.

How to choose

The honest decision rule is short.

Use a ratio (method A) when inventory is immaterial and you need the balance sheet to balance without spending time on it. A software company with a bit of hardware, a services firm with consumables. Be aware you are assuming permanent stasis, and say so in a note.

Use days (method B) when anyone will benchmark or challenge the model. Diligence, a lender, a board. Days of inventory is the language everyone in the room already speaks, and a change in days is a conversation you can have: who owns it, by when, at what cost. This is the default.

Use a unit build (method C) when mix or lead times are the actual question. If you are modelling a supplier switch, a new product line, or the working capital hit of moving from air to sea freight, a ratio cannot express any of that. A cover-months driver can.

Use ageing from flows (method D) the day someone asks why inventory is growing faster than sales. It is also the one to reach for when there is a real obsolescence risk, when the buying decision is the lever you are actually testing, or when the provision is going to be argued over in a data room.

And a fifth case worth naming: when you don't know yet. Which is most of the time at the start of a model, and which is the case nobody plans for.

The real reason nobody re-tests the choice

Not because analysts don't know the methods. They do, three of them are taught in every modelling course.

It is because switching one for another in a spreadsheet is not a small edit. The inventory line feeds the working capital schedule, which feeds the change in working capital in the cash flow statement, which feeds closing cash, which feeds the balance sheet. Swap a COGS × ratio cell for a units × cover × cost build and you have changed the shape of the block: new driver rows, new row references, and a balance check that breaks until you have chased down every link. Swap it for method D and you are adding a purchase schedule, a rolling balance and a write-off line that lands in the P&L.

A colleague once described re-basing an inventory schedule mid-diligence as "a Wednesday". They were not exaggerating, and they were not doing it twice.

So the choice made in minute ten becomes permanent, and the model quietly answers a question it was never tested on.

Ask an AI assistant to do the swap for you and it usually goes worse, not better. It rewrites the formulas it can see, hard-codes a couple of values it cannot resolve, and hands back a model where the balance check is off by 40,000 and nobody knows which of the twelve edits caused it.

What changes when structure and data are separate

The model behind this article holds all four methods at once, permanently. Not four copies of the file, four parallel computations in one structure. A single assumption cell decides which one flows into working capital:

Inventory (selected) = IF(switch = 1, method A,
                       IF(switch = 2, method B,
                        IF(switch = 3, method C,
                                       method D)))

Everything downstream reads that one line and nothing else. Change the switch from 2 to 4 and the working capital, the cash flow and the balance sheet all recompute. The balance check stays at zero on all four methods across all 72 periods, because the links were never method-specific in the first place.

That is not a spreadsheet trick, it is what you get when the logic of a model is a structure you can address rather than a grid of cells you have to rewire. Layerz keeps the two apart: the structure (what depends on what) is versioned and reusable, the data (this year's numbers, this scenario) sits on top of it. An agent driving the model over MCP changes a driver without touching the links, because it never had to hold the links in its head.

And the Excel export is still a normal, auditable workbook with live formulas, which is what you actually send to the other side.

Try it on your own numbers

The model is public and forkable, monthly over six years, with three statements that tie out and a balance check at zero on every period.

Open Inventory Modelling: Four Methods in Layerz

The full recipe, including the drivers and the structure diagram, is written up in the open Finance Models repository.

Fork it, replace the revenue and margin with yours, reshape the seasonality to your own year, then anchor all four methods on your last closed month. That last step is what makes the comparison honest. Flip the switch between 1, 2, 3 and 4 and read the closing cash line each time. It takes about five minutes.

If the four answers land within a rounding error of each other, your inventory is immaterial and you can stop thinking about it, which is a genuinely useful thing to know. If they don't, you have just found a number worth defending, and you know which method you are defending it with.

Then read one more line, on method D: the share of stock older than 60 days. If it is drifting up, you have a problem your P&L will not show you for another two years.

Further reading: How to Build an Auditable Financial Model with AI · How to Build a Financial Model with AI That Doesn't Drift · How to Stop Rebuilding Your M&A Model From Scratch Every Deal

Layerz keeps a financial model as structure separate from data, so a method swap is one cell instead of a rewiring job, and every change is versioned with its reason. Excel export is clean, standard, and never paywalled. Explore Layerz →