Blog · Board and management reporting · Sales teams
A CRM dashboard compares opportunity stages, sales activities, and closed business by team. Work through export rows, reconciliations, and the distinction between won bookings and recognized revenue.
A CRM dashboard compares open pipeline, completed selling activity, and closed business from consistent exports. A dashboard CRM managers can trust separates the pipeline snapshot from period outcomes and shows whether reported revenue comes from finance records or a CRM booking field.
Open pipeline answers what remains available to pursue at a particular date. Completed activity answers what the team did during a period. Closed outcomes answer which deals reached a final decision during that period. Recognized revenue answers a finance question that a won opportunity amount cannot answer by itself.
Give each panel a label that states the population and time basis. An opportunity opened earlier can be in the current pipeline. An opportunity closed earlier can have revenue recognized in the current period. Neither is an error if the populations are stated clearly.
The CRM dashboard template covers a reusable workbook arrangement. Here the focus is the measures and evidence needed for a sales review, rather than a particular screen layout.
Consider a US equipment services company reviewing April 2026. The opportunity snapshot below is dated April 30. All won and lost deals shown closed during April; all completed activities shown occurred during April. Opportunity amounts are the company's booked contract-value field, in USD.
| Opportunity | Team | Stage | Opportunity amount (USD) | Completed activities | Actual close date |
|---|---|---|---|---|---|
| C101 | East | Qualification | 60,000 | 4 | Open |
| C102 | East | Proposal | 90,000 | 6 | Open |
| C103 | West | Negotiation | 120,000 | 3 | Open |
| C104 | East | Won | 80,000 | 5 | April 12, 2026 |
| C105 | West | Won | 40,000 | 4 | April 20, 2026 |
| C106 | West | Lost | 70,000 | 2 | April 22, 2026 |
| Total | 460,000 | 24 |
The activity column is a count of unique completed activity IDs from the activity export, grouped by opportunity before joining it to this table. Unfinished tasks and repeated copies of the same activity are excluded. This example has no unlinked activity; a real dashboard should report unlinked completed activity separately instead of silently losing it.
Keep the unaggregated activity file available for checking. Joining every activity row directly to opportunities would repeat each opportunity amount, so summing the joined amount would overstate the book.
Open pipeline = Sum of opportunity amounts in open stages at the snapshot date
Closed-opportunity win rate = Won opportunities / Won and lost opportunities closed in the period
Qualification, Proposal, and Negotiation are the open stages in this example. Their values are $60,000, $90,000, and $120,000, respectively. Open pipeline is 60,000 + 90,000 + 120,000 = $270,000 across three opportunities.
Won business is 80,000 + 40,000 = $120,000 across two deals. Lost value is $70,000 across one deal. The closed-opportunity win rate is 2 / 3 = 66.7%, rounded to one decimal place. Open opportunities stay outside that rate because their outcomes remain unknown.
With the opportunity table in A1:F7, Excel can calculate open pipeline using:
=SUMIFS(D2:D7,C2:C7,"Qualification")+SUMIFS(D2:D7,C2:C7,"Proposal")+SUMIFS(D2:D7,C2:C7,"Negotiation")
Microsoft documents SUMIFS as a way to sum values meeting specified criteria. Use a maintained stage mapping for a longer stage list, and check that every stage maps to exactly one reporting category.
The win-rate formula for this table is:
=COUNTIFS(C2:C7,"Won")/(COUNTIFS(C2:C7,"Won")+COUNTIFS(C2:C7,"Lost"))
Microsoft's COUNTIFS documentation explains counting entries that meet criteria. These formulas use a population whose closing dates were already restricted to April. In a larger export, apply actual-close-date criteria too. Report an empty outcome population explicitly instead of turning it into zero conversion.
Grouping the same opportunity rows produces this review table:
| Team | Open pipeline (USD) | Won bookings (USD) | Lost value (USD) | Completed activities |
|---|---|---|---|---|
| East | 150,000 | 80,000 | 0 | 15 |
| West | 120,000 | 40,000 | 70,000 | 9 |
| Total | 270,000 | 120,000 | 70,000 | 24 |
East activity is 4 + 6 + 5 = 15; West activity is 3 + 4 + 2 = 9. Those counts establish workload, not the quality of selling. A technical negotiation and a routine follow-up can each appear as a completed activity while requiring different effort.
East has more open value, but West's open value is entirely in Negotiation. That suggests different review questions rather than an automatic ranking. Ask East about qualification and proposal progress; ask West about the evidence supporting the negotiation's next step. Neither stage label guarantees a future win.
For a forecast discussion, reading a forecast bridge deal by deal addresses changes in the expected number. The current snapshot cannot establish movement without a comparable earlier snapshot.
Finance supplies the following summarized April revenue rows. In this deliberately small example, all April revenue is associated with the listed won opportunities. In a live business, include revenue linked to earlier wins as well, rather than filtering finance to this month's CRM winners.
| Team | Opportunity reference | Recognized revenue (USD) |
|---|---|---|
| East | C104 | 76,000 |
| West | C105 | 38,000 |
| Total | 114,000 |
The revenue total is 76,000 + 38,000 = $114,000. Keep it beside, and distinctly labeled from, $120,000 of won bookings. The arithmetic difference is $6,000, but the table alone does not explain the accounting reason for that difference; finance must supply the bridge before anyone interprets it.
Keep the relationship between source files visible. Sales intelligence from the data you already hold covers the wider business-file review, including information that lives outside the CRM export.
Open, won, and lost opportunity values must exhaust this snapshot: 270,000 + 120,000 + 70,000 = $460,000. The corresponding opportunity counts are three open, two won, and one lost, totaling six unique IDs. Team activities reconcile as 15 + 9 = 24.
Tie the revenue total separately to finance's April ledger report. A clean CRM reconciliation does not prove a revenue total, and a balanced revenue report does not prove that every opportunity is present.
Sales operations owns opportunity uniqueness, stage mappings, and snapshot dates. Sales managers own next-step interpretation and follow-up. Finance owns the revenue definition and ledger reconciliation. Resolve disagreements in those definitions before discussing performance.
Covirage is not affiliated with Microsoft.
Bring your opportunity and activity exports, plus a finance revenue file when revenue is part of the question. Covirage maps the columns, and its tools compute and check the measures; 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 sales insights for the wider sales performance review.
Start with open opportunity value by stage and owner, completed activity for the reporting period, and won and lost outcomes. Add recognized revenue only when a finance source supports it. Each measure needs a date definition and a clear route back to its source rows.
Closed-won value is usually a CRM measure of booked business, whose field definition depends on the company. Revenue comes from the finance records and can follow a different timing or amount. Label them separately and reconcile them only with an agreed finance bridge.
Choose a review cycle that matches the decisions and export availability. Pipeline allocation may need a different cadence from recognized revenue. Always display the snapshot date and reporting period so readers can see whether the inputs are current enough for the decision.