Skip to content

SOP-INV-02: Demand Forecasting & Inventory Planning

Version: 1.0 PRODUCTION_READY
Effective Date: 2025-12-10
Owner: Operations Team
Review Cycle: Quarterly
Dependency: SOP-INV-01 (Inventory Control)

1. PURPOSE & SCOPE

1.1 Purpose

This SOP establishes the systematic process for: - Forecasting weekly demand based on active subscriptions + new customer allowance - Calculating stock days coverage and reorder points - Triggering co-packer production orders at the right time - Preventing stockouts while minimizing frozen storage costs

1.2 Scope

In Scope: - Subscription-based demand calculation - New customer demand allowance - Stock coverage monitoring - Reorder point calculation and alerting - Weekly inventory planning summary

Out of Scope: - Physical inventory control (covered by SOP-INV-01) - Batch receipt and QA hold management (covered by SOP-01) - Pack day operations (covered by SOP-PACK-01)

1.3 Key Principles

  1. Subscription demand is predictable — active subscriptions define baseline demand
  2. Buffer for growth — new customer allowance absorbs acquisition variance
  3. Lead time drives timing — reorder when stock = (demand × lead time) + safety stock
  4. Conservative in Phase A — over-stock slightly rather than risk stockout during credibility-building

2. THE DEMAND FORMULA

2.1 Weekly Demand Calculation

Total Weekly Demand (kg) = Subscription Base + New Customer Allowance

Where:
├── Subscription Base = SUM(box_kg / frequency_weeks) for all active subscriptions
│   Example: 300 customers × avg 2kg/week = 600 kg/week
│
└── New Customer Allowance = Target New Customers × Avg First Box
    Example: 20 new customers/week × 8kg = 160 kg/week

Total = 600 + 160 = 760 kg/week

2.2 Stock Days Coverage

Stock Days Coverage = Available Inventory (kg) ÷ Daily Demand (kg)

Where:
├── Available Inventory = SUM of kg_available for RELEASED batches only
└── Daily Demand = Total Weekly Demand ÷ 7

Example:
├── Available: 3,000 kg
├── Weekly Demand: 760 kg
├── Daily Demand: 760 ÷ 7 = 108.6 kg
└── Coverage: 3,000 ÷ 108.6 = 27.6 days

2.3 Reorder Point Calculation

Reorder Point (kg) = (Weekly Demand × Lead Time Weeks) + Safety Stock

Where:
├── Lead Time Weeks = Production + Lab Testing + Delivery (default: 4 weeks)
└── Safety Stock = Weekly Demand × Safety Stock Weeks (default: 2 weeks)

Example:
├── Weekly Demand: 760 kg
├── Lead Time: 4 weeks
├── Safety Stock: 2 weeks × 760 kg = 1,520 kg
└── Reorder Point: (760 × 4) + 1,520 = 4,560 kg

→ When available inventory drops below 4,560 kg, trigger production order

2.4 Suggested Order Quantity

Order Quantity = Target Stock Level - Current Stock - QA Hold Stock

Where:
├── Target Stock Level = Weekly Demand × (Lead Time + Safety + Buffer Weeks)
│   Buffer = 2 weeks (gets you to comfortable coverage post-delivery)
├── Current Stock = Available inventory (RELEASED batches)
└── QA Hold Stock = Inventory awaiting lab results (will become available)

Example:
├── Target: 760 × (4 + 2 + 2) = 6,080 kg
├── Current: 3,000 kg
├── QA Hold: 500 kg (batch in testing)
└── Order: 6,080 - 3,000 - 500 = 2,580 kg

3. CONFIGURABLE PARAMETERS

All planning parameters are stored in raw_ops.inventory_planning_config and can be adjusted without modifying SQL:

Parameter Default Description
lead_time_weeks 4 Co-packer production + lab + delivery
safety_stock_weeks 2 Buffer to absorb variance
target_new_customers_per_week 20 Expected weekly acquisitions
avg_first_box_kg 8 Average first order size
reorder_alert_threshold_days 28 Trigger "REORDER" status
critical_stock_threshold_days 14 Trigger "CRITICAL" status

To update a parameter: 

UPDATE raw_ops.inventory_planning_config 
SET config_value = 30, 
    updated_at = NOW(), 
    updated_by = 'anton'
WHERE config_key = 'target_new_customers_per_week';

4. DATABASE VIEWS

4.1 Master Dashboard View

View: analytics.v_inventory_planning_dashboard

Single-row view containing all planning metrics. Use in Metabase dashboard header.

