FORESIGHT · Analysis walkthrough

Raw rows ingested

42,131

4 tables

Analysis-ready rows

5,200

50 SKUs × 104 weeks

Backtest SKU-weeks

2,400

6 folds × 8 wk × 50

WAPE model / naive

8.75% / 11.16%

21.5% better

Holdout Dec-25

9.26%

80% band covers 79.5%

Sales at risk

₹47.9 L

8 reorder SKUs

Locked capital

₹1.48 Cr

8 markdown SKUs

1. Business problem & success metrics

NorthBay Living (D2C home & lifestyle, ~50 active SKUs in the extract, one warehouse) plans stock on spreadsheets. Best-sellers stock out; slow movers pile up. The brief asks for a weekly SKU-level forecast that beats a naive baseline, a stockout early-warning, an overstock flag, rupee impact, and a dashboard + scoring service the ops team can use unaided.

Success metric	Target (brief)	Achieved
Forecast accuracy	WAPE beats seasonal-naive on a rolling-origin backtest	8.75% vs 11.16% (21.5% better); wins 49/50 SKUs
Risk precision	Flags right often enough to act on	probability-based; 80% interval calibrated at 79.5% on holdout; triage 8 / 8 / 34
Quantified impact	Rupee sales-at-risk and locked capital	₹47.9 L at risk · ₹1.48 Cr locked
Usability	Ops can answer “what do I reorder?” unaided	Reorder & markdown lists, filters, what-if, CSV export, API
Reproducibility	Pipeline re-runs from raw with one command	python src/run_all.py (~1 min), fixed seed, 5 tests

2. Data inventory & profiling

Ingest — four CSV extracts read with pandas; columns renamed to the brief’s data dictionary (snake_case). A fifth file, Stock_data.csv (equity prices, ticker AABA 2006–2017), has no join key and is excluded.

Profile — dtypes, nulls, uniques, ranges and duplicates per column (tables below). Findings feed the cleaning rules in step 3.

sales_daily.csv (fact) — 36,550 rows × 6 columns · duplicates: 0

column	dtype	non-null	unique	min	max	mean	example
Date	str	36,550	731	2024-01-01	2025-12-31		2024-01-01
SKU	str	36,550	50	SKU001	SKU050		SKU001
Units_Sold	int64	36,550	55	0	56	14.0	5
Revenue	float64	36,550	1,327	0.0	546977.0	84707.43	18320.25
Price	float64	36,550	50	663.46	11642.45	5892.66	3664.05
Promotion	int64	36,550	2	0	1	0.1	0

sku_master.csv (dimension) — 50 rows × 8 columns · duplicates: 0

column	dtype	non-null	unique	min	max	mean	example
SKU	str	50	50	SKU001	SKU050		SKU001
Product_Name	str	50	50	Product 001	Product 050		Product 001
Category	str	50	5	Furniture	Storage		Furniture
Subcategory	str	50	10	Cabinet	Table		Chair
Launch_Date	str	50	48	2022-03-12	2024-12-09		2022-04-09
Cost_Price	float64	50	50	307.06	7748.2	3770.73	1758.45
Selling_Price	float64	50	50	663.46	11642.45	5892.66	3664.05
Gross_Margin_Per_Unit	float64	50	50	-5484.32	10224.93	2121.93	1905.6

calendar.csv (dimension) — 731 rows × 11 columns · duplicates: 0

column	dtype	non-null	nulls	unique	min	max	mean	example
date	str	731	0	731	2024-01-01	2025-12-31		2024-01-01
year	int64	731	0	2	2024	2025	2024.5	2024
month	int64	731	0	12	1	12	6.52	1
quarter	str	731	0	4	Q1	Q4		Q1
week	int64	731	0	52	1	52	26.4	1
day_of_week	str	731	0	7	Friday	Wednesday		Monday
is_weekend	int64	731	0	2	0	1	0.28	0
season	str	731	0	5	Autumn	Winter		Winter
holiday	str	8	723	4	Christmas	Republic Day		nan
is_holiday	int64	731	0	2	0	1	0.01	0
promotion_event	str	75	656	1	Seasonal Promotion	Seasonal Promotion		nan

inventory_snapshots.csv (periodic fact) — 4,800 rows × 8 columns · duplicates: 0

column	dtype	non-null	unique	min	max	mean	example
Snapshot_Date	str	4,800	24	2024-01-01	2025-12-01		2024-01-01
SKU	str	4,800	200	SKU001	SKU200		SKU001
Current_Stock	int64	4,800	954	2	2585	304.14	16
On_Order	int64	4,800	822	0	3060	213.09	23
Lead_Time_Days	int64	4,800	12	3	14	8.44	11
Safety_Stock	int64	4,800	341	5	746	83.15	7
Reorder_Point	int64	4,800	715	10	1661	211.75	18
Inventory_Value	float64	4,800	4,630	2444.2	20279867.85	1304166.47	58420.96

Sample rows

sales_daily — first 6 rows
Date	SKU	Units_Sold	Revenue	Price
2024-01-01	SKU001	5	18,320.25	3,664.05
2024-01-01	SKU002	15	57,085.35	3,805.69
2024-01-01	SKU003	5	40,391.15	8,078.23
2024-01-01	SKU004	5	34,307.85	6,861.57
2024-01-01	SKU005	12	113,919	9,493.22
2024-01-01	SKU006	7	56,742.28	8,106.04

inventory_snapshots — first 6 rows
Snapshot_Date	SKU	Current_Stock	On_Order	Lead_Time_Days	Safety_Stock	Reorder_Point	Inventory_Value
2024-01-01	SKU001	16	23	11	7	18	58,420.96
2024-01-01	SKU002	29	12	4	5	10	163,983
2024-01-01	SKU003	34	23	14	6	20	209,060
2024-01-01	SKU004	34	4	7	5	12	42,257.92
2024-01-01	SKU005	23	5	5	6	11	170,946
2024-01-01	SKU006	13	6	5	5	10	86,143.20

sku_master — first 6 rows
SKU	Product_Name	Category	Subcategory	Launch_Date	Cost_Price	Selling_Price	Gross_Margin_Per_Unit
SKU001	Product 001	Furniture	Chair	2022-04-09	1,758.45	3,664.05	1,905.60
SKU002	Product 002	Home Decor	Table	2024-05-01	3,867.09	3,805.69	-61.40
SKU003	Product 003	Kitchen	Cushion	2023-12-22	589.48	8,078.23	7,488.75
SKU004	Product 004	Lighting	Cookware	2023-04-28	1,445.74	6,861.57	5,415.83
SKU005	Product 005	Storage	Lamp	2023-04-22	5,543.63	9,493.22	3,949.59
SKU006	Product 006	Furniture	Shelf	2024-08-02	6,021.91	8,106.04	2,084.13

