Demand Forecast Template & Reorder Rules (daily / weekday / weekend)

Purpose

This template helps kitchen and purchasing teams turn point-of-sale signals, seasonality and local indicators into defensible daily and daypart forecasts plus clear reorder suggestions. Use it to reduce stockouts on peak days and excess spoilage on slow days while giving buyers simple rules they can trust and override when needed.

Overview — How the template works

Combine multiple demand signals using weighted blending, adjust for dayparts and events, calculate safety stock from variability and lead time, compute par and reorder point, then produce suggested order quantities that respect supplier lead times, minimums and pack sizes.

Inputs (maintain these data sources)

• Daily POS sales by item and daypart (last 12–26 weeks recommended)
• Event calendar (holidays, promotions, catered events, sports, weather warnings)
• Supplier lead time (days), minimum order quantity, pack size
• Current on-hand + on-order quantities
• Shelf life or usable days for perishable items
• Local indicators (construction, nearby events, school schedule) — optional

Step 1 — Build demand signals (examples)

Create several independent signals for each item and daypart, then blend them:

• Recent historical average — average daily demand for the same weekday over the last N weeks (e.g., last 8 weeks).
• Same day last period — demand from the same day last week or last comparable week.
• Moving average — centered moving average (7 or 14 days) to smooth short-term noise.
• Seasonality multiplier — weekly or monthly factor derived from longer-term data to adjust for season patterns.
• Event multiplier — override factor for known events/promotions (e.g., x1.5 for catering day).
• Local indicator — simple numeric adjustment for local known conditions (0.9–1.2).

Example default weights (tune by item class): POS history 50%, same-day-last-week 20%, moving average 15%, seasonality 10%, local/event 5%.

Step 2 — Daypart forecasts

Split the daily forecast into dayparts (breakfast, lunch, dinner, late-night) using historical daypart mix percentages. Maintain a small table per item: percent of daily sales by daypart (update monthly).

Example: If daily forecast = 100 units and dinner historically accounts for 45%, dinner forecast = 45 units.

Step 3 — Variability & safety stock (recommended rule)

Measure variability for the relevant cadence (day or daypart): calculate the standard deviation of demand (σ_d) over a rolling window (8–12 weeks using same weekdays or same dayparts).

Convert to lead-time variability: σ_LT = σ_d * sqrt(lead_time_days).

Choose service level (common choices): 90% (z=1.28), 95% (z=1.65), 99% (z=2.33). Then:

Safety stock = z * σ_LT

Rounded appropriately for unit type (pieces, liters, pans).

Step 4 — Average lead-time demand & reorder point

Compute average lead-time demand = avg_daily_demand * lead_time_days.

Reorder point (ROP) = avg_lead_time_demand + safety_stock

If the item is ordered multiple times per week, use the replenishment period in days (the period between orders) instead of lead_time_days when calculating par.

Step 5 — Par calculation and suggested order quantity

Define par as the target on-hand level after the order arrives to cover expected demand until the next replenishment plus safety stock:

Par = expected_demand_for_replenishment_period + safety_stock

Suggested order quantity = max( minimum_order, round_to_pack_size( par - (on_hand + on_order) ) ).

If result <= 0 then recommend no order. For perishable items, cap the par by shelf life (don't order more than you can use before spoilage).

Step 6 — Reorder suggestion list (fields for the sheet)

Include one row per item with these columns. These fields make the sheet actionable for buyers:

1. Item code / description
2. Unit (ea, kg, L)
3. Avg daily demand (by day / daypart)
4. Std dev (σ_d)
5. Lead time (days)
6. Safety stock (calculated)
7. Avg lead-time demand
8. Reorder point
9. On hand
10. On order (pending receipts)
11. Par (target on-hand)
12. Min order / pack size
13. Supplier (lead time reliability flag)
14. Suggested order quantity (calculated)
15. Reason / override note (free text)

Override rules & notes (when to change the forecast)

• Events & holidays: apply event multipliers in advance and mark the calendar. For known large events (caterings, sports finals) consider manual forecast increases and one-off orders.
• Promotions: use promotion uplift multipliers based on historical promo performance rather than guessing.
• Weather & local factors: short-term adjustments (±10–30%) for extreme weather or localized disruptions.
• Supplier disruptions: increase lead time and safety stock when supplier reliability falls or use alternate suppliers.
• Quick checks: if suggested order would exceed shelf-life-based consumption, reduce to safe usable level and schedule smaller, more frequent orders if possible.

Practical tips and governance

• Update forecasts daily or at least once per business day for high-turn items; weekly for slow movers.
• Segment items: perishable high-turn items use shorter windows and higher weight on recent signals; non-perishables use longer history and smaller safety factors.
• Keep a supplier master table (lead time, min order, pack size, reliability score). Recalculate lead-time variability if lead times change.
• Track forecast accuracy (MAPE per item class) and tune weights monthly. Flag items with persistent large errors for process review.
• Keep an events calendar integrated with the forecasting sheet so event multipliers are explicit and auditable.

Example quick formulas (spreadsheet-friendly)

(Replace names with your sheet columns)

• AvgDaily = AVERAGE(Last N same weekday values)
• StdDev = STDEV.P(Last N same weekday values)
• Sigma_LT = StdDev * SQRT(LeadTimeDays)
• SafetyStock = Z * Sigma_LT
• AvgLeadTimeDemand = AvgDaily * LeadTimeDays
• ReorderPoint = AvgLeadTimeDemand + SafetyStock
• Par = CEILING((AvgDaily * ReplenishmentPeriod) + SafetyStock, 1)
• SuggestedOrder = IF((Par - (OnHand + OnOrder)) <= 0, 0, MAX(MinOrder, ROUNDUP((Par - (OnHand + OnOrder))/PackSize,0)*PackSize))

KPIs to monitor

• Stockouts by item & daypart (count and lost sales estimate)
• Weekly spoilage cost by item
• Forecast accuracy (MAPE) by item class
• On-time supplier delivery % (feed to lead-time reliability)
• Days of inventory on-hand for perishables

Next steps for implementation

1. Create the sheet with the columns listed above and load 12–26 weeks of POS history.
2. Populate supplier master data (lead time, min order, pack size).
3. Set default weights and service-level (z) by item class; test for 4–8 weeks and measure MAPE and spoilage.
4. Integrate an event calendar and a simple override workflow where managers can record why adjustments were made.

When this template is not enough

For multi-location groups, or where POS and inventory are integrated, consider automating the signals using your POS and inventory feeds and building dashboards. For fast-changing demand (e.g., seasonal promos, unpredictable catering), pair the template with short-cycle reviews and rapid override records to learn what adjustments mattered.