Combine month-to-date actuals with the remaining forecast

A current-month estimate combines what has happened with what is still expected. Its two parts must cover different activity. Choose a cutoff through which the actual data is sufficiently complete, then forecast only the period or transactions beyond that boundary. A dashboard refresh time alone does not establish how much underlying activity has reached the source.

Record the metric being estimated. Cash collected, orders booked, work completed and accounting revenue are different measures. Keep actual and forecast components on the same definition, even when they come from different systems. Changing the definition at the cutoff can create a plausible total with no coherent meaning.

ICAEW's spreadsheet principles recommend a clear flow from inputs through calculations to outputs and controls over input quality. For this model, expose the actual-data cutoff and remaining-forecast method as visible inputs. They should not be buried inside a formula that only its author understands.

Consider a hypothetical month with 20 operating days and an original forecast of $100,000 in a consistently defined sales measure. The original daily profile assigns $50,000 to the first ten days and $50,000 to the remaining ten. Actual activity for the first ten days is $60,000.

Keeping the original expectation for the remaining days produces a new full-month estimate of $110,000. If supported new information raises the remaining forecast to $55,000, the estimate becomes $115,000. Adding $60,000 actuals to the original full-month $100,000 would produce an incorrect $160,000 by retaining activity already replaced.

The revised $115,000 is also different from simply doubling the first-half $60,000. That shortcut assumes the second half will behave like the first. A scheduled closure, uneven delivery pattern or concentrated month-end demand may make that assumption unsuitable.

A transaction-level remaining forecast needs a way to remove items that have become actuals. Use stable identifiers where available and investigate unmatched items. A customer payment can appear in the bank while remaining in a collection schedule; a completed delivery can remain in the planned dispatch list. Time filters alone may not prevent this duplication.

Some actual periods remain incomplete because source feeds arrive late or estimates are needed. Show those known gaps separately from the future forecast. For example, an estimate for unreported work performed before the cutoff belongs to the elapsed-period completion adjustment. It should not silently remain in the forward activity forecast as well.

Avoid overwriting recorded actuals to force agreement with an expected total. Route accounting corrections through the normal review process. The planning model can show an explicitly labeled adjustment while the issue is investigated, with an owner and a clear explanation of its effect.

At each update, separate newly observed actual activity from changes to the remaining expectation. A revised estimate may rise because the elapsed period outperformed, because future demand improved, or both. That distinction helps management judge whether the change is repeatable and whether an operating response is needed.

Retain dated versions of the full-month estimate with their actual cutoff and remaining horizon. Comparing two totals without those dates can mistake a routine replacement of forecast by actuals for a new commercial assumption. Review late data corrections separately from genuine forecast error.

Before using the result for a spending decision, reconcile actuals plus completion adjustments plus remaining forecast to the displayed month total. Check the boundary again after advancing the cutoff. A model that worked on the tenth day can duplicate or omit activity when a later refresh moves that boundary forward.