calendar — first holiday / promo rows
date	year	month	quarter	week	day_of_week	is_weekend	season	holiday	is_holiday	promotion_event
2024-01-26	2,024	1	Q1	4	Friday	0	Winter	Republic Day	1	nan
2024-03-02	2,024	3	Q1	9	Saturday	1	Spring	nan	0	Seasonal Promotion
2024-03-03	2,024	3	Q1	9	Sunday	1	Spring	nan	0	Seasonal Promotion
2024-03-09	2,024	3	Q1	10	Saturday	1	Spring	nan	0	Seasonal Promotion
2024-03-10	2,024	3	Q1	10	Sunday	1	Spring	nan	0	Seasonal Promotion
2024-03-16	2,024	3	Q1	11	Saturday	1	Spring	nan	0	Seasonal Promotion

Descriptive statistics — sales_daily

column	count	mean	std	min	25%	50%	75%	max
Units_Sold	36,550.00	14.00	8.47	0.00	7.00	13.00	20.00	56.00
Revenue	36,550.00	84,707.43	76,534.63	0.00	27,643.70	56,125.90	127,954	546,977
Price	36,550.00	5,892.66	3,138.78	663.46	3,484.09	5,649.80	8,516.60	11,642.45
Promotion	36,550.00	0.10	0.30	0.00	0.00	0.00	0.00	1.00

Descriptive statistics — inventory_snapshots (all 200 SKUs)

column	count	mean	std	min	25%	50%	75%	max
Current_Stock	4,800.00	304.14	280.89	2.00	109.00	229.00	418.00	2,585.00
On_Order	4,800.00	213.09	244.46	0.00	49.00	135.00	292.00	3,060.00
Lead_Time_Days	4,800.00	8.44	3.49	3.00	5.00	8.00	11.25	14.00
Safety_Stock	4,800.00	83.15	70.26	5.00	35.00	67.00	112.00	746.00
Reorder_Point	4,800.00	211.75	179.91	10.00	87.00	169.00	286.00	1,661.00
Inventory_Value	4,800.00	1,304,166	1,741,551	2,444.20	252,186	683,739	1,720,023	20,279,868

3. Cleaning & data-quality log

Every rule lives in src/pipeline.py and writes to data/processed/data_quality_log.json — nothing is edited by hand, and the log is what the dashboard’s Data-quality tab renders.

Table	Issue found	n	Handling	Rationale
Stock_data.csv	Unrelated file: equity price history (AABA ticker 2006–2017)	—	Excluded from the pipeline	Not part of the NorthBay star schema; no join key to any table.
sales_daily	Duplicate (date, sku) rows	0	None found — no action	One row per SKU-day is the declared grain.
sales_daily	Negative units_sold (returns?)	0	None found — no action	Returns are not demand.
sales_daily	Missing numeric values	0	None found — no action	A missing sales row means nothing sold that day.
sales_daily	revenue != units_sold * unit_price (>₹1 gap)	0	None found — internally consistent	Revenue is the booked figure; price is a reference.
sales_daily	Rows retained after cleaning	36,550	36550 → 36550
sku_master	launch_date AFTER first recorded sale	21	launch_date overridden with first_sale_date (original kept as launch_date_master)	Sales history is the stronger evidence; a product cannot sell before launch.
sku_master	list_price below unit_cost (negative gross margin)	16	Kept; flagged margin_flag='negative' and surfaced to Finance	Cannot be corrected from data — likely a master-data error or loss-leader; must be confirmed by client.
sku_master	category/subcategory pairs that look inconsistent (e.g. Kitchen→Cushion)	40	Kept the master 'category' for reporting; added 'subcategory_group' as a sanity view	Category is the client's planning hierarchy; we do not silently re-classify their catalogue.
inventory_snapshots	SKUs present in inventory but with NO sales history (SKU051–SKU200)	150	Excluded from forecasting/risk; listed separately as 'unsellable-or-unmapped stock' for the client	No demand signal → cannot forecast; likely discontinued or master-data gap. Raised as a finding.
inventory_snapshots	Duplicate (date, sku) snapshots	0	None found — no action
inventory_snapshots	Snapshot cadence	24	Monthly snapshots (1st of month); latest = 2025-12-01	Risk scoring uses the latest snapshot as the opening stock position at the forecast origin.
calendar	Blank holiday / promotion_event cells	—	Treated as 'None' (not missing)	Blank means no event that day.
sales_weekly	Partial weeks at series edges (fewer than 7 days)	50	Dropped	A 3-day 'week' would look like a demand crash and poison the model.
sales_weekly	SKU-weeks with no sales rows at all	0	Filled with 0 units	No row = no sale.

Biggest finding: 150 inventory SKUs (SKU051–SKU200) never sold a unit in two years but hold ₹17.49 Cr of stock at the latest snapshot. Excluded from forecasting (no demand signal); listed in inventory_unmapped_skus.csv for reconciliation.

4. Weekly analysis-ready dataset

Aggregate to weeks — Sunday-ending weeks; partial edge weeks dropped (a 3-day “week” looks like a demand crash). Complete SKU × week grid so silent zero-weeks are explicit. Calendar attributes (week-of-year, month, season, promo days, holiday days) and master attributes (category, launch date, price, cost) joined.

Set the forecast origin = 2025-11-30, the Sunday before the latest inventory snapshot (2025-12-01), so the stock position and the forecast start on the same day. The 4 December weeks become a true holdout (flag is_holdout).

Property	Value
Grain	one row per SKU per week
Rows	5,200 = 50 SKUs × 104 weeks
Span	2024-01-07 → 2025-12-28
Training weeks	100 (≤ origin)
Holdout weeks	4 (Dec-2025)
Total units	510,082
Total revenue	₹308.56 Cr
Columns	sku_id, week_end, units, revenue, promo_days, holiday_days, unit_price, week_of_year, month, year, season, category, subcategory, launch_date, unit_cost, list_price, weeks_since_launch, is_holdout

sales_weekly — last 10 weeks of SKU012 (note the is_holdout flag)
sku_id	week_end	units	revenue	promo_days	holiday_days	unit_price	week_of_year	month	year	season	category	subcategory	unit_cost	list_price	weeks_since_launch	is_holdout
SKU012	2025-10-26 00:00:00	141	1,237,891	0	1	8,779.37	43	10	2,025	Autumn	Home Decor	Table	1,256.89	8,779.37	94	False
SKU012	2025-11-02 00:00:00	151	1,325,685	2	0	8,779.37	44	11	2,025	Autumn	Home Decor	Table	1,256.89	8,779.37	95	False
SKU012	2025-11-09 00:00:00	155	1,360,802	2	0	8,779.37	45	11	2,025	Autumn	Home Decor	Table	1,256.89	8,779.37	96	False
SKU012	2025-11-16 00:00:00	163	1,431,037	2	0	8,779.37	46	11	2,025	Autumn	Home Decor	Table	1,256.89	8,779.37	97	False
SKU012	2025-11-23 00:00:00	155	1,360,802	2	0	8,779.37	47	11	2,025	Autumn	Home Decor	Table	1,256.89	8,779.37	98	False
SKU012	2025-11-30 00:00:00	161	1,413,479	2	0	8,779.37	48	11	2,025	Autumn	Home Decor	Table	1,256.89	8,779.37	99	False
SKU012	2025-12-07 00:00:00	167	1,466,155	0	0	8,779.37	49	12	2,025	Winter	Home Decor	Table	1,256.89	8,779.37	100	True
SKU012	2025-12-14 00:00:00	155	1,360,802	0	0	8,779.37	50	12	2,025	Winter	Home Decor	Table	1,256.89	8,779.37	101	True
SKU012	2025-12-21 00:00:00	171	1,501,272	0	0	8,779.37	51	12	2,025	Winter	Home Decor	Table	1,256.89	8,779.37	102	True
SKU012	2025-12-28 00:00:00	150	1,316,906	0	1	8,779.37	52	12	2,025	Winter	Home Decor	Table	1,256.89	8,779.37	103	True

