Cellwise Start free

Excel guide · 3 min read

How to calculate GMROI in Excel

Quick answerGMROI (gross margin return on inventory investment) = gross margin dollars / average inventory at cost. In Excel, with net sales in B2, cost of goods sold in C2 and inventory at cost at the start and end of the year in D2 and E2: =(B2-C2)/((D2+E2)/2). Footwear made $164,800 of gross margin on $105,000 of average stock, a GMROI of 1.57.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFGHIJKL
1CategoryNet salesCOGSInventory at cost, Jan 1Inventory at cost, Dec 31Gross marginAverage inventoryGMROIJackets month endInventory at cost
2Footwear412000247200980001120001648001050001.5695238095Dec 31, 2025121000
3Jackets3560001994001210001330001566001270001.2330708661Jan 31, 2026140000
4Camping228000141400640007000086600670001.2925373134Feb 28, 2026135000
5Accessories9600043200155001850052800170003.1058823529Mar 31, 2026118000
6Bikes310000232500140000154000775001470000.52721088435Apr 30, 2026102000
7May 31, 202696000
8Jun 30, 2026104000
9Jul 31, 2026128000
10Aug 31, 2026162000
11Sep 30, 2026188000
12Oct 31, 2026201000
13Nov 30, 2026176000
14Dec 31, 2026133000

Download this sheet as an Excel practice file (.xlsx)

Step 1: Gross margin dollars for each category

The sheet is an outdoor shop's year by category: net sales, cost of goods sold (COGS), and inventory at cost at the start and end of the year. Gross margin is =B2-C2, sales minus what those goods cost you. Footwear: $412,000 - $247,200 = $164,800.

Use net sales, after returns and discounts. Gross margin in dollars, not %, is what goes into GMROI.

=B2-C2

Result: 164800 shows as $164,800 with Currency format

164800: Footwear's gross margin for the year.

Step 2: Average inventory at cost

GMROI compares the margin with the money tied up in stock to earn it. The simplest average is the start and end of the year: =(D2+E2)/2, $105,000 for Footwear.

Use inventory at cost, the same basis as COGS. If your system reports stock at retail (selling) price, convert it first, for example with retail value x (1 - your margin %). Mixing retail stock with cost margin makes GMROI look worse than it is.

=(D2+E2)/2

Result: 105000 shows as $105,000 with Currency format

105000: Footwear's average inventory at cost.

Step 3: GMROI for every category

Divide gross margin by average inventory: =F2/G2 in H2, copied down. Footwear is 1.57, Jackets 1.23, Camping 1.29, Accessories 3.11 and Bikes 0.53.

Read it as gross margin dollars per dollar of stock. Accessories earn $3.11 of margin for every $1 sitting on the shelf; Bikes earn 53 cents. For the whole shop, =SUM(F2:F6)/SUM(G2:G6) gives 1.16.

A low GMROI isn't automatically bad. Bikes may bring customers in for everything else. But it does tell you where cash is working hardest, and where a smaller range or faster reorders could free some up.

=F2/G2

Result: 1.5695238095 shows as 1.57 with Number format

About 1.57: Footwear earned $1.57 of gross margin per $1 of average stock.

Seasonal stock: use the monthly average

Jackets start the year at $121,000 and end at $133,000, so the two-point average is $127,000. But stock climbs to $201,000 in October before the winter season. J2:K14 holds all 13 counts: last year's close and each month end.

=AVERAGE(K2:K14) is about $138,800, so Jackets really tie up more cash than the simple average says. GMROI on the monthly average is =F3/AVERAGE(K2:K14), 1.13 instead of 1.23.

For any category whose stock swings through the year, use monthly counts if your system has them. For steady categories the two methods land close together.

=F3/AVERAGE(K2:K14)

Result: 1.1284922395 shows as 1.13 with Number format

About 1.13: Jackets' GMROI using the 13 month-end counts.

GMROI = margin % x sales-to-stock, and a what-if

GMROI splits into two parts: gross margin % times sales / average inventory at cost. Footwear is 40% x 3.92 = 1.57. That shows the two ways to raise it: a better margin, or the same sales on less stock (faster turns).

To test a smaller stock level, put the cut in N2. With 20% less average stock and the same margin, Bikes' GMROI is =F6/(G6*(1-N2)), 0.66 instead of 0.53. Whether sales would hold at that stock level is the real question, and the sheet can't answer that: check your sell-through and stockouts.

=F6/(G6*(1-N2))

Result: 0.65901360544 shows as 0.66 with Number format

About 0.66: Bikes' GMROI with 20% less average stock and the same gross margin.

Common mistakes

Using gross margin % instead of dollars

GMROI needs margin in dollars: sales minus COGS. Margin % is only one of its two parts.

Valuing inventory at retail

Use inventory at cost, the same basis as COGS.

Using one stock count

Average at least the start and end of the period, and use monthly counts for seasonal stock.

Averaging category GMROIs for the shop

Add up gross margin and average inventory first: =SUM(margin)/SUM(inventory).

FAQ

What is the GMROI formula?

GMROI = gross margin dollars / average inventory at cost. In Excel: =(sales-COGS)/AVERAGE(inventory counts).

What does a GMROI of 2 mean?

Every dollar of average inventory at cost earned two dollars of gross margin in the period. Above 1 means the margin covered the stock investment in that period.

How is GMROI different from inventory turnover?

Inventory turnover is COGS / average inventory: how often stock sells through. GMROI uses gross margin instead of COGS, so it also reflects how profitable those sales were.

Should GMROI use yearly or monthly figures?

GMROI is usually worked out for a year. If you use one month or quarter, the result is smaller, so only compare periods of the same length.

Functions used

Related guides

Updated