Monthly demand forecast: reconcile the planning cycle

A monthly demand forecast estimates customer demand for each calendar or fiscal month and records the assumptions behind that estimate. Keep dated versions, compare them with actual demand on the same basis, and reconcile the revisions. A total that matches actual demand can still contain substantial item errors.

Define the month and the decision first

Choose calendar months or your company's fiscal periods, then use that choice in every export. Record the required delivery period rather than mixing order-entry dates with shipment dates. State whether demand means accepted orders, requested units or fulfilled sales.

The planning horizon should match the decision. A purchasing commitment needs the version available when the order was placed; a warehouse staffing decision may use a later version. Comparing those forecasts without their issue dates makes a later plan look better simply because it contains more information.

This follows the principle in Forecasting: Principles and Practice: accuracy must be evaluated using genuine forecasts against observations not used to create them. Label the horizon, freeze the version and keep late corrections separate.

A baseline demand forecast establishes the starting demand view. The monthly cycle adds documented changes and measures what happened after approval.

Assemble the planning-cycle export

Use one row per item, site, demand month and forecast version. Preserve the version's issue date and approval status. Keep actual demand in a separate export until it is matched to that combination.

Exclude canceled demand according to the stated policy. Resolve duplicate order lines before aggregation. Treat missing rows as unresolved coverage until the owner confirms they represent zero demand.

When aggregating an order export, suppose Orders has item in A, site in B, required date in C and units in D. Put the item in K1, site in K2 and month start in K3:

=SUMIFS(Orders!$D:$D,Orders!$A:$A,$K$1,Orders!$B:$B,$K$2,Orders!$C:$C,">="&$K$3,Orders!$C:$C,"<"&EDATE($K$3,1))

The exclusive next-month boundary also handles dates containing times. Microsoft's SUMIFS documentation explains the multiple-criteria aggregation used here. Covirage is not affiliated with Microsoft.

Work one monthly forecast from revision to actual

A US household-products distributor prepares this illustrative November 2026 plan for one warehouse. The early version was issued on October 23; the revised version was issued on October 30. Actual demand becomes available after the month closes. All figures are units, and baseline equals the early version in this example.

Put these columns in A:I, with the first item in row 2. Enter these Excel formulas in E2, G2, H2 and I2, respectively:

=C2+D2
=E2-B2
=E2-F2
=ABS(H2)

Fill down through row 4. Positive signed error means overforecasting; negative means underforecasting. Sum the columns in row 5, rather than taking averages of item quantities.

The revision bridge proves the plan: 2,400 baseline units + 200 promotion units = 2,600 revised units. Separately, revised forecast less actual demand is 2,600 − 2,600 = 0. The item errors also reconcile: 20 − 100 + 80 = 0.

Check whether the revision actually helped

The revised total is exact, but the absolute item errors total 20 + 100 + 80 = 200 units. Weighted absolute percentage error, using actual demand as the denominator, is:

WAPE = Sum of absolute item errors / Sum of actual demand

=SUM(I2:I4)/SUM(F2:F4)

The result is 200 / 2,600 = 7.7%, rounded to one decimal place. Signed aggregate bias is 0 / 2,600 = 0.0% on the same rounding basis. Read both: bias shows direction; WAPE shows error that cancellation hides.

Now test the early version. Its item errors are −80, −100 and −20 units, with absolute errors totaling 200. Its WAPE is also 7.7%. The revision improved the aggregate total but did not reduce overall absolute item error. It moved too much demand onto C while leaving B unchanged.

Review promotion evidence with the commercial owner before carrying the change forward. Use demand forecast bias by SKU and site for persistent directional errors, and budget versus forecast to keep the operating estimate separate from the financial target.

Where it goes wrong

• Overwriting snapshots. A revised workbook cannot prove what purchasing knew before placing an order.
• Mixing calendars. Fiscal periods and calendar months can allocate the same order differently.
• Calling sales unconstrained demand. Check stock availability; an unconstrained demand forecast separates observed sales from lost-demand assumptions.
• Judging only the total. Offsetting item errors hide the inventory and service consequences of the plan.

Reconcile your monthly cycle in Covirage

Bring a sample of your own forecast snapshots, assumptions and actual-demand exports. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring the versions used for your planning decisions.

See forecast analysis for the supporting workflow.