Implementation Guide: Auto-trigger purchase orders when sku inventory falls below reorder thresholds
Step-by-step implementation guide for deploying AI to auto-trigger purchase orders when sku inventory falls below reorder thresholds for Retail clients.
Hardware Procurement
Honeywell Voyager 1250g Barcode Scanner
$60 per unit (MSP cost via Ingram Micro/TD SYNNEX) / $95 suggested resale
Corded 1D USB laser barcode scanner for POS checkout stations. Scans product barcodes at point of sale to decrement inventory in real-time, which drives the reorder threshold logic. Industry-standard reliability for retail counter use.
Zebra TC22 Mobile Computer
Zebra TC22 Mobile Computer
$400 per unit (MSP cost via Zebra PartnerConnect) / $600 suggested resale
Rugged Wi-Fi handheld mobile computer with integrated 2D barcode scanner. Used for receiving inbound purchase orders (scan-to-receive), cycle counting to maintain inventory accuracy, and floor-walk stock checks. 6-inch display, Wi-Fi 6, Android 13. Essential for maintaining the data integrity that auto-reorder depends on.
Zebra ZD421 Direct Thermal Label Printer
$310 per unit (MSP cost) / $475 suggested resale
Desktop 4-inch direct thermal barcode label printer for printing SKU barcode labels, bin location labels, and receiving labels. Ensures every product has a scannable barcode. USB + Ethernet connectivity. Required for any items the retailer sells that arrive without manufacturer barcodes.
Zebra ZD421 Label Media - Direct Thermal Labels
$15 per roll (MSP cost) / $25 suggested resale
Consumable direct thermal label stock for the ZD421 printer. Four rolls provide approximately 8,400 labels for initial labeling project and ongoing use.
APC Smart-UPS 1500VA LCD
$450 per unit (MSP cost) / $600 suggested resale
Uninterruptible power supply to protect the POS workstation and network equipment. Prevents inventory transaction loss during power events. Critical for maintaining accurate stock counts that drive reorder triggers. Only required for on-premises server deployments (Fishbowl/QB Enterprise); optional but recommended for cloud deployments to protect POS uptime.
Network Access Point Upgrade (if needed)
Network Access Point
$130 per unit (MSP cost) / $200 suggested resale
Wi-Fi 6 access point to provide reliable wireless connectivity for Zebra TC22 mobile computers during receiving and cycle counting. Only procure if client's existing Wi-Fi does not provide adequate coverage in the stockroom/warehouse area or lacks WPA3 support.
Software Procurement
Cin7 Core - Standard Plan
$349/month (client cost); MSP earns referral commission via Cin7 Channel Partner Program
Primary inventory management platform with native auto-reorder PO generation, multi-channel inventory sync (POS, e-commerce, marketplace), supplier management, purchase order workflows, and receiving. Includes built-in POS module. Recommended for retailers with 3,000–10,000 SKUs or multi-channel selling.
Lightspeed Retail X-Series - Core Plan
$149/month (client cost); MSP earns referral commission via Lightspeed Partner Program
Alternative primary platform for brick-and-mortar-focused retailers with 500–3,000 SKUs. Combines POS with inventory management, reorder points, supplier management, purchase orders, and demand forecasting analytics. Includes e-commerce. Choose this OR Cin7 Core, not both.
Zoho Inventory - Professional Plan
$99/month (client cost); MSP earns 20% partner margin via Zoho Partner Program
Budget alternative primary platform for cost-sensitive retailers or those already using Zoho Books/CRM. Supports automatic reorder triggers, purchase order generation, multi-channel sync, and integrates natively with Zoho Books and QuickBooks Online. Best for <3,000 SKUs and <3,000 orders/month.
QuickBooks Online - Plus Plan
$99/month (client cost); MSP can manage billing via Intuit Solution Provider
Accounting system integration target. Receives synced purchase orders and bills from the inventory platform. Tracks accounts payable, COGS, and inventory valuation. Most retail SMBs already have this; if not, it must be procured.
n8n Community Edition (Self-Hosted)
$0 software cost + $20–$50/month VPS hosting (e.g., DigitalOcean Droplet or Hetzner)
Workflow automation middleware for custom integration scenarios where the primary inventory platform lacks a native connector to the client's existing POS or accounting system. Used to build custom webhook-triggered workflows that monitor inventory levels via API polling and generate POs. Only deploy if native integrations are insufficient.
Zapier - Professional Plan
$19.99/month billed annually (750 tasks) or $29.99/month billed monthly
Alternative no-code middleware for connecting inventory platform to accounting, email, and notification systems when native integrations are unavailable. Simpler than n8n but incurs per-task costs. Best for low-volume automation (<750 PO-related tasks/month). License type: SaaS - monthly subscription.
Prerequisites
- Client has an active POS system that records sales at the individual SKU level (not just transaction totals). Supported: Square, Shopify POS, Lightspeed POS, Clover, or any system with API access to sales/inventory data.
- Client has an accounting system deployed and operational — QuickBooks Online (Plus or higher), QuickBooks Desktop Enterprise, Xero, or Zoho Books. Chart of accounts must include inventory asset, COGS, and accounts payable accounts.
- Client has assigned unique SKU identifiers to every product they sell. If products lack SKUs, a SKU assignment project must be completed before this implementation begins.
- Client has a documented list of all active suppliers/vendors including: company name, primary contact name, email address, phone number, payment terms, and typical lead time in business days.
- Client has performed or is willing to perform a complete physical inventory count before go-live to establish accurate baseline stock quantities. Counts must be documented per SKU.
- Client has stable internet connectivity at each retail location: minimum 25 Mbps download / 5 Mbps upload with 99.9% uptime. A cellular failover connection is recommended.
- Client has Wi-Fi coverage extending to the stockroom/receiving area with WPA2 or WPA3 encryption, sufficient for the Zebra TC22 mobile computer to maintain a stable connection.
- Client has identified a project stakeholder (typically store manager or operations manager) who has authority to approve purchasing decisions and can dedicate 2–4 hours per week during the implementation period.
- Client has documented their current reorder process: who orders, how frequently, from which suppliers, what triggers an order (gut feel, spreadsheet, visual inspection), and average order values.
- MSP has active accounts with hardware distribution partners (Ingram Micro, TD SYNNEX, or CDW) and has enrolled in Zebra PartnerConnect and Honeywell Business Solutions Partner programs for preferred pricing.
Installation Steps
Step 1: Discovery and Requirements Documentation
Conduct an on-site or virtual discovery session with the client stakeholder to document the current inventory management workflow, pain points, supplier relationships, seasonal patterns, and technology stack. Capture the number of active SKUs, number of locations, sales channels (in-store, online, marketplace), average daily transaction volume, and current reorder frequency. Use this information to confirm the recommended platform selection (Cin7 Core, Lightspeed, or Zoho Inventory) and identify all required integrations.
Use a standardized discovery questionnaire. Key decision factors: (1) If client sells on Amazon/eBay/Shopify AND in-store → Cin7 Core. (2) If client is primarily brick-and-mortar with 1–3 locations → Lightspeed Core. (3) If client is budget-sensitive and/or uses Zoho Books → Zoho Inventory Professional. Document everything in a shared project folder — this becomes the basis for the Statement of Work.
Step 2: SKU Data Audit and Cleanup
Export the client's current product catalog from their existing POS or spreadsheets. Audit for: duplicate SKUs, missing SKUs, inconsistent naming, missing supplier assignments, missing cost prices, missing barcode/UPC values, and inactive products still in the catalog. Create a clean master SKU spreadsheet in CSV format with columns: SKU, Product Name, Category, Barcode/UPC, Primary Supplier, Supplier SKU, Unit Cost, Retail Price, Unit of Measure, Current Stock on Hand, Reorder Point, Reorder Quantity, Lead Time Days.
# pip install squareup
from square.client import Client
client = Client(access_token='YOUR_ACCESS_TOKEN', environment='production')
result = client.catalog.list_catalog(types='ITEM')import pandas as pd
df = pd.read_csv('sku_master.csv')
duplicates = df[df.duplicated(subset='SKU', keep=False)]
print(f'Found {len(duplicates)} duplicate SKU entries')
duplicates.to_csv('duplicate_skus.csv', index=False)This is typically the most time-consuming step. Budget 8–20 hours depending on catalog size. If the client has no existing digital catalog, this becomes a data entry project. Consider hiring a temporary data entry resource. Every SKU MUST have a primary supplier assigned or the auto-reorder system cannot generate a PO.
Step 3: Calculate Reorder Points and Quantities
For each SKU, calculate the reorder point using the formula: Reorder Point = (Average Daily Sales × Supplier Lead Time in Days) + Safety Stock. Safety Stock is typically calculated as: Safety Stock = Z-score × Standard Deviation of Daily Demand × √(Lead Time Days). For simplicity in initial deployment, use Safety Stock = 50% of (Average Daily Sales × Lead Time Days) as a starting heuristic. Reorder Quantity should be set to the supplier's minimum order quantity or the Economic Order Quantity (EOQ), whichever is greater.
# Input CSV must have columns: SKU, Date, Quantity_Sold
import pandas as pd
import numpy as np
sales = pd.read_csv('sales_history.csv', parse_dates=['Date'])
skus = pd.read_csv('sku_master.csv')
# Calculate average daily sales and std dev per SKU
daily_sales = sales.groupby(['SKU', 'Date'])['Quantity_Sold'].sum().reset_index()
sku_stats = daily_sales.groupby('SKU')['Quantity_Sold'].agg(['mean', 'std']).reset_index()
sku_stats.columns = ['SKU', 'avg_daily_sales', 'std_daily_sales']
sku_stats['std_daily_sales'] = sku_stats['std_daily_sales'].fillna(0)
# Merge with SKU master to get lead times
merged = skus.merge(sku_stats, on='SKU', how='left')
merged['avg_daily_sales'] = merged['avg_daily_sales'].fillna(0)
# Calculate reorder point (Z=1.65 for 95% service level)
Z = 1.65
merged['safety_stock'] = Z * merged['std_daily_sales'] * np.sqrt(merged['Lead_Time_Days'])
merged['reorder_point'] = (merged['avg_daily_sales'] * merged['Lead_Time_Days']) + merged['safety_stock']
merged['reorder_point'] = merged['reorder_point'].apply(np.ceil).astype(int)
# Set minimum reorder point of 1 for any active SKU
merged.loc[merged['reorder_point'] < 1, 'reorder_point'] = 1
merged[['SKU', 'Product_Name', 'avg_daily_sales', 'Lead_Time_Days', 'safety_stock', 'reorder_point', 'Reorder_Quantity']].to_csv('reorder_points_calculated.csv', index=False)
print('Reorder points calculated and saved.')Use a minimum of 90 days of sales history for reliable averages. If the client has seasonal products, flag them separately — seasonal SKU reorder points should be adjusted quarterly. For new products without sales history, set a conservative reorder point based on the buyer's estimate and plan to revise after 30 days of sales data. The 95% service level (Z=1.65) is standard for retail; adjust to 99% (Z=2.33) for critical/high-margin items.
Step 4: Conduct Physical Inventory Count
Coordinate a full physical inventory count at each location. This establishes the accurate baseline that the entire auto-reorder system depends on. Use the Zebra TC22 mobile computer (once configured) or printed count sheets organized by department/aisle. Compare counted quantities to the system quantities, investigate and resolve all variances greater than 5%, and update the system with actual counts.
# Generate count sheets from SKU master
import pandas as pd
skus = pd.read_csv('sku_master.csv')
count_sheets = skus[['SKU', 'Product_Name', 'Category', 'Bin_Location', 'Expected_Qty']].copy()
count_sheets['Counted_Qty'] = ''
count_sheets['Variance'] = ''
count_sheets.sort_values(['Category', 'Bin_Location']).to_csv('physical_count_sheet.csv', index=False)
print('Count sheets generated. Print for manual counting or load onto TC22.')Schedule the count during off-hours or closed periods. For retailers with >2,000 SKUs, consider a rolling count approach over 3–5 days rather than a single full count. The MSP should provide 1–2 technicians to assist with scanning if using the TC22. This step is NON-NEGOTIABLE — auto-reorder with inaccurate starting quantities will generate incorrect POs and destroy client trust immediately.
Step 5: Provision and Configure the Inventory Management Platform
Create the client's account on the chosen platform (Cin7 Core, Lightspeed, or Zoho Inventory). Configure organizational settings: company name, locations/warehouses, tax settings, currency, default units of measure. Set up user accounts with appropriate role-based permissions (Admin for owner/manager, Standard for staff, Read-Only for accounting). Enable multi-factor authentication for all admin accounts.
# Cin7 Core API: Create a new location (example)
curl -X POST 'https://api.cin7.com/api/v1/Branches' \
-H 'Authorization: Bearer YOUR_API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"branchName": "Main Store",
"isActive": true,
"address1": "123 Main St",
"city": "Springfield",
"state": "IL",
"postCode": "62701",
"country": "US"
}'
# Zoho Inventory API: Get organization details to verify setup
curl -X GET 'https://www.zohoapis.com/inventory/v1/organizations' \
-H 'Authorization: Zoho-oauthtoken YOUR_TOKEN'If using Lightspeed, create the account through the Lightspeed Partner Portal to earn referral credit. For Cin7, request a partner demo account first to build the configuration before migrating to the client's production account. Enable audit logging from day one — this is essential for SOX compliance if the client is publicly traded, and good practice regardless.
Step 6: Import SKU Master Data and Supplier Records
Import the cleaned SKU master CSV (from Step 2) into the inventory platform. Then create all supplier/vendor records with contact information, payment terms, lead times, and currency. Link each product to its primary supplier. After import, verify a random sample of 50 SKUs to confirm data integrity: correct pricing, supplier assignment, barcode, and category.
curl -X POST 'https://api.cin7.com/api/v1/Products' \
-H 'Authorization: Bearer YOUR_API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"code": "SKU-12345",
"name": "Widget Blue Large",
"category": "Widgets",
"barcode": "012345678901",
"supplierName": "Acme Suppliers",
"supplierCode": "ACM-WBL-001",
"costPrice": 12.50,
"retailPrice": 29.99,
"stockControl": true,
"minBeforeReorder": 25,
"reorderQty": 100
}'Most platforms have CSV import wizards accessible from the web UI — use those for initial bulk import. API-based import is better for ongoing sync or very large catalogs (>5,000 SKUs). CRITICAL: After import, immediately update stock quantities with the physical count data from Step 4. Do NOT rely on imported quantities from the old system.
Step 7: Configure Reorder Points and Auto-PO Rules
For each SKU, set the Reorder Point (minimum stock level that triggers a PO) and Reorder Quantity (how many units to order). Configure the platform's auto-reorder feature: in Cin7 Core, navigate to Purchasing > Auto Purchase Order settings; in Lightspeed, set reorder points per product under Inventory settings; in Zoho Inventory, enable automatic reorder under Settings > Preferences > Items. Set up PO approval workflows if required (e.g., POs over $5,000 require manager approval). Configure PO templates with the client's logo, payment terms, shipping address, and standard T&Cs.
curl -X PUT 'https://api.cin7.com/api/v1/Products/SKU-12345' \
-H 'Authorization: Bearer YOUR_API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"minBeforeReorder": 25,
"reorderQty": 100
}'curl -X PUT 'https://www.zohoapis.com/inventory/v1/items/ITEM_ID' \
-H 'Authorization: Zoho-oauthtoken YOUR_TOKEN' \
-H 'Content-Type: application/json' \
-d '{
"reorder_level": 25,
"reorder_quantity": 100
}'import pandas as pd
import requests
reorder_data = pd.read_csv('reorder_points_calculated.csv')
API_KEY = 'YOUR_API_KEY'
BASE_URL = 'https://api.cin7.com/api/v1/Products'
for _, row in reorder_data.iterrows():
payload = {
'code': row['SKU'],
'minBeforeReorder': int(row['reorder_point']),
'reorderQty': int(row['Reorder_Quantity'])
}
resp = requests.put(f"{BASE_URL}/{row['SKU']}", json=payload, headers={'Authorization': f'Bearer {API_KEY}'})
print(f"SKU {row['SKU']}: {resp.status_code}")Start with conservative (higher) reorder points for the first 30 days to avoid stockouts during the tuning period. It is better to slightly overstock initially than to have the new system cause a stockout. Schedule a reorder point review at Day 30, Day 60, and Day 90 post-go-live. For seasonal retailers, plan a major reorder point adjustment 6–8 weeks before each peak season.
Step 8: Integrate POS System with Inventory Platform
Connect the client's POS system to the inventory management platform so that every sale automatically decrements stock levels in real-time. For same-vendor stacks (e.g., Lightspeed POS + Lightspeed Inventory), this is native and requires only enabling the sync. For cross-platform setups (e.g., Square POS + Cin7 Core), configure the native integration connector or use middleware (Zapier/n8n). Verify that a test sale on the POS immediately reduces the corresponding SKU quantity in the inventory platform.
Real-time sync is critical. Even a 15-minute delay can cause issues in high-volume retail. If using Zapier, be aware of the task limit (750/month on Professional plan); each line item in each transaction consumes one task. For high-volume stores (>50 transactions/day), n8n self-hosted or native integrations are more cost-effective. Test with 10 actual sales transactions and verify inventory decrements match exactly.
Step 9: Integrate Accounting System
Connect the inventory platform to the client's accounting system (QuickBooks Online, Xero, or Zoho Books) so that approved purchase orders automatically sync as Bills/Purchase Orders in the accounting system. This ensures accounts payable is updated, COGS tracking is accurate, and the bookkeeper/accountant has visibility into purchasing activity. Configure the chart of accounts mapping: inventory asset account, COGS account, and AP account.
IMPORTANT: Coordinate this step with the client's bookkeeper or CPA. They must verify the chart of accounts mapping is correct before enabling the sync. An incorrect mapping can create significant accounting issues. Run the first week of PO syncs in a 'review before posting' mode if the platform supports it. Verify that tax-exempt resale purchases are handled correctly (no sales tax applied on POs for inventory purchased for resale).
Step 10: Configure Supplier PO Delivery
Set up the automated delivery mechanism for purchase orders to reach suppliers. Most platforms support emailing POs as PDF attachments directly to the supplier's email address. Configure the email template with the client's branding, standard terms and conditions, and shipping instructions. For larger suppliers that require EDI (Electronic Data Interchange), configure EDI 850 (Purchase Order) document exchange if the platform supports it (Cin7 Advanced plan). Test PO delivery with each supplier.
# Cin7 Core email template subject line:
Purchase Order #{po_number} from {company_name}Before sending automated POs, notify each supplier that the client is transitioning to automated purchasing. Provide the supplier with a sample PO so they know what to expect. Some suppliers may need to whitelist the sending email address. For suppliers that prefer phone orders or fax, configure the system to generate the PO but flag it for manual transmission rather than auto-emailing. Include standard UCC Article 2 terms on the PO template: pricing acceptance, delivery timeline, quality standards, and return/rejection policy.
Step 11: Configure Hardware Devices
Set up and configure all barcode scanning hardware and the label printer. Install the Zebra TC22 mobile computer with the inventory platform's mobile app (Cin7 Go, Lightspeed Scanner, or Zoho Inventory mobile app). Pair the Honeywell Voyager 1250g scanners with POS workstations via USB. Install the Zebra ZD421 label printer on the client's network and configure it with the appropriate label template for product barcode labels (Code 128, UPC-A, or EAN-13 format depending on the client's barcode standard).
Zebra ZD421 Printer Setup via Zebra Setup Utilities
Configure Network Printing (Ethernet Model)
Zebra TC22 Setup
Honeywell Voyager 1250g Setup
For the Zebra TC22, enable Zebra's StageNow or Android Enterprise enrollment if the MSP manages mobile devices via MDM. Lock down the device to kiosk mode or a managed profile so retail staff cannot install unauthorized apps. Set the TC22 to auto-connect to the store's Wi-Fi and configure a static DHCP lease for reliable connectivity. Keep the Honeywell scanner's Quick Start Guide on-site — it contains programming barcodes for switching between USB HID, RS-232, and other modes if troubleshooting is needed.
Step 12: Configure Monitoring, Alerts, and Dashboards
Set up monitoring dashboards and alert notifications so the client and the MSP are aware of system health and purchasing activity. Configure email/SMS alerts for: PO auto-generated (sent to store manager), PO requiring approval (sent to approver), inventory sync failure (sent to MSP), and weekly inventory value summary (sent to owner/CFO). Set up a dashboard showing: items below reorder point, open POs awaiting delivery, POs received this week, and inventory turnover by category.
For MSP monitoring via n8n (if deployed), create a daily health-check workflow using the following steps and configuration:
GET /api/v1/Products?filter=stockLowThe MSP monitoring email should go to a shared mailbox or ticketing system so alerts are tracked and actioned. Set up a simple uptime monitor (e.g., UptimeRobot free plan or Datadog) to ping the inventory platform's API endpoint every 5 minutes and alert the MSP if it goes down. For the client-facing dashboard, keep it simple — store managers need actionable information, not data overload.
Step 13: Parallel Testing Period (1 Week)
Run the automated system in parallel with the client's existing manual ordering process for one full week. During this period, the auto-reorder system generates POs but they are held in 'Draft' status and NOT sent to suppliers. The store manager continues to place orders manually as usual. At the end of the week, compare every auto-generated draft PO against the manual orders that were placed. Analyze discrepancies: did the system miss any orders? Did it generate unnecessary orders? Were quantities appropriate? Adjust reorder points and quantities based on findings.
import pandas as pd
auto_pos = pd.read_csv('auto_draft_pos.csv')
manual_pos = pd.read_csv('manual_orders_this_week.csv')
# Compare by supplier and SKU
comparison = auto_pos.merge(manual_pos, on=['Supplier', 'SKU'], how='outer', suffixes=('_auto', '_manual'))
comparison['qty_diff'] = comparison['Quantity_auto'].fillna(0) - comparison['Quantity_manual'].fillna(0)
discrepancies = comparison[comparison['qty_diff'] != 0]
discrepancies.to_csv('parallel_test_discrepancies.csv', index=False)
print(f'Found {len(discrepancies)} discrepancies to review')This parallel period is essential for building client confidence. Walk through every discrepancy with the store manager. Common issues: (1) Reorder points too low for fast-moving items, (2) Reorder points too high for slow-moving items, (3) Supplier lead times were estimated incorrectly, (4) Items with irregular demand patterns (e.g., event-driven). Adjust and re-run for a second parallel week if discrepancy rate exceeds 15%.
Step 14: Go-Live: Enable Auto-PO Generation and Supplier Delivery
After successful parallel testing and client sign-off, switch the auto-reorder system to live mode. Change PO generation from Draft to Active/Approved status. Enable automatic email delivery of POs to suppliers. Disable the parallel manual ordering process. Monitor closely for the first 48 hours, checking every auto-generated PO before it is sent. After 48 hours of clean operation, reduce monitoring to twice daily. After one week of clean operation, reduce to daily spot-checks.
Have the MSP's project lead available via phone/chat for the first 48 hours after go-live. Common go-live issues: (1) Email delivery to supplier lands in spam — have supplier whitelist the address, (2) A sudden large sale pushes multiple SKUs below threshold simultaneously, generating many POs at once — this is normal but may surprise the client, (3) PO quantities may need rounding to case-pack sizes — adjust reorder quantities to match supplier pack sizes. Keep the manual ordering backup process documented but discourage its use after go-live.
Custom AI Components
Reorder Point Calculator
Type: skill A Python-based calculation module that ingests historical sales data and supplier lead times to compute optimal reorder points and safety stock levels for each SKU. Uses statistical methods (average demand, standard deviation, configurable service level Z-score) rather than ML, making it deterministic and auditable. Outputs a CSV file ready for bulk import into the inventory platform. Designed to be re-run quarterly or before seasonal peaks to recalibrate thresholds.
Implementation:
#!/usr/bin/env python3
"""
Reorder Point Calculator for Retail SKU Inventory
Usage: python reorder_calculator.py --sales sales_history.csv --skus sku_master.csv --output reorder_points.csv --service-level 0.95
"""
import argparse
import pandas as pd
import numpy as np
from scipy import stats
import logging
from datetime import datetime, timedelta
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
def calculate_reorder_points(sales_file: str, sku_file: str, output_file: str, service_level: float = 0.95, lookback_days: int = 90):
"""Calculate reorder points for all SKUs based on sales history."""
# Load data
logger.info(f'Loading sales history from {sales_file}')
sales = pd.read_csv(sales_file, parse_dates=['date'])
sales.columns = [c.lower().strip() for c in sales.columns]
logger.info(f'Loading SKU master from {sku_file}')
skus = pd.read_csv(sku_file)
skus.columns = [c.lower().strip() for c in skus.columns]
# Filter to lookback period
cutoff_date = datetime.now() - timedelta(days=lookback_days)
sales_filtered = sales[sales['date'] >= cutoff_date].copy()
logger.info(f'Using {len(sales_filtered)} sales records from last {lookback_days} days')
# Calculate daily demand per SKU
daily_demand = sales_filtered.groupby(['sku', 'date'])['quantity_sold'].sum().reset_index()
# For days with zero sales, we need to fill those in
date_range = pd.date_range(start=cutoff_date, end=datetime.now(), freq='D')
all_skus = sales_filtered['sku'].unique()
full_index = pd.MultiIndex.from_product([all_skus, date_range], names=['sku', 'date'])
daily_demand_full = daily_demand.set_index(['sku', 'date']).reindex(full_index, fill_value=0).reset_index()
# Aggregate statistics per SKU
sku_stats = daily_demand_full.groupby('sku')['quantity_sold'].agg(
avg_daily_demand='mean',
std_daily_demand='std',
max_daily_demand='max',
total_sold='sum',
days_with_sales=lambda x: (x > 0).sum()
).reset_index()
sku_stats['std_daily_demand'] = sku_stats['std_daily_demand'].fillna(0)
# Merge with SKU master
result = skus.merge(sku_stats, on='sku', how='left')
result['avg_daily_demand'] = result['avg_daily_demand'].fillna(0)
result['std_daily_demand'] = result['std_daily_demand'].fillna(0)
# Z-score for desired service level
z_score = stats.norm.ppf(service_level)
logger.info(f'Service level: {service_level*100}% → Z-score: {z_score:.4f}')
# Calculate safety stock
# Safety Stock = Z * σ_demand * √(Lead Time)
result['lead_time_days'] = result['lead_time_days'].fillna(7) # Default 7 days if not specified
result['safety_stock'] = z_score * result['std_daily_demand'] * np.sqrt(result['lead_time_days'])
result['safety_stock'] = result['safety_stock'].apply(np.ceil).astype(int)
# Calculate reorder point
# Reorder Point = (Avg Daily Demand * Lead Time) + Safety Stock
result['reorder_point'] = (result['avg_daily_demand'] * result['lead_time_days']) + result['safety_stock']
result['reorder_point'] = result['reorder_point'].apply(np.ceil).astype(int)
# Set minimum reorder point of 1 for any active SKU with sales
active_mask = result['total_sold'] > 0
result.loc[active_mask & (result['reorder_point'] < 1), 'reorder_point'] = 1
# Calculate reorder quantity (EOQ or supplier minimum, whichever is greater)
# EOQ = √((2 * Annual Demand * Ordering Cost) / Holding Cost per Unit)
# Simplified: default to 2 weeks of supply or supplier minimum order qty
result['annual_demand'] = result['avg_daily_demand'] * 365
ordering_cost = 25 # Estimated cost per PO ($25 in labor/overhead)
holding_cost_pct = 0.25 # 25% of unit cost per year
result['unit_cost'] = result.get('unit_cost', pd.Series([10.0] * len(result))) # Default $10 if missing
result['holding_cost'] = result['unit_cost'] * holding_cost_pct
result['holding_cost'] = result['holding_cost'].replace(0, 0.01) # Avoid division by zero
result['eoq'] = np.sqrt((2 * result['annual_demand'] * ordering_cost) / result['holding_cost'])
result['eoq'] = result['eoq'].apply(np.ceil).astype(int)
result['eoq'] = result['eoq'].clip(lower=1)
# Use max of EOQ and supplier minimum order quantity
if 'min_order_qty' in result.columns:
result['reorder_quantity'] = result[['eoq', 'min_order_qty']].max(axis=1)
else:
result['reorder_quantity'] = result['eoq']
# Classification: A/B/C based on revenue contribution
result['revenue_contribution'] = result['total_sold'] * result.get('retail_price', result['unit_cost'] * 2)
result = result.sort_values('revenue_contribution', ascending=False)
result['cumulative_pct'] = result['revenue_contribution'].cumsum() / result['revenue_contribution'].sum()
result['abc_class'] = 'C'
result.loc[result['cumulative_pct'] <= 0.80, 'abc_class'] = 'A'
result.loc[(result['cumulative_pct'] > 0.80) & (result['cumulative_pct'] <= 0.95), 'abc_class'] = 'B'
# Output columns
output_cols = ['sku', 'product_name', 'primary_supplier', 'abc_class',
'avg_daily_demand', 'std_daily_demand', 'lead_time_days',
'safety_stock', 'reorder_point', 'reorder_quantity',
'total_sold', 'days_with_sales']
output_cols = [c for c in output_cols if c in result.columns]
result[output_cols].to_csv(output_file, index=False)
# Summary statistics
logger.info(f'\n=== REORDER POINT CALCULATION SUMMARY ===')
logger.info(f'Total SKUs processed: {len(result)}')
logger.info(f'Active SKUs (with sales): {active_mask.sum()}')
logger.info(f'Inactive SKUs (zero sales in {lookback_days} days): {(~active_mask).sum()}')
logger.info(f'A-class SKUs (80% of revenue): {(result["abc_class"]=="A").sum()}')
logger.info(f'B-class SKUs (next 15%): {(result["abc_class"]=="B").sum()}')
logger.info(f'C-class SKUs (last 5%): {(result["abc_class"]=="C").sum()}')
logger.info(f'Average reorder point: {result["reorder_point"].mean():.1f} units')
logger.info(f'Output saved to {output_file}')
return result
if __name__ == '__main__':
parser = argparse.ArgumentParser(description='Calculate reorder points for retail SKU inventory')
parser.add_argument('--sales', required=True, help='Path to sales history CSV (columns: sku, date, quantity_sold)')
parser.add_argument('--skus', required=True, help='Path to SKU master CSV (columns: sku, product_name, primary_supplier, lead_time_days, unit_cost)')
parser.add_argument('--output', default='reorder_points.csv', help='Output CSV path')
parser.add_argument('--service-level', type=float, default=0.95, help='Desired service level (0.0-1.0, default 0.95)')
parser.add_argument('--lookback-days', type=int, default=90, help='Days of sales history to analyze (default 90)')
args = parser.parse_args()
calculate_reorder_points(args.sales, args.skus, args.output, args.service_level, args.lookback_days)Inventory Threshold Monitor and PO Generator Workflow
Type: workflow
An n8n workflow that polls the inventory management platform's API on a configurable schedule (default: every 30 minutes), identifies SKUs whose stock-on-hand has fallen below their reorder point, groups those SKUs by supplier, generates a consolidated purchase order per supplier, submits the PO to the inventory platform via API, and sends an email notification to the store manager. This workflow is a fallback/supplement for platforms that lack robust native auto-reorder, or for custom integration scenarios where the MSP needs to bridge disparate systems.
Implementation
{
"name": "Inventory Auto-Reorder PO Generator",
"nodes": [
{
"parameters": {
"rule": {
"interval": [
{
"field": "minutes",
"minutesInterval": 30
}
]
}
},
"id": "schedule-trigger",
"name": "Every 30 Minutes",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [250, 300],
"typeVersion": 1.1
},
{
"parameters": {
"method": "GET",
"url": "={{$env.INVENTORY_API_URL}}/api/v1/Products",
"authentication": "genericCredentialType",
"genericAuthType": "httpHeaderAuth",
"options": {
"response": {
"response": {
"responseFormat": "json"
}
}
},
"queryParameters": {
"parameters": [
{ "name": "filter", "value": "stockLow" },
{ "name": "pageSize", "value": "500" }
]
}
},
"id": "get-low-stock",
"name": "Fetch Low Stock SKUs",
"type": "n8n-nodes-base.httpRequest",
"position": [470, 300],
"typeVersion": 4.1
},
{
"parameters": {
"jsCode": "// Filter to SKUs below reorder point that don't already have an open PO\nconst items = $input.all();\nconst lowStockItems = [];\n\nfor (const item of items) {\n const data = item.json;\n // Check if stock is below reorder point\n if (data.stockOnHand !== undefined && data.minBeforeReorder !== undefined) {\n if (data.stockOnHand <= data.minBeforeReorder && data.stockOnHand >= 0) {\n // Check for existing open PO (flag set by dedup node or API check)\n if (!data.hasOpenPO) {\n lowStockItems.push({\n json: {\n sku: data.code || data.sku,\n productName: data.name || data.productName,\n stockOnHand: data.stockOnHand,\n reorderPoint: data.minBeforeReorder,\n reorderQty: data.reorderQty || Math.max(data.minBeforeReorder * 2, 10),\n unitCost: data.costPrice || data.unitCost || 0,\n supplier: data.supplierName || data.primarySupplier || 'UNKNOWN',\n supplierEmail: data.supplierEmail || '',\n barcode: data.barcode || ''\n }\n });\n }\n }\n }\n}\n\nif (lowStockItems.length === 0) {\n return [{ json: { noReorderNeeded: true, message: 'All SKUs above reorder point' } }];\n}\n\nreturn lowStockItems;"
},
"id": "filter-and-process",
"name": "Filter Below Reorder Point",
"type": "n8n-nodes-base.code",
"position": [690, 300],
"typeVersion": 2
},
{
"parameters": {
"conditions": {
"options": { "caseSensitive": true, "leftValue": "", "typeValidation": "strict" },
"conditions": [
{
"id": "check-reorder-needed",
"leftValue": "={{ $json.noReorderNeeded }}",
"rightValue": true,
"operator": { "type": "boolean", "operation": "notEqual" }
}
],
"combinator": "and"
}
},
"id": "if-reorder-needed",
"name": "Reorder Needed?",
"type": "n8n-nodes-base.if",
"position": [910, 300],
"typeVersion": 2
},
{
"parameters": {
"jsCode": "// Group items by supplier to create consolidated POs\nconst items = $input.all();\nconst supplierGroups = {};\n\nfor (const item of items) {\n const supplier = item.json.supplier;\n if (!supplierGroups[supplier]) {\n supplierGroups[supplier] = {\n supplier: supplier,\n supplierEmail: item.json.supplierEmail,\n lineItems: [],\n totalCost: 0\n };\n }\n const lineCost = item.json.reorderQty * item.json.unitCost;\n supplierGroups[supplier].lineItems.push({\n sku: item.json.sku,\n productName: item.json.productName,\n quantity: item.json.reorderQty,\n unitCost: item.json.unitCost,\n lineCost: lineCost,\n currentStock: item.json.stockOnHand,\n reorderPoint: item.json.reorderPoint\n });\n supplierGroups[supplier].totalCost += lineCost;\n}\n\nconst output = Object.values(supplierGroups).map(group => ({\n json: {\n supplier: group.supplier,\n supplierEmail: group.supplierEmail,\n lineItemCount: group.lineItems.length,\n totalCost: Math.round(group.totalCost * 100) / 100,\n lineItems: group.lineItems,\n poDate: new Date().toISOString().split('T')[0],\n poNumber: `AUTO-${group.supplier.substring(0,3).toUpperCase()}-${Date.now()}`\n }\n}));\n\nreturn output;"
},
"id": "group-by-supplier",
"name": "Group by Supplier",
"type": "n8n-nodes-base.code",
"position": [1130, 200],
"typeVersion": 2
},
{
"parameters": {
"method": "POST",
"url": "={{$env.INVENTORY_API_URL}}/api/v1/PurchaseOrders",
"authentication": "genericCredentialType",
"genericAuthType": "httpHeaderAuth",
"sendBody": true,
"bodyParameters": {
"parameters": [
{ "name": "supplierName", "value": "={{ $json.supplier }}" },
{ "name": "poNumber", "value": "={{ $json.poNumber }}" },
{ "name": "status", "value": "Draft" },
{ "name": "lineItems", "value": "={{ JSON.stringify($json.lineItems) }}" },
{ "name": "notes", "value": "Auto-generated by inventory threshold monitor" }
]
}
},
"id": "create-po",
"name": "Create PO in Platform",
"type": "n8n-nodes-base.httpRequest",
"position": [1350, 200],
"typeVersion": 4.1
},
{
"parameters": {
"fromEmail": "={{$env.NOTIFICATION_FROM_EMAIL}}",
"toEmail": "={{$env.STORE_MANAGER_EMAIL}}",
"subject": "Auto-Reorder PO Generated: {{ $json.supplier }} - {{ $json.poNumber }}",
"emailType": "html",
"html": "<h2>Automated Purchase Order Generated</h2><p><strong>PO Number:</strong> {{ $json.poNumber }}<br><strong>Supplier:</strong> {{ $json.supplier }}<br><strong>Date:</strong> {{ $json.poDate }}<br><strong>Line Items:</strong> {{ $json.lineItemCount }}<br><strong>Total Estimated Cost:</strong> ${{ $json.totalCost }}</p><h3>Line Items:</h3><table border='1' cellpadding='5'><tr><th>SKU</th><th>Product</th><th>Current Stock</th><th>Reorder Point</th><th>Order Qty</th><th>Unit Cost</th><th>Line Total</th></tr>{{#each $json.lineItems}}<tr><td>{{this.sku}}</td><td>{{this.productName}}</td><td>{{this.currentStock}}</td><td>{{this.reorderPoint}}</td><td>{{this.quantity}}</td><td>${{this.unitCost}}</td><td>${{this.lineCost}}</td></tr>{{/each}}</table><p><em>Please review and approve this PO in your inventory management system.</em></p>"
},
"id": "notify-manager",
"name": "Email Store Manager",
"type": "n8n-nodes-base.emailSend",
"position": [1570, 200],
"typeVersion": 2.1
},
{
"parameters": {
"jsCode": "// Log the execution for audit trail\nconst timestamp = new Date().toISOString();\nconst summary = {\n executionTime: timestamp,\n message: 'No reorder needed - all SKUs above threshold',\n nextCheck: 'In 30 minutes'\n};\nconsole.log(JSON.stringify(summary));\nreturn [{ json: summary }];"
},
"id": "log-no-action",
"name": "Log No Action Needed",
"type": "n8n-nodes-base.code",
"position": [1130, 400],
"typeVersion": 2
}
],
"connections": {
"Every 30 Minutes": { "main": [[{ "node": "Fetch Low Stock SKUs", "type": "main", "index": 0 }]] },
"Fetch Low Stock SKUs": { "main": [[{ "node": "Filter Below Reorder Point", "type": "main", "index": 0 }]] },
"Filter Below Reorder Point": { "main": [[{ "node": "Reorder Needed?", "type": "main", "index": 0 }]] },
"Reorder Needed?": {
"main": [
[{ "node": "Group by Supplier", "type": "main", "index": 0 }],
[{ "node": "Log No Action Needed", "type": "main", "index": 0 }]
]
},
"Group by Supplier": { "main": [[{ "node": "Create PO in Platform", "type": "main", "index": 0 }]] },
"Create PO in Platform": { "main": [[{ "node": "Email Store Manager", "type": "main", "index": 0 }]] }
},
"settings": {
"executionOrder": "v1",
"saveManualExecutions": true,
"callerPolicy": "workflowsFromSameOwner"
}
}Environment Variables Required
INVENTORY_API_URL: Base URL of the inventory platform API (e.g.,https://api.cin7.com)INVENTORY_API_KEY: API authentication keyNOTIFICATION_FROM_EMAIL: Email address for notification senderSTORE_MANAGER_EMAIL: Store manager's email for PO notifications
Deployment Notes
Deploy on a Linux VPS (Ubuntu 22.04 LTS recommended) with Docker.
docker run -d --name n8n -p 5678:5678 -e N8N_BASIC_AUTH_ACTIVE=true -e N8N_BASIC_AUTH_USER=admin -e N8N_BASIC_AUTH_PASSWORD=<STRONG_PASSWORD> -v n8n_data:/home/node/.n8n n8nio/n8nDuplicate PO Prevention Check
Type: integration A middleware component that prevents the auto-reorder system from generating duplicate purchase orders for the same SKU when a PO is already open and awaiting delivery. This is critical because without this check, every 30-minute polling cycle could generate a new PO for the same below-threshold SKU. The component queries the inventory platform for open/pending POs before generating new ones, and maintains a local deduplication cache.
Implementation
# Flask microservice for pre-check before PO generation
#!/usr/bin/env python3
"""
Duplicate PO Prevention Service
Runs as a pre-check before PO generation. Can be called as an API endpoint
or imported as a module by the n8n workflow via HTTP Request node.
Deploy as a Flask microservice alongside n8n, or integrate logic directly
into the n8n Code node.
"""
import os
import json
import time
import logging
from datetime import datetime, timedelta
from typing import Dict, List, Set
import requests
from flask import Flask, jsonify, request
app = Flask(__name__)
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
# Configuration
INVENTORY_API_URL = os.environ.get('INVENTORY_API_URL', 'https://api.cin7.com')
INVENTORY_API_KEY = os.environ.get('INVENTORY_API_KEY', '')
OPEN_PO_CACHE_TTL = 300 # Cache open POs for 5 minutes
# In-memory cache for open PO SKUs
_cache = {
'open_po_skus': set(),
'last_refresh': 0
}
def get_open_po_skus() -> Set[str]:
"""Fetch all SKUs that appear on currently open/pending purchase orders."""
now = time.time()
if now - _cache['last_refresh'] < OPEN_PO_CACHE_TTL:
return _cache['open_po_skus']
logger.info('Refreshing open PO cache from inventory platform...')
headers = {'Authorization': f'Bearer {INVENTORY_API_KEY}'}
open_skus = set()
page = 1
while True:
resp = requests.get(
f'{INVENTORY_API_URL}/api/v1/PurchaseOrders',
headers=headers,
params={
'status': 'Draft,Pending,Approved,Ordered', # All non-received statuses
'page': page,
'pageSize': 100
},
timeout=30
)
resp.raise_for_status()
data = resp.json()
orders = data if isinstance(data, list) else data.get('data', data.get('purchaseOrders', []))
if not orders:
break
for po in orders:
line_items = po.get('lineItems', po.get('lines', []))
for line in line_items:
sku = line.get('code', line.get('sku', line.get('itemCode', '')))
if sku:
open_skus.add(sku)
page += 1
if len(orders) < 100:
break
_cache['open_po_skus'] = open_skus
_cache['last_refresh'] = now
logger.info(f'Cached {len(open_skus)} SKUs with open POs')
return open_skus
def filter_already_ordered(low_stock_skus: List[Dict]) -> List[Dict]:
"""Remove SKUs that already have open POs from the reorder list."""
open_skus = get_open_po_skus()
filtered = []
skipped = []
for item in low_stock_skus:
sku = item.get('sku', item.get('code', ''))
if sku in open_skus:
skipped.append(sku)
else:
filtered.append(item)
if skipped:
logger.info(f'Skipped {len(skipped)} SKUs with existing open POs: {skipped[:10]}...')
logger.info(f'Passing {len(filtered)} SKUs for PO generation')
return filtered
@app.route('/api/filter-reorder', methods=['POST'])
def api_filter_reorder():
"""API endpoint for n8n HTTP Request node to call.
Request body: {"items": [{"sku": "ABC", "quantity": 10, ...}, ...]}
Response: {"items": [...filtered list...], "skipped_count": N}
"""
data = request.get_json()
items = data.get('items', [])
filtered = filter_already_ordered(items)
return jsonify({
'items': filtered,
'total_submitted': len(items),
'passed_filter': len(filtered),
'skipped_count': len(items) - len(filtered),
'timestamp': datetime.utcnow().isoformat()
})
@app.route('/api/cache/refresh', methods=['POST'])
def api_refresh_cache():
"""Force refresh the open PO cache."""
_cache['last_refresh'] = 0
skus = get_open_po_skus()
return jsonify({'cached_skus': len(skus), 'status': 'refreshed'})
@app.route('/api/health', methods=['GET'])
def health_check():
return jsonify({'status': 'healthy', 'cache_age_seconds': time.time() - _cache['last_refresh']})
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5050, debug=False)FROM python:3.11-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY dedup_service.py .
EXPOSE 5050
CMD ["gunicorn", "--bind", "0.0.0.0:5050", "--workers", "2", "dedup_service:app"]flask==3.0.*
gunicorn==21.*
requests==2.31.*Quarterly Reorder Point Optimization Report
Type: workflow
A scheduled workflow that runs on the first Monday of each quarter (or on-demand) to re-analyze sales data, recalculate optimal reorder points, compare them against current thresholds, and generate a report highlighting SKUs where reorder points should be adjusted up or down. This report is emailed to the MSP account manager and the client's store manager as a PDF-formatted HTML email, driving the quarterly business review conversation and demonstrating ongoing MSP value.
Implementation:
#!/usr/bin/env python3
"""
Quarterly Reorder Point Optimization Report Generator
Schedule via cron: 0 8 1-7 1,4,7,10 1 (First Monday of Jan, Apr, Jul, Oct at 8 AM)
Or trigger manually: python quarterly_report.py --sales sales.csv --skus skus.csv --email manager@store.com
"""
import argparse
import pandas as pd
import numpy as np
from scipy import stats
from datetime import datetime
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import os
import logging
logger = logging.getLogger(__name__)
logging.basicConfig(level=logging.INFO)
def generate_optimization_report(sales_file: str, sku_file: str, service_level: float = 0.95) -> str:
"""Analyze current vs optimal reorder points and generate HTML report."""
sales = pd.read_csv(sales_file, parse_dates=['date'])
sales.columns = [c.lower().strip() for c in sales.columns]
skus = pd.read_csv(sku_file)
skus.columns = [c.lower().strip() for c in skus.columns]
# Calculate optimal reorder points from last 90 days
cutoff = datetime.now() - pd.Timedelta(days=90)
recent_sales = sales[sales['date'] >= cutoff]
date_range = pd.date_range(start=cutoff, end=datetime.now(), freq='D')
all_skus = recent_sales['sku'].unique()
full_idx = pd.MultiIndex.from_product([all_skus, date_range], names=['sku', 'date'])
daily = recent_sales.groupby(['sku', 'date'])['quantity_sold'].sum()
daily = daily.reindex(full_idx, fill_value=0).reset_index()
daily.columns = ['sku', 'date', 'quantity_sold']
sku_stats = daily.groupby('sku')['quantity_sold'].agg(['mean', 'std']).reset_index()
sku_stats.columns = ['sku', 'avg_daily', 'std_daily']
sku_stats['std_daily'] = sku_stats['std_daily'].fillna(0)
merged = skus.merge(sku_stats, on='sku', how='left')
merged['avg_daily'] = merged['avg_daily'].fillna(0)
merged['std_daily'] = merged['std_daily'].fillna(0)
merged['lead_time_days'] = merged['lead_time_days'].fillna(7)
z = stats.norm.ppf(service_level)
merged['optimal_reorder_point'] = np.ceil(
(merged['avg_daily'] * merged['lead_time_days']) +
(z * merged['std_daily'] * np.sqrt(merged['lead_time_days']))
).astype(int)
merged['optimal_reorder_point'] = merged['optimal_reorder_point'].clip(lower=0)
# Compare current vs optimal
merged['current_reorder_point'] = merged.get('reorder_point', pd.Series([0]*len(merged)))
merged['difference'] = merged['optimal_reorder_point'] - merged['current_reorder_point']
merged['pct_change'] = np.where(
merged['current_reorder_point'] > 0,
(merged['difference'] / merged['current_reorder_point'] * 100).round(1),
0
)
# Flag significant changes (>20% difference)
needs_increase = merged[(merged['difference'] > 0) & (abs(merged['pct_change']) > 20)].sort_values('difference', ascending=False)
needs_decrease = merged[(merged['difference'] < 0) & (abs(merged['pct_change']) > 20)].sort_values('difference')
no_change = merged[abs(merged['pct_change']) <= 20]
# Build HTML report
report_date = datetime.now().strftime('%B %d, %Y')
quarter = f'Q{(datetime.now().month - 1) // 3 + 1} {datetime.now().year}'
html = f"""
<html><body style='font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto;'>
<h1 style='color: #2c3e50;'>Inventory Reorder Point Optimization Report</h1>
<p><strong>Report Date:</strong> {report_date} | <strong>Quarter:</strong> {quarter}</p>
<p><strong>Analysis Period:</strong> Last 90 days of sales data | <strong>Service Level:</strong> {service_level*100:.0f}%</p>
<h2 style='color: #27ae60;'>Summary</h2>
<table style='border-collapse: collapse; width: 100%;'>
<tr><td style='padding: 8px; border: 1px solid #ddd;'>Total Active SKUs Analyzed</td><td style='padding: 8px; border: 1px solid #ddd;'><strong>{len(merged)}</strong></td></tr>
<tr><td style='padding: 8px; border: 1px solid #ddd;'>SKUs Needing Increase (>20% change)</td><td style='padding: 8px; border: 1px solid #ddd; color: #e74c3c;'><strong>{len(needs_increase)}</strong></td></tr>
<tr><td style='padding: 8px; border: 1px solid #ddd;'>SKUs Needing Decrease (>20% change)</td><td style='padding: 8px; border: 1px solid #ddd; color: #f39c12;'><strong>{len(needs_decrease)}</strong></td></tr>
<tr><td style='padding: 8px; border: 1px solid #ddd;'>SKUs Within Acceptable Range</td><td style='padding: 8px; border: 1px solid #ddd; color: #27ae60;'><strong>{len(no_change)}</strong></td></tr>
</table>
<h2 style='color: #e74c3c;'>⬆️ Recommend Increasing Reorder Point ({len(needs_increase)} SKUs)</h2>
<p><em>These items are selling faster than expected. Increase reorder points to prevent stockouts.</em></p>
<table style='border-collapse: collapse; width: 100%;'>
<tr style='background: #ecf0f1;'><th style='padding: 6px; border: 1px solid #ddd;'>SKU</th><th style='padding: 6px; border: 1px solid #ddd;'>Product</th><th style='padding: 6px; border: 1px solid #ddd;'>Current RP</th><th style='padding: 6px; border: 1px solid #ddd;'>Optimal RP</th><th style='padding: 6px; border: 1px solid #ddd;'>Change</th></tr>
"""
for _, row in needs_increase.head(20).iterrows():
html += f"<tr><td style='padding: 6px; border: 1px solid #ddd;'>{row['sku']}</td><td style='padding: 6px; border: 1px solid #ddd;'>{row.get('product_name', 'N/A')}</td><td style='padding: 6px; border: 1px solid #ddd;'>{row['current_reorder_point']}</td><td style='padding: 6px; border: 1px solid #ddd;'><strong>{row['optimal_reorder_point']}</strong></td><td style='padding: 6px; border: 1px solid #ddd; color: #e74c3c;'>+{row['difference']} ({row['pct_change']:+.0f}%)</td></tr>"
html += """</table>
<h2 style='color: #f39c12;'>⬇️ Recommend Decreasing Reorder Point (""" + str(len(needs_decrease)) + """ SKUs)</h2>
<p><em>These items are selling slower than expected. Decrease reorder points to reduce excess inventory.</em></p>
<table style='border-collapse: collapse; width: 100%;'>
<tr style='background: #ecf0f1;'><th style='padding: 6px; border: 1px solid #ddd;'>SKU</th><th style='padding: 6px; border: 1px solid #ddd;'>Product</th><th style='padding: 6px; border: 1px solid #ddd;'>Current RP</th><th style='padding: 6px; border: 1px solid #ddd;'>Optimal RP</th><th style='padding: 6px; border: 1px solid #ddd;'>Change</th></tr>
"""
for _, row in needs_decrease.head(20).iterrows():
html += f"<tr><td style='padding: 6px; border: 1px solid #ddd;'>{row['sku']}</td><td style='padding: 6px; border: 1px solid #ddd;'>{row.get('product_name', 'N/A')}</td><td style='padding: 6px; border: 1px solid #ddd;'>{row['current_reorder_point']}</td><td style='padding: 6px; border: 1px solid #ddd;'><strong>{row['optimal_reorder_point']}</strong></td><td style='padding: 6px; border: 1px solid #ddd; color: #f39c12;'>{row['difference']} ({row['pct_change']:+.0f}%)</td></tr>"
html += """</table>
<h2>📋 Recommended Actions</h2>
<ol>
<li>Update reorder points for all flagged SKUs in the inventory platform</li>
<li>Review seasonal factors — are any changes due to seasonal demand shifts?</li>
<li>Verify supplier lead times are still accurate (contact top 5 suppliers)</li>
<li>Check for any new products that need initial reorder points set</li>
<li>Review dead stock — SKUs with zero sales in 90 days may need clearance</li>
</ol>
<p style='color: #7f8c8d; font-size: 12px;'>This report was auto-generated by the MSP Inventory Optimization Service. Contact your MSP account manager to schedule a review meeting.</p>
</body></html>
"""
return html
def send_report(html_content: str, recipient_emails: list, sender_email: str = None):
"""Send the HTML report via email using SMTP."""
smtp_host = os.environ.get('SMTP_HOST', 'smtp.gmail.com')
smtp_port = int(os.environ.get('SMTP_PORT', '587'))
smtp_user = os.environ.get('SMTP_USER', '')
smtp_pass = os.environ.get('SMTP_PASS', '')
sender = sender_email or smtp_user
msg = MIMEMultipart('alternative')
msg['Subject'] = f'Inventory Reorder Point Optimization Report - {datetime.now().strftime("%B %Y")}'
msg['From'] = sender
msg['To'] = ', '.join(recipient_emails)
msg.attach(MIMEText(html_content, 'html'))
with smtplib.SMTP(smtp_host, smtp_port) as server:
server.starttls()
server.login(smtp_user, smtp_pass)
server.sendmail(sender, recipient_emails, msg.as_string())
logger.info(f'Report sent to {recipient_emails}')
if __name__ == '__main__':
parser = argparse.ArgumentParser()
parser.add_argument('--sales', required=True)
parser.add_argument('--skus', required=True)
parser.add_argument('--email', nargs='+', required=True)
parser.add_argument('--service-level', type=float, default=0.95)
args = parser.parse_args()
report_html = generate_optimization_report(args.sales, args.skus, args.service_level)
send_report(report_html, args.email)
print('Quarterly optimization report generated and sent.')Crontab entry for quarterly execution:
# Run at 8:00 AM on the first Monday of January, April, July, October
0 8 1-7 1,4,7,10 1 cd /opt/inventory-automation && python quarterly_report.py --sales /data/sales_export.csv --skus /data/sku_master.csv --email manager@store.com msp-account-manager@msp.com >> /var/log/quarterly_report.log 2>&1Zapier Auto-Reorder Zap Configuration
Type: integration
A pre-built Zapier automation for clients who prefer a no-code approach or whose inventory platform has limited native auto-PO features. This Zap monitors inventory levels via scheduled API polling, identifies below-threshold SKUs, and creates purchase orders. Suitable for low-volume retailers (<50 transactions/day) where Zapier's task limits are not a concern.
Zapier Multi-Step Zap: Inventory Auto-Reorder
Zap Configuration
Zap Name: Auto-Reorder: Low Stock → Create PO → Notify Manager
Step 1: Trigger — Schedule by Zapier
- App: Schedule by Zapier
- Event: Every Hour
- Time: Every 1 hour (customize based on client volume)
Step 2: Action — Webhooks by Zapier (GET)
- App: Webhooks by Zapier
- Event: GET
- This retrieves all active items sorted by lowest stock first
GET https://api.zoho.com/inventory/v1/items?filter_by=Status.Active&sort_column=stock_on_hand&sort_order=ascending&organization_id={{org_id}}
Authorization: Zoho-oauthtoken {{access_token}}Step 3: Action — Code by Zapier (JavaScript)
- App: Code by Zapier
- Event: Run JavaScript
- Input Data —
items:{{Step 2 body}}
// filters low-stock items and groups them by vendor
const items = JSON.parse(inputData.items).items || [];
const lowStock = items.filter(item => {
const onHand = item.stock_on_hand || 0;
const reorderLevel = item.reorder_level || 0;
return reorderLevel > 0 && onHand <= reorderLevel;
});
if (lowStock.length === 0) {
output = [{needsReorder: 'false', message: 'All items above reorder level'}];
} else {
// Group by vendor
const byVendor = {};
lowStock.forEach(item => {
const vendor = item.vendor_name || 'Unknown';
if (!byVendor[vendor]) byVendor[vendor] = [];
byVendor[vendor].push({
item_id: item.item_id,
sku: item.sku,
name: item.name,
stock_on_hand: item.stock_on_hand,
reorder_level: item.reorder_level,
reorder_quantity: item.reorder_quantity || (item.reorder_level * 2)
});
});
output = [{
needsReorder: 'true',
vendorData: JSON.stringify(byVendor),
totalItems: lowStock.length.toString()
}];
}Step 4: Filter by Zapier
Only continue if needsReorder equals true
Step 5: Action — Webhooks by Zapier (POST)
- App: Webhooks by Zapier
- Event: POST
- Body: Construct PO JSON from Step 3 vendorData (use a Formatter step to parse if needed)
- Note: For multiple vendors, use Zapier's Looping feature (available on Professional plan)
POST https://api.zoho.com/inventory/v1/purchaseorders?organization_id={{org_id}}
Authorization: Zoho-oauthtoken {{access_token}}
Content-Type: application/jsonStep 6: Action — Gmail (or Email by Zapier)
- To: Store manager email
- Subject:
Auto-Reorder Alert: {{Step 3 totalItems}} items below threshold - Body:
The following items have been automatically reordered: {{Step 3 vendorData}}. Please review in your inventory system.
Setup Notes
- Requires Zapier Professional plan ($19.99/month annual) minimum for multi-step Zaps
- OAuth connection to Zoho Inventory must be configured in Zapier's connection settings
- Monitor task usage: each execution consumes 6 tasks; at hourly frequency = ~4,380 tasks/month (may need Team plan at $103.50/month if running 24/7)
- Alternative: Set Schedule trigger to every 4 hours during business hours only (6 AM - 10 PM) to reduce task consumption to ~1,095 tasks/month
- Test thoroughly with Zapier's test mode before enabling live execution
Testing & Validation
- THRESHOLD TRIGGER TEST: Manually reduce the stock-on-hand quantity of a test SKU (choose a slow-moving item) to exactly one unit above its reorder point. Sell one unit through the POS. Verify that within the platform's check cycle (immediate for native, up to 30 minutes for n8n polling), a Draft PO is generated for that SKU with the correct supplier, quantity, and unit cost.
- MULTI-SKU SUPPLIER CONSOLIDATION TEST: Reduce stock for 3 different SKUs from the same supplier below their reorder points. Verify that the system generates a single consolidated PO to that supplier containing all 3 line items, not 3 separate POs.
- PO ACCURACY TEST: For 10 auto-generated POs, manually verify: (a) correct supplier name and email, (b) correct SKU and product name on each line item, (c) reorder quantity matches the configured quantity, (d) unit cost matches the cost price in the product record, (e) PO total is arithmetically correct.
- POS-TO-INVENTORY SYNC TEST: Process 5 different items through the POS as a single sale transaction. Within 60 seconds, verify in the inventory platform that all 5 SKU quantities have decremented by the correct amounts. Repeat with a return/refund and verify quantities increment.
- ACCOUNTING SYNC TEST: Approve a test PO in the inventory platform. Within 15 minutes, verify in QuickBooks Online (or Xero) that: (a) a corresponding Purchase Order or Bill appears, (b) the vendor name matches, (c) line items and amounts are correct, (d) it is posted to the correct Accounts Payable account.
- SUPPLIER EMAIL DELIVERY TEST: Generate a test PO and configure it to auto-email to the supplier. Verify: (a) the email is received (check spam folders), (b) the attached PDF is properly formatted with the client's logo and terms, (c) all line items, quantities, and pricing are correct on the PDF, (d) the supplier can clearly identify what is being ordered.
- DUPLICATE PO PREVENTION TEST: Trigger a PO for a SKU that drops below reorder point. Before receiving the PO (stock still low), wait for the next polling cycle. Verify that a second duplicate PO is NOT generated for the same SKU. Then simulate receiving the first PO (updating stock above reorder point) and verify the SKU is cleared for future reorder if it drops again.
- BARCODE SCANNER FUNCTIONALITY TEST: Using the Zebra TC22 mobile computer, scan 20 random products from different areas of the store. Verify that each scan correctly resolves to the right SKU in the inventory platform's mobile app. Test receiving a PO by scanning items as they arrive and verify quantities update correctly.
- EDGE CASE: ZERO LEAD TIME SUPPLIER TEST: Configure a test SKU with a supplier lead time of 0 days (local/same-day supplier). Verify the reorder point calculation handles this correctly (should equal safety stock only) and that a PO is still generated when stock drops below this point.
- EDGE CASE: HIGH-VOLUME SIMULTANEOUS DEPLETION TEST: Simulate a busy sales period where 15+ SKUs cross below their reorder points within a 1-hour window. Verify the system generates POs for all of them without errors, timeouts, or missed items. Check that POs are correctly grouped by supplier.
- ALERT AND NOTIFICATION TEST: Verify that when a PO is auto-generated: (a) the store manager receives an email notification within 5 minutes, (b) the MSP monitoring email receives a copy, (c) the notification contains the PO number, supplier name, item count, and estimated total cost.
- LABEL PRINTER TEST: Print 10 barcode labels on the Zebra ZD421 for 10 different SKUs. Verify that each label prints clearly, the barcode is scannable with both the Honeywell Voyager 1250g and the Zebra TC22, and the label information (SKU, product name, price) is legible.
- FAILOVER/OFFLINE BEHAVIOR TEST: Temporarily disconnect the internet at the retail location for 15 minutes while processing POS sales. Verify that: (a) the POS continues to function in offline mode (if supported), (b) when connectivity is restored, all sales sync to the inventory platform, (c) any missed reorder triggers are caught on the next polling cycle.
- FULL CYCLE END-TO-END TEST: Sell enough units of a tracked SKU to bring it below reorder point → verify PO is auto-generated → approve the PO → verify supplier receives email → simulate receiving the goods using the TC22 scanner → verify inventory quantity updates correctly → verify the accounting system reflects the received inventory and accounts payable entry. This should be tested for at least 3 different suppliers.
Client Handoff
The client handoff meeting should be a structured 2-hour session with the store owner/manager and any staff involved in purchasing and receiving. Cover the following topics:
Documentation to Leave Behind
- Quick Reference Card (laminated, 1 page) with daily procedures: checking dashboard, approving POs, receiving process
- Full User Guide (PDF, 15–20 pages) with screenshots for all common tasks
- Reorder Point Reference Sheet with the formula and instructions for adjusting
- Emergency Contact Card with MSP helpdesk number, email, and escalation path
- System Configuration Document (for MSP internal records) detailing all settings, API keys (encrypted), integration endpoints, and credentials vault location
- Inventory Count Procedure SOP for quarterly cycle counts
Sign-Off: Have the client sign a project completion acknowledgment confirming the system is operational, training has been delivered, and they accept the transition to managed service support.
Maintenance
Ongoing MSP Responsibilities:
Weekly (15–30 min)
- Review automated monitoring alerts for any sync failures, API errors, or connectivity issues
- Check the inventory platform's error/exception log for failed PO generations
- Verify POS-to-inventory sync is functioning (spot-check 3 random recent sales)
- Review any client-submitted support tickets
Monthly (1–2 hours)
- Review auto-generated PO volume and accuracy metrics
- Check for SKUs with reorder points of zero or very high unnecessary reorder points (data quality drift)
- Verify accounting sync is clean — no orphaned transactions or mapping errors
- Apply any platform software updates or patches (for on-prem Fishbowl/QB Enterprise)
- Review API rate limits and usage to ensure polling frequency is sustainable
- Back up configuration settings and any custom workflow definitions (n8n exports)
Quarterly (2–4 hours — aligns with Quarterly Business Review)
- Run the Reorder Point Optimization Report (custom component) and deliver findings to client
- Recalculate reorder points using the last 90 days of sales data; apply seasonal adjustments
- Review supplier lead times with the client — have any vendors changed delivery timelines?
- Audit user accounts: remove departed staff, update permissions
- Review hardware condition: barcode scanners, label printer, mobile computers
- Check label printer supply levels and order consumables
- Prepare and present the QBR deck showing: PO automation rate, stockout incidents (should trend to zero), inventory turnover improvement, and cost savings estimate
Annually
- Conduct a full system review: is the current platform still the right fit as the client grows?
- Negotiate software subscription renewals (advocate for the client on pricing)
- Replace barcode scanner batteries or devices showing wear
- Full physical inventory count — compare to system quantities, resolve variances
- Review and update PO template terms and conditions
- Update client's resale tax exemption certificates if expired
SLA Considerations
- Response time for critical issues (sync failure, PO generation stopped): 2 hours
- Response time for non-critical issues (label printer jam, user access request): 8 business hours
- Resolution time for critical issues: 4 hours
- Scheduled maintenance windows: Communicate 48 hours in advance; perform during off-hours
- Monthly uptime target for n8n/middleware (if deployed): 99.5%
Escalation Path
Reorder Point Recalibration Triggers (outside quarterly schedule)
- Client adds a new product line with >50 SKUs
- A major supplier changes their lead time by >3 days
- Client opens a new retail location
- Seasonal transition (pre-holiday ramp-up: recalculate 8 weeks before Black Friday; post-holiday: recalculate in January)
- Client reports repeated stockouts or excessive overstock on multiple SKUs
Alternatives
...
Square for Retail Plus with Manual PO Workflow
For micro-retailers with fewer than 500 SKUs and a single location, use Square for Retail Plus ($49/month per location) which provides low-stock alerts and basic purchase order creation. The system sends alerts when stock is low, but POs are created manually by the manager from the Square dashboard rather than auto-generated. This is a semi-automated approach.
Shopify POS Pro with Stocky Demand Forecasting
For retailers who have or want a strong e-commerce presence alongside brick-and-mortar, deploy Shopify POS Pro ($89/month per location on annual billing) which includes the Stocky inventory management app. Stocky uses sales history to forecast demand and suggests purchase orders with recommended quantities. POs can be generated from these suggestions and emailed to suppliers.
Fishbowl Warehouse + QuickBooks Desktop Enterprise (On-Premises)
For larger retailers with 10,000+ SKUs, warehouse operations, and deep QuickBooks dependency, deploy Fishbowl Warehouse (perpetual license starting at $4,395) integrated with QuickBooks Desktop Enterprise ($1,768/first year). Fishbowl provides advanced auto-reorder with min/max thresholds, multi-warehouse support, lot tracking, and manufacturing/kitting capabilities. All data stays on-premises.
n8n Self-Hosted Custom Integration (Build-Your-Own)
For clients with non-standard or legacy POS/ERP systems that lack native connectors to modern inventory platforms, deploy n8n Community Edition on a Linux VPS and build custom workflows that: poll the existing system's database or API for stock levels, apply reorder logic, generate POs in a specified format (CSV, PDF, email, or API call to an ordering portal), and sync results back. This is a fully custom approach.
Zoho Inventory Professional (Budget Cloud Option)
For cost-sensitive retailers or those already using Zoho Books/CRM/Analytics, deploy Zoho Inventory Professional at $99/month. Provides automatic reorder triggers, purchase order generation, multi-channel sync (Shopify, Amazon, eBay, Etsy), and native integration with Zoho Books for accounting. The Zoho Partner Program offers MSPs approximately 20% margin on subscription billing.
Recommend Zoho Inventory Professional when: the client's primary goal is budget optimization, they sell on multiple online channels, they already use or are willing to adopt the Zoho ecosystem, and they have a small team (1–2 people managing inventory).
Want early access to the full toolkit?