Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated November 10, 2026

A SKU rationalization analysis ranks every product you sell by what it earns after the real cost of carrying it, then assigns each one a decision: keep, fix, watch or cut. You can run the whole thing in Excel or Google Sheets with a year of sales data, unit costs and on-hand inventory. Below is the process we use, the metrics worth scoring, the formulas to build, and the mistakes that lead to cutting the wrong products.

Fraccfo
Make SKU Decisions With Financial Clarity
Frac CFO helps businesses use existing spreadsheets for financial modeling, forecasting, optimization, and dashboards that support clearer decisions.

Explore spreadsheet finance support

TL;DR

  • Pull 12 months of units sold, unit cost, price, on-hand stock, returns and last-sale date for every SKU into one sheet.
  • Score each SKU on profit, not volume: gross margin, GMROI, sell-through and turnover.
  • Use an 80/20 ranking to build a candidate list, then check substitution and strategic role before anything is cut.
  • Sort every SKU into keep, optimize, watch or discontinue, using written rules agreed before you look at the names.
  • Re-run the analysis on a schedule and measure the margin and cash impact against your baseline.

Table of Contents

What a SKU rationalization analysis actually decides

SKU rationalization is a review of your catalog that ends in a decision for each item. One common definition describes it as analyzing a product portfolio to decide which items to keep, modify or discontinue, working at the level of individual SKUs rather than whole product lines. You will also see it called SKU optimization, SKU reduction or assortment rationalization. The names differ but the work is the same.

For a small business, the analysis answers three money questions:

  • Which products pay for the cash and shelf space they use? Inventory is cash sitting in a box. A SKU that sells slowly at a thin margin ties up cash that a better product could use.
  • Which products look weak but support the rest of the catalog? A low-volume accessory may be what makes the main product sell.
  • Where is complexity costing you? More SKUs means more purchase orders, more counting, more listings to maintain and more chances of a stockout on the items that matter.

The result is a smaller, more profitable catalog and cash released from stock that was not earning its keep.

Signs it is time to run one

You do not need a crisis to justify the review, but these are the usual triggers:

  • Revenue is growing and cash is not.
  • Your best sellers keep running out while slow items collect dust.
  • The catalog has doubled since you last looked at it, usually through variants (color, size, pack count).
  • You cannot say which ten products make most of your profit.
  • A warehouse, supplier or marketplace fee change is about to raise your cost to hold each item.

If slow stock is your main worry, our guide to finding, valuing and fixing slow-moving inventory covers that side in more depth.

Step 1: Build one clean data sheet

Everything depends on this sheet. Make one row per SKU and gather these columns:

Column Why you need it
SKU and product name Identifier, plus a category column for peer comparison
Units sold, last 12 months Demand and velocity
Net revenue Sales after discounts and returns
Unit cost (landed) Product cost plus freight and duties, so margin is real
Units on hand and inventory value What cash is tied up
Returns Hidden margin loss
Last sale date Finds dead stock
Monthly units, 12 columns Shows seasonality and stability

One approach to the audit is to look at units sold over the past 12 months, margin per unit, on-hand stock, carrying costs, return rates and days since last sale. That list is a good minimum.

Two cautions on data. First, use landed cost, not the supplier invoice price. Second, match the review window to your sales cycle. One inventory guide suggests 30, 90 or 180 days depending on how fast you sell, and notes that seasonal items and core staples should not be held to the same bar. For most small businesses, 12 months with a monthly breakdown is the safest base.

Step 1: Build one clean data sheet

Step 2: Calculate the metrics that matter

The most common mistake is judging products on sales volume alone. A high-volume item can be a margin drain, and a slow item can be your most profitable. Weigh the sales a product drives, the stock it requires and the profit it delivers.

