Implementation Guide: Forecast raw material needs based on demand signals and current inventory

Hardware Procurement

Software Procurement

Prerequisites

• Functional ERP or inventory management system with minimum 12 months (ideally 24+ months) of historical sales order and purchase order data accessible via API, ODBC, or CSV export
• Accurate and maintained Bill of Materials (BOM) for all finished goods, including raw material components, quantities per unit, and unit of measure standardization
• Documented supplier lead times for all raw material categories (minimum: average lead time per supplier; ideal: lead time variability data)
• Stable internet connection with minimum 25 Mbps download / 10 Mbps upload for SaaS platform synchronization
• Network infrastructure supporting VLAN segmentation (or willingness to upgrade switching as specified in hardware procurement)
• Dedicated electrical circuit and rack/shelf space for the on-premises server in a climate-controlled area (not on the production floor)
• Client-side subject matter expert (SME) identified — typically the Production Manager, Purchasing Manager, or Materials Planner — available for 4-6 hours per week during implementation
• Current inventory count completed within the last 30 days, or willingness to conduct a physical inventory count before go-live
• SKU naming convention documented and consistent across ERP, warehouse labels, and supplier communications — or budget additional 2 weeks for SKU rationalization
• Administrative credentials for the existing ERP system and any connected accounting software (QuickBooks, Xero, etc.)
• Client approval of data processing agreement covering cloud storage of their inventory, sales, and supplier data in the Katana platform (hosted on AWS infrastructure)
• Defined user roles and access levels: who can view forecasts, who can approve purchase orders, who can modify BOM structures

Installation Steps

Step 1: Site Survey and Data Quality Audit

Conduct a thorough on-site assessment of the client's current manufacturing planning process, ERP system capabilities, data quality, and infrastructure readiness. This is the most critical phase — poor data quality is the #1 cause of forecasting project failure. Document all data sources, assess BOM accuracy, sample historical data for completeness, and identify gaps. Create a data quality scorecard rating each data dimension (completeness, accuracy, consistency, timeliness) on a 1-5 scale.

-- Connect to client ERP database and run data quality assessment queries
-- Sample query for SQL Server / PostgreSQL based ERPs:

