Inventory Variance Formula, With a Worked Example

The inventory variance formula is counted quantity minus expected quantity, where expected is what your records say should be there: opening stock, plus what you received, minus what you used or sold. Divide the variance by expected to get a percentage, and multiply it by the unit cost to get dollars. A negative variance means you're short. The dollar figure is the one that decides what's worth chasing.

The short version

• Expected = opening + received − used − sold − anything else written off.
• Variance = counted − expected. Negative is short, positive is over.
• Variance % = variance ÷ expected. Variance $ = variance × unit cost.
• Add up the gaps ignoring their sign as well as with it, because shorts and overs cancel out and hide trouble.
• Chase the items that are big in percent and in dollars. TaroStack works expected out for you as you go; see below.

The four formulas

Here's one item, taro paste, from the end of a week at Windward Roots, the made-up taro business I use in these examples:

The 43.2 kg "used" is six batches of taro sweet bread at 7.2 kg each. That's the recipe's number, not a weighed one, which matters later.

Two notes on signs. Some people write expected minus counted, so that short is positive. It doesn't matter which you pick, as long as you never switch. And percentages divide by expected, not counted, so the same 1.9 kg reads the same way every time.

A whole count, worked through

Here's the full count from the inventory count sheet example, next to what the records said:

Add up the dollar column and you get −$15.29. That's the net variance: what the books have to be adjusted by. Add it up ignoring the signs and you get $16.45, which is how much was actually wrong. The difference is the grated taro, which came in 58 cents over and quietly made the total look better than it was.

On a sheet with eleven items the gap between those two numbers is small. On a sheet with eighty items, overs and shorts can cancel each other out almost completely, and a net variance of nearly zero can sit on top of several items that are each badly wrong. That's why I'd always look at both.

As a share of the roughly $635 of stock the records expected, the net gap is −2.4%. That's a useful number to watch month to month. On its own it doesn't tell you much.

The same thing in Excel

With expected in C, counted in D and unit cost in G, starting in row 2:

Variance      =D2-C2
Variance %    =IF(C2=0,"",E2/C2)
Variance $    =E2*G2
Net           =SUM(H2:H12)
Ignoring sign =SUMPRODUCT(ABS(H2:H12))

The IF stops a divide-by-zero error on items you expected none of. SUMPRODUCT(ABS(...)) is the plain way to add absolute values in any version of Excel or Google Sheets. Format the percent column as a percentage.

The count sheet file has a Variance tab with all of this built in, fed from the count. It's an ordinary Excel file with no macros.

Which gaps are worth chasing

Not all of them. Chasing every 30-cent gap is how a Monday disappears. My suggestion is to flag an item only when it's over a limit in both percent and dollars:

=IF(AND(ABS(F2)>2%,ABS(H2)>5),"Look into it","")

On the count above, that flags two items: the taro paste (−7.1%, −$7.30) and the butter (−2.8%, −$5.80). The sugar is 4.6% short, which sounds like a lot, but it's $1.46, and an hour spent on it costs more than the sugar. A bigger kitchen can see the reverse: 1% of 5,000 lb of flour is 50 lb, or $25 at these prices, a small percentage that's worth chasing. Pick limits that suit your stock, and keep them the same from month to month.

Then sort by the dollar column. The top two or three are your list.

For the paste, the first question is the recipe. The 43.2 kg used came from the recipe, and if a real batch takes 7.5 kg rather than 7.2, six batches account for almost all of it. Weigh one. For the butter, the first suspect is greased pans, which no recipe mentions. The rest of the usual causes, and how to tell them apart by the shape of the gap, are in why inventory doesn't match.

Variance, shrinkage and your books

Shrinkage is the same gap turned around: (expected − counted) ÷ expected, so a short shows as a positive percentage. It's the word retailers use, and it usually means loss you can't explain.

In the books, the net variance is written off as an adjustment to inventory, and it ends up in your cost of goods sold for the period. Which account your bookkeeper uses for it is their call. What matters to you is that a count nobody reconciles turns into cost of goods sold anyway, only later and less explained. There's more on that in how to calculate cost of goods sold for a bakery.

Where this stops working

The formula is simple. Expected isn't. To get the 26.8 kg of taro paste above, somebody had to know the opening count, find every delivery, count every batch and multiply by the recipe, and do that for every item, before the first variance could be worked out. In a spreadsheet that's the inventory template with recipes doing the adding, which works until a batch or a delivery doesn't get typed in. Then the variance points at a shortage that's really a missing row, and you spend an hour looking for taro paste that was never lost.

How TaroStack does it

The hard part of the formula is expected, and in TaroStack you never work it out. Every delivery received, every batch recorded and every sale adds and subtracts as it happens, so the expected quantity is always current. When you count, on a phone, a tablet or a printed sheet, TaroStack puts each count next to it and shows the variance, and what it's worth in dollars, before you post it. The $7.30 of taro paste is on the screen while you're still standing in the walk-in, which is the one moment you can still go and look.

It helps with the why as well. From your own batch history, TaroStack checks whether a recipe really yields what it claims and which ingredient's quantity is off, which is exactly the question the paste raised. It suggests what to count next based on what it would cost to be wrong. And your stock value is kept by site and by month, so the number your bookkeeper needs is already there.

Counting and stock value are on every plan, from $49 a month; recipes and batches, and the recipe checks that come with them, are on Standard at $99. Start with a free 30-day trial, import your items from a spreadsheet, and count once. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

What is the inventory variance percentage formula?

Variance percentage = (counted − expected) ÷ expected × 100. Counting 24.9 kg when you expected 26.8 kg is −1.9 ÷ 26.8 = −7.1%. Always divide by expected, so percentages stay comparable from one count to the next.

How do I calculate inventory variance in Excel?

Put expected in one column and counted in the next, then =D2-C2 for the variance, =IF(C2=0,"",E2/C2) for the percentage and =E2*G2 for the dollars, with unit cost in G. =SUMPRODUCT(ABS(H2:H200)) totals the gaps ignoring their sign.

What does a negative inventory variance mean?

That you counted less than the records say you should have: stock was used, wasted or sold without being recorded, or the records overstated it to begin with. A positive variance is the opposite, usually a delivery or a batch recorded twice, or a count error.

What is inventory variance analysis?

Working out why each significant gap happened, not only how big it is. In practice that means sorting by dollar value, taking the top few items, and matching each gap's shape (steady, lumpy, a round multiple, growing with production) to its likely cause, then fixing how that item gets recorded.