---
id: 2d7a9e31-6f84-4c9d-b1a6-8e5c2f4d7a13
name: Optimize ingredient order quantities
kind: InventoryOptimization
cron: 0 50 6 * * *
analysis_window: 90d
comparison_periods: [7d, 28d]
---

Optimize **ingredient order quantities** to balance stock availability, holding costs, bulk purchasing discounts, and freshness requirements while minimizing waste and preventing shortages.

The process should analyze historical ingredient consumption and pricing tiers to determine **optimal purchasing quantities and delivery strategies**.

The output must contain **optimized order quantities and delivery recommendations 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 - thresholds for bulk pricing  
- supplier pricing - price per unit depending on ordered quantity  

### `GNDSALE`
- historical sales forecasts  
- waste indicators  
- shortage indicators  

## Analysis Approach

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

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

Convert menu item demand into **ingredient demand** using recipe relationships from `ITM`.

Identify recurring demand patterns such as:
- peak demand periods
- consistently high-volume ingredients
- weekday vs weekend differences.

### Stage 2 — Ingredient Consumption Forecast
Estimate **future ingredient consumption** based on historical demand patterns.

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

### Stage 3 — Inventory Risk Assessment
Use signals from `GNDSALE` to identify:
- ingredients prone to spoilage
- ingredients frequently understocked.

Compare forecasted ingredient consumption with expected stock levels to determine:
- safety stock requirements
- potential shortage risks.

### Stage 4 — Pricing and Bulk Optimization
Use `QTYPRICE` to evaluate **quantity-based pricing tiers** and supplier pricing.

Determine whether bulk purchasing discounts justify larger order quantities while considering:
- storage capacity
- holding costs
- spoilage risks.

### Stage 5 — Freshness and Delivery Optimization
Adjust ordering quantities based on:
- ingredient shelf life
- freshness requirements
- delivery schedules.

When beneficial, recommend **splitting orders across multiple deliveries** to reduce holding costs and minimize spoilage risk.

### Stage 6 — Final Ordering Optimization
Produce optimized ingredient order quantities that balance:
- stock availability
- holding costs
- bulk discounts
- freshness constraints
- waste risk.

## Output Constraints

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

## Output Format

Return structured ingredient ordering recommendations.