CRM forecasting: turn opportunity exports into a review

CRM forecasting is reviewable when a dated opportunity export can be reconciled to the sales that eventually closed. Freeze the forecast inputs, keep the same sales measure on both sides, and separate later arrivals from opportunities already visible. A current opportunity list cannot reliably reconstruct an earlier forecast.

Define the comparison before exporting

Write the target period, snapshot date and measure above the worksheet. For this example, a US industrial supplier reviews September 2026 net bookings against its September 1 forecast. All amounts are USD, after agreed booking adjustments; accounting revenue is outside this comparison.

The forecast contribution is the amount management included for each opportunity. It might come from an approved weighting or a documented override. Do not apply another probability to an amount already weighted. The comparison of sales forecasting methods covers method selection; this workflow tests the result your team supplied.

Agree whether forecast categories are mutually exclusive. A cumulative total labeled best case may already include commit. Use the underlying opportunity rows once, with their original category, rather than adding overlapping totals. Keep the definitions in your sales forecast categories review.

Preserve the source rows

Keep an untouched forecast export with snapshot timestamp, opportunity ID, owner, team, category, amount, expected close date, currency and approved contribution. Save the results export separately with opportunity ID, actual close date, outcome and booked amount.

Use opportunity ID to assemble a comparison worksheet. Start with the union of IDs in both files, so an opportunity missing from either side remains visible. Preserve original team and category for evaluating the forecast. Add current ownership in separate columns if managers also need an accountability view.

Check the source key before joining amounts. On a sheet called Snapshot, put snapshot timestamp in column A and opportunity ID in B. This Excel formula must return TRUE for each populated row, after filling down across the export:

=COUNTIFS($A$2:$A$10000,A2,$B$2:$B$10000,B2)=1

Microsoft documents COUNTIFS as counting rows that meet all supplied criteria. Here it checks that the same opportunity does not appear twice within a snapshot. If the export has product lines, aggregate those lines to opportunity level before applying this rule.

Work the opportunity comparison

The table below is the assembled comparison, with headers in row 1 and opportunities in rows 2-7. A106 was created after September 1; its opening amount and contribution are explicitly zero because it was absent from that snapshot.

With columns A-H as shown, calculate the signed dollar miss and percentage miss:

=SUM(H2:H7)-SUM(E2:E7)
=(SUM(H2:H7)-SUM(E2:E7))/SUM(E2:E7)

Actual bookings exceed forecast by $23,000, or 8.4%, rounded to one decimal place. Positive means actual is higher. The $400,000 opportunity amount is a control on the exported population, not the forecast denominator.

For East's forecast and actual respectively, use:

=SUMIFS($E$2:$E$7,$B$2:$B$7,"East")
=SUMIFS($H$2:$H$7,$B$2:$B$7,"East")

Microsoft's SUMIFS guidance describes summing amounts that meet criteria. Keep the amount and criteria ranges aligned. East has forecast bookings of $160,000 and actual bookings of $148,000; West has $115,000 and $150,000. Their misses of -$12,000 and $35,000 add to $23,000.

Reconcile before interpreting performance

First, the forecast contributions must equal the approved September 1 forecast. Second, actual bookings must equal the final September bookings register, including credits or cancellations under the same policy. Explain unmatched IDs and any manual adjustment rather than forcing an unexplained balancing row.

Then inspect the movement by original category. Commit contributes -$2,000, best case -$40,000, pipeline $35,000, excluded zero and later arrivals $30,000. These movements sum to $23,000.

The original cohort closed $268,000 against $275,000 forecast, a $7,000 shortfall. Later arrivals supplied $30,000 and reversed the overall direction. The useful question is whether managers could reasonably have seen those arrivals, rather than whether the headline result happened to beat forecast. Put this distinction in the sales forecasting report that accompanies the review.

Where it goes wrong

• Revised history. Using current categories to score the opening forecast hides which commitments actually missed.
• Multiple rows per opportunity. Product lines, revenue schedules and split ownership can multiply an amount when joined without checking the intended grain.
• Mixed measures. Annual contract value, bookings and recognized revenue cannot share one variance calculation without an explicit mapping.
• Missing arrivals or departures. An inner match drops new wins and removed opportunities. Keep unmatched IDs and investigate them.

Covirage is not affiliated with Microsoft.

Review your own CRM exports in Covirage

Bring a sample of your own frozen opportunity forecast and closed-results export. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the movements with every figure traced to its rows. Create a free Covirage account; account creation takes about a minute, then you set up your business and bring your files.

See how this supports forecast analysis.