The Opinion Page
News and comments about the issues facing today's SCM and Inventory Management professionals.
I recently heard a question from one of my colleagues regarding the proper way to compute variance. After a series of cycle counts and comparing his physical inventory count to the count value that showed on his "system" (i.e. the "Book" value), he was trying to arrive at a simple indicator, which we know as percent variance. He encountered a bit of a challenge. Here is his question:
What's the correct formula to calculate the percentage deviation in a stock count, for an individual item? I have seen 3 different formulas suggested: 1. (Book  Actual) / Book x 100% 2. (Actual  Book) / Book x 100% 3. (Actual  Book) / Actual x 100% However all these methods fail when the book or actual value is zero. Borrowing from a statistics text book's formula for variation, we could use: (Actual  Book) / (Actual + Book)/2 x 100% By dividing by the average, we avoid the problem of dividing by zero. But I've never seen this in an Inventory text book. Here was my response: When evaluating variance, I find that it is always useful to ask "what are we measuring, relative to what?". To draw an analogy, imagine that we are measuring sales volume growth this year relative to last year's sales volume. The appropriate formula to measure percent variance is (TY minus LY)/LY. It is NOT: (LYTY)/TY, or (LYTY)/LY. I think most would agree with my approach. Always, I think, if we want to measure X's variance relative to Y, then the appropriate formula (within the context that we are discussing) is (XY)/Y. Moving on to inventory variance...I now ask, what is X and what is Y? I would argue that X = actual inventory qty and Y = book inventory qty. So the appropriate formula is (Actual  Book) / Book. Why? Well, on an hourtohour, daytoday, monthtomonth basis, the "business" (in a broad sense) assumes that the book value is the correct value. Customer Service assumes this, Finance assumes this, and Operations assumes this, until there is compelling evidence (such as a cycle count) to trigger further investigation. The Book Value becomes the critical benchmark variable. If we perform an annual physical inventory, for example, and we find that the actual inventory is a higher value than the physical value, then (after appropriate investigation) we consider this to be positive variance (not a negative.) Further, the accountants will consider this a "gain" in the books: the book value of the inventory is written UP, not down. Conversely, if physical < book, then the business must writedown the financial impact and the variance is a negative (the worst of all worlds!). As such, while I might acknowledge that there may be some circumstances in which one might want to interchange the numerator and denominator of this equation, they ought not be considered interchangeable haphazardly. I would not recommend the "average" approach, as the true variance can, and will be understated...often significantly. To avoid the problem of a zero denominator, I would simply recommend creating an "if" statement  as in Excel  to identify those circumstances where the denominator is zero. One could build other conditions into the variance formula through "if" statements or otherwise to account for the product's value  if, for example, the absolute value of the variance is less than $x.00 or "y" units, then ignore the result.
1 Comment
Greg Moore
1/23/2012 05:41:36 am
For the last 4 years are small retail company has always had our actual inventory less than book. For 2011 our variance was a 55%! My bookkeeper and accountant have not given any good reason for this. The only thing I can think of is we have participated in a month long charity event (last 4 years) were we sell our merchandise off site (we keep 75% of the proceeds and the charity keeps 25%). The charity handles the cash/credit transaction and cut us a check for 75% of the sales. On our books we record the check as income (db. Cash, cr. Sales) and tally our receipts to come up with a corresponding COGS (db. CGS, cr. Inventory) to reflect the expense side. Is there some correlation I am missing to keep our book artificially high vs. physical? Thanking you in advance for your insight.
Reply
Your comment will be posted after it is approved.
Leave a Reply. 
AuthorJohn Skelton is the Principal Consultant and founder of Strategic Inventory Management. Archives
August 2016
Categories
All