-- Check historical sales order completeness (should return <5% nulls)
SELECT COUNT(*) as total_orders, SUM(CASE WHEN product_sku IS NULL OR quantity IS NULL OR order_date IS NULL THEN 1 ELSE 0 END) as incomplete_orders, ROUND(SUM(CASE WHEN product_sku IS NULL OR quantity IS NULL OR order_date IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as pct_incomplete FROM sales_orders WHERE order_date >= DATEADD(YEAR, -2, GETDATE());

-- Check BOM coverage (all finished goods should have BOM records)
SELECT fg.sku, fg.product_name, CASE WHEN b.parent_sku IS NULL THEN 'MISSING BOM' ELSE 'OK' END as bom_status FROM finished_goods fg LEFT JOIN bom_records b ON fg.sku = b.parent_sku GROUP BY fg.sku, fg.product_name, b.parent_sku;

-- Assess inventory record accuracy
SELECT COUNT(*) as total_items, SUM(CASE WHEN last_count_date < DATEADD(MONTH, -6, GETDATE()) THEN 1 ELSE 0 END) as stale_counts, SUM(CASE WHEN quantity_on_hand < 0 THEN 1 ELSE 0 END) as negative_inventory FROM inventory_master;

Step 2: Server Hardware Installation and Base Configuration

Rack or position the Dell PowerEdge T360 server, connect UPS, install the managed switch, and configure the base operating system. Set up VLANs to isolate server traffic from production floor devices. Configure iDRAC9 for remote management access by the MSP.

Rename-Computer -NewName 'MFG-FORECAST-SVR' -Restart

# After restart:
New-NetIPAddress -InterfaceAlias 'Ethernet' -IPAddress 192.168.10.10 -PrefixLength 24 -DefaultGateway 192.168.10.1
Set-DnsClientServerAddress -InterfaceAlias 'Ethernet' -ServerAddresses 192.168.10.1,8.8.8.8

# Enable Windows Remote Management for MSP RMM
Enable-PSRemoting -Force
Set-Item WSMan:\localhost\Client\TrustedHosts -Value '*' -Force

# Install Windows Updates
Install-Module PSWindowsUpdate -Force
Get-WindowsUpdate -Install -AcceptAll -AutoReboot

# Configure Windows Firewall for SQL Server
New-NetFirewallRule -DisplayName 'SQL Server' -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow
New-NetFirewallRule -DisplayName 'SQL Server Browser' -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow

enable
configure terminal
vlan 10
name SERVER-VLAN
exit
vlan 20
name CLIENT-VLAN
exit
interface GigabitEthernet1
switchport mode access
switchport access vlan 10
exit

Step 3: SQL Server Installation and Data Staging Database Setup

Install Microsoft SQL Server 2022 Standard on the server and create the staging database schema. This database serves as the central hub for cleansed, normalized data from the ERP before it's pushed to Katana via API. It also stores historical forecast results for accuracy tracking.

CREATE DATABASE ManufacturingForecast;
GO
USE ManufacturingForecast;
GO

-- Core staging tables
CREATE TABLE stg_SalesOrders (
    SalesOrderID NVARCHAR(50) PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID NVARCHAR(50),
    ProductSKU NVARCHAR(100) NOT NULL,
    ProductName NVARCHAR(255),
    Quantity DECIMAL(18,4) NOT NULL,
    UnitOfMeasure NVARCHAR(20),
    UnitPrice DECIMAL(18,4),
    ShipDate DATE,
    SourceSystem NVARCHAR(50),
    SyncTimestamp DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE stg_BillOfMaterials (
    BOMID INT IDENTITY(1,1) PRIMARY KEY,
    FinishedGoodSKU NVARCHAR(100) NOT NULL,
    FinishedGoodName NVARCHAR(255),
    RawMaterialSKU NVARCHAR(100) NOT NULL,
    RawMaterialName NVARCHAR(255),
    QuantityPer DECIMAL(18,6) NOT NULL,
    UnitOfMeasure NVARCHAR(20),
    ScrapFactor DECIMAL(5,4) DEFAULT 0,
    IsActive BIT DEFAULT 1,
    LastUpdated DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE stg_InventoryLevels (
    InventoryID INT IDENTITY(1,1) PRIMARY KEY,
    SKU NVARCHAR(100) NOT NULL,
    ItemName NVARCHAR(255),
    ItemType NVARCHAR(20) CHECK (ItemType IN ('RAW','WIP','FG')),
    QuantityOnHand DECIMAL(18,4),
    QuantityAllocated DECIMAL(18,4) DEFAULT 0,
    QuantityAvailable AS (QuantityOnHand - QuantityAllocated),
    ReorderPoint DECIMAL(18,4),
    SafetyStock DECIMAL(18,4),
    WarehouseLocation NVARCHAR(100),
    LastCountDate DATE,
    SyncTimestamp DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE stg_SupplierLeadTimes (
    SupplierLeadTimeID INT IDENTITY(1,1) PRIMARY KEY,
    SupplierID NVARCHAR(50) NOT NULL,
    SupplierName NVARCHAR(255),
    RawMaterialSKU NVARCHAR(100) NOT NULL,
    AvgLeadTimeDays INT NOT NULL,
    MinLeadTimeDays INT,
    MaxLeadTimeDays INT,
    MinOrderQuantity DECIMAL(18,4),
    UnitCost DECIMAL(18,4),
    Currency NVARCHAR(3) DEFAULT 'USD',
    LastUpdated DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE fact_ForecastResults (
    ForecastID INT IDENTITY(1,1) PRIMARY KEY,
    ForecastDate DATE NOT NULL,
    SKU NVARCHAR(100) NOT NULL,
    ForecastPeriodStart DATE NOT NULL,
    ForecastPeriodEnd DATE NOT NULL,
    ForecastedDemand DECIMAL(18,4),
    ActualDemand DECIMAL(18,4),
    ForecastSource NVARCHAR(50),
    MAPE DECIMAL(8,4),
    CreatedTimestamp DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE dim_SKUMapping (
    MappingID INT IDENTITY(1,1) PRIMARY KEY,
    ERPSourceSKU NVARCHAR(100) NOT NULL,
    KatanaSKU NVARCHAR(100) NOT NULL,
    ItemName NVARCHAR(255),
    Category NVARCHAR(100),
    IsActive BIT DEFAULT 1
);

-- Create indexes for common query patterns
CREATE INDEX IX_SalesOrders_Date_SKU ON stg_SalesOrders(OrderDate, ProductSKU);
CREATE INDEX IX_BOM_FG_SKU ON stg_BillOfMaterials(FinishedGoodSKU);
CREATE INDEX IX_Inventory_SKU ON stg_InventoryLevels(SKU);
CREATE INDEX IX_Forecast_SKU_Date ON fact_ForecastResults(SKU, ForecastPeriodStart);

Step 4: ERP Data Extraction and Initial Data Cleansing

Extract historical data from the client's ERP system and load it into the staging database. This step involves mapping ERP fields to the staging schema, handling data type conversions, deduplicating records, standardizing units of measure, and resolving BOM inconsistencies. This is typically the most time-intensive phase of the project. ``` # Example: Extracting data from QuickBooks Desktop via ODBC # First, install QuickBooks ODBC driver (QODBC by CData) # Python script for ERP data extractio...

Step 5: Katana Cloud Manufacturing Platform Setup

Create and configure the Katana Cloud Manufacturing account, set up the organizational structure, import the cleansed master data (products, BOMs, materials, suppliers), and configure warehouse locations and inventory tracking. This establishes the foundational MRP system before activating the forecasting add-on.

product_name,sku,category,type,unit_of_measure,cost
'Steel Rod 10mm',RM-STEEL-10MM,Raw Materials,material,KG,2.45
'Aluminum Sheet 2mm',RM-ALUM-2MM,Raw Materials,material,EA,15.80
'Widget Assembly A',FG-WIDGET-A,Finished Goods,product,EA,45.00

# base URL: https://api.katanamrp.com/v1/

curl -X GET 'https://api.katanamrp.com/v1/products' -H 'Authorization: Bearer <your_api_key>' -H 'Content-Type: application/json'

Step 6: Workato Integration Platform Configuration

Set up the Workato iPaaS to orchestrate bidirectional data synchronization between the on-premises ERP/staging database and Katana Cloud Manufacturing. This creates the automated data pipeline that keeps inventory levels, sales orders, and BOM changes synchronized in near-real-time.

On Windows Server, run PowerShell as Administrator:

mkdir C:\Workato\Agent
cd C:\Workato\Agent
Invoke-WebRequest -Uri 'https://files.workato.com/opa/workato-agent-windows.zip' -OutFile 'workato-agent.zip'
Expand-Archive -Path 'workato-agent.zip' -DestinationPath .

Configure agent connection profile by editing config\config.yml:

database:
  staging_db:
    adapter: sqlserver
    host: localhost
    port: 1433
    database: ManufacturingForecast
    username: etl_user
    password: <encrypted_password>

Start the agent as a Windows Service:

.\bin\install-service.bat
net start WorkatoAgent

Recipe 1: Sync Sales Orders (ERP → Staging → Katana)

• Trigger: Scheduled every 15 minutes
• Step a: Query staging DB for new/updated sales orders since last sync
• Step b: Transform data to Katana sales order format
• Step c: POST to Katana API: /v1/sales_orders
• Step d: Log sync results to fact_SyncLog table

Recipe 2: Sync Inventory Levels (Katana → Staging DB)

• Trigger: Scheduled every 30 minutes
• Step a: GET from Katana API: /v1/stock_movements
• Step b: Transform and map to staging schema
• Step c: UPSERT to stg_InventoryLevels table
• Step d: Trigger Power BI dataset refresh if threshold breached

Recipe 3: BOM Change Sync (ERP → Katana)

• Trigger: On change detected in ERP BOM table
• Step a: Detect BOM modifications in staging DB
• Step b: Transform to Katana recipe format
• Step c: PUT to Katana API: /v1/recipes/{id}
• Step d: Alert MSP if BOM change impacts active forecasts

Error Handling Configuration (All Recipes)

• Retry 3 times with exponential backoff (30s, 60s, 120s)
• On permanent failure: send email to msp-alerts@<msp-domain>.com
• Log all errors to staging DB error table

Step 7: Activate and Configure Katana Planning & Forecasting

Enable the Planning & Forecasting add-on in Katana, configure forecasting parameters, set planning horizons, define reorder rules, and run the initial demand forecast. The AI model will analyze the imported historical sales data to generate demand predictions and raw material requirement calculations via BOM explosion. ``` # In Katana web interface: # 1. Navigate to Settings > Subscriptions > Add Planning & Forecasting # Confirm add-on activation ($199/mo) # 2. Configure Forecast Settings ...

Step 8: Power BI Dashboard Configuration

Set up Power BI dashboards that provide the client's management team with real-time visibility into forecast accuracy, inventory health, raw material requirements, and supplier performance. Connect Power BI to both the SQL staging database (for historical trend analysis) and the Katana API (for current state). ``` # 1. Install Power BI Desktop on planner workstations # Download from: https://powerbi.microsoft.com/desktop # 2. Create data connections in Power BI Desktop: # SQL Server connectio...

Step 9: Parallel Running and User Acceptance Testing

Run the AI forecasting system in parallel with the client's existing planning process for 2-4 weeks. Compare AI-generated purchase recommendations against what the planner would have ordered manually. This builds confidence, identifies gaps, and allows fine-tuning before going live. ``` # Create parallel comparison tracking spreadsheet or use this SQL view: CREATE VIEW vw_ForecastComparison AS SELECT f.SKU, f.ForecastPeriodStart, f.ForecastedDemand AS AI_Forecast, m.ManualFore...

Step 10: Go-Live Cutover and Production Deployment

Transition from parallel running to the AI system as the primary forecasting and replenishment recommendation engine. Configure automated alerts, finalize user permissions, enable automated purchase order suggestion workflows, and establish the ongoing operational cadence.

CREATE PROCEDURE sp_CheckSyncHealth AS
BEGIN
    DECLARE @LastSync DATETIME2, @HoursSinceSync INT;
    SELECT @LastSync = MAX(SyncTimestamp) FROM stg_SalesOrders;
    SET @HoursSinceSync = DATEDIFF(HOUR, @LastSync, GETDATE());
    
    IF @HoursSinceSync > 2
    BEGIN
        -- Integration sync has stalled
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'MSP Alerts',
            @recipients = 'alerts@msp-domain.com',
            @subject = 'ALERT: Forecast data sync stalled',
            @body = 'Sales order sync has not run for over 2 hours. Check Workato recipes and on-prem agent status.';
    END
END;

-- Schedule to run every 30 minutes via SQL Agent
EXEC sp_add_job @job_name = 'Monitor Forecast Sync Health';

Custom AI Components

Demand Signal Aggregator

Type: integration A Python-based ETL service that runs on the on-premises server, extracting data from multiple demand signal sources (ERP sales orders, CRM pipeline opportunities, customer forecasts, and historical shipments), normalizing and cleansing the data, and loading it into the SQL staging database. This component handles the critical data quality layer between raw ERP data and the forecasting platform. Implementation: ``` # File: C:\ForecastETL\demand_signal_aggregator.py # ...

BOM Explosion Forecast Calculator

Type: workflow

A SQL stored procedure that takes the finished goods demand forecast from Katana and explodes it through the bill of materials to calculate net raw material requirements. It accounts for current inventory, safety stock levels, pending purchase orders, and WIP allocations to generate actionable net requirement quantities per raw material per time period.

Implementation:

-- BOM explosion of demand forecast into raw material requirements. Schedule:
-- Run daily after Katana forecast sync completes.

-- File: Deploy to ManufacturingForecast database
-- Purpose: BOM explosion of demand forecast into raw material requirements
-- Schedule: Run daily after Katana forecast sync completes

CREATE OR ALTER PROCEDURE sp_CalculateRawMaterialRequirements
    @ForecastHorizonMonths INT = 6,
    @IncludeSafetyStock BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Create temp table for results
    IF OBJECT_ID('tempdb..#MaterialRequirements') IS NOT NULL
        DROP TABLE #MaterialRequirements;
    
    CREATE TABLE #MaterialRequirements (
        RawMaterialSKU NVARCHAR(100),
        RawMaterialName NVARCHAR(255),
        PeriodStart DATE,
        PeriodEnd DATE,
        GrossRequirement DECIMAL(18,4),
        ScrapAllowance DECIMAL(18,4),
        TotalGrossRequirement DECIMAL(18,4),
        CurrentOnHand DECIMAL(18,4),
        PendingPOQuantity DECIMAL(18,4),
        AllocatedToWIP DECIMAL(18,4),
        SafetyStockReq DECIMAL(18,4),
        NetRequirement DECIMAL(18,4),
        SuggestedOrderQty DECIMAL(18,4),
        SuggestedOrderDate DATE,
        SupplierID NVARCHAR(50),
        SupplierName NVARCHAR(255),
        EstimatedCost DECIMAL(18,4),
        UnitOfMeasure NVARCHAR(20)
    );
    
    -- Step 1: Get demand forecast per finished good per period
    -- (Sourced from Katana API sync into fact_ForecastResults)
    DECLARE @StartDate DATE = DATEADD(DAY, 1-DAY(GETDATE()), GETDATE()); -- First of current month
    DECLARE @EndDate DATE = DATEADD(MONTH, @ForecastHorizonMonths, @StartDate);
    
    ;WITH ForecastedDemand AS (
        SELECT 
            SKU AS FinishedGoodSKU,
            ForecastPeriodStart,
            ForecastPeriodEnd,
            ForecastedDemand
        FROM fact_ForecastResults
        WHERE ForecastPeriodStart >= @StartDate
          AND ForecastPeriodStart < @EndDate
          AND ForecastSource = 'KATANA_AI'
          AND ForecastDate = (
              SELECT MAX(ForecastDate) FROM fact_ForecastResults 
              WHERE ForecastSource = 'KATANA_AI'
          )
    ),
    
    -- Step 2: Explode BOM - multiply FG demand by raw material quantity per unit
    ExplodedBOM AS (
        SELECT 
            b.RawMaterialSKU,
            b.RawMaterialName,
            b.UnitOfMeasure,
            fd.ForecastPeriodStart,
            fd.ForecastPeriodEnd,
            SUM(fd.ForecastedDemand * b.QuantityPer) AS GrossRequirement,
            SUM(fd.ForecastedDemand * b.QuantityPer * b.ScrapFactor) AS ScrapAllowance
        FROM ForecastedDemand fd
        INNER JOIN stg_BillOfMaterials b ON fd.FinishedGoodSKU = b.FinishedGoodSKU
        WHERE b.IsActive = 1
        GROUP BY b.RawMaterialSKU, b.RawMaterialName, b.UnitOfMeasure,
                 fd.ForecastPeriodStart, fd.ForecastPeriodEnd
    ),
    
    -- Step 3: Get current inventory and allocations
    CurrentInventory AS (
        SELECT 
            SKU,
            SUM(QuantityOnHand) AS OnHand,
            SUM(QuantityAllocated) AS Allocated,
            MAX(SafetyStock) AS SafetyStock
        FROM stg_InventoryLevels
        WHERE ItemType = 'RAW'
        GROUP BY SKU
    ),
    
    -- Step 4: Get preferred supplier info
    PreferredSupplier AS (
        SELECT 
            RawMaterialSKU,
            SupplierID,
            SupplierName,
            AvgLeadTimeDays,
            MinOrderQuantity,
            UnitCost,
            ROW_NUMBER() OVER (PARTITION BY RawMaterialSKU ORDER BY UnitCost ASC) AS SupplierRank
        FROM stg_SupplierLeadTimes
    )
    
    -- Step 5: Calculate net requirements
    INSERT INTO #MaterialRequirements
    SELECT 
        eb.RawMaterialSKU,
        eb.RawMaterialName,
        eb.ForecastPeriodStart,
        eb.ForecastPeriodEnd,
        eb.GrossRequirement,
        eb.ScrapAllowance,
        eb.GrossRequirement + eb.ScrapAllowance AS TotalGrossRequirement,
        COALESCE(ci.OnHand, 0) AS CurrentOnHand,
        0 AS PendingPOQuantity, -- Populated from Katana PO sync
        COALESCE(ci.Allocated, 0) AS AllocatedToWIP,
        CASE WHEN @IncludeSafetyStock = 1 THEN COALESCE(ci.SafetyStock, 0) ELSE 0 END AS SafetyStockReq,
        -- Net Requirement = Gross + Scrap + Safety Stock - On Hand + Allocated - Pending POs
        GREATEST(
            (eb.GrossRequirement + eb.ScrapAllowance 
             + CASE WHEN @IncludeSafetyStock = 1 THEN COALESCE(ci.SafetyStock, 0) ELSE 0 END)
            - (COALESCE(ci.OnHand, 0) - COALESCE(ci.Allocated, 0))
            - 0, -- PendingPOQuantity placeholder
            0
        ) AS NetRequirement,
        -- Round up to supplier MOQ
        CASE 
            WHEN GREATEST(
                (eb.GrossRequirement + eb.ScrapAllowance 
                 + CASE WHEN @IncludeSafetyStock = 1 THEN COALESCE(ci.SafetyStock, 0) ELSE 0 END)
                - (COALESCE(ci.OnHand, 0) - COALESCE(ci.Allocated, 0)),
                0
            ) = 0 THEN 0
            ELSE CEILING(
                GREATEST(
                    (eb.GrossRequirement + eb.ScrapAllowance 
                     + CASE WHEN @IncludeSafetyStock = 1 THEN COALESCE(ci.SafetyStock, 0) ELSE 0 END)
                    - (COALESCE(ci.OnHand, 0) - COALESCE(ci.Allocated, 0)),
                    0
                ) / NULLIF(COALESCE(ps.MinOrderQuantity, 1), 0)
            ) * COALESCE(ps.MinOrderQuantity, 1)
        END AS SuggestedOrderQty,
        -- Suggested order date = period start minus supplier lead time
        DATEADD(DAY, -COALESCE(ps.AvgLeadTimeDays, 14), eb.ForecastPeriodStart) AS SuggestedOrderDate,
        ps.SupplierID,
        ps.SupplierName,
        COALESCE(ps.UnitCost, 0) * GREATEST(
            (eb.GrossRequirement + eb.ScrapAllowance 
             + CASE WHEN @IncludeSafetyStock = 1 THEN COALESCE(ci.SafetyStock, 0) ELSE 0 END)
            - (COALESCE(ci.OnHand, 0) - COALESCE(ci.Allocated, 0)),
            0
        ) AS EstimatedCost,
        eb.UnitOfMeasure
    FROM ExplodedBOM eb
    LEFT JOIN CurrentInventory ci ON eb.RawMaterialSKU = ci.SKU
    LEFT JOIN PreferredSupplier ps ON eb.RawMaterialSKU = ps.RawMaterialSKU AND ps.SupplierRank = 1;
    
    -- Output: Items that need ordering NOW (suggested order date within 14 days)
    SELECT * FROM #MaterialRequirements
    WHERE SuggestedOrderDate <= DATEADD(DAY, 14, GETDATE())
      AND NetRequirement > 0
    ORDER BY SuggestedOrderDate ASC;
    
    -- Persist results for Power BI reporting
    INSERT INTO fact_MaterialRequirements (RawMaterialSKU, RawMaterialName, PeriodStart, PeriodEnd,
        GrossRequirement, NetRequirement, SuggestedOrderQty, SuggestedOrderDate,
        SupplierName, EstimatedCost, CalculatedDate)
    SELECT RawMaterialSKU, RawMaterialName, PeriodStart, PeriodEnd,
        TotalGrossRequirement, NetRequirement, SuggestedOrderQty, SuggestedOrderDate,
        SupplierName, EstimatedCost, GETDATE()
    FROM #MaterialRequirements
    WHERE NetRequirement > 0;
    
    DROP TABLE #MaterialRequirements;
END;
GO

-- Create the persistence table if it doesn't exist
IF OBJECT_ID('fact_MaterialRequirements') IS NULL
BEGIN
    CREATE TABLE fact_MaterialRequirements (
        RequirementID INT IDENTITY(1,1) PRIMARY KEY,
        RawMaterialSKU NVARCHAR(100),
        RawMaterialName NVARCHAR(255),
        PeriodStart DATE,
        PeriodEnd DATE,
        GrossRequirement DECIMAL(18,4),
        NetRequirement DECIMAL(18,4),
        SuggestedOrderQty DECIMAL(18,4),
        SuggestedOrderDate DATE,
        SupplierName NVARCHAR(255),
        EstimatedCost DECIMAL(18,4),
        CalculatedDate DATETIME2 DEFAULT GETDATE()
    );
    CREATE INDEX IX_MatReq_SKU_Date ON fact_MaterialRequirements(RawMaterialSKU, PeriodStart);
END;
GO

Forecast Accuracy Monitor

Type: agent A Python service that runs weekly to compare forecasted demand against actual demand once the forecast period has elapsed. It calculates MAPE (Mean Absolute Percentage Error), bias, and tracking signal per SKU and per product category. When accuracy degrades below configurable thresholds, it sends alerts to the MSP and client, enabling proactive model retuning before business impact occurs. Implementation: ``` # File: C:\ForecastETL\forecast_accuracy_monitor.py # Schedule:...

Katana Forecast Sync Bridge

Type: integration A Python integration service that bridges the Katana Planning & Forecasting API with the on-premises SQL staging database. It pulls forecast results from Katana, stores them locally for Power BI reporting and accuracy tracking, and pushes updated inventory snapshots from the staging database back to Katana to keep the forecasting model's inputs current. Implementation: ``` # File: C:\ForecastETL\katana_sync_bridge.py # Schedule: Windows Task Scheduler, every 30 minut...

Daily Purchase Recommendation Digest

Type: prompt A templated email digest generator that combines the BOM explosion results with supplier information to produce a clear, actionable daily purchasing recommendation report. Sent to the purchasing manager every morning, it lists what to order, from whom, how much, and by when. Implementation: ``` # File: C:\ForecastETL\purchase_digest.py # Schedule: Windows Task Scheduler, daily at 7:00 AM # Requirements: pip install pyodbc jinja2 smtplib import pyodbc import os from datet...

Testing & Validation

Client Handoff

The client handoff should be conducted as a structured half-day session (4 hours) with all key stakeholders present: Purchasing Manager, Production Manager, Plant Manager/Owner, and any material planners who will use the system daily.

Training Topics (2.5 hours hands-on)

Documentation Package to Leave Behind

• Quick Start Card (laminated, desk reference): Daily workflow checklist, key URLs, login info, support contacts
• System Architecture Diagram: One-page visual showing data flow from ERP → Staging DB → Katana → Forecasts → Purchase Recommendations
• User Guide (20-30 pages PDF): Step-by-step screenshots for all common tasks in Katana and Power BI
• Runbook for MSP (internal): Server credentials, database schemas, ETL schedule, monitoring thresholds, Workato recipe documentation, Katana API key location, escalation contacts
• Data Dictionary: Field mappings between ERP, staging database, and Katana for troubleshooting sync issues
• BOM Maintenance Guide: How to add new products, update recipes, add new raw materials, set up new suppliers

Success Criteria Review (30 min discussion)

Maintenance

Ongoing MSP Responsibilities and Cadence:

Daily (Automated)

• Monitor ETL pipeline execution via sync health check stored procedure (sp_CheckSyncHealth runs every 30 minutes)
• Review any error alerts from Workato integration recipes — respond within 4 business hours for data sync failures
• Verify the daily purchase digest email was generated and sent (check logs at C:\ForecastETL\logs\)

Weekly

• Review the Forecast Accuracy Monitor output every Monday — investigate any SKUs with MAPE >25%
• Check Workato task consumption to ensure usage stays within plan limits (2,500 tasks/month on Starter)
• Verify SQL Server backup completion on the Synology NAS (Veeam job logs)
• Review Windows Server event logs for disk space, memory, and CPU anomalies via RMM tool

Monthly

• Generate and review a monthly forecast accuracy report with the client's Purchasing Manager (30-minute call)
• Apply Windows Server security patches during a scheduled maintenance window (monthly Patch Tuesday + 1 week)
• Check for Katana platform updates and review release notes for any breaking API changes
• Review SQL staging database size and archive records older than 36 months to cold storage
• Validate that supplier lead times in the system still match actual lead times (request client confirmation)

Quarterly (Quarterly Business Review - QBR)

• Present a comprehensive forecast accuracy report: MAPE trends, bias analysis, top/bottom performing SKU categories
• Review ROI metrics: stockout reduction, inventory carrying cost changes, expedited shipping cost reduction
• Identify new demand signal sources to incorporate (e.g., customer forecasts, promotional calendars, seasonal adjustments)
• Evaluate whether BOM structures need updating based on production changes
• Assess whether the forecasting horizon or review frequency needs adjustment
• Review Katana subscription tier — upgrade to Professional ($799/mo) if client has grown past 500 active SKUs
• Recommend model retuning if MAPE has drifted above 25% for 2+ consecutive months

Annually

• Full system health check: server hardware diagnostics, database index rebuilds, ETL performance optimization
• Katana license renewal coordination and pricing review
• Power BI dashboard refresh: add new KPIs, update visualizations based on client feedback
• Disaster recovery test: restore from backup to verify RTO < 4 hours
• Review and update the data processing agreement and compliance posture (especially for CMMC/ITAR clients)

Model Retraining Triggers (initiate within 1 week of detection)

• MAPE exceeds 30% for 3 consecutive weeks across all SKUs
• Client adds >20% new SKUs or discontinues >20% existing products
• Major demand pattern shift (e.g., lost/gained a large customer, entered new market segment)
• Client changes production processes significantly (new equipment, different batch sizes)
• Supplier base changes significantly (new suppliers, different lead times)

SLA Considerations

• Data sync uptime target: 99% (max 7.2 hours downtime per month)
• Alert response time: Critical alerts (sync failure, data loss risk) within 2 hours; Warning alerts within 1 business day
• Forecast accuracy commitment: Maintain overall MAPE below 25% within 6 months of go-live; if sustained above 30%, MSP will conduct root cause analysis at no additional charge
• System restoration RTO: 4 hours for SQL staging database; Katana SaaS is vendor-managed (99.9% SLA from Katana)

Escalation Path

Alternatives

Odoo MRP with Built-in Demand Planning (Budget Option)

For budget-constrained SMBs under $2M revenue, deploy Odoo Enterprise MRP module as the all-in-one ERP and forecasting solution. Odoo provides manufacturing resource planning, inventory management, BOM management, and basic demand forecasting from historical sales data — all in a single open-source platform at $24.90-$37.40 per user per month. This eliminates the need for separate forecasting software, middleware (Workato), and in many cases the on-premises server (Odoo can be fully cloud-hosted).

Microsoft Dynamics 365 Business Central with Native Forecasting (Microsoft Ecosystem)

For clients already invested in the Microsoft ecosystem (Microsoft 365, Azure AD, Outlook/Teams), deploy Dynamics 365 Business Central Premium ($100-$110/user/month) as the ERP with its built-in MRP and MPS (Master Production Schedule) capabilities. Business Central natively integrates demand forecasts into planning, automatically generating production and purchasing recommendations based on sales orders, forecasts, and BOMs. Enhance with Azure Machine Learning AutoML for custom time-series forecasting models if the native forecasting isn't sophisticated enough.

Datup AI Demand Planning (Best-in-Class AI, Quick Deployment)

For data-mature manufacturers wanting the most advanced AI forecasting without building custom models, deploy Datup as a dedicated AI demand planning layer on top of the existing ERP. Datup uses deep learning models specifically trained for supply chain forecasting, claims 95%+ forecast accuracy, and connects to existing ERP/WMS/TMS systems. It goes live in approximately 5 weeks with no implementation fees and no user limits. Tradeoffs: Cost: Custom quote-based pricing (typically $500-$2,00...

Custom Azure ML AutoML Build (Maximum Flexibility)

For manufacturers with unique forecasting requirements, complex multi-echelon supply chains, or data science resources, build a custom forecasting solution using Azure Machine Learning's AutoML time-series forecasting capabilities. This approach provides maximum control over model selection, feature engineering, and integration architecture. Azure AutoML evaluates multiple algorithms (ARIMA, Prophet, ForecastTCN, etc.) and automatically selects the best-performing model per SKU. Tradeoffs: ...

Cin7 Core with ForesightAI (Omnichannel Manufacturers)

For manufacturers that also sell through multiple channels (direct, wholesale, e-commerce), deploy Cin7 Core with its ForesightAI demand prediction module. Cin7 predicts demand up to 24 months ahead with automated reordering and offers 450+ integrations with sales channels, 3PLs, and accounting platforms. The Advanced Manufacturing module ($599/mo) adds full MRP with production planning.