Add these columns to your sheet:

  • Gross margin per SKU: revenue minus cost of goods sold.
  • Sell-through rate: units sold divided by units received, times 100.
  • Inventory turnover: cost of goods sold divided by average inventory value.
  • GMROI: gross profit divided by average inventory cost. It tells you how many dollars of gross profit each dollar of stock produced.
  • Contribution margin: net sales minus all variable costs, which is the figure that shows what a SKU really adds. These definitions match the standard SKU performance formulas.
  • Days since last sale: =TODAY()-F2 if column F holds the last sale date.

GMROI is usually the best single ranking figure for a small business because it combines margin and stock investment. If you want a wider set of tracking ideas, our list of ecommerce KPIs and how to calculate them shows how these fit into a regular reporting routine.

A worked example

Say you sell two candle SKUs. These numbers are illustrative.

Candle A Candle B
Units sold (12 months) 4,000 600
Net revenue $60,000 $15,000
COGS $48,000 $6,000
Gross profit $12,000 $9,000
Average inventory at cost $10,000 $2,000
GMROI 1.2 4.5

Candle A has almost seven times the unit volume and looks like the star. Candle B earns $4.50 of gross profit for each dollar of stock, nearly four times as much. A volume-only review would have put Candle B on the cut list. The better question is whether Candle A’s price or cost can be fixed.

Step 3: Find the long tail with an 80/20 ranking

Most catalogs follow a Pareto-style pattern: a small group of SKUs drives most volume, and the long tail consumes overhead. In ABC analysis, Category A is about 20% of SKUs carrying roughly 80% of value, and Category C is about half of the items for only around 5% of value. The same source recommends ranking by consumption value or revenue instead of quantity.

Build it in Excel:

  1. Sort SKUs by annual revenue (or gross profit) from highest to lowest.
  2. In a new column, add a running total of revenue as a percent of the grand total.
  3. Assign classes with a formula, where H2 is the cumulative percent: =IF(H2<=0.8,"A",IF(H2<=0.95,"B","C")).

For demand stability, add an XYZ class. Calculate the coefficient of variation across the 12 monthly columns: =STDEV.P(B2:M2)/AVERAGE(B2:M2). Low values mean steady demand, high values mean erratic or seasonal demand. Set your own cutoffs, for example under 0.25 for X and over 0.5 for Z, and keep them in labeled cells so you can adjust them.

The C-class list is a candidate pool, not a cut list. Long-tail products still need two more checks.

Step 4: Check substitution and strategic role

Before cutting, ask whether customers would trade to another product in your range or leave for a competitor. One assortment guide cautions that removals should be checked for cannibalization so shoppers do not simply shift away from you.

Add a “strategic role” column with a simple tag for each candidate:

  • Traffic driver: brings customers in, even at low margin.
  • Attach item: sells alongside a core product.
  • Seasonal or launch: judged on its own calendar.
  • Duplicate: near-identical to another SKU (a color or size that overlaps).
  • None: no role beyond its own sales.

Duplicates with no role are the safest cuts. Attach items and traffic drivers need to be protected, or at least repriced.

Step 5: Score and sort every SKU

Step 5: Score and sort every SKU

Turn the metrics into a scorecard so the decision follows rules and not opinions. A weighted model across financial and operational measures such as GMROI, sell-through, velocity, contribution margin and cost-to-serve is a practical way to reflect your priorities consistently.

A simple version for a spreadsheet:

  1. Rank each metric from 1 to 5 using percentiles within the category.
  2. Weight them. For example: GMROI 35%, contribution margin 25%, sell-through 20%, turnover 10%, return rate 10%.
  3. Add the weighted ranks into one score from 1 to 5.
  4. Overlay the strategic-role tag.

If you want to see how a scorecard connects to wider business measurement, this guide to an operational efficiency scorecard is a helpful companion. Our own Performance Scorecard Excel template is built on the same weighted idea.

Decision buckets

A keep-optimize-discontinue model is easy to run:

Bucket Typical profile Action
Keep Strong across metrics, fits your goals Protect stock levels, give it prime space and marketing
Optimize Weak but salvageable Adjust price, repackage, bundle with winners or renegotiate supply
Watch Borderline or new Set a review date and a threshold
Discontinue Weak on every metric, no strategic role Phase out, clear remaining stock, reallocate the cash

