Simple Sales Forecast & Replenishment Workbook

Why this workbook matters

Forecasting the right amount of stock by daypart and item family reduces waste and spoilage while keeping guests happy. This guide helps you build a simple, maintainable spreadsheet that links recent sales, seasonality, and event signals to reorder points and basic safety stock. It favors usability over complexity so a manager or inventory owner can keep it updated and useful.

Who this is for

Small- to medium-sized restaurants, cafés, caterers, and unit managers who use a spreadsheet (or want to) and need a practical forecasting + replenishment approach that:

• Works with POS daily sales and simple calendars
• Is easy to explain and maintain
• Produces reorder points and suggested order quantities

What you'll get

• A recommended spreadsheet layout and required inputs
• Simple forecasting rules (moving average + seasonality + event multipliers)
• Safety stock and reorder point formulas you can implement immediately
• Example calculation with numbers
• Practical reorder rules (par, min/max, case packs)
• Monitoring KPIs and governance steps to keep forecasts reliable

Quick start: the minimum viable workbook

Use a single sheet with these sections: Inputs, Historical Sales, Forecast, Replenishment Rules, Suggested Order. Keep the sheet owned by one role (inventory manager or shift lead) and review weekly.

Required inputs (columns you should include)

• Item ID / Name
• Item family (protein, produce, dairy, disposables, etc.)
• Unit of issue (case, lb, each)
• Case pack / order multiple
• Lead time (days) from order to usable stock
• Review frequency (days) — how often you place orders
• Desired service level (e.g., 90%, 95%)
• Recent daily sales for a historical window (30–90 days), ideally split by daypart if you use daypart forecasting
• Event calendar / promotions (boolean or multiplier for each date)

Forecast method (practical and transparent)

Keep forecasts simple so staff will maintain them. Start with a short moving average by daypart and family, then apply seasonality and event adjustments.

Step-by-step

1. Calculate recent average daily demand (by item or family and by daypart when relevant). Example: AverageDaily = SUM(last 28 days sales) / 28.
2. Compute demand volatility: StdDevDaily = STDEV.P(last 28 days sales).
3. Estimate a seasonality factor by comparing the same weekday / same week-of-year historical average (optional for small operations): SeasonalityFactor = ThisWeekAverage / RecentAverage.
4. Apply event multipliers for known events (promotions, holidays, reservations spikes): ForecastDaily = AverageDaily * SeasonalityFactor * EventMultiplier.

Simple formulas you can paste into a spreadsheet

These assume cells with the computed numbers are present. Replace cell references with your sheet cells.

AverageDaily = AVERAGE(LAST_N_DAYS)
StdDevDaily = STDEV.P(LAST_N_DAYS)
ForecastDaily = AverageDaily * SeasonalityFactor * EventMultiplier
SafetyStock = Zscore(serviceLevel) * StdDevDaily * SQRT(LeadTime)
ReorderPoint = (ForecastDaily * LeadTime) + SafetyStock
SuggestedOrderQty = MAX(0, (TargetOnHand - OnHand)) rounded up to order multiple
  

Service-level Z scores (typical): 90% → 1.28, 95% → 1.65, 99% → 2.33. Use conservative values until you understand your variability.

Example calculation

Example item: Grilled Chicken (portions). Historical average daily demand = 20 portions. StdDevDaily = 6. Lead time = 2 days. Desired service level = 95% (z=1.65).

• SafetyStock = 1.65 * 6 * sqrt(2) ≈ 1.65 * 6 * 1.414 ≈ 14 portions
• ReorderPoint = (20 * 2) + 14 = 40 + 14 = 54 portions
• If on-hand = 60 portions, SuggestedOrderQty = 0. If on-hand = 30 portions and case pack = 10, TargetOnHand might be 80 (forecast*leadtime + safety). Order = (80 - 30) = 50 → round up to 50 (5 cases of 10).

Reorder rule patterns

Choose the pattern that fits each item:

• Par (min/max): Keep on-hand between Min and Max, order up to Max when inventory hits Min.
• Reorder point (continuous review): When on-hand ≤ ReorderPoint, place order for TargetOnHand minus on-hand, respecting case packs.
• Periodic review: On scheduled days (e.g., Mon/Wed/Fri), calculate expected demand until next delivery + safety stock and order up to that level.

Practical adjustments for perishability and cost

• Short shelf-life items: use shorter lead times, smaller order frequencies, and reduce safety stock to avoid spoilage.
• High-cost or slow-moving items: consider min-order quantities or vendor consolidation instead of large safety stock.
• Case-pack and pack-sizing: always round suggested orders to match case packs to avoid odd partial cases.

How to reduce over-ordering without increasing stockouts

• Start with conservative safety stock (learn and adjust using data). Lower it gradually only after monitoring stockouts and fill rates.
• Use ABC classification: apply simpler rules to C-items and more careful forecasting to A-items.
• Incorporate realtime signals when possible: today's reservations, weather, local events.
• Review forecast error weekly (MAPE) and investigate large deviations.
• Enforce FIFO and clear expiration labeling to reduce waste.

Monitoring & governance

Track these KPIs weekly at minimum:

• Forecast accuracy (MAPE): Average of |(Actual - Forecast)| / Actual
• Fill rate / stockouts per item
• Waste as percent of sales
• Inventory turns and days of inventory

Assign ownership: who updates the forecast inputs, who reviews exceptions, and who approves changes to service levels or par values.

Common mistakes to avoid

• Using long historical windows that hide recent changes — prefer 28–90 days depending on item stability.
• Keeping overly complex models that no one understands or maintains.
• Forgetting to include calendar events or promotions that affect short-term demand.
• Mixing units (cases vs. eaches) without clear conversion factors.

How to test and iterate

1. Run forecasts in parallel with your current practice for 4–8 weeks and compare stockouts, waste, and ordering volume.
2. Lower or raise safety stock by small amounts on a limited set of items to measure impact.
3. Use A/B style experiments across similar locations if you operate multiple units.

When to consider more advanced approaches

If you have many SKUs, integrated POS/inventory systems, and technical resources, you can progress to automated demand forecasting models, replenishment rules integrated into your procurement system, and AI-assisted demand signals. But only move forward once you have disciplined, clean historical data and a reliable process for reviewing forecasts.

Spreadsheet template schema (suggested columns)

1. ItemID, ItemName, Family, Unit, CasePack
2. LeadTimeDays, ReviewFrequencyDays, ServiceLevel
3. RecentAverageDaily, StdDevDaily, SeasonalityFactor, EventMultiplier
4. ForecastDaily, SafetyStock, ReorderPoint, OnHand, SuggestedOrderQty
5. LastOrderedDate, LastOrderQty, Notes

Next steps and capability opportunities

Once the spreadsheet is working, consider:

• Turning this workbook into an interactive form where store staff can enter on-hand counts and receive suggested orders (saves time and keeps history).
• Collecting submissions to a central store for trend analysis and dashboarding.
• Integrating POS and receiving data to auto-populate historical sales and actuals.

Ready to try it: Build the spreadsheet with the schema above, populate 28 days of sales, compute the example formulas, and run the example calculation for 5 representative items (A/B/C items). Measure forecast accuracy and waste for four weeks, then iterate.