5. EDA — seasonality & demand drivers

Monthly seasonality — average total units per week (both years, and by year)
Month	Avg units / week	Avg units / week 2024	Avg units / week 2025	Seasonality index	Read
Jan	4,749.38	4,739.50	4,759.25	0.97	average
Feb	5,126.25	5,142.00	5,110.50	1.05	average
Mar	5,922.00	5,911.80	5,932.20	1.21	peak
Apr	5,637.25	5,663.00	5,611.50	1.15	peak
May	5,534.12	5,520.75	5,547.50	1.13	peak
Jun	5,644.90	5,670.00	5,619.80	1.15	peak
Jul	4,778.38	4,786.25	4,770.50	0.98	average
Aug	4,407.89	4,406.50	4,409.00	0.90	average
Sep	4,361.89	4,382.60	4,336.00	0.89	trough
Oct	3,889.50	3,892.75	3,886.25	0.80	trough
Nov	4,313.67	4,342.50	4,290.60	0.88	trough
Dec	4,327.00	4,306.60	4,352.50	0.88	trough

Reading: Mar is the peak and Oct the trough in both years — a 52% swing that repeats almost identically. That stability is what makes a 52-week seasonal signal usable.

Weekday effect
Weekday	Avg units / SKU-day	Total units	SKU-days	Index vs weekday avg
Monday	13.03	68,430	5,250	1.00
Tuesday	13.02	68,342	5,250	1.00
Wednesday	13.04	68,451	5,250	1.00
Thursday	13.11	68,193	5,200	1.00
Friday	13.19	68,567	5,200	1.01
Saturday	16.28	84,652	5,200	1.24
Sunday	16.38	85,175	5,200	1.25

Promotion effect
Day type	SKU-days	Avg units	Avg revenue (₹)
No promotion	32,800	13.48	81,550.08
Promotion day	3,750	18.61	112,324

Holiday & season effect
Day type	SKU-days	Avg units
Normal day	36,150.00	14.02
National holiday	400.00	12.16
Spring		16.67
Summer		16.01
Winter		13.56
Monsoon		12.83
Autumn		11.77

Weekend lift +24.9%, promotion lift +38.1%, holiday effect -13.3%. Promotions are planned, so promo_days_target is a legitimate forward-looking feature.

6. EDA — categories, SKUs and dead stock

Category mix
Category	SKUs	Units (2 yrs)	Revenue (₹)	Avg price (₹)	Units share %	Revenue share %
Furniture	10	82,026	601,392,700	6,594.75	16.08	19.49
Home Decor	10	132,100	885,273,983	6,478.72	25.90	28.69
Kitchen	10	100,784	561,503,262	5,178.73	19.76	18.20
Lighting	10	102,131	457,384,575	4,897.31	20.02	14.82
Storage	10	93,041	580,007,535	6,313.81	18.24	18.80

