Blog · Forecast and pipeline · Finance and FP&A teams
Revenue forecasting automation makes recurring preparation and comparisons repeatable while people own assumptions and approvals. Follow a spreadsheet workflow that reconciles saved versions, actual revenue and unexplained segment changes.
Revenue forecasting automation makes repeated preparation and checking consistent: preserve snapshots, match segments and periods, calculate differences and identify unexplained changes. Finance and business owners still choose assumptions and approve the outlook. Start with the exported forecasts you already use before changing the underlying projection method.
A recurring revenue review has two kinds of work. Copying approved figures into a consistent layout, matching period labels and calculating differences follow explicit rules. Deciding whether a customer will renew, whether delivery will slip or whether an assumption remains credible requires evidence and an accountable owner.
Make the first group repeatable without treating the second group as settled. A worksheet can flag a missing segment. It cannot infer that the missing segment has no revenue. An arithmetic bridge can reconcile perfectly while its commercial assumptions remain weak.
Keep the projection method in its own documentation. The sales projection guide covers building an estimate; this workflow covers preparing, comparing and reviewing the estimates after they have been submitted.
Use three input sheets and a comparison sheet. In this example, the company forecasts November 2026 recognized revenue by segment in USD. Accounting confirms which ledger accounts and adjustments define actual revenue. Bookings, billings and cash receipts stay in their own measures.
| Sheet | Required columns | What each row represents |
|---|---|---|
| Forecasts | A: version; B: target period; C: segment; D: amount | One approved or proposed segment-period amount per version |
| Actuals | A: target period; B: segment; C: amount | One closed segment-period revenue amount |
| Changes | A: change ID; B: target period; C: segment; D: signed change; E: reason | One documented movement between the selected versions |
| Review | A: segment; B: prior; C: latest; D: actual; E: change; F: explained; G: residual; H: error | One segment for the selected target period |
The displayed period label is 2026-11, stored as text consistently in each sheet. Put that label in Review J1, the earlier version ID V1 in J2 and the latest version ID V2 in J3. Preserve issue dates and approval status in the supporting file register even though the compact example shows only the comparison fields.
If the original export has more detailed rows, first prepare the segment totals and retain the detail for reconciliation. Do not mix a transaction-level file with a segment-level file under a rule that expects one row from each.
List expected segments from the agreed business scope, rather than taking only the segments present in the latest file. For each Review row, check that its forecast count is exactly one for each selected version.
In Excel, the earlier-version count is:
=COUNTIFS(Forecasts!$A$2:$A$1000,$J$2,Forecasts!$B$2:$B$1000,$J$1,Forecasts!$C$2:$C$1000,A2)
Use J3 in place of J2 for the latest-version count. The corresponding actuals count is:
=COUNTIFS(Actuals!$A$2:$A$1000,$J$1,Actuals!$B$2:$B$1000,A2)
Microsoft documents COUNTIFS as counting cells that meet multiple criteria. Under the declared grain, zero means missing and a count above one means duplication or an unresolved aggregation issue. Stop the comparison for that segment until the exception is resolved; a summed zero alone cannot distinguish absence from a genuine zero.
Also reject blank or text amounts before summing. In a helper column on Forecasts, enter =ISNUMBER(D2) and fill down; on Actuals, use =ISNUMBER(C2). Apply =ISNUMBER(D2) to the Changes amounts too. Every included row must return TRUE: an explicit numeric zero passes, while a blank or a number stored as text needs resolution. Row presence and amount validity are separate checks.
After counts pass, calculate prior forecast in B2, latest forecast in C2 and actual revenue in D2:
=SUMIFS(Forecasts!$D$2:$D$1000,Forecasts!$A$2:$A$1000,$J$2,Forecasts!$B$2:$B$1000,$J$1,Forecasts!$C$2:$C$1000,A2)
=SUMIFS(Forecasts!$D$2:$D$1000,Forecasts!$A$2:$A$1000,$J$3,Forecasts!$B$2:$B$1000,$J$1,Forecasts!$C$2:$C$1000,A2)
=SUMIFS(Actuals!$C$2:$C$1000,Actuals!$A$2:$A$1000,$J$1,Actuals!$B$2:$B$1000,A2)
Microsoft's SUMIFS documentation supports summing values that meet multiple criteria. Covirage is not affiliated with Microsoft.
Calculate E2 through H2 with these formulas, respectively:
=C2-B2
=SUMIFS(Changes!$D$2:$D$1000,Changes!$B$2:$B$1000,$J$1,Changes!$C$2:$C$1000,A2)
=E2-F2
=C2-D2
Fill down only through the expected segments. Extend the bounded ranges when the input grows, and check the final populated row is included. Keep Changes restricted to the selected V1-to-V2 comparison; mixing several version bridges would count explanations from the wrong revision.
An illustrative US commercial supplier submits these segment totals. The latest forecast is compared with actuals after November closes; the change register describes only movements from V1 to V2. Positive forecast error means overforecasting. All amounts are whole USD, with no rounding required.
| Segment | Prior forecast (USD) | Latest forecast (USD) | Actual revenue (USD) | Version change (USD) | Explained change (USD) | Residual (USD) | Forecast error (USD) |
|---|---|---|---|---|---|---|---|
| Equipment | 500,000 | 520,000 | 510,000 | 20,000 | 20,000 | 0 | 10,000 |
| Service | 300,000 | 310,000 | 280,000 | 10,000 | 10,000 | 0 | 30,000 |
| Parts | 160,000 | 170,000 | 190,000 | 10,000 | 0 | 10,000 | -20,000 |
| Total | 960,000 | 1,000,000 | 980,000 | 40,000 | 30,000 | 10,000 | 20,000 |
The supporting change rows are deliberately short:
| Change ID | Segment | Documented reason | Signed change (USD) |
|---|---|---|---|
| C-E | Equipment | Revised delivery schedule approved by operations | 20,000 |
| C-S | Service | Updated contract schedule approved by the segment owner | 10,000 |
| Total | 30,000 |
Equipment changes by 520,000 − 500,000 = $20,000, all documented. Parts changes by 170,000 − 160,000 = $10,000 with no explanation row. Its residual is therefore 10,000 − 0 = $10,000.
The bridge proves 960,000 + 30,000 + 10,000 = $1,000,000. The actuals comparison independently proves 1,000,000 − 980,000 = $20,000 of net overforecasting. A residual is an unexplained revision; an error is a difference from the realized outcome. They need different follow-up.
Send the Parts revision to its submitting owner for an assumption and supporting rows. Review Service's $30,000 overforecast separately, even though its revision was fully explained. A documented change is not necessarily an accurate one.
Reconcile all segment totals to the original exports and actuals to the closed ledger. Record which actuals close was used. If accounting later adjusts revenue, retain both the original comparison and the revised one with their dates.
The forecast bridge guide helps reviewers read movements without confusing them with realized errors. A forecasting dashboard can organize the meeting's views. Businesses with recurring contracts can use the separate SaaS revenue forecasting discussion for their operating assumptions.
Bring forecast snapshots, a change register and matching revenue actuals; Covirage maps the columns, and its tools compute and check the version and actuals comparisons. The external AI model explains the computed results with every figure traced to its rows, supporting the review of supplied forecasts. Create a free Covirage account and bring a sample of your own data.
See Covirage for finance teams for the supporting finance use cases.
Start with repeated preparation: consistent columns, period labels, completeness checks and comparisons between saved versions. These tasks have explicit rules and visible exceptions. Keep assumptions, materiality judgments and final approval with the business owners who understand contracts, customers and accounting treatment.
No. Repeatable calculations can reveal changes and missing inputs, but finance must confirm the revenue basis, investigate unexplained differences and approve the final outlook. A successful review leaves a recoverable snapshot and a clear explanation of the assumptions behind material changes.
Bring the approved earlier snapshot, the proposed or approved latest snapshot, matching actual revenue and a change register. Include version identifiers, issue dates, target periods, segment identifiers and currencies. Retain enough detail to reconcile each segment total to the original exported rows.