Column Description
report_date Current date
available_kg RELEASED inventory available for allocation
qa_hold_kg Inventory pending lab results
active_subscription_count Active subscription count
subscription_weekly_kg Weekly kg from subscriptions
new_customer_weekly_kg Weekly kg allowance for new customers
total_weekly_demand_kg Combined weekly demand
stock_days_coverage Days until stockout at current rate
stock_weeks_coverage Weeks until stockout
reorder_point_kg Stock level triggering reorder
stock_status OK / REORDER / CRITICAL / STOCKOUT
reorder_required TRUE if below reorder point
suggested_order_kg Recommended production order

4.2 Weekly Forecast View

View: analytics.v_demand_forecast_weekly

6-week rolling forecast for trend visualization.

Column Description
week_starting Start of forecast week
subscription_kg Base subscription demand
new_customer_allowance_kg Growth buffer
total_forecast_kg Combined weekly forecast
cumulative_kg Running total (for stock drawdown chart)

4.3 Upcoming Billing View

View: analytics.v_upcoming_billing_demand

Actual orders expected based on next_billing_date.

Column Description
billing_date Date orders will be charged
orders_expected Number of subscription renewals
kg_required Total kg for that day's orders
boxes_8kg / boxes_12kg / boxes_16kg Breakdown by box size

5. MAKE.COM SCENARIOS

Scenario 1: Daily Inventory Planning Summary (08:00 UTC)

Name: [SOP-INV-02](SOP-INV-02_Demand_Forecasting_v1_0_PRODUCTION_READY.md) Daily Inventory Planning
Trigger: Schedule - Every day at 08:00 UTC

Modules:

  1. Schedule (Trigger)

  2. Every day at 08:00 UTC

  3. HTTP - Make a Request

  4. URL: https://[project].supabase.co/rest/v1/rpc/get_inventory_reorder_status
  5. Method: POST
  6. Headers:
    • apikey: [service_role_key]
    • Content-Type: application/json

  7. Parse response: Yes

  8. Router (based on stock_status)

Route A: Status = CRITICAL or STOCKOUT

4a. Slack - Create a Message - Channel: #ops-urgent - Text: 

🚨 *CRITICAL STOCK ALERT*

Stock Status: {{2.stock_status}}
Days Coverage: {{2.stock_days_coverage}} days
Available: {{2.available_kg}} kg

⚠️ *ACTION REQUIRED*
Reorder Point: {{2.reorder_point_kg}} kg
Suggested Order: {{2.suggested_order_kg}} kg

Weekly Demand: {{2.weekly_demand_kg}} kg
Active Subs: {{2.subscription_count}}

→ Contact co-packer immediately

Route B: Status = REORDER

4b. Slack - Create a Message - Channel: #ops-daily - Text: 

📦 *REORDER REMINDER*

Stock Status: {{2.stock_status}}
Days Coverage: {{2.stock_days_coverage}} days
Available: {{2.available_kg}} kg

Reorder Point: {{2.reorder_point_kg}} kg
Suggested Order: {{2.suggested_order_kg}} kg

→ Plan co-packer order this week

Route C: Status = OK

4c. Slack - Create a Message - Channel: #ops-daily - Text: 

📊 *Daily Inventory Status* - {{formatDate(now; "DD MMM YYYY")}}

✅ Stock Status: OK
📦 Available: {{2.available_kg}} kg
⏱️ Coverage: {{2.stock_days_coverage}} days

📈 Weekly Demand: {{2.weekly_demand_kg}} kg
👥 Active Subs: {{2.subscription_count}}
🔬 QA Hold: {{2.qa_hold_kg}} kg

  1. Error Handler (on HTTP module)
  2. Slack → #ops-alerts
  3. Text: ⚠️ [SOP-INV-02](SOP-INV-02_Demand_Forecasting_v1_0_PRODUCTION_READY.md) Scenario 1 Error: {{error.message}}

Scenario 2: Weekly Planning Report (Monday 09:00 UTC)

Name: [SOP-INV-02](SOP-INV-02_Demand_Forecasting_v1_0_PRODUCTION_READY.md) Weekly Planning Report
Trigger: Schedule - Every Monday at 09:00 UTC

Modules:

  1. Schedule (Trigger)

  2. Every Monday at 09:00 UTC

  3. HTTP - Supabase Query (Inventory Dashboard)

  4. URL: https://[project].supabase.co/rest/v1/v_inventory_planning_dashboard?select=*
  5. Method: GET

  6. Headers: apikey, Accept: application/json

  7. HTTP - Supabase Query (Weekly Forecast)

  8. URL: https://[project].supabase.co/rest/v1/v_demand_forecast_weekly?select=*
  9. Method: GET

  10. Headers: apikey, Accept: application/json

  11. HTTP - Supabase Query (Upcoming Billing - Next 7 Days)

  12. URL: https://[project].supabase.co/rest/v1/v_upcoming_billing_demand?billing_date=gte.{{formatDate(now; "YYYY-MM-DD")}}&billing_date=lt.{{formatDate(addDays(now; 7); "YYYY-MM-DD")}}

  13. Method: GET

  14. Slack - Create a Message

  15. Channel: #ops-weekly
  16. Text: 
    📋 *WEEKLY INVENTORY PLANNING REPORT*