All 50 SKUs ranked by two-year revenue (CoV = weekly coefficient of variation; Cum % = cumulative revenue share)
Rank	SKU	Category	Subcat	Units	Revenue (₹)	Avg units/wk	CoV	Price	Cost	Margin	Launch	Rev %	Cum %
1	SKU026	Furniture	Shelf	16,480	180,283,619	158.5	0.16	10,939.54	1,799.29	positive	2023-12-10	5.84	5.8
2	SKU035	Storage	Lamp	15,522	168,727,089	149.2	0.16	10,870.19	1,137.61	positive	2024-01-01	5.47	11.3
3	SKU012	Home Decor	Table	18,996	166,772,913	182.7	0.14	8,779.37	1,256.89	positive	2024-01-01	5.40	16.7
4	SKU042	Home Decor	Table	16,559	152,323,426	159.2	0.15	9,198.83	4,583.86	positive	2024-01-01	4.94	21.7
5	SKU027	Home Decor	Cabinet	17,650	150,317,990	169.7	0.15	8,516.60	307.06	positive	2023-03-19	4.87	26.5
6	SKU045	Storage	Lamp	18,553	142,427,299	178.4	0.17	7,676.78	3,634.56	positive	2022-03-16	4.62	31.1
7	SKU043	Kitchen	Cushion	16,201	141,690,220	155.8	0.15	8,745.77	2,605.61	positive	2024-01-01	4.59	35.7
8	SKU029	Lighting	Organizer	11,495	133,829,963	110.5	0.15	11,642.45	5,402.59	positive	2023-08-23	4.34	40.1
9	SKU031	Furniture	Chair	13,671	119,155,479	131.5	0.15	8,715.93	6,300.65	positive	2023-05-11	3.86	43.9
10	SKU008	Kitchen	Rug	11,908	117,206,157	114.5	0.16	9,842.64	2,775.15	positive	2024-01-01	3.80	47.7
11	SKU005	Storage	Lamp	12,146	115,304,650	116.8	0.15	9,493.22	5,543.63	positive	2023-04-22	3.74	51.5
12	SKU018	Kitchen	Rug	18,392	102,542,941	176.8	0.14	5,575.41	5,677.05	negative	2022-05-21	3.32	54.8
13	SKU049	Lighting	Organizer	18,326	95,723,479	176.2	0.14	5,223.37	2,063.03	positive	2024-01-01	3.10	57.9
14	SKU007	Home Decor	Cabinet	18,061	92,365,579	173.7	0.14	5,114.09	7,748.20	negative	2022-04-05	2.99	60.9
15	SKU017	Home Decor	Cabinet	10,337	87,630,057	99.4	0.17	8,477.32	6,703.26	positive	2023-07-19	2.84	63.7
16	SKU047	Home Decor	Cabinet	10,817	85,867,726	104.0	0.17	7,938.22	3,416.10	positive	2023-05-06	2.78	66.5
17	SKU041	Furniture	Chair	8,128	84,016,941	78.2	0.18	10,336.73	4,540.24	positive	2022-07-01	2.72	69.2
18	SKU013	Kitchen	Cushion	10,262	59,086,338	98.7	0.17	5,757.78	3,936.71	positive	2024-01-01	1.91	71.1
19	SKU019	Lighting	Organizer	11,763	56,695,896	113.1	0.16	4,819.85	2,670.84	positive	2024-01-01	1.84	73.0
20	SKU037	Home Decor	Cabinet	18,096	49,717,312	174.0	0.15	2,747.42	3,901.00	negative	2024-01-01	1.61	74.6
21	SKU046	Furniture	Shelf	6,744	48,391,842	64.8	0.17	7,175.54	1,913.03	positive	2024-01-01	1.57	76.2
22	SKU021	Furniture	Chair	6,219	47,956,450	59.8	0.17	7,711.28	6,486.92	positive	2023-07-05	1.55	77.7
23	SKU033	Kitchen	Cushion	13,182	46,901,556	126.8	0.17	3,558.00	4,657.74	negative	2022-04-12	1.52	79.2
24	SKU020	Storage	Sofa	10,095	39,345,666	97.1	0.15	3,897.54	6,700.01	negative	2023-05-11	1.28	80.5
25	SKU006	Furniture	Shelf	4,766	38,633,387	45.8	0.20	8,106.04	6,021.91	positive	2024-01-01	1.25	81.8
26	SKU044	Lighting	Cookware	7,126	38,567,907	68.5	0.18	5,412.28	488.84	positive	2023-01-25	1.25	83.0
27	SKU001	Furniture	Chair	10,465	38,344,283	100.6	0.15	3,664.05	1,758.45	positive	2022-04-09	1.24	84.3
28	SKU032	Home Decor	Table	6,795	38,137,549	65.3	0.20	5,612.59	3,806.60	positive	2022-09-07	1.24	85.5
29	SKU014	Lighting	Cookware	15,733	35,725,710	151.3	0.16	2,270.75	2,008.55	positive	2024-01-01	1.16	86.7
30	SKU022	Home Decor	Table	7,404	34,036,410	71.2	0.18	4,597.03	3,252.96	positive	2023-02-12	1.10	87.8
31	SKU003	Kitchen	Cushion	4,197	33,904,331	40.4	0.22	8,078.23	589.48	positive	2023-12-22	1.10	88.9
32	SKU030	Storage	Sofa	3,479	32,819,181	33.5	0.20	9,433.51	5,715.03	positive	2023-05-03	1.06	89.9
33	SKU002	Home Decor	Table	7,385	28,105,021	71.0	0.18	3,805.69	3,867.09	negative	2024-01-01	0.91	90.8
34	SKU040	Storage	Sofa	11,366	27,853,065	109.3	0.16	2,450.56	5,169.07	negative	2023-11-26	0.90	91.7
35	SKU004	Lighting	Cookware	3,374	23,150,937	32.4	0.21	6,861.57	1,445.74	positive	2023-04-28	0.75	92.5
36	SKU025	Storage	Lamp	1,969	22,757,722	18.9	0.25	11,558.01	1,333.08	positive	2024-01-01	0.74	93.2
37	SKU034	Lighting	Cookware	14,689	22,299,958	141.2	0.16	1,518.14	1,333.43	positive	2023-09-03	0.72	93.9
38	SKU038	Kitchen	Rug	5,610	22,154,451	53.9	0.19	3,949.10	4,630.58	negative	2024-01-01	0.72	94.7
39	SKU016	Furniture	Shelf	10,106	21,897,883	97.2	0.17	2,166.82	3,637.93	negative	2024-01-01	0.71	95.4
40	SKU036	Furniture	Shelf	3,499	19,898,813	33.6	0.23	5,687.00	5,430.12	positive	2022-03-12	0.64	96.0
41	SKU015	Storage	Lamp	2,987	18,555,602	28.7	0.22	6,212.12	5,441.06	positive	2024-01-01	0.60	96.6
42	SKU009	Lighting	Organizer	7,809	18,248,774	75.1	0.19	2,336.89	3,121.06	negative	2022-08-10	0.59	97.2
43	SKU024	Lighting	Cookware	9,528	16,853,793	91.6	0.17	1,768.87	5,539.34	negative	2024-01-01	0.55	97.8
44	SKU039	Lighting	Organizer	2,288	16,288,158	22.0	0.25	7,118.95	6,178.74	positive	2022-11-01	0.53	98.3
45	SKU023	Kitchen	Cushion	10,791	15,288,041	103.8	0.17	1,416.74	2,484.54	negative	2022-07-20	0.50	98.8
46	SKU028	Kitchen	Rug	4,087	14,239,476	39.3	0.21	3,484.09	6,348.66	negative	2024-01-01	0.46	99.2
47	SKU048	Kitchen	Rug	6,154	8,489,751	59.2	0.18	1,379.55	6,863.87	negative	2024-01-01	0.28	99.5
48	SKU010	Storage	Sofa	12,414	8,236,192	119.4	0.17	663.46	3,889.06	negative	2022-04-14	0.27	99.8
49	SKU050	Storage	Sofa	4,510	3,981,067	43.4	0.22	882.72	701.85	positive	2024-01-01	0.13	99.9
50	SKU011	Furniture	Chair	1,948	2,814,003	18.7	0.26	1,444.56	1,718.40	negative	2023-08-03	0.09	100.0

Insights: top-10 SKUs = 47.7% of revenue, bottom-10 = 4.0%; dead-stock candidates SKU028, SKU048, SKU010, SKU050, SKU011. 16 SKUs are priced below cost (16.7% of revenue) — master-data error or loss-leaders; Finance must confirm before discounting.

7. Feature engineering (leakage-safe)

Direct multi-horizon design — for every origin week t and horizon h = 1…8, one training row: features from history ≤ t plus known calendar attributes of week t+h; target = units in week t+h. Origins step weekly from week 13 onward, giving 2,400 scored rows in the backtest and a larger training set for the final model.

Feature	Definition	Source / leakage status
h	Horizon (weeks ahead, 1–8)	design
lag1…lag4, lag8, lag13	Units in the k-th week before the origin	history ≤ origin
mean4 / mean8 / mean13	Rolling mean of the last 4/8/13 weeks	history ≤ origin
std4	Rolling std of last 4 weeks (volatility)	history ≤ origin
trend4_13	mean4 − mean13 (short vs medium level)	history ≤ origin
lag52_target	Units in the same week last year (target week − 52)	history ≤ origin
lag52_mean3	Mean of last year's target week ±1	history ≤ origin
level_ratio_52	mean4 ÷ same 4 weeks last year	history ≤ origin
promo_days_target	Planned promotion days in the target week	calendar (known in advance)
holiday_days_target	Holiday days in the target week	calendar
week_of_year, month, season	Calendar position of the target week	calendar
weeks_since_launch	Product age at the target week	master data
unit_price	Selling price	master data
sku_id, category	Identity (LightGBM categorical)	master data

Leakage guard: tests/test_foresight.py::test_no_future_leakage_in_training_rows asserts that lag1 equals the last observed week ≤ origin and every target week is strictly after the origin.

8. Metric & baseline

WAPE = Σ|actual − forecast| ÷ Σ actual   (primary — robust when some SKUs sell few units)
Bias = Σ(forecast − actual) ÷ Σ actual   (secondary — systematic over/under-forecast)
MAPE = mean(|actual − forecast| ÷ actual) over actual > 0   (reported, not optimised)
Seasonal-naive: forecast(t+h) = actual(t+h−52)   (falls back to mean of last 4 weeks if last year is missing)

Horizon fixed at 8 weeks. The baseline is the bar: if the model cannot beat it on the backtest, the baseline ships.

9. Model

