Blog · Board and management reporting
Define a dashboard around the decisions its readers make. Use a worked revenue and margin example to choose measures, filters, comparison periods and reconciliation checks.
A customizable dashboard lets readers change measures, filters and comparison periods while keeping the underlying definitions consistent. Start with the decisions the audience makes, then work backward to the source rows and calculations. A layout is useful when each change of view still produces a figure that can be proved.
Write a sentence naming the reader, the decision and the reporting cadence. For a US distributor, that might be: the sales director reviews monthly revenue and gross margin by region before changing account priorities.
That sentence determines what belongs on the page. Revenue shows scale; gross margin shows what remains after the cost of goods sold; order count helps distinguish larger orders from more orders. A daily shipment queue would need a different view.
Microsoft's dashboard design guidance recommends considering the audience and emphasizing essential information that can be understood at a glance. Apply that principle before choosing colors or chart types. Keep the period, units and selected scope beside the numbers they qualify.
This is a requirements workflow you can use across reporting tools. The Excel dashboard guide covers that implementation, while dashboard software covers tool selection. Decide what must be calculated before choosing where to display it.
Suppose Summit Supply, a fictional US distributor, summarizes its order export into the following period and region rows. Revenue is net of returns and discounts; cost of goods sold belongs to those same orders. Each order belongs to one region and one period.
In a worksheet, use columns A through E in the order shown, with the data in rows 2 through 5. Period labels are text values exactly as displayed here.
| Period | Region | Revenue (USD) | COGS (USD) | Orders |
|---|---|---|---|---|
| September 2026 | North | 120,000 | 78,000 | 120 |
| September 2026 | South | 80,000 | 52,000 | 80 |
| October 2026 | North | 132,000 | 88,000 | 110 |
| October 2026 | South | 108,000 | 70,000 | 90 |
Preserve the original order identifiers in the underlying export. If an order has multiple lines, summarize those lines before counting orders; counting line records would change the meaning of the measure.
Write a short definition for every selectable measure. Revenue and COGS are additive across regions. Gross margin and average order value are ratios. They must be recalculated after filtering, rather than added or averaged across the displayed rows. Use the established gross profit margin definition for that measure.
For a small verification sheet, put the current period in H2, the region in H3 and the comparison period in H4. Initially use October 2026, All and September 2026. Offer only region choices present in the export, plus the explicit All choice.
In H6, calculate selected revenue:
=IF($H$3="All",SUMIFS($C$2:$C$5,$A$2:$A$5,$H$2),SUMIFS($C$2:$C$5,$A$2:$A$5,$H$2,$B$2:$B$5,$H$3))
Use the same formula in H7 with the sum range changed to $D$2:$D$5 for COGS. In H8, change that sum range to $E$2:$E$5 for orders. The criteria stay identical, so the measures describe the same selected business.
These formulas work in Excel and Google Sheets. Google's SUMIFS reference defines the function as summing a range subject to multiple criteria; its criteria ranges must correspond to the values being summed.
Calculate gross profit in H9, gross margin in H10 and average order value in H11:
=H6-H7
=IF(H6=0,NA(),H9/H6)
=IF(H8=0,NA(),H6/H8)
Show an unavailable ratio when its denominator is zero. Do not display a zero percentage as though a valid margin had been measured.
For prior-period revenue in H12, repeat the H6 formula with $H$4 replacing $H$2. Revenue growth in H13 is:
=IF(H12=0,NA(),H6/H12-1)
Format margin and growth cells as percentages. These formulas verify the measures; the chosen reporting tool determines how readers make their selections.
The company view produces the following comparison. Dollar amounts are exact; percentages are rounded to two decimal places after calculation.
| Measure | September 2026 (USD) | October 2026 (USD) | Calculation for October |
|---|---|---|---|
| Revenue | 200,000 | 240,000 | 132,000 + 108,000 |
| COGS | 130,000 | 158,000 | 88,000 + 70,000 |
| Gross profit | 70,000 | 82,000 | 240,000 - 158,000 |
| Average order value | 1,000 | 1,200 | 240,000 / 200 |
| Measure | September 2026 | October 2026 | Calculation for October |
|---|---|---|---|
| Gross margin | 35.00% | 34.17% | 82,000 / 240,000 |
| Orders | 200 | 200 | 110 + 90 |
Revenue growth is 240,000 / 200,000 - 1 = 20.00%. Order count is unchanged, while average order value rises. Gross profit grows by 82,000 / 70,000 - 1 = 17.14%, less than revenue growth. Gross margin falls by 0.83 percentage points, calculated from the unrounded ratios.
Put revenue, gross margin and orders at the top. Place the period comparison beneath them, followed by a region table that explains the selected total. Each measure answers part of the sales director's question without requiring a separate visual for every available column.
Switching the region to North changes October revenue to $132,000 and prior revenue to $120,000. Growth becomes 10.00%. The comparison must inherit the same region filter; otherwise a regional result would be compared with a company result. Use variance analysis when the next question concerns changes against an approved plan.
The October regions provide a simple reconciliation: $132,000 + $108,000 = $240,000 of revenue and $44,000 + $38,000 = $82,000 of gross profit. The company margin is therefore 82,000 / 240,000 = 34.17%.
The regional margins are 33.33% and 35.19%. Their simple average, calculated before rounding, is 34.26%, which does not equal the company margin. Changing the layout cannot fix that aggregation error; dividing summed profit by summed revenue does.
Before approving the view, compare the unfiltered totals with the source report, check missing region assignments and confirm the export cutoff. Clear all selections and recover the company figure. Then test a region and verify that both periods change together. For a channel-specific application, the SEO dashboard owns search traffic and lead measures.
Covirage is not affiliated with Microsoft or Google.
Bring an order export or Google Sheet with periods, regions, revenue and costs. Covirage maps the columns, its tools compute and check the comparisons, and 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 data.
See self-service analytics for the supporting business workflow.
Offer choices that change a business decision, such as period, region, department or measure. Keep definitions, units and reconciliation rules consistent. A useful filter narrows the question without quietly changing how a metric is calculated.
Put the decision measures first, the comparisons next and the supporting breakdown below. Keep the selected period and filters visible. Use a table when readers need exact values and a chart when they need to see a pattern.
They can share definitions and source totals while using different views. Finance may need company margins, while sales needs regional performance. Agree on which filters apply to each measure and preserve a common company view for reconciliation.