adambrousell
Pizza Sales Inventory Forecasting Model
Analyzing a full year of pizza restaurant transaction data to optimize box inventory purchasing, identify demand patterns, and reduce waste through data-driven forecasting.
Introduction
A pizza restaurant needed to optimize its box inventory purchasing to reduce waste, prevent stockouts, and improve operational efficiency. Using 12 months of cleaned transaction data (January–December 2015, 48,620 line items across 21,350 orders), this analysis identifies demand patterns by time of day, day of week, pizza size, and seasonality to build a reliable inventory forecasting model.
The core assumption is that box inventory needs to match demand patterns by size—and that granular time-based demand data can replace the gut-feel purchasing decisions that lead to both overstocking and stockouts.
Dataset Overview
Problems Identified
1. No Size-Specific Purchasing Strategy
Box inventory was ordered uniformly across sizes despite highly uneven demand. Large boxes account for 38% of all units sold but were being ordered at the same cadence as Small (29%) and Medium (32%). XL and XXL combined represent just 1.2% of demand—chronic overstocking on these sizes ties up capital.
2. Time-of-Day Demand Spikes Unaccounted For
Peak demand concentrates heavily in the 12:00–13:00 lunch window and 17:00–19:00 dinner window. Without hour-level forecasting, prep and inventory staging missed these spikes, leading to slower throughput during the highest-revenue periods.
3. Day-of-Week Variance Ignored
Friday generates 37% more revenue than Sunday ($136K vs $99K annually). Staffing and inventory weren’t calibrated to this spread, leading to Friday stockouts and Sunday excess.
Analysis: Demand Patterns
Sales Heatmap — Day × Hour
The heatmap reveals two consistent demand peaks: a sharp lunch spike at 12:00–13:00 and a broader dinner plateau from 17:00–19:00. Friday is the highest-volume day across nearly every hour. Sunday and Monday show notably weaker demand, especially after 20:00.
Revenue by Category
| Category | Revenue | Share |
|---|---|---|
| Classic | $220,053 | 26.9% |
| Supreme | $208,197 | 25.4% |
| Chicken | $195,920 | 23.9% |
| Veggie | $193,690 | 23.7% |
Revenue is remarkably even across categories, with Classic leading at 26.9%. This balance means inventory planning can focus on size distribution rather than category-specific ingredients as the primary lever.
Revenue by Day of Week
| Day | Revenue | Index vs Avg |
|---|---|---|
| Friday | $136,074 | 116 |
| Thursday | $123,529 | 106 |
| Saturday | $123,182 | 105 |
| Wednesday | $114,408 | 98 |
| Tuesday | $114,134 | 98 |
| Monday | $107,330 | 92 |
| Sunday | $99,204 | 85 |
Analysis: Box Inventory Forecasting
Weekly Box Demand by Size
Size Distribution — Annual Volume
| Size | Units Sold | Share | Boxes/Week (Avg) |
|---|---|---|---|
| Large | 18,956 | 38.2% | 365 |
| Medium | 15,635 | 31.5% | 301 |
| Small | 14,403 | 29.1% | 277 |
| XL | 552 | 1.1% | 11 |
| XXL | 28 | 0.1% | <1 |
Large, Medium, and Small account for 98.8% of all box consumption. XL and XXL combined are negligible. The weekly average is approximately 900 boxes total, but Friday-heavy weeks push toward 1,000+ while Sunday-start holiday weeks can dip below 650.
Solutions
Solution A: Size-Weighted Weekly Ordering (Recommended)
- Order boxes on a fixed weekly cadence using the 38/32/29/1 size ratio derived from historical demand
- Base order: 400 Large, 330 Medium, 300 Small, 12 XL per week (~1,042 total with 15% buffer)
- Eliminate standing XXL orders—switch to on-demand from supplier or keep 5 in reserve
- Pros: Simple to implement, reduces both waste and stockouts, maps directly to demand
- Cons: Doesn’t account for week-to-week variance; seasonal spikes require manual adjustment
Solution B: Day-of-Week Forecasting with Staged Delivery
- Split weekly order into two deliveries: Monday (covering Mon–Wed) and Thursday (covering Thu–Sun)
- Thursday delivery is 40% larger to cover the Friday peak and weekend demand
- Uses the heatmap demand pattern to stage prep and dough production by shift
- Pros: Tighter alignment with demand curve, less dead inventory mid-week
- Cons: More complex logistics, requires supplier with twice-weekly delivery capability
Solution C: Rolling 4-Week Moving Average
- Calculate a rolling 4-week average for each size and auto-generate next week’s order
- Add a 10% buffer for peak weeks (identified by holiday calendar and event schedule)
- Automate via Excel or POS integration to reduce manual forecasting labor
- Pros: Self-adjusting, smooths out single-week anomalies, scalable
- Cons: Lags behind sudden demand shifts (e.g., new menu launch); requires data pipeline
Conclusion
This analysis demonstrates that pizza box inventory can be optimized through granular demand data at the size, day, and hour level. The key takeaways are:
Size matters more than category
Revenue is evenly split across categories, but size distribution (38/32/29) is the primary lever for box purchasing. Matching orders to this ratio eliminates the most common source of waste and stockouts.
Friday drives the week
Friday generates 37% more revenue than Sunday. Any inventory or staffing model that treats all days equally will under-serve Friday and over-serve Sunday.
Lunch rush is the choke point
The 12:00–13:00 hour is the highest-demand period every day. Staging prep, dough, and box inventory before this window is the highest-ROI operational change.
Next Steps
Recommended: Implement Solution A immediately, build toward Solution C
- Week 1: Switch to size-weighted ordering (400L / 330M / 300S / 12XL). Track actual consumption against forecast daily.
- Week 2–4: Validate forecast accuracy. Adjust buffer percentage based on observed variance.
- Month 2: Build the rolling 4-week moving average model in Excel. Begin automating order generation.
- Month 3: Integrate with POS data for real-time demand tracking. Evaluate split-delivery logistics (Solution B) for further optimization.
- Ongoing: Revisit size ratios quarterly as menu changes may shift demand (e.g., new XL-only promotional item).