SKU level demand forecasting: avoid aggregate blind spots

SKU level demand forecasting compares demand at the item level before adding it into categories or company totals. Reconcile the supplied item forecasts to their approved totals, then calculate errors against comparable actuals. An accurate category total can still conceal the wrong stock in the wrong items.

Define the item comparison before opening the totals

Start with an export containing SKU, category, location, demand period, forecast version, forecast units and actual demand units. Keep the forecast creation date as well as the period it predicts. Comparing a late revision with an earlier forecast makes the results look better without improving the original decision.

Select one forecast version and one location for the first review. Aggregate duplicate transaction lines only after checking their identifiers. Every resulting row should represent one SKU, location, period and version. A product description is useful for reading, but the stable SKU identifier is the matching key.

Use demand forecasting for the overall planning process. This review answers whether the item detail agrees with the totals and whether aggregate accuracy conceals item-level errors.

Match the item universe and measurement units

Build the expected SKU list from the product master and approved forecast. Include launches, runouts and products with zero expected demand. Confirm whether actual demand means requested orders or fulfilled shipments, and keep that definition consistent across items.

Use a matching-status column to distinguish a true zero from a missing export row. An unmatched SKU needs investigation before calculating a percentage. For example, a discontinued product can legitimately have no new demand, while a missing shipment file cannot establish the same conclusion.

Covirage is not affiliated with Microsoft.

Microsoft's COUNTIFS documentation describes counting rows that satisfy all supplied criteria. With SKU, category and period in Raw columns A:C, an expected SKU in J2 and expected period in K2, this formula checks the selected version's summarized rows:

=COUNTIFS(Raw!$A$2:$A$5,J2,Raw!$C$2:$C$5,K2)

Expect one match after summarization. Zero is missing; more than one needs a grain or duplication review. If the file includes several locations or versions, add those criteria rather than accepting several matches as normal.

Work the same rows at SKU and category level

An illustrative US distributor reviews its August 31, 2026 forecast for September 2026. All rows below refer to the same site and forecast version. Quantities are individual units, with pack conversions already completed.

Place SKU in A, category in B, period in C, forecast in D and actuals in E. Use F for signed error and G for absolute error:

=D2-E2
=ABS(F2)

Fill down through row 5.

The Core category forecast is 100 + 80 + 20 = 200 units. Its actual demand is 80 + 100 + 20 = 200 units. The category is exactly right in total, but A is over by 20 units and B is under by 20 units. Those errors do not provide interchangeable inventory when customers want a specific SKU.

Persistent signed errors across periods belong in forecast bias by SKU and site. Here the issue is what aggregation hides within the same period.

Reconcile the category summary to the detail

Put category names in Summary!A2:A3. Sum the forecast for the first category with:

=SUMIFS(Raw!$D$2:$D$5,Raw!$B$2:$B$5,A2)

Use the corresponding E range for actuals and G range for absolute errors. SUMIFS sums values meeting the selected conditions, so add period and location criteria when the export contains more than the selected comparison.

Each total must equal its detail column. If Summary columns B:D contain the three measures, check:

=SUM(B2:B3)-SUM(Raw!D2:D5)
=SUM(C2:C3)-SUM(Raw!E2:E5)
=SUM(D2:D3)-SUM(Raw!G2:G5)

All three differences are zero. Also compare the 250-unit forecast with the approved category plan. A clean spreadsheet reconciliation proves that rows add up; it does not prove that the file contains the approved version.

Calculate error before aggregation

The total's absolute net error is only 10 units. Dividing by 240 actual units gives 4.2%, rounded to one decimal place. That measures the error in total volume, not the item allocation.

Weighted absolute percentage error uses item absolute errors:

=SUM(G2:G5)/SUM(E2:E5)

The result is 50 / 240 = 20.8%. Core alone is 40 / 200 = 20.0%, despite its zero net error. Calculate with unrounded values and format the final percentages to one decimal place.

Keep both views: category volume for the broad operating plan and item error for allocation quality. Use the discussion of good forecast accuracy to choose decision-relevant comparisons. For items with frequent zero-demand periods, intermittent demand forecasting covers the distinct demand pattern.

Where it goes wrong

• Totals are the only check. Equal category sums cannot establish that each item is planned correctly.
• Percentages are averaged. Sum absolute unit errors and divide by summed actuals for the weighted measure.
• Missing actuals become zero. Confirm source coverage before assigning zero demand to an unmatched item.
• Pack sizes are mixed. Normalize quantities before adding them, and retain the conversion evidence.
• Constrained shipments stand in for demand. Review availability through out-of-stock sell-out gaps before judging the forecast against shipments.

Check your SKU forecast file in Covirage

Bring your supplied SKU forecasts, actual demand export and category mapping, including location, period and forecast version. Covirage maps the columns, and its tools compute and check the item comparisons and totals; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; creating the account takes about a minute.

See forecast analysis for the supporting business workflow.