Rolling order forecast: update demand from open orders

A rolling order forecast refreshes expected order intake, projects the resulting backlog and adds a new future period after each close. Start with the open-order register and reconcile new orders, cancellations and shipments before revising any assumptions. Existing backlog is already booked work, so it must not appear again as new intake.

Define intake, backlog and the forecast version

Use a consistent operational definition. In this example, gross intake means new units booked during the month; cancellations are units canceled during that month, including cancellations of older orders. Net intake is gross intake less cancellations. Backlog is accepted, unshipped units remaining at the cutoff.

The rolling forecast definition explains the moving horizon. This application needs two linked views: a flow forecast for future bookings and a stock projection for unshipped orders. Keep the frozen forecast used for comparison separate from the refreshed outlook.

Order intake and backlog are operational measures. They are not recognized revenue: FASB's revenue guidance bases recognition on satisfying performance obligations. A shipment measure in this workbook is therefore a backlog movement, rather than an instruction to book revenue.

Reconcile the open-order export first

Export order ID, line ID, customer, product, booked date, quantity changes, cancellation date, shipped quantity and remaining quantity. Retain event dates as well as the latest order status. A current status alone cannot reconstruct what changed during the month.

Closing backlog = Opening backlog + Gross new orders − Cancellations − Shipments

Net order intake = Gross new orders − Cancellations

A US equipment manufacturer closes October 2026 with these unit movements. The illustration has no returns, reinstatements or other adjustments; a real register should show those separately when they change open quantities.

Place this table in A:F, with product details in rows 2:3. In Excel, calculate F2 and fill down:

=B2+C2-D2-E2

The total must satisfy 900 + 860 − 60 − 810 = 890. Tie the opening 900 to the prior close and the closing 890 to the current open-order report. An equation can balance while both snapshots omit the same customer, so check the export control totals as well as the identity. The distinction is explained in control totals and identities.

Measure the frozen intake forecast

The prior forecast expected October net intake of 640 Pump A units and 310 Valve B units. Compare that dated expectation with net intake, rather than gross orders or closing backlog.

On a separate sheet, put these columns in A:F. E2 contains =C2-D2; F2 contains =E2-B2. Fill both down. Actual intake is 150 units below the frozen forecast, or 150 / 950 = 15.8% below it, rounded to one decimal place.

The product differences reconcile: −180 + 30 = −150. Retain the product and customer detail when revising assumptions; a combined shortfall does not mean every account weakened. Distinguish a canceled contract from an order whose booking date merely moved.

Refresh the remaining months and extend the horizon

After October closes, the team supplies the following assumptions for gross intake, cancellations and shipments. November starts with the verified 890 units. Each later month starts with the preceding projected close.

January is the added period. With this table in A:G, net intake in E2 is =C2-D2, closing backlog in G2 is =B2+E2-F2, and the next opening in B3 is =G2. Copy through the horizon.

November's check is 890 + 900 − 880 = 910. If the team changes only November cancellations from 60 to zero, net intake becomes 960 and closing backlog becomes 970. The 60-unit increase comes entirely from that assumption; shipments and opening orders have not changed.

For detail totals, use a sheet named Events with period in A, product in B, event type in C and positive units in D. Store period keys as text, such as 2026-10:

=SUMIFS(Events!D:D,Events!A:A,"2026-10",Events!B:B,"Pump A",Events!C:C,"New order")

Microsoft documents SUMIFS as summing values that meet multiple criteria. Use the same selection with event type changed to cancellation or shipment to rebuild each movement. Covirage is not affiliated with Microsoft.

Where rolling order forecasts go wrong

• Adding backlog to new intake. An existing order remains in opening backlog until shipped or canceled. It is not another booking next month.
• Counting amendments as full new orders. An increase should contribute its incremental units, rather than the entire revised line quantity.
• Mixing cutoff dates. A late cancellation in one export and an earlier backlog snapshot leave an unexplained movement.
• Overwriting the forecast. Preserve the frozen version before revisions; otherwise the comparison measures hindsight. Keep the budget and forecast distinct for the same reason.

Sales operations owns booking definitions and customer assumptions; the order desk confirms movements, and operations supplies shipment expectations. Finance maintains the separate revenue treatment and checks period boundaries.

Compare your order files in Covirage

Bring the open-order snapshots, movement export and dated intake forecast: Covirage maps the columns, its tools compute and check intake variances and backlog reconciliations, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See Covirage for industrial manufacturers for the supporting industry context.