---
id: 8f2c6a14-1b4e-4f7a-9d8c-5e7a2c3b6d91
name: Forecast menu item demand and inventory ordering
kind: InventoryForecast
cron: 0 45 6 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Forecast **menu item demand by day or service period** and convert demand into **inventory purchasing recommendations** to reduce food waste, prevent shortages, and ensure ingredient availability.

The process should analyze historical sales patterns and translate forecasted menu item demand into **ingredient usage forecasts and optimized ordering plans**.

The output must contain **ordering recommendations and alerts only**, without explanations or descriptions of methodology.

## Known Tables & Columns

### `GNDITEM`
- item (integer) - menu item identifier — references itm.id  
- bohname (varchar) - menu item name — join itm on gnditem.item = itm.id and use itm.longname  
- price (decimal) - current selling price  
- revid (integer) - revenue center identifier  

### `ITM`
- id (integer) - menu item identifier  
- longname (varchar) - menu item name  
- recipe relationships - mapping of menu items to ingredient usage quantities  

### `QTYPRICE`
- quantity tiers - quantity thresholds for bulk pricing  
- supplier pricing - price per unit depending on order quantity  

### `GNDSALE`
- historical forecasts and signals related to sales behavior  
- waste indicators  
- shortage indicators  

## Analysis Approach

### Stage 1 — Historical Demand Extraction
Extract historical sales data from the last **90 days** using `GNDITEM`.

Aggregate sales by:
- item
- day of week
- service period or time window if available.

Identify recurring demand patterns such as:
- weekday vs weekend demand differences
- peak service periods
- frequently ordered menu items.

### Stage 2 — Menu Item Demand Forecast
Estimate **expected sales for each menu item** for upcoming days or service periods based on historical demand patterns.

Adjust forecasts using contextual signals such as:
- seasonal demand variations
- upcoming promotions
- scheduled events or high-traffic periods.

### Stage 3 — Ingredient Consumption Forecast
Use `ITM` to map menu items to their **ingredient and recipe relationships**.

Convert predicted menu item demand into **ingredient consumption forecasts** by calculating required ingredient quantities based on recipe usage.

### Stage 4 — Inventory and Shortage Risk Detection
Use historical signals from `GNDSALE` to detect:
- ingredients frequently understocked
- ingredients frequently wasted.

Compare forecasted ingredient usage with expected stock levels to identify:
- potential shortages
- potential overstock situations.

### Stage 5 — Ordering Optimization
Use `QTYPRICE` to evaluate **quantity-based pricing tiers and supplier options**.

Determine optimal order quantities by considering:
- forecasted ingredient usage
- safety stock buffers
- delivery schedules
- bulk purchase discounts.

### Stage 6 — Smart Ordering Adjustments
Enhance ordering recommendations by:
- aligning deliveries with freshness requirements
- recommending alternative suppliers when better pricing tiers exist
- suggesting menu substitutions if key ingredients are forecasted to be unavailable.

## Output Constraints

- Use historical data from the **last 90 days** only.
- Prefer aggregated summaries.
- Avoid requesting more than **3 database queries** unless necessary.
- Return **ordering recommendations and alerts only**.
- **Do not include explanations, reasoning, or methodology**.

## Output Format

Return structured ordering recommendations and alerts.