Retail assortment planning: choose the product mix

Retail assortment planning chooses the products each store carries and the inventory depth behind them, using demand, margin and practical buying limits. Start with a comparable SKU-by-store export, separate poor demand from poor availability, and turn the findings into decisions a buyer can approve.

The planning workflow below produces a reviewable buying list. The separate inventory assortment guide explains product breadth and depth in more detail.

Set the decision before opening the export

Define the category, selling period, store groups and decision date. Decide whether the review can add products, change quantities, transfer stock or discontinue existing lines. A midseason replenishment review has different options from a new-season range review.

Write down the constraints beside the plan: purchase budget at cost, shelf space, minimum presentation quantities, supplier order multiples and delivery dates. Treat essential sizes, entry price points and contractual commitments as explicit requirements. They should not disappear because a sales ranking puts them near the bottom.

Give the buyer responsibility for the range decision, the planner responsibility for the calculations, and the store team responsibility for availability exceptions. Finance checks the purchasing envelope. This makes it clear who can approve an exception to a suggested quantity.

Build one comparable SKU-store file

Bring sales after returns and discounts, the cost of those sales, closing sellable units, open purchase orders and days available for sale. Use the same reporting cutoff across files. Keep damaged stock separate from sellable stock and transfers separate from customer sales.

Keep a stable SKU identifier and a store identifier. Product descriptions can change or contain near duplicates. GS1 US distinguishes globally recognized product identifiers from retailers' internal SKUs; preserve the relevant identifier when matching the sales and stock exports.

A fictional Wisconsin home-goods retailer reviews September 1–28, 2026. Its source worksheet uses columns A through H in the order below. There are no open purchase orders for these rows.

Tie the net sales and COGS totals to the corresponding category report before making decisions. Gross profit uses net sales less COGS, consistent with the IRS explanation of gross profit for merchandise businesses. Keep the company's cost definition consistent between stores; the detailed gross profit margin method explains that calculation.

Calculate margin and availability before ranking products

Add columns I through L for gross profit, gross margin, units per available week and weeks of cover. Enter these formulas in row 2 and fill down through row 5:

I2 = D2-E2
J2 = IF(D2>0,I2/D2,"")
K2 = IF(G2>0,C2/G2*7,"")
L2 = IF(K2>0,F2/K2,"")

Format J as a percentage. Missing availability or zero sales velocity leaves a blank for review rather than a reassuring zero. The weekly rate is observed sales per available week, not proven unconstrained demand.

The total gross profit is 5,440 − 3,376 = $2,064. Total margin is 2,064 / 5,440 = 37.9%, rounded to one decimal place. Calculate it from totals rather than averaging row percentages:

=SUM(I2:I5)/SUM(D2:D5)

For a store-level reconciliation, the following returns Milwaukee's $3,200 of sales. SUMIFS applies multiple criteria to a sum, so the same pattern can include a category or period in a larger worksheet. Covirage is not affiliated with Google.

=SUMIFS(D2:D5,A2:A5,"Milwaukee")

Turn the calculation into a constrained buying list

The buyer sets an illustrative target of four weeks of stock in Q1. This is a supplied planning assumption, not a recommended standard. Add M for proposed order units, N for historical unit cost and O for proposed order cost:

M2 = IF(K2="","",MAX(0,ROUNDUP(K2*$Q$1-F2-H2,0)))
N2 = IF(C2>0,E2/C2,"")
O2 = IF(OR(M2="",N2=""),"",M2*N2)

Milwaukee's towel proposal is 30 × 4 − 60 = 60 units. Madison's is 20 × 4 − 0 = 80. Against the buyer's $1,800 purchase budget, the $1,680 proposal leaves $120 before changes in current purchase cost or order multiples.

These quantities are review candidates. Replace historical cost with a supplier quote before approving an order. Check delivery timing, presentation minimums and carton sizes, then recalculate the spend. Madison's towel rate comes from fewer available days, so inspect those dates before accepting the implied quantity.

For an addition, document the customer need and test allocation separately because the new SKU has no observed sales history. For a removal, specify how remaining stock will be sold or transferred. The software evaluation guide covers how to test whether a tool can preserve these requirements.

Where assortment plans go wrong

• Stockouts read as weak demand. Madison sold fewer towels while the item was unavailable. Compare availability before deciding to remove it.
• Store totals hide local excess. Milwaukee's mat cover needs attention even if the category total looks acceptable. Keep store-level decisions visible.
• Historical velocity treated as a promise. Promotions, weather and season changes can make an observed rate unsuitable for the next buying period.
• A cost budget checked against retail sales value. Proposed purchases consume the budget at purchase cost. Validate current quotes and inbound cost treatment before approval.
• An order approved without exceptions. A formula can suggest stock that arrives after the selling window. Record delivery and space checks with the decision.

Review your own SKU file in Covirage

Bring a sample of your own SKU sales, stock and availability export to Covirage. Covirage maps the columns, its tools compute and check the figures, and the external AI model explains the store and product differences with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.

See revenue driver analysis for the supporting approach to explaining sales movements.