Guide - PRS

1. System Overview

The Purchase Recommendation System (PRS) converts raw inventory data into actionable purchase orders through a fully transparent, explainable pipeline.

Upload Data

Excel files for items, sales, vendors, stock

Run Pipeline

Forecast → Replenish → Rank vendors

Review Results

Filter, sort, column selector

Create Orders

Draft, edit, group by vendor

2. Processing Workflow

1

Canonical Weekly Series — Merges weekly, monthly, and annual sales into a unified weekly timeline. Priority: Weekly > Monthly > Annual. Fills gaps using proportional distribution or equal split.

2

Anomaly Detection — Flags weeks where |z-score| > threshold or % change exceeds limit. Also computes recent vs historical trend for dampening/boosting.

3

Forecasting — WMA (Weighted Moving Average, weights 0.5/0.3/0.2), Prophet (Facebook's time-series model), or Hybrid (average of both). MAPE is computed for model comparison.

4

Replenishment — Computes CV, demand type, safety stock, min/max stock, and recommended order quantities (with and without open POs). Applies demand adjustment if enabled.

5

Vendor Rankings — Scores each vendor per SKU using availability and price. Type-2 vendors (specialty) ranked before Type-1 for their covered SKUs. Resolves discounts (detailed > flat > none).

6

Order Allocation — Allocates quantities sequentially to ranked vendors. Sets BestVendor1/2/3, AllocationSummary, EstimatedTotalCost, BackorderRisk.

7

ABC-FMS Classification — Classifies each SKU by sales value (A/B/C) and velocity (Fast/Medium/Slow). Derives Priority Group (P1–P4).

8

Budget Engine — If a budget limit is set: Full mode drops lowest-priority SKUs; Partial mode iteratively reduces quantities round-robin until within budget.

3. Glossary

SKU	Stock Keeping Unit — unique product identifier
MRP	Maximum Retail Price — base price before discount
μ (Mean)	Weighted average weekly demand
σ (Sigma)	Weighted standard deviation of weekly demand
CV	Coefficient of Variation = σ / μ. Measures demand volatility.
SS	Safety Stock — buffer against demand uncertainty
Min Stock	Minimum desired inventory level = μ×H + SS
Max Stock	Maximum desired inventory = Min + μ×H
H	Planning horizon in weeks
z	Service level Z-score (95% → z=1.65)
WMA	Weighted Moving Average (weights: 50%, 30%, 20%)
Prophet	Facebook's time-series forecasting model (requires ≥12 weeks)
MAPE	Mean Absolute Percentage Error — forecast accuracy metric
RecQty(ExclPO)	Recommended quantity excluding open POs
RecQty(InclPO)	Recommended quantity after subtracting open POs
FinalQty	Final order quantity (may be overridden or budget-adjusted)
Type-1 Vendor	General supplier — eligible for all SKUs
Type-2 Vendor	Specialty supplier — eligible only for covered SKUs, ranked first
Alpha (α)	Weight for availability in vendor scoring (1=availability only, 0=price only)

4. Configuration Parameters

Parameter	Default	Description
Forecast Mode	Auto	Auto picks best model by MAPE; WMA/Prophet/Hybrid also available
Horizon Weeks	4	How many weeks to plan ahead (H)
Service Level	95%	Stock availability target; determines z-score for safety stock
Alpha	0.8	Vendor scoring weight for availability vs price
Z-Threshold	3.0	Anomaly detection sensitivity — higher means fewer anomalies
CV Stable	< 0.5	Below this CV = Stable demand
CV Variable	< 1.0	Below this CV = Variable demand (otherwise Erratic)
Trend Threshold	30%	Recent vs historical change % to trigger trend flag
Demand Adjustment	Off	Multiplies Safety Stock by (1 + adj%)
Budget	None	Total order budget cap
Budget Mode	Full	Full=drop SKUs; Partial=reduce quantities round-robin

5. Flags Reference

Flag	Badge Color	Meaning	Action
Unfulfilled	Red	SKU has open customer orders that weren't fulfilled	Prioritize for immediate purchase
LowHistory	Yellow	Insufficient historical data for reliable forecast	Verify manually; consider override
Anomaly	Blue	One or more weeks had statistical sales anomalies	Review whether spike is real demand
NewSKU	Grey	SKU found in stock/orders but not in ItemMaster	Add to ItemMaster for next run
NoDiscount	Black	No discount data for the top-ranked vendor	Check vendor discount sheet
NoData	Light	No sales history at all — forecast unavailable	Gather demand data; use override
Trend ↑	Red arrow	Recent 3-week average is ≥30% above historical average	Safety stock boosted 10% automatically
Trend ↓	Blue arrow	Recent 3-week average is ≥30% below historical average	Safety stock reduced 10% automatically

6. ABC-FMS Classification

ABC (Sales Value)

A	Top 80% of annual sales value	Critical
B	Next 15% of annual sales value	Important
C	Bottom 5% of annual sales value	Routine

FMS (Velocity)

Fast	Mean weekly ≥ 10 units
Medium	Mean weekly 3–9 units
Slow	Mean weekly < 3 units

Priority Groups

Priority	ABC+FMS combinations	Meaning
P1-Critical	A+Fast, A+Medium	Never stock out — monitor daily
P2-High	A+Slow, B+Fast	High attention — weekly review
P3-Medium	B+Medium, B+Slow, C+Fast	Standard replenishment cycle
P4-Low	C+Medium, C+Slow	Minimal safety stock; consider dropship

7. Key Formulas

CV = σ / μ
SS = z × σ × √H
Min = μ × H + SS
Max = Min + μ × H

ExclPO = Max(0, Min − Stock)
InclPO = Max(0, Min − (Stock+PO))
NetPrice = MRP × (1 − discount%)
Score = α×Avail + (1−α)×Price

WMA = 0.5×w1 + 0.3×w2 + 0.2×w3
Hybrid = (WMA + Prophet) / 2
MAPE = avg|actual−forecast|/actual

DemandAdj: SS × (1 + adj%/100)
TrendUp: SS × 1.10
TrendDown: SS × 0.90

Service Level → Z: 90%=1.28, 95%=1.65, 98%=2.05, 99%=2.33

8. Vendor Engine

Discount Priority: Detailed Sheet (vendor+SKU exact match) > Flat (vendor-wide) > 0% + NoDiscountDataFlag
Type-2 Precedence: Type-2 vendors cover only specific SKUs and are always ranked above Type-1 vendors for those SKUs
Availability Score: vendor_stock / recommended_qty (capped at 1.0; 1.0 if stock unknown)
Price Score: min_price / vendor_price (1.0 for the cheapest vendor)
Combined Score: α × Availability + (1-α) × Price
Allocation: Sequential — fill from Vendor 1, then 2, then 3. BackorderRisk=High if any qty remains unfulfilled.

9. Draft Orders

Go to Orders and click New Draft Order
Use Import from Run to pull all SKUs with FinalQty > 0 from any run
Edit quantities, vendor assignments, and apply discount overrides per line
The By Vendor grouping lets you review exactly what each vendor will receive
Set a budget on the order to see real-time over/under budget warnings
Multiple drafts can coexist — e.g. one per vendor or one per category

10. Best Practices

Always upload at least 12 weeks of sales data to enable Prophet forecasting
Use SalesMonthly and SalesAnnual as fallbacks when weekly data is sparse
Run with Hybrid mode for best accuracy when data is sufficient
Set Service Level to 98% for A-class, critical SKUs; 90% for C-class slow movers
Use the Overrides sheet to lock quantities for specific SKUs or put SKUs on replenishment hold
Review Erratic demand SKUs manually — high CV means the model has high uncertainty
New SKUs (yellow rows) need to be added to ItemMaster with MRP for proper pricing
Use ABC-FMS Priority Groups to focus attention on P1-Critical items first
Export to Excel after each run for records and offline analysis