Build a retail demand comparison from product-store forecasts, sales and availability records. Separate promotion assumptions and stockout weeks, calculate forecast error with spreadsheet formulas, and identify the stores that need review.
Demand forecasting for retail estimates customer purchases by product, store and week. Build the comparison from the forecast issued before the selling period, the sales recorded afterward and evidence that the product was available. Keep promotion assumptions and store differences visible so the total does not hide the reason for a gap.
The broad demand forecasting process explains the planning cycle. The retail job adds a practical constraint: each row must represent the same item, selling location and calendar week across all exports.
Use stable product and store identifiers rather than descriptions alone. Define whether units mean individual items, multipacks or cases. Assign returns consistently and keep separate channels separate when a store fulfills online orders as well as selling to walk-in customers.
Bring together these fields:
| Export | Fields needed | What it establishes |
|---|---|---|
| Issued forecast | Product, store, week, version date, baseline units, promotion units | The expectation before the selling period |
| Sales | Product, store, transaction date, units and returns | Recorded sales for the matching period |
| Availability | Product, store, date, in-stock time or confirmed restrictions | Whether sales could represent demand |
| Store calendar | Store, date, planned opening time, closures | The selling opportunity |
| Promotion calendar | Product, store, event dates and event identifier | Which trading conditions applied |
Check that combining files has not multiplied sales rows. A promotion file with overlapping events can create duplicate product-store-week records if each event receives the entire week's sales. Resolve that before summing anything.
Selling calendars matter. Hyndman and Athanasopoulos describe how trading-day counts can affect sales. For a retailer, a closed store and an open store with no sales should remain distinct observations.
Save the forecast version available before the week began. Its total should equal baseline units plus the stated promotion adjustment. If the retailer changes assumptions during the week, preserve the original and revised versions rather than replacing one with the other.
Use the same forecast horizon for every row. Comparing a forecast issued immediately before sales with one issued much earlier mixes different information sets. The forecast evaluation guidance explains why evaluation needs observations that were not used to fit the forecasting method.
Define the error sign before the review. Here, forecast minus actual is positive when the retailer forecast too much. Absolute error measures the size of the gap without allowing positive and negative errors to cancel.
A US retailer reviews one travel-mug SKU across four Texas stores for the same completed week. All stores planned to open for seven days. The Dallas item was unavailable for two complete selling days; the other stores had confirmed full availability.
The forecast components are assumptions issued before the week. Promotion flags identify event conditions, not proven lift.
| Store | Baseline units | Planned promotion units | Total forecast units | Actual units | Planned days | In-stock days | Promotion |
|---|---|---|---|---|---|---|---|
| Austin | 100 | 0 | 100 | 90 | 7 | 7 | No |
| Dallas | 80 | 20 | 100 | 70 | 7 | 5 | Yes |
| San Antonio | 60 | 0 | 60 | 66 | 7 | 7 | No |
| Houston | 50 | 10 | 60 | 54 | 7 | 7 | Yes |
| Total | 290 | 30 | 320 | 280 | 28 | 26 |
The component check is 290 + 30 = 320 forecast units. The sales check is 90 + 70 + 66 + 54 = 280 units, which must tie to the sales export for this SKU and week.
For the calculation sheet, use the following layout. Enter the store rows in rows 2 through 5.
| Column | Content | Formula in row 2, if calculated |
|---|---|---|
| A | SKU | Input |
| B | Store | Input |
| C | Total forecast units | Input from the component check |
| D | Actual units | Input |
| E | Planned selling days | Input |
| F | Confirmed in-stock days | Input |
| G | Promotion flag | Input |
| H | Signed error units | =C2-D2 |
| I | Absolute error units | =ABS(H2) |
| J | Fully available comparison | =IF(F2=E2,"Yes","No") |
Fill H2:J2 down through row 5. The availability rule works here because the evidence identifies complete unavailable days. With partial-day restrictions, compare selling hours or finer intervals instead.
| Store | Signed error units | Absolute error units | Fully available comparison |
|---|---|---|---|
| Austin | 10 | 10 | Yes |
| Dallas | 30 | 30 | No |
| San Antonio | -6 | 6 | Yes |
| Houston | 6 | 6 | Yes |
| All rows | 40 | 52 | |
| Fully available rows | 10 | 22 |
Weighted absolute percentage error, or WAPE, divides summed absolute error by summed actual units. It is a sales-weighted comparison; a store with no actual sales can still contribute error when the group's denominator is positive.
WAPE = Sum of absolute errors / Sum of actual units
Bias percentage = Sum of signed errors / Sum of actual units
In Microsoft Excel, calculate WAPE for the fully available rows:
=SUMIFS(I2:I5,J2:J5,"Yes")/SUMIFS(D2:D5,J2:J5,"Yes")
For bias on the same rows:
=SUMIFS(H2:H5,J2:J5,"Yes")/SUMIFS(D2:D5,J2:J5,"Yes")
Microsoft documents SUMIFS as summing cells whose associated criteria are satisfied. Format the results as percentages and treat a zero denominator as undefined rather than as zero error. Covirage is not affiliated with Microsoft.
The eligible stores have 90 + 66 + 54 = 210 actual units and 22 absolute error units. Their WAPE is 22 / 210 = 10.5%, rounded to one decimal place. Their bias is 10 / 210 = 4.8%, with positive bias meaning overforecasting.
Across all stores, WAPE is 52 / 280 = 18.6%, also rounded to one decimal place. Keep that raw result visible, but label the availability restriction. Dallas's 30-unit gap is not proof of 30 lost sales, and excluding Dallas is not proof that the chain's demand forecast was accurate.
Austin needs a review of its ordinary demand assumption. Dallas first needs an availability investigation. Houston needs a comparison between its planned event effect and evidence from comparable selling periods; use sales promotion forecasting for that separate task.
The demand planner owns the issued forecast and comparison definitions. Store operations owns availability evidence, marketing owns promotion assumptions, and finance checks how unit differences affect commercial expectations. The SKU-and-site bias diagnostic helps identify repeated local errors that a chain total can conceal.
Keep this table behind a forecasting dashboard so every exception can be inspected at the store and product level.
Bring a sample of your own product-store forecast, sales and availability files to Covirage. Covirage maps the columns, and its tools compute and check the comparisons; 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 export.
See forecast analysis in Covirage for the supporting workflow.
Start at the level where the operating decision happens, usually product and store over a defined selling period. Summarize those rows for chain reporting, while retaining the detail so strong sales at one store do not hide shortages or overestimates elsewhere.
Stockouts restrict sales, so a forecast above recorded sales may reflect unavailable inventory rather than weak demand. Keep the raw sales comparison, identify affected periods and show any estimated missed demand separately with its assumptions and supporting availability evidence.
Define a consistent convention. A useful retail view separates ordinary demand from the incremental effect expected from a specific promotion, then compares actual sales with their combined forecast. Do not assume that the full sales increase proves the promotion's incremental effect.
It needs enough comparable observations to represent its selling calendar and relevant seasonal patterns. A new store or recently introduced product may lack that evidence, so label any borrowed comparison from another store or product and review the assumption as observations arrive.