Inventory Variance | 4 mins read

How to Calculate Inventory Variance in 3 Simple Steps

how to calculate inventory variance in 3 simple steps
Jin Hyun

By Jin Hyun

Shrinkage of inventory is almost unavoidable for most product-based businesses. Whether it is due to shoplifting, theft, poor management, or loss, its, unfortunately, a common occurrence for many. To avoid this from affecting the profitability of a company, variance reports should be frequently produced to increase the visibility and accountability of inventory tracking.

Inventory variance sums up the discrepancy of an item or balance in a company's inventory system with the actual number of that product. Once ascertained, these discrepancies are then calculated and documented in a variance report to track the level of shrinkage.

Understanding how to calculate inventory variance reports is a vital part of successful inventory tracking as these reports give companies the opportunity to identify stock loss or damage, as well as rectify financial statements and inventory inaccuracies to maximize the gross profit.

Most Common Causes of Inaccuracies

From stock damage to theft and misplaced or improperly stored items - there is a multitude of reasons why a company's variance reports may reveal inconsistencies between actual stock counts and official records.

Explore some of the main reasons for stock discrepancies below.

  • Inventory loss - The loss of goods is a prime contributor to inventory differences, ultimately accounting for about one percent of all sales. This loss (or shrinkage) is a direct result of shoplifting, employee theft, or even supplier fraud.
  • Misplaced inventory - In a nutshell, misplaced inventory takes place when an item or product has been uploaded to the database and then put in the wrong physical location. This also occurs when customers pick up a product and then place it somewhere else.
  • Human error - Possible human errors could include everything from inaccurate physical counts to picking and placement mistakes made by employees. While this wide-spreading cause of inventory variance is inevitable and tough to eradicate, excellent staff training is a great way to prevent this issue.
  • Poor management of returns - When a customer returns items, there's a risk that those products will be incorrectly coded and then delivered back into storage with mismatched data. This highlights another opportunity for companies to place airtight methods and processes in place for returns, alongside effective training for all staff members.

3 Steps For Calculating Variance

3 steps for calculating variance 1591392382 1005

Inventory variance can be calculated by using a variance formula that works by either monetary value or percentage. Following these steps can help businesses get one step closer to reducing stock errors, ensuring inventory accuracy, and minimizing profit loss.

Dollar Amount Value Variance = Cost of Goods Sold (COGS) in Dollars - Usage in Dollars
Percentage Variance = (Variance in Percentage / Usage in Dollars) X 100

Step 1- Find the COGS.
To determine the cost of goods sold, simply multiply the number of units (or products) sold during a pre-determined period of time by the cost per unit.

COGS = number of units sold x cost per unit.

For example, take the month of December for a bicycle shop. If 20 bikes were sold at a cost of $250 each, then the formula would be-

20 (bikes sold) X $250 (the cost) = $5000(COGS)

Step 2- Determine the inventory usage value.
Find and calculate this usage by discovering how much stock was utilized over a set time period.

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

Going back to the previous example of the bicycle shop, this formula seeks to calculate the inventory usage of bikes in the shop over those 31 days. The starting inventory is what's actually in-store before December 1 and the ending inventory is what is left on the 31st after the shop closes. To ensure this formula is used correctly, the bike shop must further factor in the number of bikes ordered in December which is the received product inventory.

Hypothetically, if the number of bikes at the beginning inventory was 30, the ending inventory was 8, and the company ordered 5 new bicycles that month the formula works as such-

30 + 5 - 8 = 27 (inventory usage)

With each bike costing $250, the monetary value of the inventory usage is-

27 bikes X $250 (cost) = $6,750 (usage in dollars)

Step 3- Calculate inventory variance using the formula.
Using the figures calculated by the previous formulas, the final step is to plug in the appropriate numbers to determine the variance.

This can be calculated for December as follows-

$5000 (COGS) - $6750 (Usage in $) = $1750 (variance in $)

When expressed as a percentage this would be -25%.

While Excel is a great way to input and calculate these formulas, variance reports should be produced as frequently as possible to ensure inventory accuracy. To do this, businesses should look towards investing in inventory management software, which can produce variance reports instantly with no manual calculations involved.

What is An Ideal Inventory Variance Percentage?

To reap the full benefits of inventory variance reports, businesses should understand what an acceptable variance percentage is.

After all, with human errors being inevitable, variance in inventory records versus the physical counts should be expected. With that said, if total variances surpass 10%, measures should be taken immediately to address the discrepancies.

While the ideal discrepancy percentage varies by size and industry, with some companies aiming for below 1%, this figure should never exceed the 10% mark as this could result in serious financial consequences.