Creating a dashboard: from business question to view

Creating dashboard views starts with the business question, the measures that answer it, and source totals you can prove. Choose the view after agreeing on those foundations. A useful dashboard tells its reader what changed, how far it is from the target, and who should act.

Start with a decision the reader can make

For a US distributor's monthly management meeting, the question could be: are we selling enough, keeping the expected margin, and delivering what we promised? That question calls for revenue, gross margin, and on-time delivery. A list of every available field would make the meeting slower.

Write a short requirement for each measure: the decision, the person responsible, the reporting period, the comparison, and the source. For revenue, the decision might be whether the sales director needs a recovery plan. For delivery, it might be where the operations manager should investigate missed commitments.

Keep this step independent of the display software. The Excel dashboard guide covers implementation once the requirements are settled. Here the task is deciding what the view must mean and proving that its contents answer the question.

Define the measures before combining files

Use a September 2026 management workbook built from a sales ledger and shipment register. Revenue and cost belong to September's accounting period. Delivery uses shipments whose promised dates fall in September, including late and still-open shipments. These are different populations answering different questions; revenue need not be limited to those shipment records.

Use the same reporting cutoff in the workbook notes, but preserve the different date rules. Store targets in a separate plan tab with period, region, measure, unit, and approved value. A missing target should appear as missing, rather than becoming an assumed zero.

Keep lasting definitions in a versioned metric record. Record whether a measure is a dollar amount, a count, or a ratio; that choice determines how it can be totaled.

Build a small calculation table

The following regional export summary contains the complete inputs for this example. Dollars are USD. Counts are whole shipments, and displayed percentages are rounded to one decimal place after calculation.

Put these inputs on a sheet named Source, with headers in row 1 and the regional records in rows 2 and 3.

For each region, gross profit, margin, and delivery rate are:

F2 = B2-C2
G2 = IF(B2=0,NA(),F2/B2)
H2 = IF(D2=0,NA(),E2/D2)

Copy those formulas to row 3. Add the inputs in row 4 with =SUM(B2:B3) through =SUM(F2:F3). Calculate the total ratios as =F4/B4 and =E4/D4, because this example has nonzero denominators.

For a selected region named in View!A2, calculate revenue using:

=SUMIFS(Source!B2:B3,Source!A2:A3,View!A2)

Google's SUMIFS documentation describes summing a range against multiple criteria; add a period criterion when your source contains multiple periods. These formulas use standard Excel and Google Sheets syntax. Covirage is not affiliated with Microsoft or Google.

Compare actuals with the approved plan

The distributor approved company targets of $420,000 revenue, 38.0% gross margin, and 95.0% on-time delivery. Place measure, actual, and target in columns A through C of a separate comparison table.

Use =B2-C2 for the dollar gap and =(B2-C2)/C2 for the revenue percentage gap, which is -4.8% after rounding. For ratios stored as decimals, =(B3-C3)*100 returns the margin gap in percentage points. Repeat that calculation for delivery.

East is below West on both ratios, so it belongs on the investigation list. The table does not prove why. Ask for discount detail and shipment exception reasons before attributing the result to a salesperson or carrier. A performance dashboard develops this target-versus-actual review further.

Prove the result before arranging the view

Reconcile $400,000 revenue and $252,000 COGS to the September ledger totals. Their difference must equal $148,000 gross profit. Independently count 200 due shipments, of which 184 are on time and 16 are not: 184 + 16 = 200.

The total margin is 148,000 / 400,000 = 37.0%. Averaging the regional margins would give 37.5%, because it assigns equal weight to unequal revenue. Similarly, 184 / 200 = 92.0%; averaging the regional delivery rates would give 92.5%.

Use a small reconciliation block with source total, calculated total, and difference. Every expected difference should be zero before publication. The spreadsheet total checks explain further ways to detect duplicated and omitted records.

Place actuals and targets at the top of the view, a regional breakdown below, and a short action list beside the exceptions. Show period, units, cutoff, and definition notes where the reader can find them. Ask a colleague to locate the revenue shortfall and the delivery concern without your explanation.

Where creating a dashboard goes wrong

• Designing before defining. A polished revenue card still misleads if returns are excluded in one file and included in another.
• Combining records at different levels. Joining one invoice to several shipment lines can repeat the invoice amount. Aggregate each source to the agreed reporting level first.
• Averaging percentages. Recalculate each total ratio from its numerator and denominator, as the example demonstrates.
• Hiding missing information. Google's IFERROR reference explains that the function returns a supplied value when an expression errors. Replacing every error with zero can conceal a broken reference or unavailable input.
• Publishing without an owner. Give each unresolved exception a responsible person, an action, and a review date. A colored cell does not establish accountability.

Build the measures from your own file in Covirage

Bring your management workbook, ledger export, or Google Sheet with the measures and targets you want to compare. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See self-service analytics for business teams for the supporting approach.