How much gross profit does each inventory dollar earn?
Markup alone tells you almost nothing. A boutique marking up 127% on cost can quietly earn less per inventory dollar than a department store marking up about half as much, because the department store sells its stock 3.7 times a year and the boutique sells it twice. GMROI is the number that settles it, and it comes straight off two statements you already have.
Words on this sheet
- Inventory turns: How many times over a year you sell through the stock you typically hold.
Finance Intermediate Data Table free
After you install, this is the model to open.
What Does Each Dollar of Stock Earn in a Year?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
One click fills the table with GMROI and days on hand at every inventory level, so you can see exactly where your return crosses each competitor's.
- GMROI at your numbers
- $2.65 gross profit per $1 of average inventory
- The two levers
- 127% × 2.08 markup on cost times turns per year
- Days of inventory on hand
- 175 days, at 2.08 turns a year
- Hold $240,000 at year end
- $3.05 GMROI, up 15% on the same gross profit
Your 127.3% markup turns just 2.08 times a year, so each dollar of inventory brings back $2.65 of gross profit and you sit only $0.10 ahead of a department store that marks up 69% but turns 3.7 times. The lever that moves fastest is stock, not price: hold $240,000 at year end instead of $320,000 and GMROI climbs to $3.05 while days on hand fall from 175 to 152. Let ending inventory drift past about $344,000 and you fall behind that department store benchmark without changing a single tag.
The model
Four figures from your fiscal year drive everything: revenue, cost of goods sold, and inventory at cost at the start and end of the year. The data table then re-runs the model across eight ending-inventory levels.
| Annual revenue | $1,450,000 |
| Cost of goods sold | $638,000 |
| Beginning inventory at cost | $292,000 |
| Ending inventory at cost | $320,000 |
| Ending inventory tested | $200,000 to $480,000 in $40,000 steps |
| Benchmark GMROIs | 2.55 department store, 2.52 off-price, 1.75 warehouse club |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Where does a 35% hurdle rate actually come from?What Return Should a Risky Project Have to Clear?
- Which assumption is this strategic bet actually resting on?What Has to Be True: Strategic Bet Stress Test
- The headline says $12M. What is the earn-out worth?What Is the Earn-Out Actually Worth?
- Will the synergies actually cover the premium?Will the Synergies Cover the Premium?
- Can you afford the hiring plan?Can We Afford This Hiring Plan?
- Sign the flat round, or wait for the milestone?Raise Now, or Wait for the Milestone?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.