Calculating Variance: The Inventory "Actual" vs. "Book" Example

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: (LY-TY)/TY, or (LY-TY)/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 (X-Y)/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 hour-to-hour, day-to-day, month-to-month 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 write-down 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.