Mastering Inventory Profitability // A Step-by-Step GMROI Calculation Guide

Step-by-Step Guide to Calculating GMROI on Excel


Step 1: Gather Your Data

Before you start, ensure you have the following data:

• Net Sales: Total revenue generated from the sale of goods.
• Cost of Goods Sold (COGS): The total cost of purchasing the goods that were sold.
• Average Inventory: The average value of your inventory over a specific period.

Step 2: Calculate Gross Margin

Gross margin is the difference between net sales and COGS.
This value represents the profit made before deducting other expenses.

Formula:

Gross Margin = Net Sales - COGS

In Excel, if Net Sales is in cell A2 and COGS is in cell B2, the formula will be:

=A2 - B2


Step 3: Calculate Average Inventory

Average inventory is typically calculated over a specific period (monthly, quarterly, or annually).
If you have the beginning and ending inventory values, use the following formula:

Formula:

Average Inventory = (Beginning Inventory + Ending Inventory) / 2

In Excel, if Beginning Inventory is in cell C2 and Ending Inventory is in cell D2, the formula will be:

=(C2 + D2) / 2


Step 4: Calculate GMROI

GMROI is calculated by dividing the gross margin by the average inventory.
This will give you the return on every dollar invested in inventory.

Formula:

GMROI = Gross Margin / Average Inventory

In Excel, if Gross Margin is in cell E2 and Average Inventory is in cell F2, the formula will be:

=E2 / F2

Example Calculation

Let's say you have the following data:

• Net Sales: $500,000
• COGS: $300,000
• Beginning Inventory: $50,000
• Ending Inventory: $70,000

• Calculate Gross Margin:

Gross Margin = $500,000 - $300,000 = $200,000

• Calculate Average Inventory:

Average Inventory = ($50,000 + $70,000) / 2 = $60,000

• Calculate GMROI:

GMROI = $200,000 / $60,000 = 3.33

This means that for every dollar invested in inventory, the retailer earns $3.33 in gross margin.