A gentler way to discontinue is to reduce order quantities before eliminating a product entirely, which gives you a chance to see whether demand really disappears.

Step 6: Put the cash impact in the sheet

A decision is only a good one if it shows up in cash and margin. For each cut or fix, estimate:

  • Inventory value released (units on hand times unit cost, less likely markdown loss).
  • Carrying cost saved (storage, handling, insurance, financing and shrinkage).
  • Revenue at risk if customers do not substitute.
  • Margin gained if the released cash goes to a high-GMROI SKU.

This is where the work connects to your cash flow forecast. Clearing dead stock at a discount gives you a one-time cash inflow, and you should feed it into your forecast as exactly that. Our product mix optimization guide shows how to allocate limited cash and capacity to the items that earn the most. Businesses in other trades see the same effect: tracking real profit per item is what shows which inventory deserves the next purchase order.

Governance: keep it rules-based

SKU reviews fail when they become emotional or political. Without clear criteria, decisions become subjective, so one practitioner recommends transparent evaluation periods and rules tied to financial targets. A few habits help:

  • Write the thresholds down before you see the SKU names.
  • Give the founder’s favorite products the same test as everyone else’s.
  • Keep a log of each decision and its reason.
  • Compare category GMROI before and after the changes so you know the review worked, not just that the catalog got smaller.

Common mistakes

  • Cutting by revenue rank alone. Use profit and cash metrics.
  • Ignoring seasonality. A winter item looks dead in July.
  • Using supplier price instead of landed cost. It flatters margins.
  • Skipping the attach check. You cut the cable and lose the device sale.
  • One and done. Catalogs grow back. Put the review on the calendar, quarterly for fast-moving businesses and at least annually for the rest.

Our perspective on SKU analysis

Most small businesses we meet already have the data for a SKU rationalization analysis. It is sitting in a sales export, a purchasing sheet and a stock count that have never been put side by side. That is why we build these reviews in the spreadsheets our clients already use. You keep full control of the numbers and you understand every formula behind a decision.

We design the model so the numbers finally tell a story: which products fund your growth, which tie up cash, and what releasing that cash would let you do. Our approach is to connect the SKU scorecard to your cash flow forecast and KPI dashboard, so the analysis becomes a routine that supports decisions and not a one-time cleanup.

frac

Get the analysis built for your catalog

If you would rather not build the model from scratch, we can do it with you. Our Profitability and Pricing Analysis shows margin by product so you can see which SKUs deserve capital. Our one-time finance projects cover profitability reviews, KPI dashboards and cash flow forecasts, all in Excel or Google Sheets.

Optimization and Decision Modeling

For ongoing support, our fractional CFO plans start at $600 per month on the Starter plan, and the Launch Kit is a one-off $650. You can also browse our Excel templates for dashboards and scorecards you can adapt yourself.

FAQ

What is the difference between SKU rationalization and SKU reduction?

They are mostly used as synonyms. Rationalization is the broader analysis that decides keep, fix or cut for each item, while reduction describes the outcome when the answer is to cut. A good analysis often ends with fewer SKUs but also with repriced, bundled or re-sourced ones.

How often should I run a SKU rationalization analysis?

Most small businesses should do a full review once a year and a lighter check each quarter on the C-class and watch items. Match the cadence to your sales cycle, and review seasonal products after their season ends.

Can I do SKU rationalization in Excel?

Yes. A single sheet with revenue, landed cost, inventory and monthly units can calculate margin, GMROI, sell-through, ABC class and a weighted score using basic formulas such as SUMIFS, RANK and IF. Google Sheets works the same way.

How many SKUs should I cut?

There is no ideal number. Let the scorecard and the substitution check decide, and measure the result by the change in category GMROI and released cash. Cutting too many can hurt attach sales, so phasing out gradually is safer.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Sources

Leave a Reply