Pipeline Reconciliation: Explain the Change Between Two Totals

A pipeline reconciliation explains how one open-pipeline total became another. Start with the opening snapshot, add and subtract the changes that affect the report, and compare the result with the ending snapshot. Keep any unexplained difference visible until you can trace it to records.

If Monday's pipeline was 500 and Friday's is 525, the increase of 25 doesn't tell you how much new pipeline was created. New opportunities, closed deals, amount changes and records moving into or out of the report can all contribute. A useful reconciliation makes those contributions readable.

First, make the two totals answer the same question

Write down the report definition before doing the arithmetic. For example: open opportunities belonging to a particular team, with expected close dates in a specified quarter, measured using the opportunity amount in one reporting currency. Record the opening and ending timestamps, including their time zone.

Keep the amount basis consistent. An unweighted opportunity amount and an amount multiplied by a forecast probability are different measures. Annual contract value and total contract value are different too. Choose the one your report uses; don't switch halfway through the comparison.

Freeze the team definition as well. If the report follows whoever currently belongs to Team North, a staffing change can move opportunities into the total without any new selling activity. If it follows a fixed list of owners, preserve that list. Either approach can answer a useful question, provided the reader knows which one you're using.

Save enough detail to reproduce each total: stable opportunity ID, inclusion status, amount, currency, stage, owner and the fields used by the report's filters. The data lineage of the report matters here. A screenshot of a total won't tell you which record changed.

A worked example: ten opportunities, two snapshots

The following example is fictional. Every amount is in thousands of the same reporting currency, using unweighted opportunity value. These are potential deal amounts, not revenue or customer payments. The report includes open opportunities owned by a fixed team and expected to close in the chosen quarter.

The six opening opportunities total 500. By the ending snapshot, several have changed and four other opportunities have appeared in the history.

A zero in this table means the opportunity contributes nothing to that snapshot's open-pipeline total. It doesn't mean the opportunity didn't exist. H existed outside the quarter, I was closed and J entered and left during the period. Preserve those distinctions in the supporting data.

Build the amount bridge

Use signed movements: positive amounts increase the included pipeline and negative amounts decrease it. In this example, the movements reconcile as follows.

500 + 135 − 95 − 40 + 30 − 20 − 60 + 50 + 25 = 525.

The report can now say something specific: open pipeline increased by 25, while 135 of opportunity value was newly created in scope. Those figures answer different questions. Treating the net increase as new creation would hide the closures and other changes.

Closed-won value removes an opportunity from this open-pipeline report. It doesn't establish recognized revenue, an invoice, payment or the timing of any of those events. Those need their own definitions and records.

One opportunity can contribute more than one movement

J is the easy row to miss. Its opening and ending contributions are both zero, so a comparison of the two snapshots alone won't reveal its creation and closure. Its +15 and −15 cancel in the bridge, but both matter when describing activity during the period.

That isn't double-counting J in the ending total. It is recording two different changes to the same opportunity. Use a stable ID to connect the changes, and give each recorded change an identifier or sequence position so the same event isn't imported twice. Keep the unit of analysis clear: snapshot rows describe opportunities; movement rows describe changes.

The same issue arises when an amount changes before a deal closes. Suppose an open opportunity starts at 80, increases to 100 and then closes won at 100. Its movements are +20 and −100, leaving an ending contribution of zero. Subtracting the opening 80 and also recording the +20 would incorrectly leave 20 in open pipeline.

For a removal, subtract the amount included immediately before that event, rather than the opening-snapshot amount. Record entries at their included entry value and amount-only changes as the difference. For a transfer between teams, record the amount leaving one team and entering the other at the transfer. Don't call that new company-wide pipeline.

Choose a rule for changes that happen together

An opportunity might change amount, owner and expected close date in one save. Your history may not expose an order among those field changes. The total change is still observable, but its allocation among movement categories may depend on your reporting rule.

For example, a record worth 60 outside the quarter could enter it with a new amount of 75. You could record +75 as an entry at the updated amount. A different documented approach might separate a +60 entry and a +15 amount change. Both reach the same ending contribution; their category totals differ.

Choose one approach, document it and apply it consistently. Don't invent timestamps to make one change appear to precede another. If the available history only supports a combined scope-and-amount change, label it that way. A clear combined category is more informative than a precise-looking split with no supporting sequence.

Apply the same care to reopened opportunities. In this example, I contributes +25 as reopened pipeline, separate from new creation. That is the example's reporting choice, not a universal CRM rule. State how your report treats reopening and preserve the earlier closure.

Keep the unexplained residual on the report

Calculate the residual as ending snapshot total minus the ending total explained by the bridge. Here it is 525 − 525 = 0.

If the ending snapshot instead says 535, report an unexplained +10. Don't create an “other growth” entry merely to force the calculation to match. Look for missing records, duplicate movement rows, an amount captured at the wrong time, changed filters, deleted or merged records, and differences in currency conversion.

If the reporting currency is converted, document whether the snapshots use fixed or changing exchange rates. An exchange-rate movement can change a converted total without any change to the underlying opportunity amount. Treat that explanation separately from sales activity.

A zero residual shows that the recorded arithmetic reconciles. It doesn't prove that the amounts are realistic, that the deals are qualified or that every movement was categorized correctly. You still need to review the underlying records. Likewise, a higher pipeline coverage ratio doesn't explain where the additional value came from.

A worksheet for the next review

Download the blank pipeline-reconciliation worksheet to record your report definition, snapshots, movements and unresolved differences.

Keep three linked parts: the two snapshots, the movement log and the summary bridge. Write the report definition at the top of each export or workbook so a colleague can reproduce the same population.

• Report definition: included team or owners, open stages, expected-close period, amount basis, reporting currency and conversion rule.
• Snapshot details: opening and ending timestamps with time zones, stable opportunity IDs, inclusion status and contributing amount.
• Movement log: opportunity ID, event ID or sequence, time when available, category, signed amount and source reference.
• Summary: opening total, signed category totals, calculated ending total, observed ending total and unexplained residual.
• Review notes: simultaneous-change policy, unavailable history, corrections and the person checking unresolved differences.

If you only have two snapshots, produce a record-by-record change comparison and say that it cannot reconstruct every intervening movement. J's creation and closure are the reason that limit matters. Ask what history is available before reporting gross creation or closure totals.

The useful result is a sentence your team can defend: “Open pipeline rose by 25; the bridge includes 135 newly created, 95 closed won and these other recorded movements. The residual is zero.” Keep that explanation beside the totals, with the supporting records available for review.