LightGBM parameter	Value	Why
objective	tweedie (variance power 1.2)	non-negative, right-skewed count-like demand
n_estimators	600
learning_rate	0.03
num_leaves	31
min_child_samples	20
subsample / colsample_bytree	0.8 / 0.8 (freq 1)	regularisation
reg_lambda	1.0
random_state	42	fixed seed → reproducible

One model for all 50 SKUs (identity as a categorical feature) so shared seasonality and promotion effects are learned jointly; Tweedie objective suits non-negative, right-skewed demand; predictions clipped at 0.

10. Rolling-origin backtest

Six origins in 2025, four weeks apart (2025-05-18, 2025-06-15, 2025-07-13, 2025-08-10, 2025-09-07, 2025-10-05). At each, the model is retrained from scratch on data ≤ origin and scored on the next 8 weeks. No random splits.

By fold
Fold	Origin	WAPE model	WAPE naive
1	2025-05-18	7.98%	10.04%
2	2025-06-15	8.48%	10.97%
3	2025-07-13	9.38%	11.69%
4	2025-08-10	9.21%	12.13%
5	2025-09-07	8.99%	11.37%
6	2025-10-05	8.70%	11.03%

By horizon
Weeks ahead	WAPE model	WAPE naive
1	8.02%	10.66%
2	8.62%	10.60%
3	8.77%	10.95%
4	9.18%	11.67%
5	8.71%	11.09%
6	8.61%	11.08%
7	8.63%	11.23%
8	9.57%	12.07%

By category
Category	WAPE model	WAPE naive
Furniture	9.67%	12.12%
Home Decor	8.32%	10.37%
Kitchen	8.75%	11.50%
Lighting	8.39%	10.79%
Storage	8.95%	11.46%

Model WAPE by fold × horizon
Fold	Origin	h1	h2	h3	h4	h5	h6	h7	h8
1	2025-05-18	6.1%	6.9%	7.7%	8.9%	8.8%	8.6%	6.9%	10.1%
2	2025-06-15	7.8%	8.8%	7.1%	10.2%	7.6%	8.9%	9.4%	8.1%
3	2025-07-13	7.4%	9.9%	10.3%	8.2%	9.6%	8.2%	10.4%	11.3%
4	2025-08-10	9.8%	7.9%	10.3%	10.1%	9.6%	8.5%	7.6%	9.9%
5	2025-09-07	9.2%	8.5%	8.7%	9.4%	8.2%	10.0%	9.9%	8.2%
6	2025-10-05	8.5%	10.4%	8.9%	8.3%	8.5%	7.5%	7.8%	9.9%

Per-SKU backtest (sorted by model WAPE) — the model wins on every SKU except one
SKU	Category	WAPE model	WAPE naive	Improvement %
SKU049	Lighting	4.62%	6.46%	+28.4
SKU018	Kitchen	4.96%	6.31%	+21.4
SKU043	Kitchen	5.98%	8.91%	+32.9
SKU031	Furniture	6.03%	7.39%	+18.5
SKU014	Lighting	7.04%	8.57%	+17.8
SKU027	Home Decor	7.11%	8.79%	+19.2
SKU012	Home Decor	7.11%	8.54%	+16.7
SKU037	Home Decor	7.24%	8.40%	+13.7
SKU001	Furniture	7.26%	10.54%	+31.1
SKU045	Storage	7.36%	10.06%	+26.8
SKU007	Home Decor	7.64%	8.85%	+13.7
SKU033	Kitchen	7.67%	8.58%	+10.6
SKU029	Lighting	7.69%	10.69%	+28.1
SKU040	Storage	7.72%	9.78%	+21.1
SKU042	Home Decor	7.76%	9.15%	+15.1
SKU026	Furniture	7.90%	9.38%	+15.8
SKU019	Lighting	7.93%	11.10%	+28.6
SKU035	Storage	7.95%	7.43%	-7.0
SKU010	Storage	8.18%	11.83%	+30.8
SKU005	Storage	8.50%	10.59%	+19.7
SKU016	Furniture	8.95%	10.65%	+15.9
SKU017	Home Decor	8.96%	13.30%	+32.6
SKU047	Home Decor	8.98%	11.24%	+20.2
SKU034	Lighting	9.11%	12.00%	+24.1
SKU023	Kitchen	9.15%	9.94%	+7.9
SKU020	Storage	9.22%	13.62%	+32.3
SKU022	Home Decor	9.93%	12.53%	+20.8
SKU009	Lighting	9.99%	13.97%	+28.5
SKU008	Kitchen	10.00%	14.76%	+32.2
SKU024	Lighting	10.08%	11.96%	+15.8
SKU002	Home Decor	10.27%	17.10%	+40.0
SKU044	Lighting	10.27%	11.92%	+13.8
SKU038	Kitchen	10.39%	14.97%	+30.6
SKU021	Furniture	10.60%	15.28%	+30.6
SKU030	Storage	10.81%	15.74%	+31.3
SKU013	Kitchen	11.60%	13.69%	+15.3
SKU041	Furniture	11.91%	12.05%	+1.2
SKU003	Kitchen	12.13%	18.86%	+35.7
SKU046	Furniture	12.57%	13.83%	+9.1
SKU028	Kitchen	12.74%	22.80%	+44.1
SKU006	Furniture	12.77%	16.61%	+23.1
SKU050	Storage	14.63%	17.47%	+16.2
SKU032	Home Decor	15.04%	16.45%	+8.6
SKU048	Kitchen	15.26%	16.88%	+9.6
SKU004	Lighting	15.33%	15.38%	+0.4
SKU015	Storage	15.83%	20.32%	+22.1
SKU036	Furniture	16.40%	25.14%	+34.8
SKU025	Storage	18.16%	22.81%	+20.4
SKU039	Lighting	20.51%	25.32%	+19.0
SKU011	Furniture	25.34%	34.26%	+26.0

Overall WAPE

8.75%

naive 11.16%

MAPE

10.8%

naive 13.8%

Bias

+0.32%

naive +0.31%

SKUs won

49/50

folds won 6/6

11. Prediction intervals & December holdout

Relative residuals (actual − forecast) ÷ forecast from the backtest, per horizon, give the 10th/90th percentiles for an 80% band and a sigma used by the risk layer.

Interval calibration by horizon
Horizon h	q10 (rel.)	q90 (rel.)	rel. sigma
1	-0.14	0.17	0.14
2	-0.15	0.17	0.14
3	-0.16	0.15	0.13
4	-0.17	0.16	0.14
5	-0.15	0.14	0.13
6	-0.16	0.14	0.14
7	-0.16	0.15	0.14
8	-0.17	0.18	0.15

Top-revenue SKU: history, baseline, forecast with 80% band, December holdout dots.

December-2025 holdout (never trained on)	Value
Holdout weeks	4
WAPE model	9.26%
WAPE seasonal-naive	12.42%
Bias model	-0.15%
80% interval coverage	79.5% (target 80%)

12. Feature importance