Week of {{formatDate(now; "DD MMM YYYY")}}
━━━━━━━━━━━━━━━━━━━━━━

*CURRENT STATUS*
Stock: {{2.available_kg}} kg ({{2.stock_days_coverage}} days coverage)
Status: {{2.stock_status}}
QA Hold: {{2.qa_hold_kg}} kg

*DEMAND FORECAST*
Subscription Base: {{2.subscription_weekly_kg}} kg/week
New Customer Buffer: {{2.new_customer_weekly_kg}} kg/week
Total Weekly: {{2.total_weekly_demand_kg}} kg/week

*NEXT 7 DAYS BILLING*
{{#each 4.data}}
• {{billing_date}}: {{orders_expected}} orders ({{kg_required}} kg)
{{/each}}

*REORDER STATUS*
Reorder Point: {{2.reorder_point_kg}} kg
Reorder Required: {{2.reorder_required}}
Suggested Order: {{2.suggested_order_kg}} kg

━━━━━━━━━━━━━━━━━━━━━━
Lead Time: {{2.lead_time_weeks}} weeks
Safety Stock: {{2.safety_stock_weeks}} weeks

6. METABASE DASHBOARD

Dashboard: "Inventory Planning"

Row 1: Status Cards (4 across) - Stock Status (OK/REORDER/CRITICAL with color) - Days Coverage - Available Stock (kg) - Weekly Demand (kg)

Row 2: Demand Forecast Chart - Line chart from v_demand_forecast_weekly - X-axis: week_starting - Y-axis: total_forecast_kg - Overlay: cumulative_kg as area

Row 3: Upcoming Billing Calendar - Table from v_upcoming_billing_demand - Show next 6 weeks - Highlight high-volume days

Row 4: Reorder Decision - Reorder Point vs Current Stock (gauge chart) - Suggested Order Quantity - "Order Now" indicator if reorder_required = TRUE

7. OPERATIONAL PROCEDURES

7.1 Daily Check (08:15 AM)

  1. Review Slack message from Scenario 1
  2. If status = REORDER or CRITICAL:
  3. Check upcoming billing demand
  4. Contact co-packer if order needed
  5. Update ops_events with decision

7.2 Weekly Review (Monday AM)

  1. Review Weekly Planning Report in Slack
  2. Validate subscription count matches Shopify
  3. Adjust target_new_customers_per_week if acquisition trending differently
  4. Confirm lead time assumption still accurate
  5. Place production order if reorder_required = TRUE

7.3 Parameter Tuning (Monthly)

When to increase safety stock: - Experienced a near-stockout - Co-packer has shown delivery variance - Lab turnaround has exceeded SLA

When to decrease safety stock: - Storage costs becoming significant - Demand very stable for 60+ days - Confident in co-packer reliability

When to adjust new customer allowance: - Actual acquisition 20%+ above/below target for 4+ weeks - Launching new marketing campaign - Pausing paid acquisition

8. PHASE A DEFAULTS

For launch with 0-500 customers:

Parameter Value Rationale
Lead Time 4 weeks Conservative until co-packer proves faster
Safety Stock 2 weeks Buffer for Phase A learning curve
New Customers/Week 20 Target 80-100/month ramp
Avg First Box 8kg Most common starter size
Reorder Threshold 28 days ~1 month warning
Critical Threshold 14 days 2-week urgent alert

Phase B Adjustments (500-2,000 customers): - Consider reducing safety stock to 1.5 weeks if reliable - Increase new customer allowance based on actual acquisition rate - Add seasonal adjustment factors (Q4 dip, January spike)

9. FAILURE MODES

Failure Detection Response
Scenario 1 doesn't run No 08:00 Slack message Check Make.com history, run manually
Subscription count = 0 Dashboard shows 0 demand Check Seal sync, investigate data gap
Stock status stuck on OK Never triggers reorder Validate inventory table has correct kg
Coverage shows NULL Division by zero Ensure at least 1 active subscription
Co-packer misses lead time Batch arrives late Increase lead_time_weeks parameter

10. METABASE DASHBOARD QUERIES

10.1 Dashboard Header Cards

Card 1: Stock Status 

SELECT 
    stock_status,
    CASE stock_status
        WHEN 'OK' THEN '✅'
        WHEN 'REORDER' THEN '📦'
        WHEN 'CRITICAL' THEN '🚨'
        WHEN 'STOCKOUT' THEN '❌'
    END AS status_icon
FROM analytics.v_inventory_planning_dashboard;

Card 2: Days Coverage 

SELECT 
    stock_days_coverage AS "Days",
    stock_weeks_coverage AS "Weeks"
FROM analytics.v_inventory_planning_dashboard;

Card 3: Available Stock 

SELECT 
    available_kg || ' kg' AS "Available",
    qa_hold_kg || ' kg' AS "QA Hold"
FROM analytics.v_inventory_planning_dashboard;

Card 4: Weekly Demand 

SELECT 
    total_weekly_demand_kg || ' kg' AS "Weekly Demand",
    active_subscription_count AS "Active Subs"
FROM analytics.v_inventory_planning_dashboard;

10.2 Demand Forecast Chart

Line chart: 6-week forecast 

SELECT 
    week_starting,
    subscription_kg AS "Subscription Demand",
    new_customer_allowance_kg AS "New Customer Buffer",
    total_forecast_kg AS "Total Forecast"
FROM analytics.v_demand_forecast_weekly
ORDER BY week_starting;

10.3 Stock Drawdown Chart

Area chart: Cumulative demand vs available stock 

SELECT 
    week_starting,
    cumulative_kg AS "Cumulative Demand",
    (SELECT available_kg FROM analytics.v_inventory_planning_dashboard) AS "Current Stock"
FROM analytics.v_demand_forecast_weekly
ORDER BY week_starting;

10.4 Upcoming Billing Table

Table: Next 6 weeks of orders 

SELECT 
    billing_date AS "Date",
    orders_expected AS "Orders",
    kg_required AS "kg Required",
    boxes_8kg AS "8kg",
    boxes_12kg AS "12kg",
    boxes_16kg AS "16kg"
FROM analytics.v_upcoming_billing_demand
ORDER BY billing_date
LIMIT 42;

10.5 Reorder Decision Panel

Single value cards 

-- Reorder Point
SELECT reorder_point_kg || ' kg' AS "Reorder Point"
FROM analytics.v_inventory_planning_dashboard;

-- Current vs Reorder (for gauge)
SELECT 
    available_kg AS "Current",
    reorder_point_kg AS "Reorder Point",
    ROUND((available_kg / NULLIF(reorder_point_kg, 0) * 100)::numeric, 0) AS "% of Reorder Point"
FROM analytics.v_inventory_planning_dashboard;

-- Suggested Order
SELECT 
    CASE 
        WHEN reorder_required THEN '⚠️ ORDER NOW: ' || suggested_order_kg || ' kg'
        ELSE '✅ No order needed'
    END AS "Action"
FROM analytics.v_inventory_planning_dashboard;

10.6 Planning Parameters Reference

Table: Current config values 

SELECT 
    config_key AS "Parameter",
    config_value AS "Value",
    description AS "Description",
    updated_at AS "Last Updated"
FROM raw_ops.inventory_planning_config
ORDER BY config_key;

11. DOCUMENT CONTROL

Version History:

Version Date Author Changes
1.0 DRAFT 2025-12-10 Anton + Claude Initial creation
1.0 PRODUCTION_READY 2025-12-10 Anton + Claude SQL deployed, Make.com scenario built, Metabase queries added

Deployment Checklist:

  • [x] Configuration table created (raw_ops.inventory_planning_config)
  • [x] Subscription demand view (analytics.v_subscription_demand)
  • [x] Inventory status view (analytics.v_inventory_status)
  • [x] Master planning dashboard view (analytics.v_inventory_planning_dashboard)
  • [x] Weekly forecast view (analytics.v_demand_forecast_weekly)
  • [x] Upcoming billing view (analytics.v_upcoming_billing_demand)
  • [x] RPC function for Make.com (public.get_inventory_reorder_status)
  • [x] Permissions granted
  • [x] Make.com scenario created ([SOP-INV-02](SOP-INV-02_Demand_Forecasting_v1_0_PRODUCTION_READY.md) Daily Inventory Planning)
  • [ ] Make.com scenario activated (pending launch)
  • [ ] Metabase dashboard built (optional, queries provided)

Approval:

Role Name Date
Founder Anton 2025-12-10

Next Review Date: 2026-03-10 (Quarterly)

Document Location: - Master: Google Drive > Operations > SOPs > [SOP-INV-02](SOP-INV-02_Demand_Forecasting_v1_0_PRODUCTION_READY.md)_v1_0_PRODUCTION_READY.md

Protocol Raw — Verified safe, batch by batch.