Repository Docs & Knowledge Base

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.