Feature	Gain	Share
sku_id	2,781,543	52.02%
mean4	1,436,039	26.85%
mean8	652,554	12.20%
mean13	160,179	3.00%
week_of_year	119,274	2.23%
lag52_mean3	57,946	1.08%
season	56,180	1.05%
lag52_target	24,360	0.46%
weeks_since_launch	18,499	0.35%
month	16,489	0.31%
promo_days_target	8,062	0.15%
lag1	5,582	0.10%
unit_price	4,188	0.08%
trend4_13	1,548	0.03%
level_ratio_52	1,158	0.02%
holiday_days_target	1,137	0.02%
lag13	653	0.01%
h	460	0.01%
lag8	408	0.01%
category	334	0.01%
std4	326	0.01%
lag2	254	0.00%
lag3	202	0.00%
lag4	201	0.00%

13. Risk scoring formulas (transparent, no black box)

D_LT = forecast demand over the lead time (weekly forecast interpolated to days) · σ_LT = summed weekly sigma
P_lt = P[ D_LT > on_hand ] = 1 − Φ((on_hand − D_LT)/σ_LT)   ← stock runs out before the open order lands
P_rop = P[ D_LT + safety_stock > on_hand + on_order ]   ← inventory position already below the reorder point
Stockout score = max(P_lt, P_rop)
weeks_of_cover = (on_hand + on_order) ÷ average weekly forecast
Overstock score = clip((weeks_of_cover − 8) ÷ 8, 0, 1)   → 0 at ≤8 weeks, 0.5 at 12, 1 at ≥16
Sales at risk (₹) = max(0, D_LT − on_hand, D_LT + SS − on_hand − on_order) × list price
Locked capital (₹) = max(0, on_hand + on_order − 8-week demand) × unit cost
Reorder qty = demand over (lead time + 4-week review) + safety stock − (on_hand + on_order)

Quadrant	Rule (threshold 0.5)	Action
Reorder now	stockout ≥ 0.5 and overstock < 0.5	Raise a replenishment order (quantity given)
Markdown / clear	overstock ≥ 0.5 and stockout < 0.5	Promote / discount; defer open orders
Watch / volatile	both ≥ 0.5	Investigate erratic demand manually
Healthy	both < 0.5	No action

Calibration note: a first version compared 8-week demand with stock on hand and flagged 36/50 SKUs for reorder — technically true, practically useless for a team that reorders monthly. Reframing stockout as “will it run out before the next order can land” produced the 8 / 8 / 34 triage below.

14. Risk results — all 50 SKUs

