< back to resources

Variance Formula & Definition: How to Find Inventory Variance

October 22, 2019
|
Scott

The word variance looms large in the retail and hospitality industries. It’s more commonly known as “loss” or “shrinkage.” And it’s the key to identifying issues with the smooth and profitable operation of your bar or restaurant: spotting miscounting, over pouring, spillage, and even theft.

Calculating your variance is the by-product of consistent and accurate bar inventory management. Whether that’s beer keg tracking, wine inventory, liquor control, or anything in between.

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

First, let's look at exactly what inventory variance is, how to find variance, how Excel can help you find variance, and, finally, how using an inventory variance report saves time and money.

Variance Definition: 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.

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 is considered.

What Is Inventory Variance 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. It tells you, either in dollars or in a percentage, how much product you’re paying for and using—but not making money on. That’s how much of your stuff is slipping through the cracks.

How Can Variance Analysis Help Managers?

Every high-performing bar analyzes and manages their variance. That’s a big part of why they’re high-performing. Though every bar or restaurant on earth can benefit from variance analysis. Here’s how:

  • You’ll make more money. This is the obvious one. A high inventory variance means you’re not collecting enough money for the totality of what you’re selling.
  • You’ll identify shrinkage problems before they become disastrous. You may have a staff member who plays loose with comp rules. You may have another staff member who is accidentally over pouring. Analyzing variance consistently identifies problems like this and gives you the chance to solve them before your bottom line really feels it.
  • Your purchase orders will be accurate. Knowing which types of alcohol have the highest variance (it’s often types of hard liquor because of free pouring) allows you to factor that in when placing orders. If you can tighten up your vodka variance, for example, maybe you don’t need to order as much next time.

What’s a Favorable Variance for Bars and Restaurants?

Most hospitality businesses run a variance of around 20-25%. A favorable variance is anything under 20%. In a perfect world, 10% or less. Though that’s pretty hard to achieve. You may hear talk of “zero-variance” strategies and managers, but don’t be fooled. No bar or restaurants has 0% variance. It’s impossible.

What Is an Unfavorable Variance for Bars and Restaurants?

An unfavorable variance for a bar or restaurant is anything over 25%. But thankfully, if you’re losing a quarter of your inventory, there’s probably a lot of opportunity for you to lower it. 

How Can Variances Be Corrected?

Here are some tried-and-true ways our clients use to lower their variance, be it for alcohol, food, or any other supplies.

Have Standardized Recipes for Every Drink

It’s obviously important to serve your guests consistent drinks. But having standardized recipes for every drink also prevents bartenders from over-pouring or giving out unsanctioned comps. A standardized drink recipe consists of the following:

  • The exact quantity of each ingredient
  • The glass size used to make the drink
  • The procedure for preparing the drink
  • Any special garnishes or condiments to be used

Recipes should be written up, printed out, and revised at least every six months.

Take Inventory Regularly

On average, a bar loses around 20-25% of its monthly sales due to spilled drinks, overstocking, theft, and other inefficiencies. However, more than 40% of bar owners only do inventory once a month because it is such a tedious and time-consuming task. A good inventory period is inventory done at least once a week. Start by making sure that every drink you offer is accounted for in your POS system, including free drinks.

Then use either free printable liquor inventory sheets or inventory management software  to take inventory consistently. Frequent inventory identifies problems before they become a disaster.

Train Staff Properly

The best way to improve your liquor controls and beverage inventory is by properly training your staff. Here are some helpful tips to start:

  • Make sure every staff member has their own copy of your employee handbook. Then you can easily refer to it for professionalism in the workplace, consequences for violation, handling conflicts at work, and other policies and procedures.
  • Ensure that your staff is familiar with the standardized recipes. Print them out and have them available at the bar area for your staff’s convenience.
  • Walk your staff through the bar’s layout and organization.
  • Regularly test your staff’s understanding of health and safety best practices.
  • Offer free in-house trainings and workshops, or suggest outside courses for professional certificates, to improve your staff’s skills.

