< back to resources

How to Calculate Inventory Variance | Meaning & Formula

October 22, 2019
|
BinWise Staff

The word variance looms large in the retail and hospitality industries. More commonly known as “loss” or “shrinkage,” variance is key to identifying issues with the smooth and profitable operation of your bar or restaurant: spotting miscounting, overpouring, spillage, and even theft. And calculating your variance is the by-product of consistent and accurate bar inventory management.

But what exactly is variance, and how does it apply to bars and restaurants? 

First, let's look at what variance is fundamentally, how to find variance, how Excel can help you find variance, and, finally, how using a variance report can save you lots of time and money.

Definition of Variance: What Is Variance?

Variance Meaning

What does variance mean? Variance refers to the difference or discrepancy between two or more numbers or sets of numbers. It's very similar to what shrinkage is. When you’re calculating the variance of something, you’re calculating how much one set varies from the other set.

Why Is Variance Used?

Any time anyone wants to make an informed decision about data sets, variance is needed to account for the natural fluctuation in numbers. Think of height, wages, calories, or any other way we quantify our world. To arrive confidently at decisions involving large amounts of variable data, variation must be considered.

What Does Variance Mean in the Context of Bars and Restaurants?

The meaning of variance in the hospitality industry is pretty simple. It’s the difference between the cost of goods sold and the cost of goods used. Basically, it tells you either in dollars or in a percentage, how much product you’re paying for and using, but not making money on.

How to Find Variance: Inventory Variance Formula

To calculate the variance of an item, you can follow either one of the following formulas, using monetary value or percentage. You'll be able to do this after taking bar inventory and getting your counts.

Monetary Value Variance = Cost of Goods Sold (COGS) in $ - Usage in $
Percentage Variance =  (Variance in $ ÷ Usage in $) x 100

Step 1: Determine the Cost of Goods Sold

First, find the COGS by multiplying the number of units sold during the time period by the cost per unit.

COGS = Number of Units Sold x Cost Per Unit

For example, you want to calculate the COGS for vodka in January. Your record shows that you sold 20 bottles of vodka in that month. With each bottle costs $25, you will have the following:

20 (Units Sold) x $25 (Cost Per Unit) = $500 (COGS)

Step 2: Find the Inventory Usage Value

To calculate inventory usage, you need to find the amount an item has used over a time period. This can be calculated using this simple formula:

Inventory Usage = Starting Inventory + Received Product Inventory – Ending Inventory

In the example, you're calculating the inventory usage of vodka in your bar in January. Your beginning inventory is what you have before service begins on January 1st, and your ending inventory is what you have left on January 31st after closing. You would also need to account for the amount of vodka that you ordered in January, which is your received product inventory.Let’s assume that your beginning inventory is 30, your ending inventory is 8, and you ordered 5 bottles of vodka in January:

30 + 5 - 8 = 27 (Inventory Usage)

At $25 per bottle of vodka, the monetary value of your inventory usage is:

27 bottles x $25 per bottle = $675 (Usage in $)

Step 3: Calculate Variance Using The Formula

Now that you have the two key numbers you need, either 27 bottles of $675 worth of vodka, simply put one in the formula to calculate your product variance. Here's how to calculate the variance of vodka in the month of January:

$500 (Cost of Goods Sold) – $675 (Usage in $) = - $175 (Variance in $)

Or:

(- $175 ÷ $675) x 100 = - 25% (Variance in Percentage)

How to Calculate Variance in Excel

You can quickly use a formula to calculate variance in Excel. And that’s especially convenient because we’ve got a free bar inventory spreadsheet available for download as both a PDF and an Excel document. If you download the Excel version, you can input the cost of goods sold, inventory usage, and have Excel calculate variance for you with a variance formula.

Let’s take the example from above. Here are those values entered into the two associated columns of the bar inventory spreadsheet:


With $675 in column I and $500 in column J—both in row 6—our Excel variance formula would look like:

=((J6-I6)/I6) * 100

Copying and pasting the above formula into the corresponding cell in the variance column (K) will give you your percentage variance in Excel.

Variance Report in BinWise Pro

If you’re just not feeling calculating variance manually, be it completely on your own or with Excel, a bar inventory management platform will usually have a variance report feature.

BinWise Pro is a bar inventory software that comes fully-equipped with an industry-leading variance report that ensures your bar or restaurant accounts for every single thing sold, broken, or otherwise depleted from your inventory. Quickly and accurately, the BinWise Pro variance report minimizes loss and boost profits.

And If You Don’t Know, Now You Know (What Variance Is)

It is important to keep an eye on the variance of every inventory item to quickly identify liquor inventory control issues, prevent them from happening in the future, and keep an accurate inventory. But calculating variance manually can be very time-consuming and error-prone. We recommend finding a system like BinWise Pro that automates the process and gives you an extensive variance report so you can make smart business decisions. Book a demo with us to learn more about how BinWise Pro can help you determine your variance, calculate inventory usage, and save time.