Full risk table, ranked by value at stake
#	SKU	Category	Quadrant	Stockout	Overstock	P_lt	P_rop	OH	OO	LT d	SS	Fcst/wk	D_LT	D_8w	Cover wk	Days OH	Order qty	Excess	Sales at risk	Locked capital	₹ at stake	Margin	Action
1	SKU020	Storage	Markdown / clear	0.00	0.91	0.00	0.00	1,002	353	3	129	88.50	37.90	708.30	15.30	79.20	0	646.70	₹0	₹43.3 L	₹43.3 L	negative	Promote or discount — 15 weeks of cover vs 8-week horizon
2	SKU006	Furniture	Markdown / clear	0.00	1.00	0.00	0.00	359	485	13	129	44.20	75.00	353.40	19.10	56.90	0	490.60	₹0	₹29.5 L	₹29.5 L	positive	Promote or discount — 19 weeks of cover vs 8-week horizon
3	SKU012	Home Decor	Reorder now	1.00	0.00	1.00	1.00	47	60	12	16	171.50	281.00	1,371.80	0.60	1.90	919	0.00	₹20.5 L	₹0	₹20.5 L	positive	Raise a replenishment order for ~919 units before stock runs out
4	SKU011	Furniture	Markdown / clear	0.00	1.00	0.00	0.00	753	269	3	179	17.50	6.50	140.30	58.30	300.70	0	881.70	₹0	₹15.2 L	₹15.2 L	negative	Promote or discount — 58 weeks of cover vs 8-week horizon
5	SKU036	Furniture	Markdown / clear	0.00	1.00	0.00	0.00	383	135	3	77	32.00	13.30	255.70	16.20	83.90	0	262.30	₹0	₹14.2 L	₹14.2 L	positive	Promote or discount — 16 weeks of cover vs 8-week horizon
6	SKU025	Storage	Markdown / clear	0.00	1.00	0.00	0.00	1,124	61	14	121	17.10	31.10	137.20	69.10	458.90	0	1,047.80	₹0	₹14.0 L	₹14.0 L	positive	Promote or discount — 69 weeks of cover vs 8-week horizon
7	SKU015	Storage	Markdown / clear	0.00	1.00	0.00	0.00	187	245	9	119	23.50	26.20	188.20	18.40	55.60	0	243.80	₹0	₹13.3 L	₹13.3 L	positive	Promote or discount — 18 weeks of cover vs 8-week horizon
8	SKU017	Home Decor	Reorder now	1.00	0.00	0.99	1.00	95	26	12	108	88.30	140.80	706.40	1.40	7.50	506	0.00	₹10.8 L	₹0	₹10.8 L	positive	Raise a replenishment order for ~506 units before stock runs out
9	SKU031	Furniture	Reorder now	1.00	0.00	1.00	1.00	93	40	13	28	124.50	207.00	995.70	1.10	5.20	617	0.00	₹9.9 L	₹0	₹9.9 L	positive	Raise a replenishment order for ~617 units before stock runs out
10	SKU004	Lighting	Markdown / clear	0.00	1.00	0.00	0.00	259	254	6	98	29.00	23.90	231.70	17.70	62.60	0	281.30	₹0	₹4.1 L	₹4.1 L	positive	Promote or discount — 18 weeks of cover vs 8-week horizon
11	SKU050	Storage	Markdown / clear	0.00	1.00	0.00	0.00	328	525	14	69	38.90	71.80	311.40	21.90	59.00	0	541.60	₹0	₹3.8 L	₹3.8 L	positive	Promote or discount — 22 weeks of cover vs 8-week horizon
12	SKU038	Kitchen	Healthy	0.00	0.21	0.00	0.00	159	310	12	56	48.40	80.50	387.50	9.70	23.00	0	81.50	₹0	₹3.8 L	₹3.8 L	negative	No action needed; leave as is
13	SKU042	Home Decor	Reorder now	0.98	0.00	0.98	0.01	90	120	6	41	148.30	125.10	1,186.40	1.40	4.20	559	0.00	₹3.2 L	₹0	₹3.2 L	positive	Raise a replenishment order for ~559 units before stock runs out
14	SKU001	Furniture	Healthy	0.00	0.24	0.00	0.00	771	147	4	159	92.70	51.10	741.60	9.90	58.20	0	176.40	₹0	₹3.1 L	₹3.1 L	positive	No action needed; leave as is
15	SKU049	Lighting	Healthy	0.00	0.07	0.00	0.00	780	599	5	206	161.80	106.10	1,294.50	8.50	33.70	0	84.50	₹0	₹1.7 L	₹1.7 L	positive	No action needed; leave as is
16	SKU040	Storage	Reorder now	1.00	0.00	1.00	1.00	67	29	9	36	97.50	114.30	780.40	1.00	4.80	512	0.00	₹1.3 L	₹0	₹1.3 L	negative	Raise a replenishment order for ~512 units before stock runs out
17	SKU034	Lighting	Reorder now	1.00	0.00	1.00	0.18	34	103	5	34	130.20	91.70	1,041.70	1.10	1.80	536	0.00	₹87,590	₹0	₹87,590	positive	Raise a replenishment order for ~536 units before stock runs out
18	SKU010	Storage	Reorder now	1.00	0.00	1.00	0.99	83	70	13	22	109.50	196.10	876.00	1.40	5.30	519	0.00	₹75,057	₹0	₹75,057	negative	Raise a replenishment order for ~519 units before stock runs out
19	SKU032	Home Decor	Healthy	0.00	0.04	0.00	0.00	278	214	11	60	59.30	86.50	474.50	8.30	32.80	0	17.50	₹0	₹66,658	₹66,658	positive	No action needed; leave as is
20	SKU002	Home Decor	Healthy	0.00	0.03	0.00	0.00	526	29	3	94	67.60	26.70	540.60	8.20	54.50	0	14.40	₹0	₹55,866	₹55,866	negative	No action needed; leave as is
21	SKU003	Kitchen	Healthy	0.00	0.27	0.00	0.00	349	12	3	88	35.60	14.30	285.20	10.10	68.50	0	75.80	₹0	₹44,709	₹44,709	positive	No action needed; leave as is
22	SKU023	Kitchen	Reorder now	0.90	0.00	0.90	0.32	130	79	12	41	98.10	157.50	784.60	2.10	9.30	418	0.00	₹38,906	₹0	₹38,906	negative	Raise a replenishment order for ~418 units before stock runs out
23	SKU005	Storage	Healthy	0.00	0.00	0.00	0.00	471	291	6	129	108.10	87.30	865.20	7.00	30.50	0	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
24	SKU007	Home Decor	Healthy	0.14	0.00	0.14	0.00	207	460	8	98	165.00	180.20	1,320.30	4.00	8.80	413	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
25	SKU008	Kitchen	Healthy	0.00	0.00	0.00	0.00	325	155	3	91	102.90	42.90	823.00	4.70	22.10	107	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
26	SKU009	Lighting	Healthy	0.07	0.00	0.07	0.00	75	89	7	63	68.90	62.10	551.50	2.40	7.60	242	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
27	SKU013	Kitchen	Healthy	0.00	0.00	0.00	0.00	151	95	4	32	86.10	48.40	688.80	2.90	12.30	216	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
28	SKU014	Lighting	Healthy	0.00	0.00	0.00	0.00	309	334	10	130	136.70	190.20	1,093.70	4.70	15.80	297	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
29	SKU016	Furniture	Healthy	0.00	0.00	0.00	0.00	255	187	11	30	84.80	126.70	678.40	5.20	21.10	90	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
30	SKU018	Kitchen	Healthy	0.03	0.00	0.03	0.00	235	102	8	66	163.10	185.80	1,304.50	2.10	10.10	700	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
31	SKU019	Lighting	Healthy	0.20	0.00	0.20	0.00	227	114	14	25	102.80	202.70	822.30	3.30	15.50	291	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
32	SKU021	Furniture	Healthy	0.01	0.00	0.01	0.00	88	97	9	36	54.80	64.90	438.20	3.40	11.20	168	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
33	SKU022	Home Decor	Healthy	0.01	0.00	0.01	0.00	95	194	8	76	68.30	70.40	546.60	4.20	9.70	182	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
34	SKU024	Lighting	Healthy	0.00	0.00	0.00	0.00	341	18	14	60	82.10	160.10	657.00	4.40	29.10	194	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
35	SKU026	Furniture	Healthy	0.00	0.00	0.00	0.00	160	70	5	31	144.80	103.80	1,158.50	1.60	7.70	512	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
36	SKU027	Home Decor	Healthy	0.00	0.00	0.00	0.00	567	103	3	83	164.30	64.90	1,314.40	4.10	24.20	207	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
37	SKU028	Kitchen	Healthy	0.00	0.00	0.00	0.00	174	55	11	114	37.00	55.70	295.90	6.20	32.90	108	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
38	SKU029	Lighting	Healthy	0.00	0.00	0.00	0.00	261	520	5	196	105.10	70.10	840.60	7.40	17.40	0	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
39	SKU030	Storage	Healthy	0.00	0.00	0.00	0.00	166	32	9	43	31.40	41.30	251.10	6.30	37.00	30	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
40	SKU033	Kitchen	Healthy	0.42	0.00	0.42	0.00	208	168	12	55	122.00	202.50	975.80	3.10	11.90	391	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
41	SKU035	Storage	Healthy	0.00	0.00	0.00	0.00	670	76	8	137	137.60	155.10	1,100.90	5.40	34.10	214	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
42	SKU037	Home Decor	Healthy	0.00	0.00	0.00	0.00	355	687	8	315	159.00	183.00	1,272.40	6.60	15.60	223	0.00	₹0	₹0	₹0	negative	No action needed; leave as is
43	SKU039	Lighting	Healthy	0.00	0.00	0.00	0.00	93	35	3	17	18.90	6.50	151.50	6.80	34.40	0	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
44	SKU041	Furniture	Healthy	0.00	0.00	0.00	0.00	440	40	4	78	76.30	39.50	610.10	6.30	40.40	0	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
45	SKU043	Kitchen	Healthy	0.00	0.00	0.00	0.00	559	211	12	142	149.90	232.80	1,199.40	5.10	26.10	252	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
46	SKU044	Lighting	Healthy	0.00	0.00	0.00	0.00	223	39	5	55	62.10	37.60	496.50	4.20	25.20	73	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
47	SKU045	Storage	Healthy	0.00	0.00	0.00	0.00	307	142	4	88	161.10	89.60	1,288.80	2.80	13.30	445	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
48	SKU046	Furniture	Healthy	0.00	0.00	0.00	0.00	119	182	4	46	62.00	32.10	496.00	4.90	13.40	43	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
49	SKU047	Home Decor	Healthy	0.00	0.00	0.00	0.00	201	129	9	44	90.70	107.70	725.60	3.60	15.50	254	0.00	₹0	₹0	₹0	positive	No action needed; leave as is
50	SKU048	Kitchen	Healthy	0.00	0.00	0.00	0.00	106	50	3	22	54.00	21.40	432.30	2.90	13.70	123	0.00	₹0	₹0	₹0	negative	No action needed; leave as is

15. Reorder & markdown lists

Reorder now — 8 SKUs
SKU	Product	Category	Order qty	Sales at risk	Days of stock
SKU012	Product 012	Home Decor	919	₹20.5 L	1.90
SKU017	Product 017	Home Decor	506	₹10.8 L	7.50
SKU031	Product 031	Furniture	617	₹9.9 L	5.20
SKU042	Product 042	Home Decor	559	₹3.2 L	4.20
SKU040	Product 040	Storage	512	₹1.3 L	4.80
SKU034	Product 034	Lighting	536	₹87,590	1.80
SKU010	Product 010	Storage	519	₹75,057	5.30
SKU023	Product 023	Kitchen	418	₹38,906	9.30

