< back to Blog home
By
Matthew Krimmel

How to Calculate Inventory Variance: Formula and Tips

Table of Contents
Thank you! Check your inbox for details.
Oops! Something went wrong while submitting the form.
variance calculator inventory management software

The word variance looms large in the retail and hospitality industries (see the hospitality definition), especially in the context of bar inventory. Rightfully so, as a high variance can sink your business. That makes it the key to identifying problems.

When you lower variance percentage, you're eliminating miscounting, over pouring, spillage, and even theft.

How do you lower it? Well, first you have to know it.

So, let's look at exactly what inventory variance is. Then we'll go over how to find variance.

What Is Variance?

The meaning of variance is the difference or discrepancy between two or more numbers or sets of numbers. It's 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. It accounts 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 definition 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. Knowing your variance helps you reduce the volume of wholesale products or raw materials inventory that you order on a regular basis.

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 performers.

However, every bar or restaurant on earth can benefit from variance analysis:

  • 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 it gives you the chance to solve them before your bottom line really feels it. This also saves a lot of time when it comes to restaurant bookkeeping and updating your restaurant chart of accounts.
  • Your purchase orders will be accurate. Know which types of alcohol have the highest variance. It’s often types of hard liquor because of free pouring. This allows you to factor it 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 variance of around 20-25%. A favorable variance is anything under 20%.

In a perfect world, your variance is 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 have 0% variance because it's simply impossible.

What Is an Unfavorable Variance for Bars and Restaurants?

An unfavorable variance for a bar or restaurant is anything over 25%. If you’re losing a quarter of your inventory, there are many opportunities for you to lower it. 

Mastering bar inventory ebook

How Can Variances Be Corrected?

Here are 3 effective strategies our clients use to lower their variance for alcohol, food, or any other supplies:

1- Have a Standardized Recipe for Every Drink

It’s obviously important to serve consistent drinks to your guests. It 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 type and 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 6 months.

2- Take Inventory Regularly

On average, a bar loses around 20-25% of its monthly sales. All due to spilled drinks, overstocking, theft, and other inefficiencies. You don't even want to know what happens when alcohol expires.

Over 40% of bar owners only do inventory once a month because it's so tedious and time-consuming. That's not to mention all the work involved if you have so much inventory that you rely on warehousing, too. A good inventory period is performed 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 a free bar inventory template or inventory management software. Both help take inventory consistently. And frequent inventory identifies problems before they become a disaster.

3- Train Staff Properly

The best way to improve your liquor controls and beverage inventory organization is by properly training your staff.

Here are 5 ways to do it:

1- Make sure every staff member has their own copy of your employee handbook. Then you can easily refer to it for policies and procedures.

2- 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.

3- Walk your staff through the bar’s layout and organization.

4- Regularly test your staff’s understanding of health and safety best practices.

5- Offer free in-house training 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

Percentage variance is the byproduct of consistent and accurate bar inventory management.

It includes beer keg tracking, wine inventory, liquor control, or anything in between.

How to Find Percent Variance

To calculate the percent variance of an item, you’ll need 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 is the variance in dollars divided by inventory usage in dollars multiplied by 100.

How to Calculate Variance Percentage

Here's the variance percentage formula:

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

Inventory Variance Example

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

Determine the Cost of Goods Sold‍

First, find the COGS by adding the starting inventory with any received inventory, minus ending inventory. Then multiply 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. You 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.

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 $)

How to Calculate Variance in Percentage

Now you have the two key numbers you need- either 27 bottles or $675 worth of vodka. So put them 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
Variance Percentage= (- $175 ÷ $675) x 100
Variance Percentage= - 26%

Variance is always expressed as an absolute value. That means negative numbers become positive numbers. If your variance percentage 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. Both as 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 inventory template

Like this:

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


Variance Formula 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) gives you variance percentage 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. All to ensure your bar or restaurant accounts for every single thing sold, broken, or otherwise depleted from your inventory.

Check it out in action:

Restaurant operations across the country use the BinWise Pro inventory variance report. They use it 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 like excess inventory, decreases pour cost (see liquor cost calculator), sets better par level, and grows profit margin. It comes with a barcode scanner app for inventory to ease the process. Your restaurant profit and loss statement will thank you.

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. There's even a bar inventory app.

beverage inventory management sofrware

Frequently Asked Questions About How to Calculate Inventory Variance

What is inventory variance?

Inventory variance is the amount of product sold versus the amount of product used over a set period of time. Ideally, these numbers should be the same.

How do you calculate inventory discrepancy?

1- Perform a physical count of all available inventory on hand.

2- Compare it with the inventory you should have.

3- If the two counts match, then your inventory is accurate without any inconsistencies. If they don't match, then there's an obvious discrepancy.

What is variance in bar inventory?

Variance reports are essentially an analysis to see how liquor usage compares with sales. Variance can be expressed in ounces, percentages, and in value (either at cost or at retail).

Book a Demo
Reduce inventory counting time by as much as 85%. Schedule a demo now:
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.