Demand forecasting: signal checklist & simple weekly model

Welcome — why this matters

Good demand forecasts do two things: they help you avoid stockouts on busy periods (protecting sales) and they stop you overprepping perishable ingredients (reducing spoilage). This guide gives a compact, practical workflow you can implement with POS data and a simple spreadsheet. It focuses on near-term operational needs: weekly rolling forecasts with daypart granularity that translate to par and prep targets.

What this guide helps you do

• Collect and prioritize demand signals (what to trust first).
• Create a rolling 4‑week weighted forecast per daypart.
• Apply seasonality and event adjustments (weather, bookings, holidays).
• Convert forecasts into par, prep, and ordering guidance.
• Monitor forecast accuracy and iterate.

Quick checklist — signals to collect

1. POS sales by daypart and item — primary signal. Use last 12 weeks of comparable dayparts if possible.
2. Transactions / covers / guests — helpful where pricing or check size varies.
3. Bookings & reservations — firm commitments that should override partial estimates.
4. Special events & promotions — internal events, sports, local festivals.
5. Weather — rain, heat, snow can change demand by category and daypart.
6. Staffing constraints or menu changes — if an item is pulled, forecast should reflect that.
7. Delivery/aggregator channel shifts — track separately if volume moved off-premise.

Step-by-step: a simple rolling 4-week daypart forecast

This method balances recent behavior with a modest smoothing approach that’s easy to implement in Excel or Google Sheets.

1. Prepare weekly daypart series

   For each menu item (or aggregated group), build a table of the last 4 weeks of sales for the specific daypart (e.g., lunch Tue, dinner Fri). Include the same weekday comparison if relevant (compare last 4 Tuesdays for an upcoming Tuesday).

2. Apply weighted recent-week average

   Give more weight to recent weeks. A common weight set is: 0.50, 0.30, 0.15, 0.05 (week -1 through week -4). Formula (Excel):

   =SUMPRODUCT(SalesRange,WeightsRange)/SUM(WeightsRange)

   Example: last 4 same‑day sales = [80, 70, 75, 60]. Forecast = (0.5*80 + 0.3*70 + 0.15*75 + 0.05*60) = 76.25 ≈ 76 portions.

3. Adjust for seasonality & day-of-week patterns

   Calculate a simple seasonality factor as: current period average / historical baseline average (e.g., this month vs long-run baseline). Multiply the weighted forecast by this factor when current season differs from baseline.

4. Incorporate firm signals

   If bookings/reservations are available, combine them with the forecast. A straightforward rule: take max(forecast, confirmed bookings) for items tied to covers, or else apply an additive amount proportional to bookings.

5. Apply event and weather multipliers

   Translate known events into multipliers (e.g., marquee sporting event +20% for wings/pizzas). For weather, use simple rules (rain reduces patio covers by X%). Keep multipliers conservative and document source and rationale.

6. Round and set safety buffer

   Round forecasts to practical prep units and add a safety buffer (commonly 5–15% depending on perishability and criticality). For very perishable items use a lower buffer and frequent intraday prep updates.

7. Translate to par / prep / order quantities

   Use yield and portion size to convert portion forecasts to ingredient quantities. Example: Forecasted portions = 76 portions. Portion size of sauce per portion = 0.12 kg. Ingredient required = 76 * 0.12 = 9.12 kg. Add safety buffer and existing on-hand to determine order or prep quantity.

Example spreadsheet layout (columns)

• Date
• DayPart (Lunch/Dinner/Brunch)
• Item
• Sales_Wk1 (most recent)
• Sales_Wk2
• Sales_Wk3
• Sales_Wk4
• WeightedForecast (formula above)
• SeasonalityFactor (manual or calculated)
• BookingsOverride
• EventMultiplier
• AdjustedForecast = WeightedForecast * SeasonalityFactor * EventMultiplier OR max(WeightedForecast, BookingsOverride)
• SafetyBuffer%
• PrepTarget = AdjustedForecast * (1 + SafetyBuffer%)
• IngredientQty = PrepTarget * PortionSize / Yield

Helpful formulas

• Weighted average: =SUMPRODUCT(Sales_WkRange,WeightsRange)/SUM(WeightsRange)
• Adjusted forecast with bookings: =MAX(WeightedForecast,BookingsOverride)
• Prep target: =ROUNDUP(AdjustedForecast*(1+SafetyBuffer%),1) (round to practical unit)
• MAPE (to measure accuracy): =AVERAGE(ABS((Actuals - Forecasts)/Actuals))

Key metrics to track (weekly)

• MAPE by daypart and item — target varies, under 15% is a reasonable initial goal for stable items.
• Bias (systematic under- or over-forecasting) — aim for near zero.
• Stockouts and lost covers — track number and estimated margin lost.
• Spoilage / waste due to overprepping — track ingredient cost impact.

Common mistakes and how to avoid them

• Using total weekly sales only — disaggregate by daypart and weekday patterns before forecasting.
• Overreacting to a single outlier week — use weighted smoothing and investigate outliers before adjusting permanently.
• Ignoring firm signals like bookings — these should take precedence if they are reliable.
• Applying too many ad hoc multipliers without documenting rationale — record every manual adjustment and its effect.

Operational cadence

Run the rolling forecast once per day for the upcoming 7–14 days (more often during busy periods). Review accuracy weekly and revise weights, seasonality baselines, and event rules monthly or after major changes (menu, hours, staffing).

Where to go next — practical improvements

• Automate inputs: pull POS daily sales and bookings into the sheet to reduce manual copying.
• Create a simple dashboard showing MAPE, bias, stockouts and spoilage cost per week.
• Build item groups (by perishability and sales stability) and use different safety buffers per group.
• When ready, move from a spreadsheet to a small replenishment tool that connects POS, inventory, and ordering — start by instrumenting the signals reliably.

Wrap-up

This approach is intentionally simple so teams can implement quickly and iterate. Start with the highest-impact items (high cost, high variability, or items that frequently stockout). Focus first on collecting reliable signals, then on discipline — run the process each day, document manual adjustments, and measure accuracy. Over time you’ll be able to reduce safety buffers and cut spoilage without increasing stockouts.

Resources to copy into your systems

Suggested starter actions:

1. Export last 12 weeks of POS by daypart into a spreadsheet.
2. Build the four sales columns and the weighted average formula described above.
3. Add columns for bookings and event multipliers and a prep target column that converts portions into ingredient quantities.
4. Track actuals vs forecast for 4 weeks and compute MAPE.

If you want, the next iteration could convert this guide into an interactive forecast worksheet that saves daily runs and tracks accuracy history.