Markdown / clear — 8 SKUs
SKU	Product	Category	Excess units	Locked capital	Weeks cover
SKU020	Product 020	Storage	647	₹43.3 L	15.30
SKU006	Product 006	Furniture	491	₹29.5 L	19.10
SKU011	Product 011	Furniture	882	₹15.2 L	58.30
SKU036	Product 036	Furniture	262	₹14.2 L	16.20
SKU025	Product 025	Storage	1048	₹14.0 L	69.10
SKU015	Product 015	Storage	244	₹13.3 L	18.40
SKU004	Product 004	Lighting	281	₹4.1 L	17.70
SKU050	Product 050	Storage	542	₹3.8 L	21.90

16. Rupee impact

By category
Category	SKUs	Sales at risk	Locked capital
Furniture	10	₹9.9 L	₹62.0 L
Home Decor	10	₹34.6 L	₹1.2 L
Kitchen	10	₹38,906	₹4.2 L
Lighting	10	₹87,590	₹5.8 L
Storage	10	₹2.1 L	₹74.4 L

Measure	Value
Sales at risk (lead-time shortfall × price)	₹47.9 L
Locked capital (excess beyond 8 weeks × cost)	₹1.48 Cr
Inventory on hand at cost (50 sellable SKUs)	₹5.12 Cr
Unmapped inventory (150 SKUs, no sales)	₹17.49 Cr
Quadrants	Healthy: 34, Markdown / clear: 8, Reorder now: 8

17. Dashboard — how it works

The dashboard (index.html) is a static page: src/export_web.py packs the forecast, risk table, 26 weeks of history per SKU, backtest metrics and the data-quality log into data/dashboard.json (~92 KB). The page loads it once, then everything — filters, sorting, charts, what-if — runs in the browser. risk_engine.js is a line-for-line port of src/risk.py, so the what-if slider and the API give the same numbers as the batch job.

Tab	What it shows	Notes
Overview	5 KPI cards, decisioning bubble grid (click a bubble → explorer), rupee impact by category, top priorities, total demand with forecast band	filters: category, quadrant, search
Reorder & markdown list	Reorder list with order qty and days of stock; markdown list with weeks of cover and margin flag; sortable full table; CSV export	sort by any column
Forecast explorer & what-if	Per-SKU history + forecast + 80% band + seasonal-naive + Dec holdout dots; lead-time slider, on-hand/on-order/safety inputs → instant re-score with a plain-language “why”	stretch goal from the brief
Model accuracy	Backtest by fold, horizon, category; feature importance; honest limitations text	—
Data quality	The pipeline’s data-quality log and dataset summary	—
Scoring API	Endpoint documentation and a live “try it” box	calls /api/score
States	Loading spinner while fetching; explicit error card if data is missing; empty-state cards when filters match nothing	acceptance criterion D5.4

Overview

Reorder & markdown lists

Forecast explorer & what-if

Model accuracy

A Streamlit edition (app/streamlit_app.py) with identical logic is included for Streamlit Community Cloud, as recommended in the brief.

18. Scoring API

Method	Endpoint	Returns
GET	/api/health	status, forecast origin, model, backtest WAPE
GET	/api/score?sku=SKU012	8-week forecast (mean, lo80, hi80, naive) + risk + action for one SKU
GET	/api/score?sku=SKU012&lead_time_days=21&on_hand=30	what-if override (also on_order, safety_stock)
GET	/api/score?sku=SKU012,SKU020 · ?all=true	batch (summary totals + quadrants)
POST	/api/score {"skus":[…],"overrides":{…}}	batch with overrides
—	errors	unknown SKU → 404 · bad number → 400 · empty → 400 with example · bad JSON → 400 · other method → 405

{
 "forecast_origin": "2025-11-30",
 "count": 1,
 "results": [
  {
   "sku_id": "SKU012",
   "product_name": "Product 012",
   "category": "Home Decor",
   "inventory_position": {
    "on_hand": 47,
    "on_order": 60,
    "lead_time_days": 21,
    "safety_stock": 16,
    "overridden": true
   },
   "forecast": {
    "origin": "2025-11-30",
    "horizon_weeks": 8,
    "weeks": [
     "2025-12-07",
     "2025-12-14",
     "2025-12-21",
     "2025-12-28",
     "2026-01-04",
     "2026-01-11",
     "2026-01-18",
     "2026-01-25"
    ],
    "mean": [
     162.6,
     165.8,
     163.9,
     166.7,
     171.9,
     178.5,
     181.7,
     180.6
    ],
    "\u2026": "lo80, hi80, seasonal_naive, total_8w"
   },
   "risk": {
    "stockout_score": 1.0,
    "overstock_score": 0.0,
    "quadrant": "Reorder now",
    "action": "Raise a replenishment order for ~N units before stock runs out",
    "\u2026": "p_stockout_lead_time, weeks_of_cover, reorder_qty, sales_at_risk_inr, locked_capital_inr"
   }
  }
 ]
}

Served by a Netlify serverless function (netlify/functions/score.mjs); a FastAPI twin (service/main.py, docs at /docs) is included for Render.

19. Reproducibility & tests

pip install -r requirements.txt
python src/run_all.py      # pipeline → forecast + backtest → risk → figures → dashboard data   (~60 s)
python -m pytest tests -q  # metrics, risk quadrants, output consistency, leakage guard, API errors
python reports/build_reports.py    # PDFs: EDA memo, executive readout, project report, video scripts
python src/build_analysis_page.py  # this page

Path	Purpose
src/pipeline.py	D1 ingest · clean · weekly features · data-quality log
src/forecast.py	D3 baseline · LightGBM · rolling-origin backtest · intervals · forecast
src/risk.py	D4 stockout/overstock scoring · actions · rupee impact
src/eda.py	D2 figures + EDA summary
src/export_web.py	dashboard data bundle
web/	D5 static dashboard + risk_engine.js + this page
netlify/functions/score.mjs	D6 serverless scoring API
app/streamlit_app.py · service/main.py	Streamlit + FastAPI editions
notebooks/01–03	executed notebooks with outputs
reports/	D2 memo · D7 readout · project report · video scripts · figures
tests/	5 pytest tests

A fresh end-to-end rerun reproduced the headline numbers bit-for-bit (seed 42).

20. Assumptions, limitations & monitoring

Topic	Assumption / limit
Stock position	Latest monthly snapshot = opening position at the origin; on-order arrives at end of lead time
Future calendar	Promotions/holidays after 31-Dec-2025 unknown → assumed 0
History depth	Two years = one prior seasonal cycle; new SKUs lean on category peers
Distribution	Lead-time demand ≈ normal; sigma scaled from backtest relative errors per horizon
Scope	No price optimisation, supplier selection, live integrations or automated POs (per brief)
Monitoring	Retrain monthly; alert if 4-week WAPE loses to seasonal-naive for 2 consecutive months or \|bias\| > 5%; track realised stockouts among “Healthy” SKUs

Bottom line: demand is predictable to within about 9%. NorthBay’s problem is timing, not demand — fast sellers ordered too late, slow sellers too often. FORESIGHT turns that into two short lists every month.