Blog · AI and self-service analytics · Sales teams
Use CRM and revenue exports to compare pipeline, ownership gaps and forecast changes. Build a weekly sales-operations review with spreadsheet formulas, a forecast bridge and clear exception handoffs.
AI for a sales operations manager is useful when it explains a weekly change that can be traced to the CRM records behind it. Preserve the prior export, compare the same opportunity population and reconcile the totals before asking for an explanation. That produces a review with specific exceptions and owners.
The operations owner needs to know what changed in open pipeline, which opportunities lack an owner and why the quarter forecast moved. The existing sales analytics overview covers the measures; this workflow establishes their source checks and handoffs. The broader AI analytics overview explains how checked calculations support the explanation.
Choose the reporting quarter, currency and snapshot cutoff. State whether amounts represent bookings, recurring contract value or recognized revenue. Define the forecast rule separately from the pipeline rule, then keep both unchanged between snapshots unless the review explicitly reconciles a policy change.
For this US company, the quarter forecast equals closed-won bookings plus open opportunities marked Commit. Open pipeline includes all open opportunities expected to close within the quarter. It includes Best case opportunities even though those do not enter this forecast. All amounts are USD.
Save CRM snapshots for October 16 and October 23, 2026, using the same cutoff time. Include opportunity ID, account, owner, status, forecast category, amount, expected close date and last update. Retain the source row and export timestamp for each record.
Use one row per opportunity. If the export contains product lines, aggregate those lines to the opportunity before combining it with an opportunity-level amount. Build the comparison from every identifier in either snapshot, including deals that disappeared or newly entered the filtered population.
Bring a revenue or booking export with the corresponding transaction reference where available. The guide to sales intelligence from existing files explains why those commercial records answer different questions. Do not require recognized revenue to equal new bookings: establish the documented relationship between them.
The table below is the completed comparison, with columns A through H. Current amount and category come from October 23; the forecast contribution columns apply the agreed rule to each snapshot. The owner cell for opportunity O-102 is empty.
| ID | Owner | Status | Current amount (USD) | Category | Prior forecast (USD) | Current forecast (USD) | Change (USD) |
|---|---|---|---|---|---|---|---|
| O-101 | East team | Open | 120,000 | Commit | 120,000 | 120,000 | 0 |
| O-102 | Open | 90,000 | Best case | 90,000 | 0 | -90,000 | |
| O-103 | West team | Won | 50,000 | Won | 60,000 | 50,000 | -10,000 |
| O-104 | Central team | Open | 40,000 | Commit | 0 | 40,000 | 40,000 |
| Total | 300,000 | 270,000 | 210,000 | -60,000 |
O-102 was previously Commit and now needs ownership and category confirmation. O-103 closed below its previous forecast contribution. O-104 entered the quarter's Commit population. A zero means confirmed no forecast contribution, not unavailable history. These are recorded state changes; sales managers still need to confirm the commercial reasons.
In H2, subtract the prior contribution from the current contribution and fill down:
=G2-F2
For the total forecast change:
=SUM(G2:G5)-SUM(F2:F5)
The result is 210,000 - 270,000 = -$60,000. The row changes also sum to -$60,000, giving an independent bridge check.
Use this Excel formula for current open pipeline:
=SUMIFS(D2:D5,C2:C5,"Open")
It returns 120,000 + 90,000 + 40,000 = $250,000. Microsoft's SUMIFS documentation specifies how to sum amounts against criteria; all ranges must cover the same rows.
Count open opportunities with no owner, then measure their value:
=COUNTIFS(C2:C5,"Open",B2:B5,"")
=SUMIFS(D2:D5,C2:C5,"Open",B2:B5,"")
The result is one opportunity worth $90,000. Microsoft documents COUNTIFS for counting records that meet all supplied criteria. Clean whitespace-only owner fields before applying an empty-cell test. Covirage is not affiliated with Microsoft.
The unowned share of open pipeline is 90,000 / 250,000 = 36.0%, rounded to one decimal place. Label the denominator: this is a share of pipeline value, not a share of opportunity count. A large unowned deal deserves a different handoff from several small incomplete records.
First prove the current forecast composition:
=SUMIFS(D2:D5,C2:C5,"Won")
=SUMIFS(D2:D5,C2:C5,"Open",E2:E5,"Commit")
Closed-won bookings are $50,000. Open Commit is 120,000 + 40,000 = $160,000. Together they equal the current forecast: 50,000 + 160,000 = $210,000. Separately, 250,000 of open pipeline plus 50,000 of won deals equals the $300,000 current amount total.
Then bridge the snapshots:
| Forecast movement | Amount (USD) |
|---|---|
| Prior snapshot quarter forecast | 270,000 |
| O-102 leaves Commit | -90,000 |
| O-103 closes below prior contribution | -10,000 |
| O-104 enters Commit | 40,000 |
| Current quarter forecast | 210,000 |
The identity is 270,000 - 90,000 - 10,000 + 40,000 = $210,000. A net movement alone would hide both the new commitment and the reduced booking. Keep those gross movements visible when selecting the weekly sales dashboard content.
Sales operations sends O-102's blank owner and category change to the responsible sales manager. Finance confirms O-103's final booking against the commercial record, while its sales manager explains the reduction. The Central team confirms the evidence supporting O-104's commitment.
Record the opportunity ID, old value, new value, question, reviewer and due date. At the next cutoff, compare the corrected source records with the exception list. Keep the review focused on changes that affect decisions; use the separate B2B sales KPI guide for the wider scorecard.
Bring a sample of your own dated CRM and revenue exports to Covirage, along with the quarter filters and forecast definition. 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, then set up your business and bring your files.
See Covirage for sales teams for the broader team context.
Check the snapshot dates, reporting currency, opportunity identifiers, close-date filters and forecast definitions. Confirm that each opportunity appears once at the intended reporting level. Separate missing fields from recorded zero amounts, and reconcile the exported population before interpreting weekly changes.
A current export shows the present record state. It cannot establish the previous state unless suitable history is included. Keep the prior dated snapshot or a reliable change-history export so opportunity additions, removals and amount changes can be demonstrated.
Exclude closed-won deals from open pipeline. A quarter forecast may include closed-won bookings plus selected open opportunities under the company's forecast policy. Label each population clearly so a booked sale is not also counted as an open deal.
Sales operations owns the comparison and the exception list. Sales managers confirm opportunity ownership, stage and commitment. Finance resolves booking and revenue differences. Give each exception a source record, responsible reviewer and follow-up date so the next review can verify its resolution.