And to tighten up liquor controls, bar owners have a wealth of liquor control tools and methods at their disposal.

Inventory Variance Formula: Variance Calculator and Equation

Inventory variance is important. You should figure out what yours is. Conveniently, it’s not hard. Here’s how to do it.

How to Find Percent Variance

To calculate the percent variance of an item, you’ll first need to know your cost of goods sold (COGS) and inventory usage in dollars. You'll be able to do this after taking bar inventory and getting your counts. Subtracting the inventory usage from the COGS gives you your variance in dollars.

Percent variance, then, is the variance in dollars divided by inventory usage in dollars multiplied by 100.

Inventory Variance Equation

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

Inventory Variance Example

Here’s a step-by-step variance example in action. 

Step 1: Determine the Cost of Goods Sold‍

First, find the COGS by adding the starting inventory with any received inventory, minus ending inventory. multiplying the number of units sold during the time period by the cost per unit.

COGS = Starting Inventory + Received Inventory - Ending Inventory

For example, you want to calculate the COGS for vodka in January. Your records show that you started with 20 bottles of vodka, received 10 bottles of vodka, and ended the month with 10 bottles of vodka. With each bottle costing $25, you will have the following:

$500 (Starting Inventory of 20 Bottles) + $250 (Received Inventory of 10 Bottles) - $250 bottles (Ending Inventory of 10Bottles) = $500 (20 Bottles)

Your COGS is $500.

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. 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. That’s 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: Use the Inventory Variance Formula

Now you have the two key numbers you need. Either 27 bottles of $675 worth of vodka. So put ‘em in the formula to calculate your product variance. Here's how to calculate the variance of vodka in the month of January:

Monetary Variance = $500 (Cost of Goods Sold) – $675 (Usage in $)
Monetary Variance = - $175
Percent Variance = (- $175 ÷ $675) x 100
Percent Variance = - 26%

Variance is always expressed as an absolute value. That means negative numbers become positive numbers. If your calculation spits out -26% variance, the industry refers to this as 26% variance.

Inventory Variance Calculator

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 spreadsheet version, open it in Microsoft Excel or Google Sheets. Then input the usage amount and COGS. The spreadsheet calculates the variance for you.

How to Calculate Variance in Excel

If you want to calculate variance on your own in Excel or Google Sheets, here’s how. Let’s take the example from above. Here are those values entered into the two associated columns of an inventory spreadsheet:

how to calculate variance in excel


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 inventory variance in Excel.

What Is a Variance Report?

A variance report contrasts projected financial outcomes with actual financial outcomes. In finance, they’re used to compare planned budgets with actual budgets. And in hospitality they’re often used to compare the cost of goods sold with how much of those goods were used. Either way, they’re a document that contrasts what is ideally supposed to happen with what actually happened.

Inventory Variance Report in BinWise Pro

Using inventory management software speeds up and error-proofs variance analysis. BinWise Pro is bar and restaurant inventory software that comes fully-equipped with an industry-leading variance report to ensure your bar or restaurant accounts for every single thing sold, broken, or otherwise depleted from your inventory. Check it out in action:

Hospitality operations across the country use the BinWise Pro inventory variance report to minimize loss and boost profit. It’s an indispensable piece of restaurant technology.

Use a Purpose-Built Beverage Liquor Management Software

Managing alcohol inventory control manually is tedious, complicated, and time-consuming—whether you're a small business or a big player with multiple locations. It also can't guarantee an error-free result. Entering numbers for a thousand bottles into multiple spreadsheets takes time away from countless other business-critical tasks. If you use a liquor inventory system like BinWise Pro, you’re using an inventory variance report that leverages years of experience across countless bars and restaurants. It saves time and money, eliminates errors, decreases pour cost, sets better par levels, and grows profit margin. You owe it to your business to at least explore ways to control and lower inventory variance. Book a demo and we’ll walk you through it.