Blog · Board and management reporting · Finance and FP&A teams
Build a gross margin dashboard from product, customer, and month-level revenue and cost exports. The worked example separates mix effects from changes in product margin and checks every view against the monthly total.
A gross margin dashboard compares profit dollars and margin rates by product, customer, and month. Start with net sales and their matching cost of goods sold, then build every view from that same population. The useful result is a short list of drivers to investigate, supported by totals that reconcile.
For the underlying definition, use the gross profit margin formula and example. This walkthrough focuses on preparing the file, building the views, and explaining a monthly movement.
Retain month, customer, product, net revenue, and matching cost of goods sold. If the export contains invoice lines, also retain invoice and line identifiers so repeated sales or costs can be found before aggregation. Use USD throughout this example.
Reconcile net revenue and COGS to the closed income statement before comparing customers. The SEC's guide to financial statements explains that subtracting cost of sales from net revenue gives gross profit. Your dashboard needs those same cost boundaries in each period; selling and administrative costs belong in a later profitability view.
The following US distributor export is already grouped by month, customer, and product. There are no returns or unassigned costs in this compact example. Enter each month as its first calendar date and display it as a month name.
| Month | Customer | Product | Net revenue (USD) | COGS (USD) |
|---|---|---|---|---|
| August 2026 | North | A | 120,000 | 78,000 |
| August 2026 | South | A | 80,000 | 52,000 |
| August 2026 | North | B | 60,000 | 42,000 |
| August 2026 | South | B | 40,000 | 28,000 |
| September 2026 | North | A | 100,000 | 65,000 |
| September 2026 | South | A | 60,000 | 39,000 |
| September 2026 | North | B | 90,000 | 67,500 |
| September 2026 | South | B | 50,000 | 37,500 |
Place the table in A1:E9. Add gross profit in F and gross margin in G. Enter gross profit in F2:
=D2-E2
Enter margin in G2, format it as a percentage, and fill both formulas down:
=IF(D2=0,"",F2/D2)
A blank rate means the denominator is zero; the row's dollars must remain visible. The first row gives 120,000 − 78,000 = $42,000 gross profit and 42,000 / 120,000 = 35.0% margin.
Put September 1, 2026, in K1 and product A in J2. Enter revenue in K2:
=SUMIFS($D$2:$D$9,$A$2:$A$9,$K$1,$C$2:$C$9,$J2)
Enter COGS in L2:
=SUMIFS($E$2:$E$9,$A$2:$A$9,$K$1,$C$2:$C$9,$J2)
Enter gross profit in M2:
=K2-L2
Enter the margin rate in N2:
=IF(K2=0,"",M2/K2)
Put B in J3 and fill the formulas down. Microsoft's SUMIFS documentation confirms that the function sums values meeting multiple criteria; here those criteria are month and product. Covirage is not affiliated with Microsoft.
The monthly view answers whether the company changed. All displayed rates below are rounded to one decimal place, using unrounded dollars in the calculation.
| Month | Revenue (USD) | COGS (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|---|
| August | 300,000 | 200,000 | 100,000 | 33.3% |
| September | 300,000 | 209,000 | 91,000 | 30.3% |
Revenue is unchanged, while gross profit falls by $9,000 and margin falls by 3.0 percentage points. The product view identifies where the change appears:
| September product | Revenue (USD) | COGS (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|---|
| A | 160,000 | 104,000 | 56,000 | 35.0% |
| B | 140,000 | 105,000 | 35,000 | 25.0% |
| Total | 300,000 | 209,000 | 91,000 | 30.3% |
Then build a separate customer summary. Use the same formulas, replacing the product criteria range $C$2:$C$9 with the customer range $B$2:$B$9 and placing the customer name in J2.
| September customer | Revenue (USD) | COGS (USD) | Gross profit (USD) | Gross margin |
|---|---|---|---|---|
| North | 190,000 | 132,500 | 57,500 | 30.3% |
| South | 110,000 | 76,500 | 33,500 | 30.5% |
| Total | 300,000 | 209,000 | 91,000 | 30.3% |
North contributes more gross profit dollars, while South has a slightly higher rate. Keep both measures visible: ranking customers by rate alone hides the size of their contribution. A broader margin dashboard can include other profit levels, but this view keeps gross margin's cost scope fixed.
August product A earned (200,000 − 130,000) / 200,000 = 35.0%; B earned (100,000 − 70,000) / 100,000 = 30.0%. Apply those rates to September product revenue to isolate the effect of the changed revenue mix:
| Bridge step | Calculation | Gross profit (USD) |
|---|---|---|
| August actual | 300,000 − 200,000 | 100,000 |
| September revenue at August product rates | 160,000 × 35% + 140,000 × 30% | 98,000 |
| September actual | 56,000 + 35,000 | 91,000 |
The mix effect is 98,000 − 100,000 = −$2,000. The remaining product-rate effect is 91,000 − 98,000 = −$7,000. Together they explain −$9,000. Because total revenue is unchanged, this particular bridge needs no separate total-revenue effect.
The export proves that B's margin fell; it does not prove why. Price, unit cost, returns, and cost adjustments need their own supporting rows before a reviewer names a cause. Finance owns the reconciliation, while sales and product owners investigate the commercial explanation.
Product gross profit sums to 56,000 + 35,000 = $91,000. Customer gross profit independently sums to 57,500 + 33,500 = $91,000. Both must equal September's ledger-based gross profit. Divide that total by $300,000 revenue for the company rate.
Upload a sales-and-cost file with product, customer, month, revenue, and COGS; Covirage maps the columns, and its tools compute and check the margin dollars, rates, and totals. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See customer profitability for related account-level questions.
Match the refresh to the availability of reliable costs. A monthly closed-ledger view is useful for finance review. More frequent views can support commercial decisions, provided estimated costs are labeled and later reconciled to posted costs.
Gross margin deducts cost of goods sold from revenue. Customer profitability may also deduct account-specific selling, delivery, and service costs. Keep those later deductions in a separate view so a change in cost scope does not distort gross margin trends.
Show their dollar amounts and flag the rate as unavailable. Do not turn a division error into a zero margin. Investigate whether the row represents a cost adjustment, a credit, or an incomplete match before interpreting its performance.