Learn calculate GMROI (Gross Margin Return on Investment) in Excel. The Turn Earn Index and GMROI are two simple ways to help manage your inventory and run your business. Need to know the GMROI formula? Read on!
GMROI formula definition:
GMROI (Gross Margin Return on Investment) evaluates profitability of inventory with a ratio. It analyzes a company’s ability to turn inventory into cash, above the cost of the inventory.
GMROI formula to manage inventory
I found the formula to calculate #GMROI: GMROI = #GrossMargin $ / Avg Inventory Investment Click To TweetGross Margin Return on Investment, or GMROI, is a way to manage inventory. The GMROI formula uses the gross profit earned for each dollar of your investment in inventory. The calculation is (gross margin $$)/(average inventory investment $$). For example, let’s say your annual gross margin dollars, or your annual gross profit, is $250,000 and your average inventory value is $200,000, then your GMROI is 1.25 (sometimes GMROI is multiplied by 100, and in this case would be 125). Retailers or distributors typically use GMROI to evaluate inventory. Manufacturers use it, too, but not as often.
A number or ratio higher than 1 indicates a company is selling its inventory at a higher value than it paid for the inventory. A general rule of thumb for retail stores is to have a GMROI of 3.2 or greater.
GMROI Gross Margin Return on Investment calculation example
The GMROI formula tells you how many dollars of gross profit are earned from each dollar you have in inventory. In the above example, we’re producing $1.25 in gross profit for every $1 invested in inventory.
GMROI measures inventory differently than the Turn/Earn Index (see below) and neither is “better.” It’s like 12 inches is not “better” than 30.5 centimeters because they’re essentially the same length.
Turn and Earn Index definition
The turn and earn index analyzes inventory turnover and gross margin using a ratio. To calculate turn and earn, multiply inventory turns by gross margin percentage.
Using the Turn and Earn Index vs. GMROI to manage inventory
I found the formula to calculate the Turn Earn Index = #GrossMargin x Inventory Turns #accounting Click To TweetIf you’re a purchasing manager or accountant you may want to justify selling of stocking a SKU (stock keeping unit) with the Turn/Earn Index. It’s also known as the Turn/Earn ratio or Turn/Earn formula, or simply as Turn and Earn. It’s a simple way to help manage inventory and you can evaluate your entire inventory or just a part number to see if it is worth stocking or selling.
Calculate the Turn/Earn index for your inventory by multiplying gross margin x turnover. “Turnover” is simply your inventory turns. Scroll down for a definition of inventory turns.
For an example of Turn/Earn, if your inventory turns 5 times in a year and you have a 35% margin your Turn/Earn index is 175 (35 x 5 = 175).
To calculate the Turn/Earn index for an individual part number or SKU, simply multiply gross margin x turnover (units sold per year). For example a product with a gross margin of 35% that sells 5 per year would have a Turn/Earn Index of 175 (35 x 5 = 175).
Companies typically look for a minimum Index of 100 to 150. There are a lot of reasons for higher or lower numbers and a lot of it depends on whether you are a manufacturer, distributor or a retailer.
A manufacturing company might shoot for a lower minimum index of 100 for an individual part number. Reasons might be that part number is required to support a broader product line, or it is necessary to support replacement parts or components, or it needed to address demand from specific market segments that might be new for the manufacturer.
A retail store might have a higher Index goal of 150 for a SKU. There are reasons to have a higher Turn/Earn Index number. These include limited shelf space, catering to a specialized retail market or keeping inventory low to conserve cash.
Inventory Turns definition
Inventory turns is a measurement of how many times inventory is sold or consumed in a given time period, usually one year. Calculate it by cost of goods sold (COGS) divided by inventory cost.
How to calculate inventory turns in Excel
The traditional way to measure SKUs over time is by “turns,” or how many times the inventory turns over each year.
One long formula for inventory turns (written here in ‘Excel spreadsheet-friendly’ format) is:
(($ beginning inventory + purchases over a defined period – ending inventory)/(ending inventory)) x (number of time periods).
The number of time periods is typically 12 months.
A shorter formula to measure inventory turns (again, in ‘Excel-friendly’ format) is:
(Cost of goods from inventory over 12 months)/(Average inventory investment over 12 months).
Use benchmarking to measure key performance indicators and compare your business against your peers. Hedges & Company managed the SEMA Financial Benchmarking program for seven years, a free member benefit for SEMA members.
Another related article: read about how price increases/decreases and unit sales affect gross profit.