Basic Demand Forecasting & Replenishment Spreadsheet

What this calculator does

This spreadsheet takes historical POS sales and converts them into usable short-term demand forecasts, recommended par (on-hand) levels, and suggested order quantities that factor in supplier lead times, minimum order multiples, safety stock, day-of-week and holiday effects, and simple scenario toggles (growth, events, promotions).

Who this is for

Chefs, managers, buyers, and operators who want a straightforward, auditable forecast-to-order workflow they can run weekly or daily. Works well for independent restaurants, small groups, and operations that export POS usage into a spreadsheet.

Key features

• Rolling 4-week demand smoothing (configurable)
• Day-of-week and holiday adjustments
• Usage-to-order mapping for bulk or prepped items
• Safety stock rules with a simple service-level knob
• Suggested order quantities that factor supplier lead-time and order multiples
• Scenario toggles for short-term events or growth

Required inputs

1. Historical daily or weekly POS sales by item (at least 4 weeks recommended)
2. Current on-hand inventory, on-order quantities, and par targets (if any)
3. Supplier lead times (days) and minimum order quantities / pack sizes
4. Usage-to-order conversion (e.g., 1 case = 24 servings)
5. Desired service level (a simple slider or factor; e.g., 95%)
6. Known upcoming events or promotions (dates and expected lift percent)

Outputs you’ll get

• Smoothed forecast (daily or weekly) for the chosen horizon
• Recommended par level per item (forecast during lead time + safety stock)
• Suggested order quantity that respects pack sizes, minimums, and current on-hand / on-order
• Simple flags: risk of stockout, likely overstock / spoilage risk

How the core calculations work (plain language)

1) Smooth recent demand to reduce noise: by default the sheet uses a rolling 4-week average of historical weekly usage. You can switch to exponential smoothing if you prefer a faster-reacting model.

2) Adjust for day-of-week and holidays: scale the forecast when a particular day historically behaves differently (e.g., weekends). For holidays or events, multiply the base forecast by an expected uplift factor.

3) Forecast demand during supplier lead time: ForecastLeadDemand = average daily usage * leadTimeDays.

4) Calculate safety stock to protect service level: a simple rule is SafetyStock = ServiceFactor * StdDevDailyUsage * sqrt(LeadTimeDays). Use a ServiceFactor map (1.0–2.5) to approximate common service levels—1.65 ~ 95% service, 2.33 ~ 99% (these are approximations; tune with historical stockout data).

5) Recommended par = ForecastLeadDemand + SafetyStock.

6) Suggested Order = RoundUpToPackSize( max(RecommendedPar - (OnHand + OnOrder), 0 ) ). Also enforce minimum order quantities and adjust for inbound shipments.

Example formula snippets (spreadsheet-ready)

• Rolling 4-week average (weekly data): =AVERAGE(Last4WeeksRange)
• ForecastLeadDemand (days): =DailyAvg * LeadTimeDays
• SafetyStock (simple): =ServiceFactor * STDEV.P(DailyUsageRange) * SQRT(LeadTimeDays)
• RecommendedPar: =ForecastLeadDemand + SafetyStock
• SuggestedOrder: =CEILING( MAX(RecommendedPar - (OnHand + OnOrder), 0), PackSize )

Common mistakes and how to avoid them

• Feeding sales instead of usage: If menu prep (yield) or waste is high, convert POS sold into ingredient usage with recipe yields before forecasting supply purchases.
• Ignoring lead-time variability: If suppliers are inconsistent, increase the service factor or model lead-time variability explicitly.
• Not mapping pack sizes: Forgetting pack sizes leads to repeated partial-case orders and excess inventory. Always round orders to supplier pack multiples.
• Using too short a history for seasonal items: Quick smoothing works for stable SKUs; increase history for slow-moving or seasonal items.
• Blindly trusting the model: Run the sheet in parallel for a few cycles and review suggested orders with your buyer or chef before fully switching over.

How to tailor this model to your operation

• Set smoothing window: Shorten (2–3 weeks) for fast-moving items, lengthen (8–12 weeks) for slow or seasonal items.
• Segment items: Use one set of parameters for perishables and another for dry goods or non-food supplies.
• Use different service factors by SKU criticality: Keep a higher service level for menu staples and lower for experimental or low-margin items.
• Map recipes: Convert finished-plate sales into ingredient-level forecasts using your recipe cards so orders reflect true ingredient usage.

Measuring success

Track forecast accuracy (MAPE or RMSE), inventory turns, spoilage dollars, and stockout incidents. Expect early adjustments: validate assumptions over 4–8 weeks and tune service factors, smoothing windows, and pack-size handling accordingly.

Next practical steps

1. Export the last 8–12 weeks of POS sales by item and prepare a mapping to your supply SKUs.
2. Populate lead times, pack sizes, and current on-hand in the spreadsheet.
3. Run the model for the next 1–2 order cycles and review suggested orders with the buying/chef team.
4. Record outcomes (order received, actual usage, waste) to refine parameters and improve accuracy.

When to consider a heavier-weight approach

If you have multi-location synchronization needs, frequent promotional volatility, or direct digital connections to POS and supplier systems, consider an integrated forecasting workflow that automates data import and order creation. See the Capability Enhancement notes below for ideas.

Quick troubleshooting

• If suggested orders spike unexpectedly, check for an isolated sales outlier in the input data or a mis-entered pack size.
• If the tool keeps under-ordering, increase the service factor or examine whether lead times are underestimated.

Preserve this spreadsheet as a living tool: keep a version history, track parameter changes, and log why you changed a service level or lead time so future teams can learn what worked.