Demand forecasting for existing products

Demand forecasting for existing products uses comparable sales history to estimate future demand, then checks the estimate against what actually happened. The important work is separating routine sales from promotions, stockouts and a genuine change in the product's market. A low shipment figure means different things when customers stopped ordering and when the shelf was empty.

Start with a product-period export

Use one row per SKU, site and week, or another period that matches the purchasing decision. Bring unit sales, forecast units, forecast issue date, promotion status and availability records. Keep returns and canceled orders identifiable instead of silently netting unlike transactions together.

Choose what actual demand means. Shipments are an observable measure, but they cannot reveal all orders lost during a stockout. Customer orders may be more useful when order dates and cancellations are reliable. Do not switch between these definitions during the comparison.

Freeze the forecast that existed when the decision was made. Comparing actuals with a forecast edited after the selling week measures hindsight. Keep the issue date beside the target period, even if the latest spreadsheet normally overwrites older versions.

Build a baseline from comparable history

A US office-supply distributor is forecasting an established carton of shipping labels. These historical weeks had the same pack size and distribution footprint, no promotion and full availability. Put units in B2:B5 on a history sheet.

The ordinary weekly baseline is 420 / 4 = 105 units. In Excel:

=AVERAGE(B2:B5)

Microsoft defines AVERAGE as the arithmetic mean. Its documentation also distinguishes empty cells, which references ignore, from numeric zeros, which are included. A missing sales record therefore needs investigation before it becomes part of the baseline.

This short history illustrates the calculation, not a seasonal forecasting policy. For a seasonal item, compare the same selling periods and check whether customer coverage changed. Forecasting demand for an existing product does not make every old observation relevant.

Keep adjustments visible

The planner expects a promotion to increase ordinary demand by an assumed 20%. That assumption is not a measured effect: the promotional forecast is 105 × 1.20 = 126 units. Retain the ordinary baseline and the adjustment separately so the commercial team can explain the difference.

The review sheet below uses columns A:G and rows 2:5. Forecasts were saved before each week. The product remained available during the week flagged for a possible life-cycle change; that label calls for investigation rather than establishing a cause.

In E2 and F2, filled down:

=IF(D2="Yes",B2-C2,"")
=IF(D2="Yes",ABS(E2),"")

Positive error means the forecast was high. The stockout row remains in the file, with its shipment gap visible, but is excluded from the comparable-demand score. Do not invent lost units to make it scoreable.

Reconcile the score before changing the plan

For comparable rows, forecast units total 336 and actual units total 320. Signed error is -5 + 6 + 15 = 16 units; absolute error is 5 + 6 + 15 = 26 units.

Weighted absolute percentage error = Sum of absolute errors / Sum of comparable actual units

Forecast bias = Sum of signed errors / Sum of comparable actual units

The scores are 26 / 320 = 8.1% absolute error and 16 / 320 = 5.0% positive bias, rounded to one decimal place. Calculate the comparable denominator explicitly:

=SUMIFS(C2:C5,D2:D5,"Yes")
=SUM(F2:F5)/SUMIFS(C2:C5,D2:D5,"Yes")
=SUM(E2:E5)/SUMIFS(C2:C5,D2:D5,"Yes")

Microsoft documents SUMIFS as adding cells that meet specified criteria. Use the same eligibility rule for both forecast and actual totals. The reconciliation is 336 - 320 = 16, matching the signed errors; investigate any disagreement before reporting accuracy.

For a larger export, forecast bias by SKU and site shows how to keep opposite misses from disappearing inside a company total.

Turn the review into a decision

The routine miss alone is not evidence that the baseline needs replacing. The promotion missed in the opposite direction, so raising every future week would also raise promotional forecasts that were already high. Ask the commercial owner whether the promotion ran as planned before changing its assumed uplift.

Check the possible stage change against customer orders, distribution changes and replacement products. Demand forecasting over the product life cycle covers that stage review; new product demand forecasting covers launches without usable sales history. Keep those jobs separate from this established-item comparison.

Where it goes wrong

• Treating stockout shipments as unconstrained demand. That can drive the next forecast down precisely when availability needs attention.
• Absorbing a promotion into ordinary history. A temporary event then inflates weeks without the offer.
• Joining different pack sizes or sites. Unit changes and distribution shifts can look like customer demand changes.
• Editing the scored forecast. Keep the original issue date and record any revised plan as another version.

Covirage is not affiliated with Microsoft.

Check your established products in Covirage

Bring a sample of your own sales export, dated forecasts, promotion flags and availability records. 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, which takes about a minute, then set up your business and bring your files.

See Covirage for supply-chain teams for the supporting industry context.