Merchandising dashboard: assortment, sales, and margin

A merchandising dashboard shows which product categories sell, which hold inventory, and which earn gross profit. Start with comparable SKU rows and show assortment count, sell-through, stock, and margin together, so a sales increase does not hide a weaker product mix.

Start with the merchandising question

The useful question is whether each category deserves its current assortment and inventory commitment. A general operating report may show total store sales; the merchandising view needs category and product detail that supports buying, range, and pricing decisions.

Use one reporting period and one unit of measure. A case and an individual pack are different units, even when they share a product description. Keep the stock-keeping unit, category, and active status in a product master. Count active products from that master if you want assortment breadth, including products that sold nothing.

The worked example uses a US snack brand's direct sales for September 2026. All products were active throughout the month, every product has one row, and there are no returns, transfers, or inventory adjustments. Those assumptions make the quantity reconciliation explicit; a real export needs separate columns for movements that occur.

Put the export into a usable layout

Use columns A through H as shown. Net sales exclude discounts and returns. COGS is the cost attached to the units sold, rather than the month's inventory purchases.

Maintain the same cost policy across categories. For purchased merchandise, IRS Publication 538 describes cost as invoice price after appropriate discounts plus transportation or other acquisition charges. Confirm the actual ledger treatment with finance; a supplier invoice price alone may not match reported COGS. The cost of goods sold guide covers that distinction.

Calculate the product measures

For this dashboard, use a period-stock definition of sell-through:

Sell-through = Units sold / (Opening units + Units received)

Gross profit = Net sales - COGS

Gross margin = Gross profit / Net sales

In Excel, put available units in I2, sell-through in J2, gross profit in K2, and margin in L2. Fill down through row 5:

=C2+D2
=IF(I2=0,NA(),E2/I2)
=G2-H2
=IF(G2=0,NA(),K2/G2)

The formulas return the following. Display percentages to one decimal place; calculate totals from unrounded values.

D2 has the highest margin percentage and the lowest sell-through. Its $1,800 gross profit is also the smallest contribution. That combination warrants a product review; margin percentage alone would give a different impression. The gross profit margin guide explains the weighted calculation in more depth.

Build the category comparison

Put category names in N2:N3. For Bars in N2, these formulas count the example's SKU rows and aggregate units and sales:

=COUNTIFS($B$2:$B$5,N2,$A$2:$A$5,"<>")
=SUMIFS($E$2:$E$5,$B$2:$B$5,N2)
=SUMIFS($G$2:$G$5,$B$2:$B$5,N2)
=SUMIFS($K$2:$K$5,$B$2:$B$5,N2)

Microsoft documents SUMIFS as summing values that meet multiple criteria; COUNTIFS counts rows meeting all specified conditions. Here the count works because each SKU appears once. Transaction exports require a distinct product count instead. Covirage is not affiliated with Microsoft.

Bars sell-through is 1,000 / 1,600 = 62.5%. Drinks reaches the same rate despite its uneven product performance. Total margin is 16,400 / 45,000 = 36.4%, rounded. Show category totals first, then the underlying products; neither view is sufficient alone.

Prove the figures before changing the range

The stock check is opening plus receipts minus units sold minus closing. For the total, 2,000 + 1,200 - 2,000 - 1,200 = 0. Test each SKU too:

=C2+D2-E2-F2
=SUM(G2:G5)-SUM(H2:H5)-SUM(K2:K5)

Both checks should return zero under the stated assumptions. Match the $45,000 sales total to the same-period sales register and the $28,600 COGS total to finance's report. Keep stock value and stock units separate. If holding efficiency is the question, use inventory turnover alongside this view.

Where it goes wrong

• Missing zero-sale products. A sales-only export understates assortment and hides inactive stock.
• Averaging product rates. Divide category totals; a small SKU should not carry the same weight as a large one.
• Different stock movements. Transfers and write-offs need their own reconciliation columns.
• Returns in the wrong period. Apply a documented policy for credit notes and returns so category comparisons remain consistent.

Compare your own assortment in Covirage

Bring a sample of your own SKU, sales, inventory, and cost files; Covirage maps the columns, and its tools compute and check the category figures. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring the export behind your next merchandising review.

See how this fits consumer goods brands.