Advanced Forecasting & Scenario Planning Workbook

Advanced Forecasting & Scenario Planning Workbook

This workbook helps you plan for a range of plausible futures, test price and promotion elasticity, and translate scenario demand into inventory and labor needs. It is designed for multi-location rollouts and special events planning. Use the templates and worked examples below to build reproducible scenarios you can share and iterate on.

How to use this workbook

1. Fill in baseline operating metrics for the reference period (daily sales, average check, baseline labor hours, inventory coverage).
2. Define three scenarios: base (expected), downside (pessimistic), and upside (optimistic). Express each as % change vs baseline for sales, price, and promo spend.
3. Run sensitivity checks for price and promo elasticity (simple % change tests or multiple-step sensitivity bands).
4. Translate scenario sales into inventory requirements and labor hours using the formulas below.
5. Record results per location or event and compare tradeoffs (profitability, spoilage risk, staffing constraints).

Baseline Inputs (template)

• Reference period (days): ______
• Baseline average daily sales (units or covers): ______
• Baseline average revenue per sale / average check: ______
• Baseline labor hours per day (total scheduled hours): ______
• Average days of inventory coverage you normally hold: ______
• Estimated daily spoilage/loss %: ______

Scenario definitions (template)

For each scenario enter percent changes versus baseline. Use negative values for declines.

Simple formulas (copy into a spreadsheet)

Use these basic formulas to convert inputs into scenario outcomes. Replace variables with your numbers.

• Scenario daily sales (revenue) = Baseline daily sales × (1 + sales_pct / 100) × (1 + price_change_pct / 100)
• Scenario daily covers/units = Baseline daily covers × (1 + sales_pct / 100)
• Inventory needed (units) = Scenario daily units × days_of_inventory_coverage × (1 + spoilage_pct / 100)
• Labor hours required ≈ Baseline labor hours × (1 + sales_pct / 100) × Labor elasticity factor

  Tip: Estimate a simple labor elasticity factor (for example, 0.6–0.9) to reflect that not all sales change maps equally to hours (e.g., a 10% sales increase may need only a 6–9% increase in scheduled hours).

• Scenario profit impact = (Scenario revenue — Scenario food cost — Scenario labor cost — Additional promo cost)

Testing price and promo elasticity

Run sensitivity by changing price in 1–3 bands (e.g., −5%, 0%, +5%) and observe resulting revenue and unit changes using assumed price elasticity. Example approach:

1. Choose an assumed price elasticity (e.g., −0.7 means a 1% price increase → 0.7% decline in demand).
2. Calculate change in units = price_change_pct × price_elasticity.
3. Calculate net revenue change = (1 + price_change_pct) × (1 + units_change) — 1 (approximate).

Run the same style of sensitivity for promotions: estimate lift per dollar of promo spend (or per promotional tactic) and test a few spend levels.

Worked example (quick)

Baseline: 200 covers/day, $25 avg check, 40 labor hours/day, 7 days inventory coverage, 5% spoilage.

• Upside: sales +15%, price +0%, promo +10% → daily revenue ≈ 200 × 1.15 × $25 = $5,750
• Inventory needed ≈ (200 × 1.15) × 7 × 1.05 ≈ 1,681 units (rounded)
• Labor hours ≈ 40 × 1.15 × 0.8 (assumed elasticity) ≈ 36.8 → round to staffing schedule implications

Questions to surface when comparing scenarios

• How does profit change after additional promo or price moves?
• Which scenario pushes inventory risk (spoilage) above acceptable limits?
• What staffing gaps or overtime risk appear under each scenario?
• At what point does adding promotional spend reduce margin despite increased volume?

Multi-location and event rollout tips

• Run the workbook per location or cluster to capture local demand shape and lead times.
• For events, use shorter reference periods (days or hours) and convert inventory by meal/event unit rather than daily cover.
• Capture local supply constraints (single-source ingredients) and factor them into downside scenarios.
• Use a small pilot to validate elasticity assumptions before full rollout.

Next steps & recommended deliverables

1. Populate baseline inputs for each location into a simple spreadsheet template (columns for Baseline, Base scenario, Downside, Upside).
2. Run the sensitivity bands for price and promo with 2–3 elasticity assumptions (conservative, likely, optimistic).
3. Translate scenario outputs into a short action plan: ordering adjustments, scheduling changes, promotional triggers, and risk mitigation steps.
4. Store scenario results and assumptions (date, author, version) so you can learn from outcomes and refine elasticity assumptions.

Templates & export suggestions

Suggested spreadsheet columns: Location, Reference period, Baseline covers, Baseline avg check, Baseline labor hrs, Sales % (scenario), Price % (scenario), Promo % (scenario), Projected revenue, Projected units, Inventory needed, Labor hrs required, Notes, Assumptions, Author.

Where this workbook can improve with platform capabilities

This HTML workbook is a practical starting place. It becomes more powerful if turned into a small interactive tool that stores scenario inputs per location, renders scenario comparison tables automatically, and links to POS/inventory data so assumptions can be validated. See capability notes below.

Keep a short log of your scenario experiments and outcomes to build organizational memory and avoid overconfidence in single-point forecasts.