# VERIFICATION_INVENTORY.md > Generated 2026-05-18. A complete, structural inventory of what the OLD Streamlit > app (`app.py` + `forecast_engine.py` + `update_sales.py` + `PromoTool/` + `PromoCalendar/`) > actually does, file-by-file and page-by-page. This is the source of truth that > the new FastAPI + React stack will be validated against — for every page, every > CSV read, every formula. > > No code is changed by producing this report; all sources are read-only. --- ## How to use this document The new stack should reproduce the **business semantics** of every section below. When numbers differ between old and new, this file is the reference for "what is the right answer". - §1 — Data dictionary: every CSV in `data/` with its header. - §2 — `app.py` — every page function (filters, data sources, formulas, metrics, charts, tables). - §3 — `forecast_engine.py` — inputs, outputs, all key formulas (cap/floor, channel split, GBR, ERP-aware promo). - §4 — `update_sales.py` — channel mapping, promo-detection rules. - §5 — `PromoTool/` — CM Planner / Forecaster / History / Performance / Marketing pages. - §6 — `PromoCalendar/` — unified calendar with conflict detection. Anything not listed in §2–§6 is incidental scaffolding (caching wrappers, Streamlit-only widget styling, audit logs that don't drive numbers). --- ## 1 · Data dictionary — every CSV under `data/` Headers verified from disk on 2026-05-18. Sizes rounded to KB. | File | Size | Header | |---|---:|---| | `backtest_fa.csv` | 345 KB | `sku,year,week,forecast,actual,forecast_retail,forecast_wholesale,actual_retail,actual_wholesale,channel_mode,ws_share,model,model_retail,model_wholesale,cat,oznaka` | | `cat_uplift.csv` | <1 KB | `grupacija,cat_promo_uplift` | | `coupon_daily.csv` | 56 KB | `date,coupon_code,coupon_name,coupon_type,units,orders,discount` | | `coupon_dating_summary.csv` | 52 KB | `coupon_code,coupon_name,coupon_type,estimated_when,skus,orders,units,rev_before,rev_after,discount` | | `coupon_sales_by_sku_week.csv` | 237 KB | `sku,year,week,coupon_units,coupon_orders,coupon_discount,coupons_used` | | `coupon_timeline.csv` | 64 KB | `coupon_code,coupon_name,coupon_type,first_order,last_order,orders,units,skus,rev_before,rev_after,discount,days_active` | | `coupon_weekly_totals.csv` | 1 KB | `year,week,orders,units,rev_after,discount,top_coupon` | | `coupon_with_dates.csv` | 1.7 MB | `coupon_id,coupon_code,coupon_name,coupon_type,coupon_config_discount,product_id,sku,model,product_name,orders_count,units_sold,revenue_before_coupon,revenue_after_coupon,product_coupon_discount_total,allocated_discount_for_coupon,avg_coupon_discount_percent,avg_unit_price_before_coupon,avg_unit_price_after_coupon,window_from_name,erp_top_weeks,estimated_when` | | `erp_promo_calendar.csv` | 16 MB | `sku,year,week,promo_types,is_erp_promo` | | `factor_history.csv` | 876 KB | `run_date,run_year,run_week,target_year,target_week,sku,factor` | | `forecast_for_supply.csv` | 124 KB | `sku,year,week,demand` | | `incoming_supply.csv` | 5 KB | `sku,year,week,qty` | | `mp_input.csv` | 8 KB | `sku,CW19,CW20,CW21,CW22,CW23,CW24,CW25,CW26,CW27,CW28,CW29,CW30,CW31` | | `mp_input_detail.csv` | 11 KB | `sku,type,kam,CW16,...,CW28,name,cat,oznaka,CW14,CW15` *(column order is ragged — schema-cleanup pending)* | | `nc30.csv` | 883 KB | `sku,nc30_price` | | `promo_performance.csv` | 3.3 MB | `sku,promo_start_year,promo_start_week,promo_end_year,promo_end_week,n_promo_weeks,qty_before_avg,qty_during_avg,qty_after_avg,actual_uplift,cannibalization,net_effect,promo_types,cat,oznaka` | | `promo_weeks_flag.csv` | 6 KB | `year,week,campaign,units,orders,coupons,first_day,last_day` | | `sales_clean.csv` | 11 MB | `sku,year,week,qty_retail,qty_webshop,qty_wholesale,qty_total,avg_ppp_retail,normal_ppp_retail,retail_discount_pct,is_retail_promo,avg_ppp_webshop,normal_ppp_webshop,webshop_discount_pct,is_webshop_promo,is_wholesale_spike,is_any_promo,promo_pct_volume,ruc_retail,ruc_webshop,ruc_wholesale,ruc_total` | | `sales_detailed.csv` | 67 MB | `source_country,source_file,date,year,week,dokument,partner,naziv_partnera,sku,naziv,proizvodjac,mj_troska,naziv_mj_troska,jedinica,naziv_jedinice,tip_dok,kategorija_artikla,grupacija_artikla,naziv_grupacije,podkat,podkategorija,komercijalist,kolicina,nabavna_vrijednost_eur,ruc_eur,ruc_pct,porezna_osnovica_eur,pdv_eur,vrijednost_eur,odobreni_rabat_eur,drzava` | | `sku_category_map.csv` | 521 KB | `sku,name,cat` | | `sku_costs.csv` | 178 KB | `sku,cost_price,ruc` | | `sku_plan_list.csv` | 50 KB | `sku,name,cat,oznaka,vpc,ws_xyz,ws_cv,ws_nz_weeks,total_xyz,total_cv,total_nz_weeks,ws_share_26w` | | `sku_prices.csv` | 381 KB | `sku,avg_sell_price,normal_retail_ppp,normal_webshop_ppp,qty_retail,qty_webshop,qty_wholesale,weeks_active` | | `sku_subcat_map.csv` | 543 KB | `sku,name,sub_cat,grup` | | `sku_uplift.csv` | 374 KB | `sku,grupacija,promo_weeks,normal_weeks,avg_normal,avg_promo,promo_uplift,ws_spike_weeks,ws_uplift` | | `stock.csv` | 60 KB | `sku,on_hand` | | `stock_stores.csv` | 75 KB | `sku,on_hand` *(HR aggregate)* | | `stock_stores_at.csv` | 21 KB | `sku,on_hand` *(AT aggregate)* | | `stock_stores_slo.csv` | 38 KB | `sku,on_hand` *(SLO aggregate)* | | `supply_master.csv` | 21 KB | `sku,supplier,lead_time_weeks,moq` | | `three_scenarios_per_po.csv` | 19 KB | `sku,po_year,po_week,po_label,qty,cost_price,eur_unlock,opening_now_combined,fwd_avg_weekly_demand,floor_units_2w,base_min_stock_from_delivery,cut_min_stock_from_delivery,safe_to_cancel,no_demand,category,tier,weeks_cover_now,post_delivery_cover,scenario_a,scenario_b,scenario_c` | | `Uspjesnost po kuponu i proizvodu..csv` | 1.6 MB | `coupon_id;coupon_code;coupon_name;coupon_type;coupon_config_discount;product_id;sku;model;product_name;orders_count;units_sold;revenue_before_coupon;revenue_after_coupon;product_coupon_discount_total;allocated_discount_for_coupon;avg_coupon_discount_percent;avg_unit_price_before_coupon;avg_unit_price_after_coupon` *(semicolon-delimited Magento export)* | | `vp_input.csv` | 8 KB | `sku,CW19,CW20,CW21,CW22,CW23,CW24,CW25,CW26,CW27,CW28,CW29,CW30,CW31` | | `vp_input_detail.csv` | 38 KB | `sku,type,kam,buyer,CW19,...,CW31` | | `webshop_coupon_orders.csv` | 2.9 MB | `order_id,date_added,order_status_id,order_status,coupon_id,coupon_code,coupon_name,coupon_type,coupon_config_discount,product_id,sku,model,product_name,quantity,unit_price_before_coupon,unit_price_after_coupon,coupon_discount_per_unit,line_total_before_coupon,line_total_after_coupon,line_coupon_discount_total,coupon_discount_percent,order_coupon_total,order_all_coupons_total,allocated_line_discount_for_coupon,order_total,year,week` | **Files NOT in `data/` but referenced by app pages**: - `data/Polleo_Demand_Plan.xlsx` — main forecast output (multi-sheet workbook) - `data/Polleo_Demand_Planning_Book*.xlsx` — input workbook fed to engine - `data/Weekly_Sales_Update*.xlsx` — weekly upload pattern - `data/upload_Rekapitulacija*.xlsx` — country ERP exports - `data/coverage_template.xlsx` — formatting template for coverage workbook - `data/rabatne.xlsx` — ERP promo source (used by `build_erp_promo.py`) - `data/sku_logistics.csv` *(created by Supply › Logistics page on demand)* - `data/orders_draft.json` — draft procurement orders - `data/sop_decisions.json` — S&OP meeting decisions - `data/consensus/snapshot_*.json` — consensus snapshots (DP cycle history) - `data/plan_history/Polleo_Demand_Plan_*.xlsx` — auto-archived plan workbooks - `data/corrections.json` — planner factor overrides - `data/watchlist_signoff.json` — Top-30 watchlist sign-off - `data/kam_cm_config.json` — KAM/CM roster + Slack config - `data/slack_cycle.json` — active Slack cycle state - `data/buyer_partner_map.json` — KAM buyer → ERP partner code map (per-buyer VP FA) - `data/npd_skus.csv` — NPD tracker (created on first NPD upload) --- ## 2 · `app.py` — every page function inventory Exhaustive page-by-page inventory of every Streamlit page function in [app.py](app.py) (10,933 lines). Generated from full reads of each function body. ## Master list of page functions (grouped by module) **Demand module** (`DEMAND_PAGES`) - `page_dashboard` (L925) — defined but routed identically to `page_demand_planning` - `page_demand_planning` (L1054) - `page_update_sales` (L1422) - `page_run_forecast` (L1506) - `page_input` (L1708) — generic, invoked twice as `vp_input` and `mp_input` - `page_revenue` (L1781) - `page_sku_management` (L2456) — routed via `sku_list` - `page_kam_inputs` (L2734) - `page_download` (L3559) - `page_forecast_accuracy` (L5294) - `page_top30_watchlist` (L5868) - `page_consensus_plan` (L6066) - `page_sop_meeting` (L6502) - `page_erp_promo` (L6626) **Supply module** (`SUPPLY_PAGES`) - `page_supply_dashboard` (L7267) - `page_supply_projection` (L7306) - `page_supply_scenarios` (L7681) - `page_supply_coverage` (L8111) - `page_supply_alerts` (L8163) - `page_supply_order_entry` (L8198) — routed via `supply_orders` - `page_supply_download` (L8260) - `page_supply_upload` (L8291) - `page_supply_moq` (L8668) - `page_supply_logistics` (L8699) - `page_supply_costs` (L8927) - `page_supply_inventory_health` (L9053) — routed via `supply_health` - `page_supply_store_overstock` (L9375) - `page_supply_settings` (L9656) - `page_supply_coverage_workbook` (L10239) — routed via `supply_coverage_wb` **NPD module** (`NPD_PAGES`) - `page_npd_upload` (L10388) - `page_npd_list` (L10551) --- ## DEMAND MODULE ### page_dashboard (L925–1052) **Filters:** - `Kategorija` (selectbox, key `dash_cat`) → `["All"] + sorted(sc["cat"].unique())` - `Oznaka` (selectbox, key `dash_ozn`) → `["All"] + OZNAKA_TIERS` (Gold/Silver/Bronze) - `View` (radio, key `dash_view`) → `["Weekly","Monthly"]` - `SKU` (selectbox, key `dash_sku`) → `["All SKUs"] + sorted(filtered["sku"].unique())` **Data sources:** - `load_sales_data()` → `sales_clean.csv` — columns used: `sku`, `name`, `cat`, `oznaka`, `year`, `week`, `yw`, `qty_total` - `compute_exceptions(sc)` — derives spikes & zero-sales **Formulas / business logic:** - Filter chain: cat → oznaka → sku - Weekly aggregation: `agg = filtered.groupby(["year","week"])["qty_total"].sum()`, sorted by `yw = year*100+week` - Monthly aggregation: parse `year+week+"1"` with `format="%G%V%u"` → `dt.to_period("M")`, group on month - Label: `"CW" + week + " '" + (year%100)` - Exceptions: spike = `>30%` WoW change; zero-sales = 3+ weeks at 0 **st.metric calls:** - `"Demand spikes"` = `len(exceptions["spikes"])` (help: `>30% week-over-week change`) - `"Zero-sales alerts"` = `len(exceptions["zero_sales"])` (help: `3+ weeks with no sales`) **Charts:** - `plotly_chart`: single line+markers+text of `qty_total` per week or month, blue `#2F5496`, "Quantity" **Tables:** - Spike `st.dataframe`: columns `SKU, Name, Tier, Change, Prev, Curr, Week` (inside expander) - Zero-sales `st.dataframe`: columns `SKU, Name, Tier, Weeks at 0` Routing note: `current_page == "dashboard"` calls `page_demand_planning()` (page_dashboard is defined but unreachable in the router). --- ### page_demand_planning (L1054–1421) **Filters:** - `Kategorija` (selectbox, key `dp_cat`) → `["All"] + sorted(sc["cat"].unique())` - `Oznaka` (selectbox, key `dp_ozn`) → `["All"] + OZNAKA_TIERS` - `XYZ class` (multiselect, key `dp_xyz`) → distinct `sc["xyz"]` values (default = all) - `From` (date_input, key `dp_date_from`) — defaults to `max_date - 26 weeks` - `To` (date_input, key `dp_date_to`) — defaults to `max_date + 14 weeks` - `SKU` (selectbox, key `dp_sku`) → `["All"] + sorted(sku_name_map keys)` - `Statistical forecast` (checkbox, key `wif_stat`) default True - `+ Planner factors` (checkbox, key `wif_fac`) default True - `+ VP/MP inputs` (checkbox, key `wif_vpmp`) default True - (Conditional) per-CW factor `number_input`s (keys `dpf_{cw_label}`) — 0.0–5.0 step 0.05 default 1.00 **Data sources:** - `load_sales_data()` → `sales_clean.csv` — `sku, name, cat, oznaka, xyz, year, week, yw, qty_total` - `load_demand_plan()` → `Polleo_Demand_Plan.xlsx` (`Demand Output - Total` / Demand Planning blocks) — fields: `cws`, `rows[].SKU, .Category, .baseline[], .factors[]` - `load_vp_mp_inputs()` → `vp_input.csv`, `mp_input.csv` — `sku` + `CW##` columns - `data/sku_uplift.csv` — `sku, year, week, promo_flag` (for the orange diamond promo marker) - `load_corrections()` / `save_corrections()` → `data/corrections.json` (`factors[sku][cw_num] = factor`) **Formulas / business logic:** - `yw_from / yw_to = iso_year*100 + iso_week`, filter history `sku_data["yw"]` between these - Hist aggregation: groupby (year, week, yw) on `qty_total` - Aggregated baseline per CW: `forecast_baseline[j] = Σ round(baseline)` across matched SKUs - Adjusted baseline: `forecast_adjusted[j] = Σ round(baseline × factor)` - VP/MP values: sum of `CW{n}` columns across selected SKUs - `forecast_total[j] = forecast_adjusted[j] + vp_values[j] + mp_values[j]` - What-if total (`whatif_total[j]`): - if `show_stat`: start with baseline; if `show_factors`: switch to adjusted - if `show_vpmp`: add vp + mp on top - History display: last 12 weeks before forecast start (`fc_start_cw = int(cws[0].replace("CW",""))`) - Promo weeks set is keyed by `int(year)*100 + int(week)` **st.metric calls:** none (info displayed in chart/tables) **Charts:** - `plotly_chart`: - blue actuals line (last 12 weeks) `#2F5496` with text labels - green forecast line `#548235` (solid or dashed) bridged from last actual - optional dotted green "Forecast (stat. only)" line if VP/MP present - optional VP bars `rgba(47,84,150,0.45)` width 0.4 - optional MP bars `rgba(255,107,53,0.45)` width 0.4 - `barmode="overlay"`, x-axis ordered actuals → forecast **Tables:** - `st.dataframe`: columns `Week, Actual, Forecast (stat.), VP input, MP input, Forecast (total), Promo` (🔶 if yw in promo_weeks) --- ### page_update_sales (L1422–1504) **Filters / inputs:** - `Upload sales file(s) (.xlsx)` (`file_uploader`, multiple) - `Wholesale add-on mode (e.g. RIZ-only upload)` (checkbox) - Per `REQUIRED_CSV` files: `csv_{fname}` (`file_uploader`, CSV) inside expander - "🔄 Process sales update" button **Data sources:** - Uploaded `.xlsx` files saved as `data/upload_{safe_name}.xlsx`; preview via `pd.read_excel(... nrows=5)` (auto-detects first non-Sheet1 sheet) - Triggers `update_sales.py` via `subprocess` with `cwd=DATA_DIR`, optional `--merge-wholesale` arg, env `PYTHONIOENCODING=utf-8`, timeout 300s **Formulas / business logic:** Delegates to external `update_sales.py` script. Captures stdout/stderr (last 2000/1000 chars) for display. On success: `clear_all_caches()`. **st.metric calls:** none **Charts:** none **Tables:** Preview of first 5 rows of each uploaded xlsx in an expander --- ### page_run_forecast (L1506–1706) **Filters / inputs:** - "🚀 Run forecast engine" button **Data sources:** - `file_status()` — checks `REQUIRED_CSV` and `planning_book` - Picks first `data/Polleo_Demand_Planning_Book*.xlsx` - Pre-flight: tries to open `OUTPUT_FILE` (`Polleo_Demand_Plan.xlsx`) in append mode to detect Excel lock - Backs up existing plan to `data/plan_history/Polleo_Demand_Plan_{ts}.xlsx`, prunes to last 5 - Calls `apply_corrections_to_xlsx()` (writes corrections to Planning Book) then `forecast_engine.py` as subprocess with progress streaming - On success calls `save_consensus_snapshot(f"CW{cw} auto-save")` **Formulas / business logic:** - `cw_start = cw + 1`, forecast horizon `CW{cw+1} → CW{cw+13}` - Stage progress mapping for log keywords: `Loading data→20`, `Cleaning→30`, `Samples→40`, `GBR MAE→60`, `BACKTEST→80`, `Building workbook→85`, `Saved→95`, `DONE→100` - Plan file age check: warns if `Polleo_Demand_Plan.xlsx` mtime > 60s after run **st.metric calls:** none (uses `st.info` blocks) - `c1.info`: "Forecast window CW{cw+1} → CW{cw+13}" - `c2.info`: "Corrections {n_fac} SKUs with factors" - `c3.info`: "Last run {date}" or "Never" **Charts:** none **Tables:** none (uses `st.code` for engine log tail) --- ### page_input (L1708–1779) — generic for VP and MP Signature: `page_input(sheet_name, label, key_prefix)`; called with `("Demand Input VP","VP (wholesale)","vp")` and `("Demand Input MP","MP (marketing/retail)","mp")`. **Filters / inputs:** - `Search SKU` (text_input, key `{key_prefix}_search`) - Per rtype (`on-top demand`, `regular increase`) `st.data_editor` with column `CW{w}` `NumberColumn(min_value=0, step=1, format="%d", width=70)` - "Save {rtype}" button per rtype **Data sources:** - `load_input_sheet(sheet_name)` — reads `Demand Input VP` / `Demand Input MP` sheet from `Polleo_Demand_Plan.xlsx`; returns `cws`, `rows[].SKU, .Name, .Category, .Type, .values[]` - `load_corrections()` / `save_corrections()` → `data/corrections.json` (`{vp|mp}[sku][f"{rtype}_{cw_num}"]`) **Formulas / business logic:** - Two segments by `r["Type"]` value: "on-top demand" and "regular increase" - Per row, per CW: if `saved[f"{rtype}_{cw_num}"]` use saved else use `r["values"][j]` - Save: iterate edited rows; non-zero values stored under correction key **st.metric calls:** none **Charts:** none **Tables:** Two `st.data_editor` per segment; columns: `SKU, Name, Cat, CW{w}` × len(cws) --- ### page_revenue (L1781–2454) **Filters:** - `View` (radio, key `rev_mode`) → `["Revenue","RUC (margin)"]` (RUC option only if `ruc_total` data present) - `Category` (selectbox, key `rev_cat`) → `["All"] + cats from plan rows` - `Source` (selectbox, key `rev_source`) → `["All","VP only","MP only"]` - `Months` (multiselect, key `rev_month`) → all months covering past 26 actual weeks + 13 forecast weeks; default = last 4 past + future - `Weeks` (multiselect, key `rev_week`) → CWs within selected months - `Include non-planned SKUs` (checkbox, key `rev_inc_nonplanned`) — only enabled when `cat_filter=="All"` **Data sources:** - `load_demand_plan()` — `Polleo_Demand_Plan.xlsx` (`cws`, `rows[].SKU, .Category, .baseline[], .factors[]`) - `load_sku_prices()` → `sku_prices.csv` — `sku, avg_sell_price` (delivered as dict `sku→price`) - `load_sales_data()` → `sales_clean.csv` — `sku, year, week, qty_retail, qty_webshop, qty_wholesale, qty_total, ruc_retail, ruc_webshop, ruc_wholesale, ruc_total` - `load_vp_mp_inputs()` → `vp_input.csv`, `mp_input.csv` - `_load_past_forecast_total_for_cw(_cur_w)` — reads `data/plan_history/Polleo_Demand_Plan_*.xlsx` for the current week's forecast (per-SKU units) - `compute_revenue_bridge(plan, snapshots)` + `load_consensus_snapshots()` → `data/consensus/snapshot_*.json` **Formulas / business logic:** - RUC per unit by channel computed over last 4 weeks of `sc`: - `retail = (ruc_retail+ruc_webshop)/(qty_retail+qty_webshop)` - `wholesale = ruc_wholesale/qty_wholesale` - `blended = ruc_total/qty_total` - `_get_rate(sku, channel)` returns RUC rate (RUC mode) or price (Revenue mode) - `ws_share[sku]` = wholesale fraction of channel mix over last 13 weeks - Baseline contribution per category, per CW: - `units = baseline[j] × factor[j]` - `cat_vals[c][j] += units × rate_blended` - `cat_vals_ws[c][j] += units × ws_share × rate_wholesale` - `cat_vals_retail[c][j] += units × (1-ws_share) × rate_retail` - VP on-top contribution: `cat_vp[c][j] += vp_units × rate_wholesale` - MP on-top contribution: `cat_mp[c][j] += mp_units × rate_retail` - Display line: - `"VP only"` → `total_base_ws + total_vp` - `"MP only"` → `total_base_retail + total_mp` - `"All"` → `total_base + total_vp + total_mp` - Gross-up ratios over last 13 weeks of history (when `include_nonplanned`): - `gross_up_ws = ws_all_eur / ws_planned_eur` - `gross_up_retail = rt_all_eur / rt_planned_eur` - `gross_up = total_all_eur / total_planned_eur` - Applied to forecast line ONLY, not VP/MP bars - Past actuals per (y,w): aggregate sales_clean filtered to selected months/weeks; per source_filter, compute revenue (`qty × price`) or RUC (`ruc_*` columns) - Current-week forecast: from archived plan_history file or fallback to first future week × gross-up - Margin % = `total_13wk_RUC / total_13wk_revenue × 100` (computed when RUC view) **st.metric calls:** - `"{N}-week {unit}"` = `f"€{total_13wk:,.0f}"` where unit ∈ {Revenue, RUC} - `"Avg weekly"` = `f"€{total_13wk / n_weeks_f:,.0f}"` - (RUC view) `"Margin %"` = `total_13wk / max(rev_total,1) * 100` - (Revenue view) `"Categories"` = `len(cat_vals)` **Charts:** - Main `plotly_chart`: combined axis past actuals → current → forecast - Actuals scatter (gray `#444444` squares) with labels - Forecast scatter (blue `#2F5496` or green `#006600` if RUC; dashed when past present) - VP bars `rgba(47,84,150,0.45)` (if All/VP only and >0) - MP bars `rgba(255,107,53,0.45)` (if All/MP only and >0) - Bridge `plotly_chart` (Waterfall) when ≥1 snapshot: `[prev_label, Volume, VP/MP inputs, New SKUs, Discontinued, This cycle]` with green/red/blue per measure **Tables:** - `st.dataframe` "Category breakdown": `Category, Baseline {unit}, VP on-top {unit}, MP on-top {unit}, Shown {unit}` --- ### page_sku_management (L2456–2697) **Filters / inputs (across 4 tabs):** VIEW tab: - `Search` (text_input, key `sku_mgmt_search`) - `Category` (selectbox, key `sku_mgmt_cat`) → `["All"] + plan.cat.unique()` - `OZNAKA` (selectbox, key `sku_mgmt_ozn`) → `["All"] + OZNAKA_TIERS` EDIT OZNAKA tab: - `Search SKU or name` (text_input, key `edit_search`) - `Category` (selectbox, key `edit_cat`) - `data_editor` (key `oznaka_editor`) — column `oznaka` is `SelectboxColumn(options=OZNAKA_TIERS)` ADD tab: - `Search by SKU or name` (key `add_search`) - `Category` (selectbox, key `add_cat`) - `data_editor` (key `add_editor`): `add` (checkbox), `oznaka` selectbox (default `03 BRONZE`), shows top 50 by 12wk volume REMOVE tab: - `Search SKU or name` (key `rm_search`) - `Category` (key `rm_cat`) - `data_editor` (key `remove_editor`): `remove` checkbox column **Data sources:** - `data/sku_plan_list.csv` — `sku, name, cat, oznaka, vpc` - Initialization: `Polleo_Demand_Planning_Book*.xlsx` sheets `Run Rate` (cols SKU, name, cat, oznaka) and `cijena po artiklu` (col E = price) - `data/sku_category_map.csv` (Add tab) - `data/sales_clean.csv` — for last-12w volume column in Add tab **Formulas / business logic:** - Initialization parses Planning Book starting row 4 → SKU rows - Add tab merges 12w volume: `last_12 = sc.sort(year,week).groupby("sku").tail(12)`; `vol = last_12.groupby("sku")["qty_total"].sum()` - Save writes `sku_plan_list.csv` **st.metric calls:** - `"Total SKUs"` = `len(plan)` - Per tier `"01 GOLD" / "02 SILVER" / "03 BRONZE"` = `len(plan[plan["oznaka"]==ozn])` **Charts:** none **Tables:** - View: `st.dataframe` columns `SKU, Name, Category, OZNAKA` - Add: `st.data_editor` columns `add, sku, name, cat, last_12wk_qty (if present), oznaka` - Remove: `st.data_editor` columns `remove, sku, name, cat, oznaka` --- ### page_kam_inputs (L2734–3286) Three tabs: Base inputs / Slack cycle / Combined view. **Filters / inputs (Base inputs tab):** - One `st.file_uploader` per roster member, key `kam_base_up_{key}` (xlsx only) - "💾 Save as base inputs" button (key `kam_base_save`) **Filters / inputs (Slack cycle tab):** - "📤 Distribute templates" / "🔔 Send nudges to pending" / "📥 Collect + combine responses" buttons - `Cycle active — start a new one anyway` (checkbox, key `slack_confirm_redo`) - "↩️ Delete last bot message" button - `Also delete bot messages from Slack channel` (checkbox, key `slack_also_delete`) - `I understand — wipe the active cycle.` (checkbox, key `slack_confirm_cancel`) - "🗑️ Cancel cycle now" button **Filters / inputs (Combined view tab):** - "🧹 Rebuild vp_input.csv & mp_input.csv from details" button **Data sources:** - `data/sku_plan_list.csv` — required - `data/kam_cm_config.json` — `kam_cm_config[{key}].{display_name, role, buyers[]}`, `slack_config.{bot_token, channel_id, deadline_day, deadline_hour}` - Template upload parsing: tries `_meta`-aware multi-sheet template (auto-detects Type col by header at row 4, CW columns) → falls back to `_parse_legacy_format` - Writes per-role detail CSV: `data/vp_input_detail.csv`, `data/mp_input_detail.csv` (columns `sku, type, kam, buyer, CW{w}…`) - `_rebuild_inputs_from_detail()` regenerates `vp_input.csv`, `mp_input.csv` (max-per-(sku,type)-across-buyers, sum types per sku) - `slack_agent.SlackAgent(data_dir=DATA_DIR)` — `distribute_all`, `send_nudges`, `collect_responses`, `combine_collected`, `delete_last_bot_message`, `cleanup_channel` - `data/slack_cycle.json` (current cycle state with submissions) - `_wipe_slack_cycle()` removes `slack_cycle.json`, collected response JSON files, and `VP_Input_*.xlsx` / `MP_Input_*.xlsx` / `vp_response_*.xlsx` / `mp_response_*.xlsx` **Formulas / business logic:** - Per-KAM merge on save: drop only the rows belonging to KAMs in upload; preserve past CW columns from old detail - "Max-wins dedupe": if same SKU appears across multiple buyer sheets, take MAX per (sku, type) across buyers, then SUM types per SKU - Diagnostic builds a table listing each person's detail file presence, total rows, rows-for-this-person, configured buyers, buyer breakdown **st.metric calls:** - `"Slack token"` = `"✅ set" / "❌ missing"` - `"Channel"` = `slack_cfg.get("channel_id")` or `"—"` - `"Deadline"` = `f"{deadline_day} {deadline_hour}:00"` - (Active cycle) `"Total"` / `"Submitted"` / `"Pending"` / `"Errors"` = counts of submission statuses **Charts:** none **Tables:** - Submissions: `Person, Role, Status, Submitted, SKUs, Units` + icon - Diagnostic: `Person, Role, Detail file, File exists, Total rows in file, Rows for this person, Configured buyers, Buyer breakdown in detail, Pre-fill outcome` - Combined view per role: full `vp_input.csv` / `mp_input.csv` dump inside expander --- ### page_download (L3559–3653) **Filters / inputs:** - "📥 Download Polleo_Demand_Plan.xlsx" download button - Per `REQUIRED_CSV` files: download_button inside expander - "🔄 Refresh forecast_for_supply.csv" button - `Pick a Polleo_Demand_Plan.xlsx` (`file_uploader`, key `upload_demand_plan`) - `VP / MP CSV update mode` (radio, key `upload_plan_mode`) → `["Override (xlsx fully replaces existing inputs)","Merge (preserve past CW history)"]` - "Apply uploaded plan" button (key `btn_apply_plan`) **Data sources:** - `OUTPUT_FILE` (`Polleo_Demand_Plan.xlsx`) - Each `REQUIRED_CSV` (full list of demand CSVs) - `data/forecast_for_supply.csv` (writer from `write_forecast_for_supply()`) - Uploaded xlsx → `_apply_uploaded_demand_plan(bytes, override=bool)` which backs up old plan, replaces live plan, extracts VP/MP from `Demand Input VP/MP` sheets and merges into `vp_input.csv` / `mp_input.csv` **Formulas / business logic:** Delegates writing to helper functions. **st.metric calls:** none **Charts:** none **Tables:** none (download buttons + status captions only) --- ### page_forecast_accuracy (L5294–5451) Four top-level tabs: Global FA / KAM-CM projections FA / Model-only FA / Live FA. Each tab uses `_render_fa_tab` (L4354). **Filters (page-level):** - `Apply Planner Factor` (toggle, key `fa_apply_factor`) default False **Filters (inside each `_render_fa_tab`):** - `Oznaka tier` (selectbox, key `{prefix}_ozn_tier`) → `["All"] + fa_data["oznaka"].unique()` - `XYZ class` (multiselect, key `{prefix}_xyz`) → distinct XYZ values - `Select Week(s)` (multiselect, key `{prefix}_week_sel`) → CW labels (default last 4) - `Exclude top-N SKUs` (number_input, key `{prefix}_excl_n`) min 0 max 50 - `Select Month` (selectbox, key `{prefix}_month_sel`) → available months - `Exclude top-N SKUs (monthly)` (number_input, key `{prefix}_excl_n_mo`) - (KAM/CM sub-tabs) `All`, `Both VP + MP`, `VP only`, `MP only`, `VP per buyer` - (Backtest section) `Backtest weeks` (number_input, key `bt_weeks`, 1–26 default 8) + "Re-run backtest" button **Filters (inside `_render_sku_drilldown`):** - `Select SKU` (selectbox, key `drill_sku_sel`) — sorted by total abs error **Filters (inside `_render_fa_top10_impactors`):** - `Week` (selectbox, key `fa_top10_week`) → CW labels **Data sources:** - `load_sales_data()` → `sales_clean.csv` (per-channel `qty_*`) - `_build_fa_dataset("total"/"kam_cm_projections"/"model_only", sc)` — uses: - `data/backtest_fa.csv` (`sku, year, week, forecast, actual` + optional `forecast_retail, forecast_wholesale, channel_mode`) - `data/forecast_log.csv` (`sku, target_year, target_week, forecast, run_date, run_id`) - `data/sku_plan_list.csv` (`sku, cat, oznaka, total_xyz`) - `_build_live_fa_data(sc)` → `forecast_log.csv` first-forecast-per-week - `_collect_vp_history()` / `_collect_mp_history()` → walks `data/consensus/snapshot_*.json` to extract per-(sku,year,week) on-top history - `_load_factor_history_map()` → `data/factor_history.csv` - `_build_per_buyer_vp_fa(sc)` — per-buyer VP commitment vs wholesale actual **Formulas / business logic:** - `error = abs(forecast - actual)`, `variance = forecast - actual`, `fa = max(0, 1 - error/actual)`, `bias = variance/actual` - Weekly tab uses **per-week-average**: `fa_avg = mean over weeks of (1 - |fc-ac|/ac)`; `fa_signed_avg = mean of fc/ac`; `bias_avg = mean of (fc-ac)/ac` - Monthly tab uses **sum-then-divide**: `fa = max(0, (1-|var|/ac)*100)` with monthly totals - "Top SKUs ruining FA" ranks by `abs_error` aggregated over selected weeks, head 15 - Tier and XYZ structural breakdown tables use same per-week-average rule - ISO month mapping: `datetime.strptime(f"{y}-W{w:02d}-1", "%G-W%V-%u").strftime("%B")` - Apply Planner Factor: `forecast_adjusted = forecast × factor_map[(sku, year, week)]`, clipped to existing FA columns - `_render_sku_drilldown`: - `stat_baseline_total = stat_retail + stat_wholesale` (if channel-split available) - `total_forecast = stat_baseline + vp_on_top` - `stat_err = stat_retail - retail_actual` (channel-split mode) or `stat_baseline - retail_actual` (legacy) - `vp_err = (stat_wholesale + vp_on_top) - qty_wholesale` (or `vp_on_top - qty_wholesale` legacy) - Attribution: "Spot on" if `|stat_err|+|vp_err| < 5%·actual`; "Stat over/under" / "VP over/under" / "Mixed" based on 1.5× margin - Channel mix dominance: ≥80% ws = "Wholesale-dominant"; ≥50% = "Wholesale-leaning"; ≥20% = "Mixed"; else "Retail-dominant" - `_render_fa_top10_impactors`: top 3 cats by actual volume for selected week; top 10 SKUs per cat by `|fc-ac|` **st.metric calls (per tab):** - `"Actuals"` = `tot_ac:,` - `"Forecast"` = `tot_fc:,` (delta `tot_var:+,`) - `"FA signed"` = `f"{fa_signed_overall:.1f}%"` - `"BIAS"` = `f"{bias_overall:+.1f}%"` - `"SKUs"` = `n_skus` - (Monthly) same 5 metrics with monthly totals - (Drill-down) `"Total forecast"`, `"Total actual"`, `"FA"`, `"Bias"` **Charts:** - (Drill-down) `plotly_chart`: stacked bars `stat_retail` (`#C5D9F1`) + `stat_wholesale` (`#9AC3DE`) + `vp_on_top` (`#2F5496`), plus actuals lines (`#548235` total, `#C00000` wholesale dashed) **Tables:** - Per-tab via `_render_fa_table`: per-category FA table with cols `Category, SKU-weeks, SKUs, Actuals, Forecast, Variance, FA %, FA signed %, BIAS %, Hit Rate %` - Top SKUs ruining FA: `SKU, Category, Tier, Actual, Forecast, Abs Error, FA%, FA signed%, Bias%, Share of total err` - Tier breakdown / XYZ breakdown tables - FA TOP 10 Impactors: 3 side-by-side tables per top-3 category with `SKU, Name, Actual, Forecast, FA%` - Drill-down detail table with channel split columns + attribution **Downloads:** Each table has a matching `.xlsx` download via `_fa_xlsx_download`. --- ### page_top30_watchlist (L5868–6063) **Filters / inputs:** - `Filter` (radio, key `wl_filter`) → `["All 30","CHECK only","Unreviewed only"]` - `Sort` (radio, key `wl_sort`) → `["Rank (volume)","Change % (big swings first)","CHECK first"]` - `data_editor` (key `watchlist_editor_{week_key}`) with `Reviewed` checkbox column - `Drill into SKU` (selectbox, key `wl_drill_sku`) — informational jump helper **Data sources:** - `_compute_watchlist_data()`: - `data/sales_clean.csv` — `sku, year, week, qty_wholesale` - `data/sku_plan_list.csv` — `sku, name, oznaka, cat, ws_xyz` (+ optional `ws_cv`) - `_read_demand_plan_review_and_fc(OUTPUT_FILE, skus)` — reads `Polleo_Demand_Plan.xlsx` for Review flag (col 45) and next-4 Baseline FC - `_load_watchlist_signoff(week_key)` / `_save_watchlist_signoff` → `data/watchlist_signoff.json` (`{week_key}.{sku}.{signed, at}`) **Formulas / business logic:** - Window = last `TRAILING_WEEKS` (26) weeks before current ISO week; defensively excludes future weeks - Rank: top `WATCHLIST_SIZE` (30) SKUs by `Σ qty_wholesale` over the 26-week window - `last4_ws_avg = mean(last 4 weeks wholesale qty for SKU)` - `next4_fc_avg = mean(next 4 weeks Baseline FC for SKU)` - `chg_pct = (next4_avg - last4_avg)/last4_avg × 100` (special-case: 999 when no baseline; NaN when both 0) - Review flag "CHECK" comes from forecast_engine (forecast avg ±25% off run rate avg) **st.metric calls:** - `"Top 30 wholesale volume (26w)"` = `f"{meta['top_ws_26w']:,}"` (delta `f"{pct_of_total:.0f}% of all wholesale"`) - `"⚠️ Flagged CHECK"` = `f"{meta['n_check']} / {WATCHLIST_SIZE}"` (delta_color inverse) - `"Reviewed ({week_key})"` = `f"{signed_n} / {WATCHLIST_SIZE}"` - `"No forecast yet"` = `f"{meta['n_no_plan']}"` **Charts:** Trail mini-LineChartColumn inside the data_editor (last 4 actual + next 4 forecast = 8 points) **Tables:** - `st.data_editor` columns: `#, SKU, Tier, XYZ, Name, 26w ws, Trail (last 4 → next 4), Last 4 avg, Next 4 avg, Δ %, Flag, Reviewed` --- ### page_consensus_plan (L6066–6132) **Filters / inputs:** - `Snapshot label` (text_input, key `snap_label`) default `f"CW{cw} S&OP lock"` - "💾 Save snapshot" button - Per-snapshot "🗑️" delete button (key `del_snap_{i}`) - `Snapshot A` / `Snapshot B` (selectbox, keys `cmp_a`, `cmp_b`) - "Compare" button **Data sources:** - `load_demand_plan()` → live `Polleo_Demand_Plan.xlsx` - `load_consensus_snapshots()` → `data/consensus/snapshot_*.json` (`label, timestamp, n_skus, total_rev, filename, rows[]`) - `save_consensus_snapshot(label)` writes new JSON **Formulas / business logic:** - Compare: revenue and SKU diff between snapshots A and B - `added = b_skus - a_skus`, `removed = a_skus - b_skus` **st.metric calls (in compare panel):** - `"Revenue change"` = `f"€{a['total_rev']:,} → €{b['total_rev']:,}"` (delta `f"{b-a:+,}"`) - `"SKU count"` = `f"{a['n_skus']} → {b['n_skus']}"` (delta `f"{b-a:+}"`) **Charts:** none **Tables:** none (one-line markdown per snapshot) --- ### page_sop_meeting (L6502–6620) Calls `_render_promo_one_slider()` first, then a global S&OP summary. **Filters / inputs (`_render_promo_one_slider`, L6135):** - `Month` (selectbox, key `promo_month`) → built from `vp_input.csv` + `mp_input.csv` CW columns - `Top N` (number_input, key `promo_topn`) min 5 max 30 default 10 - `Rank by` (selectbox, key `promo_rank`) → `["Revenue €","RUC €","Units"]` - "📥 Download S&OP slide (.pptx)" download_button **Filters / inputs (S&OP summary):** - Inside "➕ Add decision" expander: `Decision` (text_input, `new_dec`), `Owner` (text_input, `new_owner`), `Status` (selectbox, `new_status`) → `["Pending","In progress","Done","Overdue"]`; "Add" button **Data sources:** - `_render_promo_one_slider`: `vp_input.csv`, `mp_input.csv`, `sku_plan_list.csv` (name, cat), `sku_prices.csv`, `sales_clean.csv` (last 8 weeks RUC), optional `data/sop_template.pptx` - `load_sales_data()`, `load_demand_plan()`, `load_sku_prices()` - `compute_accuracy_metrics(sc)`, `compute_exceptions(sc)`, `compute_plan_revenue(plan, sku_prices)` - `data/sop_decisions.json` (decision tracker) **Formulas / business logic:** - Promo one-slider: - `target_cws = month_to_cws[sel_month]` - `vp_units = Σ vp_input[sku, cw] for cw in target_cws` - `mp_units = Σ mp_input[sku, cw] for cw in target_cws` - `total_units = vp_units + mp_units` - `Revenue € = total_units × price` - `RUC € = total_units × ruc_per_unit` (blended from last 8w `ruc_total/qty_total`) - Rank by selected column, head Top N - Global: - `total_rev = compute_plan_revenue(plan, sku_prices)` (Σ baseline × factor × price across 13w) - `fa_pct = acc_df["fa"].mean() × 100` - `bias_pct = Σ bias / max(Σ actual, 1) × 100` - `weekly_rev[j] = Σ_SKUs baseline[j] × factor[j] × price` - Risk rows: includes Bronze/Silver/Gold tiers with `fa < 0.4` **st.metric calls:** - (Promo) `"Promo SKUs"` = `len(df_out)`; `"Total units"` = `total_units`; `"Revenue €"` = `total_rev`; `"RUC €"` = `total_ruc` - (Summary) `"13-wk revenue"` = `f"€{total_rev:,.0f}"`; `"Forecast accuracy"` = `f"{fa_pct:.1f}%"`; `"Bias"` = `f"{'+'…}{bias_pct:.1f}%"`; `"Exceptions"` = `n_exceptions` **Charts:** - Revenue projection bar chart: weekly revenue for each `CW` in plan, blue `#2F5496` **Tables:** - Promo one-slider `st.dataframe`: `SKU, Name, Category, VP units, MP units, Total units, Revenue €, RUC €` - Top risks `st.dataframe`: `Risk, Impact, Action` - Decisions `st.dataframe`: `Decision, Owner, Status, Created` **Downloads:** PPTX one-slide with KPI cards + table (cols `SKU, Name, Category, Units, Revenue €, RUC €`) via `_build_promo_pptx` --- ### page_erp_promo (L6626–6736) **Filters / inputs:** - `Upload rabatne.xlsx` (file_uploader, key `erp_upload`) - "🔄 Process promo data" button (key `btn_build_erp`) - "🔄 Recalculate uplift from ERP data" button (key `btn_recalc`) **Data sources:** - `data/erp_promo_calendar.csv` — `sku, year, week, …` - Uploaded `data/rabatne.xlsx` - `data/sku_plan_list.csv` (to filter to planning SKUs) - External scripts: `build_erp_promo.py` (rabatne.xlsx → erp_promo_calendar.csv), `recalc_uplift_erp.py` (rebuilds `sku_uplift.csv` and `cat_uplift.csv`; backs up old to `*_old.csv`) **Formulas / business logic:** delegated to external scripts. UI just reports counts and previews. **st.metric calls:** none (uses `st.success/st.caption` for counts) **Charts:** none **Tables:** - Upcoming promos preview: full erp_promo_calendar filtered to `(year>cy) OR (year==cy AND week>=cw)`, sorted by year/week/sku, head 50 --- ## SUPPLY MODULE ### page_supply_dashboard (L7267–7303) **Filters / inputs:** none **Data sources (via `_sup_data_guard`):** - `sup_load_stock()` → `stock.csv` (WH only) — `sku, on_hand` - `sup_load_forecast()` → `forecast_for_supply.csv` — `sku, year, week, demand` - `sup_load_incoming()` → `incoming_supply.csv` — `sku, year, week, qty` - `sup_load_master()` → `supply_master.csv` merged with `sku_plan_list.csv` — `sku, name, category, tier, supplier, lead_time_weeks, moq` - `sup_params()` → safety stock params from session state (`fa, z, target_coverage, horizon`) - `sup_build_coverage(...)` produces `cov_rows, summary, totals` **Formulas / business logic:** via `sup_build_coverage`: - `safety_stock = z × avg_demand × (1-fa) × sqrt(LT)` - `rop = avg_demand × LT + ss` - Weekly walk-forward: `closing[j] = stock[j-1] - demand[j] + incoming[j]` - `coverage_wk = closing/avg_weekly_demand` per week - `flag ∈ {order_now, order_next_week, ok}` based on stockout timing vs LT **st.metric calls:** - `"SKUs in scope"` = `len(summary)` - `"🔴 Order now"` = `(summary["flag"]=="order_now").sum()` - `"🟡 Next week"` = `(summary["flag"]=="order_next_week").sum()` - `"Avg coverage wk"` = mean of `summary["coverage_wk_now"]` (∞ stripped) **Charts:** none **Tables:** - "By category" `st.dataframe`: `skus, order_now, order_next, avg_cov` rolled up - "By tier" `st.dataframe`: same columns --- ### page_supply_projection (L7306–7679) **Filters / inputs:** - `Category` (multiselect, key `proj_cat`) → all `master["category"]` - Per-KAM expander, per-KAM checkbox `proj_vpkam_{kam}` - Per-buyer checkbox `proj_vpbuyer_{kam}_{b}` (only when KAM included and buyer present in `vp_input_detail.csv`) **Data sources:** - Same as dashboard plus: - `sup_load_stock_stores()` → `stock_stores*.csv` (long format if available) - `data/sku_costs.csv` — `sku, cost_price` - `data/vp_input_detail.csv` — `sku, kam, buyer, CW{w}` per-row - `data/kam_cm_config.json` (for KAM → buyers map) - `load_sales_data()` for long-tail run-rate proxy **Formulas / business logic:** - Long-tail extension: SKUs with stock but no forecast get a flat demand of `last 13w mean qty_total` over the projection horizon - Per-KAM/buyer VP subtraction: melt `vp_input_detail.csv` excluded rows over CW columns; subtract from `forecast["demand"]` (clip ≥0) - Aggregate per (year, week, yw): `opening = Σ stock`, `demand = Σ demand`, `incoming = Σ incoming`, `closing = Σ closing`, `closing_value = Σ (cost_price × closing)` - Current stock units = `Σ stock["on_hand"]` (after merge with stores), value = `Σ on_hand × cost_price` **st.metric calls:** - `"Current stock (units)"` = `int(current_units):,` - `"Current stock (€)"` = `f"{current_value:,.0f}"` - `"Demand next {N}w"` = `int(total_demand):,` - `"Incoming next {N}w"` = `int(total_incoming):,` **Charts:** - Combined `plotly_chart`: red demand bars (negative), green incoming bars (positive), blue closing-stock line `#2F5496`, `barmode="relative"` - Optional `plotly_chart` "Stock value roll-forward": orange `#B8860B` closing stock value line **Tables:** - Weekly detail (in expander) `st.dataframe`: `Week, Opening, Demand, Incoming, Closing, Closing value €` --- ### page_supply_scenarios (L7681–8108) **Filters / inputs:** - `Supplier(s) in scope` (multiselect, key `scen_suppliers`) — defaults to top-3 by € next 8 weeks - `Window start (CW)` / `Window end (CW)` (number_input, keys `scen_win_start`/`scen_win_end`) - `Stock basis` (radio, key `scen_basis`) → `["WH only","WH + stores"]` - Preset buttons: "🍃 Gentle", "⚖️ Balanced", "🔥 Aggressive" - `Postpone trigger (weeks of current cover)` (slider, key `scen_postpone_trigger`, 1–12) - `Postpone delay (weeks)` (slider, key `scen_postpone_delay`, 1–12) - `Enable cancel` (checkbox, key `scen_cancel_on`) - `Cancel threshold (weeks of current cover)` (slider, key `scen_cancel_threshold`, min=postpone_trigger to 52) - `Target ceiling (€)` (number_input, key `scen_target`, 1M–20M default 5M) - `Filter by action` (multiselect, key `scen_action_filter`) → `["CANCEL","POSTPONE","REVIEW","PRODUCE"]` **Data sources:** same as projection, plus `vp_input_detail.csv` not used here. **Formulas / business logic:** - For each PO in scope: - `cover_now = real_weeks_cover(stock_now, demand_series[next 13w starting CW{cw+1}])` - `cover_post = real_weeks_cover(stock_now+qty, demand_series[next 13w starting PO_week])` - `action_raw = scen_classify(cover_now, cover_post, cancel_threshold, postpone_trigger)`: - CANCEL if `cover_now ≥ cancel_threshold` - POSTPONE if `cover_now ≥ postpone_trigger` (else not CANCEL) - PRODUCE/REVIEW otherwise - Sanity check `scen_sanity_check`: if stacked POSTPONEs cause stockout, latest-week PO flips back to PRODUCE - Cash projection: weekly per-SKU `(stock_open) + Σ incoming - Σ demand × cost`; anchored at current WH+stores stock value - `cover_vs_lt = cover_now_wk - lead_time_wk` **st.metric calls:** - `"Baseline peak €"` = `f"€{base_peak:,.0f}"` - `"Scenario peak €"` = `f"€{scen_peak:,.0f}"` (delta `scen_peak - base_peak`, inverse color) - `"€ unlocked (cancel + postpone)"` = `f"€{total_impact:,.0f}"` - `"Under target?"` = `"✅ yes" / "❌ no"` - `"POs affected"` = count of CANCEL+POSTPONE **Charts:** - `plotly_chart`: baseline line (gray dotted) + scenario line (`#2F5496`) + target hline (`#c0392b` dashed) **Tables:** - Per-PO `st.dataframe`: `sku, Name, tier, category, supplier, PO week, Qty, Cost €, € value, Lead time (wk), Cover now (wk), Cover − LT, Cover if accepted (wk), Action, New week` **Downloads:** Cancel list xlsx, Postpone list xlsx with cols `sku, name, tier, supplier, original_delivery_week, qty, cost_price, eur_value, action, new_delivery_week_or_blank, our_notes` --- ### page_supply_coverage (L8111–8160) **Filters / inputs:** - `Supplier` (selectbox) → `["All"] + summary.supplier.unique()` - `Category` (selectbox) → `["All"] + summary.category.unique()` - `Tier` (selectbox) → `["All"] + summary.tier.unique()` - `Flag` (selectbox) → `["All","Needs action","order_now","order_next_week","ok"]` - `Pick SKU` (selectbox, key `sup_cov_sel`) — for SKU detail grid **Data sources:** via `_sup_data_guard()` (stock/forecast/incoming/master) + `sup_build_coverage(...)`. **Formulas:** same as dashboard. **st.metric calls:** none (caption shows count, horizon, FA%, Z) **Charts:** none **Tables:** - Summary `st.dataframe`: sortable by `flag, coverage_wk_now`; height 380 - SKU weekly grid (transposed): rows `Stock, Demand, Incoming, Closing, Coverage wk`, columns = ISO weeks - Info line for selected SKU: `Safety stock, ROP, LT, MOQ, Suggested order qty` --- ### page_supply_alerts (L8163–8195) **Filters / inputs:** none **Data sources:** same as coverage. **Formulas:** `flag.isin(["order_now","order_next_week"])` subset; flag_label uses `🔴 Order now` / `🟡 Next week`. **st.metric calls:** none **Charts:** none **Tables:** Per supplier (groupby) inside expander, `st.dataframe`: `flag_label, sku, name, tier, lt_weeks, on_hand, avg_weekly_demand, safety_stock, coverage_wk_now, moq, suggested_qty` --- ### page_supply_order_entry (L8198–8257) **Filters / inputs:** - `data_editor` (key `sup_order_editor`) with `order_qty` (NumberColumn min 0 step 100) and `note` (TextColumn); `suggested`, `moq` are disabled NumberColumns - "💾 Save draft" button **Data sources:** - Same supply data via `_sup_data_guard` - `sup_load_orders()` / `sup_save_orders(orders)` → `data/orders_draft.json` keyed by SKU with `{qty, supplier, moq, note, saved_at, cw}` **Formulas / business logic:** Pre-fills `order_qty` from saved order or `suggested_qty`. Save writes each row to orders_draft.json with timestamps. **st.metric calls:** none **Charts:** none **Tables:** `st.data_editor` columns: `sku, name, supplier, flag, suggested, moq, order_qty, note` --- ### page_supply_download (L8260–8288) **Filters / inputs:** "📥 Download Supply plan (Excel)" download button **Data sources:** `sup_load_orders()` → `orders_draft.json` **Formulas / business logic:** Filters to qty > 0, writes single xlsx sheet `Supply_plan` via openpyxl. **st.metric calls:** none **Charts:** none **Tables:** Plain dataframe of orders (all columns from orders dict). --- ### page_supply_upload (L8291–8467) **Filters / inputs:** 5 separate file_uploaders + 1 generation button: - `stock.csv or .xlsx` (key `sup_up_stock`) - Per-country: `{country} store stock ({fname})` (key `sup_up_stock_stores_{country.lower()}`) — defined by `STORE_STOCK_FILES` constant - `incoming_supply.csv or .xlsx` (key `sup_up_in`) - `supply_master.csv or .xlsx` (key `sup_up_mas`) - "🔄 Generate from current Demand Plan" button **Data sources / writes:** - `stock.csv`: auto-normalises Croatian column names (`šifra`/`sifra`/`Šifra` → `sku`; `zaliha`/`stanje` → `on_hand`); groups by sku summing on_hand; drops zero rows - Store stock long format (`sku, store_code, store_name, on_hand`) saved to per-country file; excludes `jedinica==1` (WH); dedupes (sku, store_code) summing on_hand - Legacy 2-col format still accepted but warns user - `incoming_supply.csv`, `supply_master.csv`: pass-through write - `forecast_for_supply.csv` via `write_forecast_for_supply()` **Formulas / business logic:** column normalisation only; otherwise pure I/O. **st.metric calls:** none **Charts:** none **Tables:** none (status captions only) --- ### page_supply_moq (L8668–8681) Wrapper around `supply_reference_editor`. **Filters / inputs:** - File uploader (key `moq_upload`) for `sku, moq` xlsx/csv - Editor key `moq_editor` (column: `moq` NumberColumn min 0 step 100) **Data sources:** `data/supply_master.csv` (storage), `data/sku_plan_list.csv` (decoration with name, category, tier) **Formulas / business logic:** Upserts MOQ per SKU without disturbing other columns. **st.metric / Charts:** none **Tables:** Merged master `st.data_editor` with `name, category, tier` disabled + editable `moq` column. --- ### page_supply_logistics (L8699–8722) Wrapper around `supply_reference_editor` for `sku_logistics.csv`. **Filters / inputs:** - File uploader (key `logistics_upload`) - Editor for cols `pcs_per_transport_pkg`, `transport_pkgs_per_pallet`, `pcs_per_pallet` **Data sources:** `data/sku_logistics.csv`, `data/sku_plan_list.csv` **Formulas / business logic:** `_logistics_badges` flags rows where `pcs_per_pallet ≠ pcs_per_transport_pkg × transport_pkgs_per_pallet` (tolerance 0.5) **Tables:** Per-SKU `st.data_editor` similar pattern. --- ### page_supply_costs (L8927–8994) Wrapper around `supply_reference_editor` for `sku_costs.csv` plus an "Impute missing prices" panel. **Filters / inputs:** - File uploader (key `costs_upload`) for `sku, cost_price, ruc` - Editor for cost_price (NumberColumn min 0 step 0.01 format `%.2f`) and ruc - "Run analysis" button (key `costs_impute_run`) - "✅ Apply suggestions to sku_costs.csv" button (key `costs_impute_apply`) **Data sources:** - `data/sku_costs.csv` — `sku, cost_price, ruc` - `data/sku_subcat_map.csv` (`sku, name, sub_cat, grup`), `sku_category_map.csv`, `sku_plan_list.csv` (for imputation) **Formulas / business logic (impute_missing_costs, L8775):** Tries each strategy and stops at first with ≥3 neighbours: 1. Same sub_cat + grammage ±25% → median `cost_price` 2. Same sub_cat + keyword overlap ≥2 → median `cost_price` or `median(per_gram) × target_grams` if grammage known 3. Same sub_cat → median `per_gram × target_grams` (grammage known) 4. Same cat → median `per_gram × target_grams` (grammage known) 5. Same cat → flat median `cost_price` (grammage unknown) - `_parse_grammage(name)`: extracts largest sensible weight in grams from product name via regex (`kg`, `g`, `ml`, `l`, `oz`, `lb`) - `per_gram = cost_price / grams` **st.metric / Charts:** none **Tables:** - Imputation suggestions `st.dataframe`: `sku, name, category, sub_cat, grams, suggested_cost, method, neighbours, sample_skus` - Standard reference editor data_editor **Downloads:** "📥 Download suggestions (Excel)" → `imputed_prices.xlsx` --- ### page_supply_inventory_health (L9053–9372) **Filters / inputs:** - `Service level — Gold` (slider, key `sl_gold`, 80–99 default 98) - `Service level — Silver` (slider, key `sl_silver`, 80–99 default 95) - `Service level — Bronze` (slider, key `sl_bronze`, 80–99 default 92) - (FA-aware expander) `Weeks of history needed to trust per-SKU FA` (slider, key `ih_fa_min_weeks`, 2–12 default 6) - (FA-aware expander) `Global fallback FA (%)` (slider, key `ih_fa_global`, 30–95 default 70) - `Tier` (selectbox, key `ih_tier`) → tier values - `Category` (selectbox, key `ih_cat`) → cat values - `Supplier` (selectbox, key `ih_sup`) → supplier values - `Status` (selectbox, key `ih_status`, default index=1 = `overstock`) → `["All","overstock","stockout_risk","balanced"]` **Data sources (via `_load_inventory_inputs`):** - `stock.csv` (`sku, on_hand`) - `supply_master.csv` (`sku, supplier, lead_time_weeks, moq`) - `incoming_supply.csv` (`sku, qty`) - `forecast_for_supply.csv` (long or wide CW format) - `sku_costs.csv` (`sku, cost_price`) - `sku_prices.csv` (`sku, avg_sell_price`) - `sku_plan_list.csv` (`sku, name, oznaka, cat, total_cv`) - `sku_logistics.csv` (loaded but not used here) - `_model_only_fa_per_sku()` — per-SKU measured FA from model-only FA view **Formulas / business logic:** - `z` from service level via `scipy.stats.norm.ppf` (or lookup fallback) - Per-SKU FA: `measured` if `n_weeks ≥ fa_min_weeks` else `global` fallback; clipped to `[0.30, 0.95]` - `avg_weekly = Σ demand / n_weeks` of `forecast_for_supply` series - `sigma_weekly = avg_weekly × cv` where `cv` from `total_cv` (default 0.5) - `forecast_err_sigma = avg_weekly × (1 - fa_used)` - `combined_sigma = sqrt(sigma_weekly² + forecast_err_sigma²)` - **safety_stock = z × combined_sigma × sqrt(LT)** - `safety_weeks = safety_stock / avg_weekly` - `max_cover_needed = 1.5 × LT + safety_weeks` - `max_on_hand = demand_over(max_cover_needed, series)` (sums first N weeks of weekly demand, partial trailing week prorated, extrapolates past horizon with mean) - `cover_now = on_hand / avg_weekly` - `excess_units = max(0, on_hand - max_on_hand)` - `shortage_units = max(0, safety_stock - on_hand)` - `excess_eur = excess_units × cost_price` (or sell_price proxy if costs missing) - Status: `overstock` if excess>0, else `stockout_risk` if shortage>0, else `balanced` **st.metric calls:** - `"€ frozen in overstock ({nabavna|⚠️ sell (proxy)})"` = `f"€{total_overstock_eur:,.0f}"` - `"Overstock SKUs"` = `n_overstock` - `"Stockout-risk SKUs"` = `n_short` - `"Balanced SKUs"` = `n_balanced` **Charts:** none **Tables:** - Main `st.dataframe`: `sku, name, tier, category, supplier, on_hand, incoming, avg_weekly_fc, cv, lt_weeks, fa_used, fa_source, safety_weeks, max_cover_needed, cover_now, excess_units, excess_eur, status` - "Overstock € by supplier" rollup `st.dataframe`: `supplier, skus, excess_units, excess_eur` **Downloads:** Inventory health xlsx with sheets `Inventory health` + `By supplier` --- ### page_supply_store_overstock (L9375–9653) HR-only per-store overstock. **Filters / inputs:** - `Tier filter` (multiselect, key `store_ov_tier`) → `["01 GOLD","02 SILVER","03 BRONZE"]` (default all) - `SL Gold` (slider, key `store_ov_sl_gold`, 80–99 default 98) - `SL Silver` (slider, key `store_ov_sl_silver`, 80–99 default 95) - `SL Bronze` (slider, key `store_ov_sl_bronze`, 80–99 default 92) - `Show insufficient-history rows` (checkbox, key `store_ov_show_insuf`, default False) **Data sources:** - `sup_load_stock_stores_long(country="HR")` → `stock_stores.csv` HR (long format `sku, store_code, store_name, on_hand`) - `data/sales_detailed.csv` — `drzava, tip_dok, sku, jedinica, year, week, kolicina` - `data/erp_promo_calendar.csv` — `sku, year, week, is_erp_promo` - `data/sku_plan_list.csv` (`tier`=oznaka, `category`=cat, `name`) - `data/sku_costs.csv` (`sku, cost_price`) - `data/sku_prices.csv` (`sku, avg_sell_price`) **Formulas / business logic:** - Scope: HR + RCM (retail POS) only; `jedinica != 1` (excludes WH) - Promo exclusion: drop sales rows where `(sku, year, week)` has `is_erp_promo==1` in ERP calendar - Per (sku, store): `avg_weekly = mean(qty)`, `sigma = std(qty)`, `weeks_with_sales = count` - Optimal stock formula (LT=1): - `safety_stock = z × sigma × sqrt(LT)` where `z` per-tier from `norm.ppf(service_level)` - `cycle_stock = LT × avg_weekly` - `optimal_stock = safety_stock + cycle_stock` - Insufficient history: `weeks_with_sales < MIN_WEEKS` (4) - `overstock_units = max(0, on_hand - optimal_stock)` (forced to 0 when insufficient_history) - `overstock_eur_cost = overstock_units × cost_price` - `overstock_eur_sell = overstock_units × sell_price` **st.metric calls:** - `"Overstock SKU×store pairs"` = `f"{len(over):,}"` (caption: `of {len(merged):,} total`) - `"Overstock units"` = `f"{total_units:,}"` - `"Overstock € (cost)"` = `f"€{total_cost:,.0f}"` - `"Overstock € (sell)"` = `f"€{total_sell:,.0f}"` - `"Insufficient history"` = `f"{insuff}"` **Charts:** none **Tables (4 tabs):** - "By tier" `st.dataframe`: `Pairs, Units, € cost, € sell` grouped by tier - "By category" `st.dataframe`: same grouped by category - "By store" `st.dataframe`: same grouped by `(store_code, store_name)` - "Detail (SKU × store)" `st.dataframe`: `sku, Name, tier, category, Store #, Store, On hand, Avg/wk, σ, Weeks (non-promo), Safety stock, Cycle stock, Optimal, Overstock units, Cost €, Sell €, Overstock € cost, Overstock € sell, Insuff. history` **Downloads:** XLSX with sheets `detail, by_tier, by_store` --- ### page_supply_settings (L9656–9683) **Filters / inputs:** - `Forecast accuracy (FA)` (slider, 0.30–0.95 step 0.05) - `Service level factor (Z)` (slider, 1.00–2.50 step 0.05) - `Target coverage after reorder (weeks)` (slider, 4–16, step 1) - `Projection horizon (weeks)` (slider, 8–26, step 1) **Data sources:** `st.session_state["sup_params"]` (initialized from `SUP_DEFAULTS`) **Formulas / business logic (displayed via `st.latex`):** - `SS = Z × D̄ × (1-FA) × √LT` - `ROP = D̄ × LT + SS` - `Q_order = max(MOQ, T_cov × D̄ - S_on_hand - S_incoming_in_LT)` **st.metric / Charts / Tables:** none --- ### page_supply_coverage_workbook (L10239–10356) **Filters / inputs:** "🔄 Refresh coverage workbook now" button (key `cov_refresh`) **Data sources:** - Template `data/coverage_template.xlsx` (must be present) - All source files read by `_refresh_coverage_workbook(out_path)`: - `stock.csv`, `stock_stores.csv`, `stock_stores_at.csv`, `stock_stores_slo.csv` - `incoming_supply.csv`, `vp_input.csv`, `mp_input.csv`, `Polleo_Demand_Plan.xlsx` **Formulas / business logic:** - Rewrites 4 source sheets (Stock, Demand, Demand on-top, Incoming supply); keeps POKRIVENOST sheet formulas/colors untouched - `_patch_pokrivenost(ws, cy, cw)`: hides past-week columns, writes current-week stock as hard value, extends Order Status with `🔵 Pull in / 🔵 Pull in + order more` - Reads week anchor positions from the template (`Demand col F = Week N`, `Incoming col B = Week N`, `On-top col F = CW N`) **st.metric / Charts:** none **Tables:** - Source files status `st.dataframe`: `File, Status, Last modified, Description` - List of recent `Coverage_W*.xlsx` outputs (in expander) **Downloads:** Generated `Coverage_W{cw}.xlsx` --- ## NPD MODULE ### page_npd_upload (L10388–10548) **Filters / inputs:** - `Upload .xlsx or .csv` (file_uploader, key `npd_up`) - "💾 Save uploaded SKUs" button (key `npd_save_up`) - Manual add form `npd_manual`: - `SKU code *` (text_input, key `npd_man_sku`) - `Product name *` (text_input, key `npd_man_name`) - `Category` (text_input, key `npd_man_cat`) - `Notes` (text_input, key `npd_man_notes`) - `Launch year` (number_input, key `npd_man_ly`, 2024–2030 default `cy`) - `Launch ISO week` (number_input, key `npd_man_lw`, 1–53 default `cw`) - `Projection horizon (weeks)` (number_input, key `npd_man_h`, 1–52 default 13) - `Projected retail (units / week)` (number_input, key `npd_man_pr`) - `Projected wholesale (units / week)` (number_input, key `npd_man_pw`) - "➕ Add SKU" form_submit_button - "Pick SKU to delete" (selectbox, key `npd_del`); "Confirm delete" button (key `npd_del_btn`) - "⬇️ Download blank template (Excel)" download_button **Data sources:** - `data/npd_skus.csv` — `sku, name, category, launch_year, launch_week, proj_retail_weekly, proj_wholesale_weekly, proj_horizon_weeks, added_date, notes` (via `_load_npd` / `_save_npd`) **Formulas / business logic:** - Upload: incoming SKUs replace same-keyed rows in `df_old`; column defaults fill missing fields; saved after preview - Manual add: dedupes by sku, sets `added_date = today` **st.metric / Charts:** none **Tables:** - Preview of uploaded df (head 20) - Current NPD list `st.dataframe`: full `NPD_COLS` - Template starter xlsx (col widths set programmatically) --- ### page_npd_list (L10551–10705) **Filters / inputs:** - `Category` (selectbox, key `npd_list_cat`) → `["All"] + categories from NPD list` - `Only SKUs with sales` (checkbox, key `npd_list_live`) - "⬇️ Download Excel" download_button **Data sources:** - `_load_npd()` → `data/npd_skus.csv` - `load_sales_data()` → `sales_clean.csv` — `sku, year, week, qty_retail, qty_webshop, qty_wholesale, qty_total` **Formulas / business logic:** - For each NPD SKU: - Filter sc to `sku` and `year*100+week >= launch_year*100 + launch_week` (if launch set) - `act_r = Σ qty_retail + Σ qty_webshop` - `act_w = Σ qty_wholesale` - `act_total = Σ qty_total` - `weeks_with_data = nunique(year, week)` - `proj_total_period = (proj_r + proj_w) × horizon` - `target_to_date = (proj_r + proj_w) × weeks_with_data` - `attainment = act_total / target_to_date × 100` - Color-coding (`Attainment %`): green ≥90, yellow ≥70, red >0, blank otherwise **st.metric calls:** - `"NPD SKUs"` = `n_skus` - `"Already live"` = `n_live` - `"Total actual units"` = `f"{total_actual:,}"` - `"Avg attainment %"` = `f"{avg_attainment:.1f}%"` if any live else `"—"` **Charts:** none **Tables:** - Main styled `st.dataframe`: `SKU, Name, Category, Launch, Proj retail/wk, Proj WS/wk, Proj total ({horizon}w), Weeks live, Actual retail, Actual WS, Actual total, Attainment %` **Downloads:** `npd_tracker_{year}w{week:02d}.xlsx` (single sheet `NPD tracker`, blue header at row 3, freeze pane A4, autofilter) --- ## Cross-cutting helpers worth flagging These are not page functions but every page depends on them. They define data loading semantics the new stack must replicate: - `load_sales_data()` — wraps `sales_clean.csv`; provides `yw = year*100+week`, plus `xyz` decoration from `sku_plan_list.csv` - `load_demand_plan()` — reads `Polleo_Demand_Plan.xlsx` "Demand Planning" blocks; returns `{cws[], rows[{SKU, Category, baseline[], factors[]}]}` and falls back to per-block reconstruction if `Demand Output - Total` is empty/uncached - `load_sku_prices()` — `sku_prices.csv` → `dict[sku, avg_sell_price]` - `load_vp_mp_inputs()` — `{vp, mp}` DataFrames from `vp_input.csv` / `mp_input.csv` - `load_input_sheet(sheet_name)` — `Demand Input VP/MP` sheet from the plan xlsx, returns `cws[]` and `rows[{SKU, Name, Category, Type, values[]}]` - `compute_accuracy_metrics(sc)` — per-SKU FA from latest backtest_fa - `compute_exceptions(sc)` — spike (>30% WoW) and zero-sales (3+ weeks) lists - `compute_revenue_bridge(plan, snapshots)` — current vs previous snapshot revenue waterfall - `_sup_data_guard()` — bundles `stock, forecast, incoming, master` for supply pages; shows error if any required file missing - `sup_build_coverage(...)` — drives all supply coverage flag logic; produces `cov_rows`, `summary`, `totals` - `compute_plan_revenue(plan, sku_prices)` — Σ `baseline × factor × price` across 13w - `_load_factor_history_map()` — `data/factor_history.csv` → `{(sku, target_year, target_week): factor}` - `_apply_factor_to_fa(fa_df, factor_map)` — multiplies `forecast` by factor; recomputes derived columns Constants used throughout: `OZNAKA_TIERS = ["01 GOLD","02 SILVER","03 BRONZE"]`, `FORECAST_WEEKS = 13`, `TRAILING_WEEKS = 26`, `WATCHLIST_SIZE = 30`, `SUP_DEFAULTS`, `STORE_STOCK_FILES = [("HR","stock_stores.csv"), ("AT","stock_stores_at.csv"), ("SLO","stock_stores_slo.csv")]`, `REQUIRED_CSV` (set of demand CSVs), `OUTPUT_FILE = data/Polleo_Demand_Plan.xlsx`, `CONSENSUS_DIR = data/consensus/`, `ORDERS_FILE = data/orders_draft.json`, `NPD_FILE = data/npd_skus.csv`, `COVERAGE_TEMPLATE = data/coverage_template.xlsx`. --- ## 3 · `forecast_engine.py` — pipeline + formulas ### Inputs (every `pd.read_csv` / `pd.read_excel` / `load_workbook`) - `erp_promo_calendar.csv` — read in `_load_erp_promo()`; columns: `sku, year, week, is_erp_promo`. Filtered to `is_erp_promo == 1`. Returns `(erp_set, erp_min_yw, erp_max_yw)`; the min/max yw bound the ground-truth window — inside it, ERP wins; outside, statistical `is_any_promo` is used. - `sales_clean.csv` — read multiple times: in `load_promo_data()` (cols `sku, year, week, qty_total, qty_retail, is_any_promo, retail_discount_pct, is_wholesale_spike`); in `build_run_rate_from_sales()` (cols `sku, year, week, qty_total, qty_retail, qty_wholesale, is_wholesale_spike`); in `get_latest_prices()` (cols `sku, year, week, qty_retail, qty_webshop, avg_ppp_retail, avg_ppp_webshop`); in `forecast_portfolio()` (cols `sku, year, week, qty_total`). - `sku_uplift.csv` — read in `load_uplift()`; cols `sku, promo_uplift, promo_weeks, normal_weeks`. Kept only where `promo_uplift > 1 AND promo_weeks >= 2 AND normal_weeks >= 3`. - `cat_uplift.csv` — read in `load_uplift()`; cols `grupacija, cat_promo_uplift`. Kept where `cat_promo_uplift > 1`. - `sku_subcat_map.csv` — read in `load_subcat_map()`; cols `sku, sub_cat`. Returned as dict. - `sku_plan_list.csv` — primary SKU list; cols `sku, name, cat, oznaka` (renamed `ozn`), `vpc`. - `Polleo_Demand_Planning_Book*.xlsx` — fallback SKU list (only if `sku_plan_list.csv` missing). Sheet `Run Rate` cols A=SKU, B=Name, C=Cat, D=OZNAKA from row 4; sheet `cijena po artiklu` (col A=sku, col E=VPC) for prices. - `Polleo_Demand_Plan.xlsx` — optional, read in `read_existing_corrections()` (`data_only=True`). Sheet `Demand Planning` for planner factors (row `br+2`, CW headers col 32 row 5), sheets `Demand Input VP` / `Demand Input MP` for on-top demand + regular increase per CW. Used to preserve planner corrections across runs. - `vp_input.csv` — read in `load_input_csv()` and `_load_hist_ontop()`; cols `sku` + `CW*`. Used for future on-tops (matched to `new_cws`) and historical display (last 26 target weeks). - `mp_input.csv` — same shape and treatment. - `sku_prices.csv` — fallback when latest-week price missing; cols `sku, avg_sell_price, normal_retail_ppp, normal_webshop_ppp, qty_retail, qty_webshop, qty_wholesale, weeks_active`. - `sku_category_map.csv` — for portfolio (non-DP SKU) categorization; cols `sku, cat`. ### Outputs (every `to_csv` / `to_excel` / `save()`) - `Polleo_Demand_Plan.xlsx` — overwritten via `wb_out.save(output_file)`. Sheets created, in order: - **`Demand Planning`** (active) — 11-row block per SKU: SKU header, Baseline FC, Planner Factor, Adjusted FC, VP on-top, VP regular, MP on-top, MP regular, On-top Total, TOTAL DEMAND, blank. Cols A–E = SKU / Artikl / Grupacija / Oznaka / Label, F–AE = 26 RR history weeks, AF onward = N_FC forecast CWs, plus `Review` and `Promo` cols at end. - **`Demand Input VP`** — 2 rows per SKU (`on-top demand`, `regular increase`), cols A–E + CW cols. - **`Demand Input MP`** — same structure as VP. - **`Revenue Dashboard`** — Planned + Full Portfolio sections; per-grupacija revenue per CW + 13-wk total; includes stacked bar chart. - **`Forecast Detail`** — flat one-row-per-SKU: SKU, Name, Grupacija, Oznaka, Model, Pattern, Uplift, Cann%, then N_FC forecast cols. - **`Demand Output - Total`** — pulls TOTAL DEMAND row from DP via `='Demand Planning'!{CL(32+j)}{src}` formulas. - **`Demand Output - On Top`** — pulls On-top Total row similarly. - **`Price Reference`** — SKU, Avg Sell Price, Retail PPP, Webshop PPP, Retail Qty, Webshop Qty, Wholesale Qty, Weeks Active. - `data/forecast_log.csv` — appended via `append_forecast_log()`. Cols: `run_id, run_date, sku, year, week, target_year, target_week, forecast, forecast_total, forecast_retail, forecast_wholesale, baseline, on_top_vp, on_top_mp, promo_uplift, planner_factor, model_used, channel_mode`. **Append-only**, never truncated. `forecast = int(round(baseline * planner_factor + on_top_vp + on_top_mp))`. - `factor_history.csv` — written in main `run()`. Cols `run_date, run_year, run_week, target_year, target_week, sku, factor`. Idempotent on `(run_year, run_week)` — overlapping run rows are removed before append. ### Key constants used (sources of truth) | Const | Value | Where used in engine | |---|---|---| | `PROMO_DISCOUNT_PCT_THRESHOLD` | 10 | Imported in `update_sales.py`, not engine | | `WS_SPIKE_MULT` | 3.0 | Imported but **dead in engine**; real spike rule lives in `update_sales.py` | | `FORECAST_CAP_MULT` | 2.0 | Hard-coded inline literal `2.0` | | `FORECAST_FLOOR_MULT` | 0.5 | Hard-coded inline literal `0.5` | | `WS_CAP_MULT` | 1.5 | Hard-coded inline literal `1.5` | | `PROMO_UPLIFT_FALLBACK` | 1.35 | Hard-coded in `get_uplift()` | | `UPLIFT_MIN_PROMO_WEEKS / UPLIFT_MIN_NORMAL_WEEKS` | 2 / 3 | Hard-coded in `load_uplift()` | | `FORECAST_WEEKS` | 13 | `N_FC = 13` in `run()` | | `TRAILING_WEEKS` | 26 | RR display window in DP sheet | | `FLOOR_LOOKBACK` | 8 | `min(8, len(yc))` for floor median | | `YW_MULTIPLIER` | 100 | `yw = year*100 + week` encoding | **⚠️ `forecast_engine.py` does NOT actually `from constants import …`.** All thresholds are hard-coded numeric literals in the engine source. `constants.py` is the documented source of truth; only `update_sales.py` imports from it. Replicating in the new stack should centralize these. ### Pipeline overview (one paragraph) Entry point `run(input_file=None)`. (1) Resolves input workbook (Planning Book glob fallback); sets `output_file = Polleo_Demand_Plan.xlsx`, `sales_csv = sales_clean.csv`. (2) Anchors weeks: `cy, cw = current ISO week`, `CW_START = cw + 1`, `N_FC = 13`. (3) Loads SKU list from `sku_plan_list.csv` (preferred) or Planning Book. (4) `build_run_rate_from_sales()` loads `sales_clean.csv` → per-SKU lists of `qty_total`, `qty_retail`, `qty_wholesale`, `is_wholesale_spike` flags aligned to `target_weeks`. (5) `read_existing_corrections()` on prior plan to preserve planner factors + VP/MP on-tops. (6) Loads VP/MP historical on-tops for 26-week display. (7) **Spike-cleaning** (v3.7): for each SKU, if any week is `is_wholesale_spike`, replace its wholesale qty with the SKU's non-spike wholesale median; produces training-only `s['full']`. UI run-rate keeps raw spikes. (8) `load_promo_data()` — ERP truth inside the ERP window, statistical fallback outside. (9) Loads `sku_uplift.csv` + `cat_uplift.csv` + `sku_subcat_map.csv`. (10) Per-SKU `clean_series(y, pi, uplift)` deflates short isolated promo streaks (1–3 weeks); **SKIPS** if >40% of history is promo or last 4+ weeks are consecutive promo. For brand-new SKUs (`detect_new_article`), seeds last 8 history weeks before first sale with `find_proxy_sales() × 0.5` (sub-cat → keyword → cat median). (11) Builds GBR feature rows for non-dead, non-intermittent SKUs with `len ≥ 12`; `train_gbr()` grid-search over `max_depth ∈ {3,5,7}`, `max_iter ∈ {100,200}`, `learning_rate ∈ {0.05,0.1}`, recency-weighted (`0.92^(n-1-i)`). (12) **StatsForecast batch** on three series per SKU (total / `_R` retail / `_W` wholesale) with `[AutoARIMA(1), AutoCES(1), AutoTheta(1), CrostonOptimized, ADIDA, IMAPA, TSB(0.2,0.2)]`, plus seasonal wholesale-only batch `AutoARIMA(4), AutoTheta(4)` (labels suffixed `_s4`). (13) Per-SKU model competition: backtest on last `TEST=4` weeks of `y_clean`. Models = SES, Croston, Hybrid, WMA, SeasonIdx, GBR, plus SF outputs. Loss = `mean(|fc - act|) × PENALTY[m]` where `PENALTY = {'GBR': 1.15}`. (14) Generates full N_FC forecast from winner; applies cap (`fc ≤ mean(nz_recent_13) × 2.0`) and floor (`fc ≥ median(nz_floor_8) × 0.5`). (15) **Channel split** (v3.6) if `ws_share ≥ 0.5` AND both SF channel outputs exist AND wholesale_sum > 0: picks `best_r` / `best_w` via `_pick_best_channel()` (seasonal `_s4` penalised 1.10×), applies retail cap `mean × 2.0` and wholesale cap `max(last 26 nz) × 1.5`, zeros `fc_retail[j]` where `mp_vals[j] > 0`. Otherwise total-mode: when `mp_vals[j] > 0`, scales week's forecast by `(1 - retail_phys_share)`. (16) Latest-week prices via `get_latest_prices()`; falls back to `sku_prices.csv`. (17) `portfolio_fc` for non-DP SKUs (weighted avg last 8 weeks, skipping stale). (18) Builds 7 workbook sheets, saves. (19) Appends `forecast_log.csv` and writes `factor_history.csv`. ### Key formulas (verbatim from source) ```python # Cap (run() ~L1403-1406): nz_recent = yc[-min(13, len(yc)):]; nz_pos = nz_recent[nz_recent > 0] if len(nz_pos) >= 3 and pat not in ('dead', 'intermittent'): fc = np.minimum(fc, np.mean(nz_pos) * 2.0) # Floor (~L1407-1410): nz_floor = yc[-min(8, len(yc)):]; nz_floor = nz_floor[nz_floor > 0] if len(nz_floor) >= 3 and pat not in ('dead', 'intermittent'): fc = np.maximum(fc, np.median(nz_floor) * 0.5) # Per-channel caps (split branch, ~L1466-1474): r_nz = r_hist[-min(13, len(r_hist)):]; r_nz = r_nz[r_nz > 0] if len(r_nz) >= 3: fc_retail = np.minimum(fc_retail, np.mean(r_nz) * 2.0) w_nz = ws_hist[-min(26, len(ws_hist)):]; w_nz = w_nz[w_nz > 0] if len(w_nz) >= 3: fc_wholesale = np.minimum(fc_wholesale, np.max(w_nz) * 1.5) # Channel-split decision (~L1414-1432): tot_hist = ws_hist.sum() + r_hist.sum() ws_share = (ws_hist.sum() / tot_hist) if tot_hist > 0 else 0.0 split_available = ( ws_share >= 0.5 and sku in sf_fc_retail and sku in sf_fc_wholesale and len(sf_fc_retail[sku]) > 0 and len(sf_fc_wholesale[sku]) > 0 and ws_hist.sum() > 0 ) # split: fc = fc_retail + fc_wholesale (fc_retail[j] = 0 where mp_vals[j] > 0) # total: if mp_vals[j] > 0 and fc[j] > 0: fc[j] *= (1 - retail_phys_share) ``` ```python # Promo cleaning skip rule (clean_series, ~L222-227): total_promo = sum(1 for t in range(len(pf)) if pf[t].get('is_promo', 0)) promo_pct = total_promo / max(len(pf), 1) # If >40% of history is promo OR last 4+ weeks are consecutive promo → skip cleaning if promo_pct > 0.40 or consecutive_end >= 4: return yc # Otherwise: only streaks of 1-3 promo weeks get deflated by yc[s] /= uplift. ``` ```python # GBR feature model: FC_COLS = ['lag1','lag2','lag3','lag4','rm4','rm8','rs4','slope','level', 'woy','month','grup','ozn','vpc','cv','zc','med', 'is_promo','disc','pct_retail','ws_spike'] # target = yi[t] # HistGradientBoostingRegressor(loss='absolute_error', ...) + grid search # Recency weights: sw[ix] = 0.92**(n-1-i) # Per-SKU split: last 4 samples = test, rest = train ``` ```python # Model selection — _pick_best_channel (~L1441-1455): for mn, vals in fc_candidates.items(): pen = 1.10 if mn.endswith('_s4') else 1.0 scores[mn] = np.mean(np.abs(pred - actuals)) * pen return min(scores, key=scores.get) # Top-level: PENALTY = {'GBR': 1.15} — GBR must beat by 15% to win. ``` ```python # ERP/statistical promo merge (load_promo_data, ~L397-417): yw = iso[0] * 100 + iso[1] if erp_set is not None and erp_min_yw <= yw <= erp_max_yw: is_promo = 1 if (str(sku), int(iso[0]), int(iso[1])) in erp_set else 0 else: is_promo = int(r.get('is_any_promo', 0)) # Inside ERP coverage window → ground truth; outside → statistical fallback. ``` ### `append_forecast_log` — columns + dedupe rule Writes 18 cols in `FORECAST_LOG_COLS`. **No dedupe** — every run appends. Header written only when file does not yet exist. Downstream consumers must pick the **earliest** row per `(sku, target_year, target_week)` for Live FA semantics (the "first forecast made for that week" rule). ### Models in scope Confirmed actually called: - **StatsForecast**: AutoARIMA (season_length 1 and 4), AutoCES (1), AutoTheta (1 and 4), CrostonOptimized, ADIDA, IMAPA, TSB(0.2, 0.2). Plus Naive as fallback. - **Engine-only**: SES (`ses_forecast`), Croston (`croston_forecast`, distinct from SF's), Hybrid (`hybrid_fc`), WMA (`wma_capped_forecast`), SeasonIdx (`seasonal_indexed_forecast`), GBR (`gbr_predict`). - **Channel-split variants** ("_split"): sum of retail + wholesale per-channel SF fc; seasonal variants on wholesale labelled `*_s4`. - **No Holt / Holt-Winters anywhere** — explicit comment notes the CLAUDE.md guardrail. --- ## 4 · `update_sales.py` — ingestion + promo flags ### Inputs (file selection) 1. **Command-line args** (priority 1): any `.xlsx`/`.xls` paths passed as positional args. `--merge-wholesale` flag is a mode switch, not a file. 2. **Pattern globs** (priority 2, scanned in order): - `Weekly_Sales_Update*.xlsx` - `weekly_sales_update*.xlsx` - `sales_update_*.xlsx` - `Sales_*.xlsx` 3. **Fallback** (priority 3): any `*.xlsx` not starting with `Polleo_Demand`, `sku_`, `VP_Input`, or `MP_Input`. For each picked file, `read_one_file()` uses `openpyxl.load_workbook(filepath, read_only=True)` to inspect sheet names, skips any sheet named `Sheet1..Sheet9` / `sheet1..sheet9`, picks the first remaining sheet (or first sheet if none remain). Then tries `pd.read_excel(... header=skip)` for `skip ∈ 0..5` until cols containing `datum`/`date` or `artikal`/`artikl`/`sku` appear. Also reads: - `sales_clean.csv` — for overlap removal and union with the new data (also in `--merge-wholesale` mode to add RIZ wholesale qty/RUC into existing rows). - `erp_promo_calendar.csv` — checked via `os.path.exists`; presence triggers `recalc_uplift_erp.main()`. - `sku_category_map.csv` — if exists, merges new category rows. - `sku_subcat_map.csv` — if exists, merges new sub-category rows. ### Output - **`sales_clean.csv`** — overwritten. Full column list in `keep_cols`: ``` sku, year, week, qty_retail, qty_webshop, qty_wholesale, qty_total, avg_ppp_retail, normal_ppp_retail, retail_discount_pct, is_retail_promo, avg_ppp_webshop, normal_ppp_webshop, webshop_discount_pct, is_webshop_promo, is_wholesale_spike, is_any_promo, promo_pct_volume, ruc_retail, ruc_webshop, ruc_wholesale, ruc_total ``` Aggregation: `df.groupby(['sku','year','week','channel']).agg(qty=('qty','sum'), avg_ppp=('ppp','median'), txns=('qty','count'))` where `ppp = value.abs() / qty` per row. Pivot by channel → one row per `(sku, year, week)`, `qty_total = qty_retail + qty_webshop + qty_wholesale`. `avg_ppp` per channel recomputed as median per `(sku, year, week)`. Overlapping weeks in existing `sales_clean.csv` are removed before append (full replacement). - **`sku_prices.csv`** — overwritten. Built from `compute_recent_avg_sell()` (revenue/qty over last 12 active weeks, wholesale excluded), `compute_recent_normal_ppp()` for retail and webshop (mode of last 12 weeks, max tiebreak). - **`sku_category_map.csv`** — written if any uploaded file had `Naziv grupacije` column. Combined with existing rows; later upload wins per SKU. Values normalized via `CAT_MAP` dict (Slovenian/German → Croatian). - **`sku_subcat_map.csv`** — written if `Kategorija artikla` or `podkat` col detected. **Triggered chain** (each `try/except`-wrapped, best-effort): 1. `recalc_uplift_erp.main()` — only if `erp_promo_calendar.csv` exists; rebuilds `sku_uplift.csv` + `cat_uplift.csv` from ERP truth. 2. `build_detailed_sales.main()` — writes `data/sales_detailed.csv` for analytics granularity. **Does NOT feed forecasting.** 3. `build_promo_performance.main()` — writes `data/promo_performance.csv` (per-campaign uplift / cannibalization / net_effect). ### Column auto-detection — inline in `read_one_file()` **Pass 1** (exact match, case-insensitive after lower+strip): - `date` ← `datum`, `date` - `sku` ← `artikal`, `artikl`, `sku` - `qty` ← `količina`, `kolicina`, `qty`, `quantity` - `value` ← `vrijednost €`, `vrijednost`, `value` - `tip` ← `tip dok.`, `tip dok`, `tip_dok`, `doc type` - `cat` ← `naziv grupacije` - `name` ← `naziv` - `ruc` ← any col starting with `ruc`, `marž`, `marza`, or equal to `margin`, provided `%` NOT in name **Pass 2** (fuzzy substring; only if missed in pass 1): - `date`: `'datum' in cl` or `'date' in cl` - `sku`: `'artikal'`/`'artikl'`/`'sku'` substring - `qty`: `'količ'` or `'kolic'` substring - `value`: `'nabavn' not in cl` AND (`'vrijednost'`/`'vrednost'`/`'value'` substring) - `tip`: `'tip dok'` or `'tip_dok'` substring - `cat`: `'naziv grupac'`, fallback `'grupacija' in cl and 'naziv' not in cl and 'artikla' not in cl` - `ruc`: substring `'ruc'`/`'marž'`/`'marz'`/`'margin'`/`'marge'`, provided `'%' not in cl` **Sub-category detection** (separate pass for `sku_subcat_map.csv`): - `_subcat_col`: `'kategorija artikla' in cl` or `cl == 'podkat'` - `_grup_col`: `'naziv grupacije' in cl` Required keys: `['date', 'sku', 'qty', 'value', 'tip']`. If any missing, file is skipped with `ValueError`. ### `channel()` function — THE EXACT MAPPING (verbatim) ```python def channel(t): if t == 'RCM': return 'retail' if t in ('WSA','WSB','WSC','WSD'): return 'webshop' # RAC = B2B-ish web orders (gyms/resellers via the website), priced and # serviced as wholesale. KAMs started booking wholesale through RAC, so # it counts as wholesale (matches lookup_channel_map; changed 2026-06). if t in ('RAC','TRC','VPT','VPB','RIZ','RPE'): return 'wholesale' return 'other' ``` `VALID_TIPS = {'RCM','WSA','WSB','WSC','WSD','TRC','VPT','VPB','RAC','RIZ','RPE'}`. Rows with `qty <= 0` are filtered out (no returns). **Note (2026-06):** RAC moved retail-webshop→wholesale and RPE was added — same change applied to the live FastAPI upload (`upload_repo.VALID_TIPS`), `lookup_channel_map`, and `WS_CHANNEL_MAP_IDS`. ### Promo detection logic All flags are recomputed across the FULL `sales_clean.csv` (existing + new combined) after merge so historical rows pick up the latest anchor price. - **`compute_recent_normal_ppp(df, qty_col, ppp_col, window=12)`** — per-SKU "current regular" price = **mode of `avg_ppp_*` over most recent 12 weeks with qty>0 and ppp>0; ties broken by max** (matches the v_3 reprice-anchored rule). - **`is_retail_promo`** — threshold = `PROMO_DISCOUNT_PCT_THRESHOLD` (= 10): ```python retail_discount_pct = (1 - avg_ppp_retail / normal_ppp_retail) * 100 is_retail_promo = (retail_discount_pct > PROMO_DISCOUNT_PCT_THRESHOLD).astype(int) ``` - **`is_webshop_promo`** — same on webshop. - **`is_wholesale_spike`** — `WS_SPIKE_MULT = 3.0` is imported but NOT used; the actual rule is stricter: ```python p90 = per_sku 0.90 quantile of non-zero wholesale qty big = (qty_wholesale > 3 * p90) & (qty_wholesale > 1000) isolated = max(prev_wh, next_wh) < 0.5 * qty_wholesale is_wholesale_spike = (big & isolated).astype(int) ``` In English: flag when week's qty is `>3× SKU's own p90 of non-zero wholesale weeks` AND `> 1000 units` AND both neighbouring weeks have `< 0.5× spike-week qty`. Continuous high-wholesale streaks (3+ big consecutive weeks) are intentionally NOT flagged. - **`is_any_promo`** — logical OR of retail+webshop promo flags. Wholesale spikes are deliberately excluded: ```python is_any_promo = ((is_retail_promo == 1) | (is_webshop_promo == 1)).astype(int) ``` - **`promo_pct_volume`** — initialised as a column in `keep_cols` but **never computed**. Stub for downstream tooling; value remains 0. ### Country detection from filename **There is none in `update_sales.py`.** The script is country-agnostic — it concatenates all uploaded files into one dataset; country origin is not preserved beyond a per-row `tip_dok`. Patterns like `*SveUkupno*` (CRO) or `*VsegaSkupaj*` (SLO/AT) seen in `build_detailed_sales.py` have no behavioural effect here. The closest country handling is the inline `CAT_MAP` dict that normalises Slovenian (`OBLAČILA IN OBUTEV`, `BORILNA OPREMA`, `ŠPORTNA PREHRANA`, `BIO IN SUPERFOODS`, `DRINKWARE IN DOM`, `STORITVE`) and German (`BEKLEIDUNG UND SCHUHE`, `KAMPFSPORT AUSRÜSTUNG`, `SPORTNAHRUNG`, `BIO UND SUPERFOODS`, `DRINKWARE UND HOME`, `DIENSTLEISTUNGEN`) category names to Croatian canonical labels. ### Notable surprises for the new stack 1. `WS_SPIKE_MULT` from constants is imported but **unused** — the inline isolated-outlier rule is the real spec. 2. `promo_pct_volume` is always 0 — declared but never computed. 3. `forecast_log.csv` has **no dedupe** — every run appends; Live FA semantics require picking earliest per `(sku, target_year, target_week)`. 4. Channel split kicks in only when `ws_share ≥ 0.5` AND both SF channel forecasts present AND wholesale_sum > 0. --- ## 5 · `PromoTool/` — CM (Nabava) + Marketing apps Standalone Streamlit. Shares `../data/` with the main app, plus its own `PromoTool/data/` for marketing source files and `cm_promotions.csv` proposals. ### `app.py` — sidebar + routing - Path hygiene: pins PromoTool dir to top of `sys.path`, pops cached `promo_data` so it never resolves to the sibling PromoCalendar module. - `st.set_page_config(title="Promo Tool", layout="wide")`. - Mode switcher (sidebar `st.radio`): `🛒 Nabava (CM)` / `📣 Marketing (Web)`. - Routing: - Marketing mode → single page `📣 Past Web Promotions` → `page_marketing_history.render()`. - CM mode → `st.radio` over `🛒 Promo Planner`, `📋 Moji prijedlozi`, `🔮 Promo Forecaster`, `📜 Past Promotions`, `📊 Promo Performance`. Routed by emoji prefix. ### `page_planner.py` **Filters / sidebar widgets**: `promo_name` (text), `source` selectbox (4 options), `promo_type` selectbox (`Univerzalna`, `Partner / Passport`, `Rok istek`, `Otvaranje`, `Dani centra`, `VISA`), `default_mechanic` selectbox, `outcome` radio (4 options), `target` numeric input, start/end week selectboxes (from `horizon_weeks(13)`), `default_discount` slider (0–80%, step 5), `mode` radio (`Normal` / `Target mode`), category selectbox, parent selectbox (from `build_parent_map(plan)`), search text input, SKU multi-select (`st.data_editor` checkbox table, top 50), channels multiselect (default Retail+Webshop). Plus basket management (groups, dissolve, clear all). **Data sources** — all via `load_all()` in `promo_data.py` (reads `../data/`): `sales_clean.csv`, `sku_plan_list.csv`, `sku_prices.csv`, `sku_costs.csv`, `erp_promo_calendar.csv`, `sku_subcat_map.csv`, `stock.csv`, `incoming_supply.csv`, `supply_master.csv`, `sku_uplift.csv`. **Key calculations**: - Conflicts: `detect_conflicts(sy, sw, ey, ew, basket)` vs `cm_promotions.csv`. - Effective discount: `effective_discount(mechanic, user_pct)` — 1+1 → 50, 2+1 → 33.33, 3+1 → 25, 4+1 → 20, else user value. - Uplift suggestion: `suggest_uplift(sales, erp, sku, discount)` → uplift, p90 upside, band/disruptor/ceiling. - Baseline run rate: `base_run_rate(sales, erp, sku, lookback=26)`. - Analog method weekly shape: `promo_pattern_for_sku(...)` → `weekly_qty` of length `n_weeks`. - P&L: - `promo_qty = sum(weekly_pattern_qty)`; `base_qty = base_avg × n_weeks`; `incremental = promo_qty − base_qty` - `promo_price = price × (1 − discount/100)`; `promo_revenue = promo_qty × promo_price`; `base_revenue = base_qty × price` - `promo_ruc_unit = max(0, promo_price − cost)`; `promo_ruc = promo_qty × promo_ruc_unit`; `ruc_delta = promo_ruc − base_ruc` - `breakeven = price / promo_price` - Margin verdict: `uplift ≥ breakeven` → success. - Upside: `upside_qty = base_avg × n_weeks × upl["uplift_p90"]`. - NC30 check: `check_nc30(sku, promo_price)` reads `data/nc30.csv`; OK if `promo_price ≤ nc30 + 0.01`. - Stock coverage: `on_hand` from `stock.csv` + multi-store; `cov_now = on_hand / base_avg`; `stock_at_start = on_hand − base_avg × weeks_until_promo`; `stock_at_end = stock_at_start − promo_qty`. Lead-time-aware order recs (default `lead_time=8w` from `supply_master`). - Target mode: `req_uplift = target / (base_avg × n_weeks)`; `est_disc = max(0, min(50, 20 + (req_uplift − 1.35) / 0.04))`. - Shadow-promos: `_detect_shadow_promo_weeks(sales, sku, spike_mult=2.5)` — trailing-4-week median spike, excluding ERP-recorded weeks. - Cannibalization (sub-cat siblings): - Composite proximity = `0.50 × size_prox + 0.30 × flavor_prox + 0.20 × price_prox`. - Size proximity bins (ratio): ≥0.85 → 1.00, ≥0.65 → 0.75, ≥0.40 → 0.45, ≥0.25 → 0.30, else 0.20; missing → 0.50. - Flavor proximity: same → 1.00, different known → 0.40, missing → 0.25. - Price proximity: `max(0.10, 1 − distance / cat_scale)` where `cat_scale = max((cat_median − cat_p25) × 2, 1)`. - Tier factor: GOLD → 0.70, BRONZE → 1.30, else 1.00. - Overlap factor: sibling on own ERP promo in window → 0.30, else 1.00. - Filters: `proximity ≥ 0.75`, `ws_share ≤ 0.35`, no overlap. - Per-sibling loss = `baseline_wk × n_weeks × drop_pct` where `drop_pct = historical_sibling_drop_pct(...)` (median observed retail+web drop during focus's past promo weeks), fallback 15%; clamped to 0..0.95. - Net category metrics: `net_units = incremental_units − total_loss_units`, etc. - Parent overview: arithmetic sum of per-child `base_run_rate` and `suggest_uplift` outputs. **Charts / tables / metrics**: - Per-SKU header metrics row: Price, Baseline/wk, Uplift (with rich tooltip), Discount, XYZ. - 5-metric row: Total promo qty, Avg per week, Peak week, Incremental vs no-promo, Realistic upside (p90). - Forecast chart `_build_forecast_chart`: hist 12-wk actuals bar + analog-shape promo line + base dashed + post-promo dip dotted. - P&L 5-col: promo revenue, base revenue, revenue delta, RUC delta, breakeven uplift. - Coverage 5-col: coverage now, stock now, @ start, @ end, after promo. - Per-week qty override grid (`number_input` 7 cols). - Cannibalization table (top 10) + tail summary + Net 3-metric row. - Summary tab: median KPIs, net category impact, upside scenario, per-group expanders, pivot table per-SKU-per-week, line chart (per-SKU + total + upside). **Save actions**: "Create & save promotion" → `append_promotion(rows)` to `PromoTool/data/cm_promotions.csv`; "Submit to Promo Calendar" → dynamic import of sibling `PromoCalendar/promo_data.py`, calls `add_promo(...)` with status `💡 idea`. ### `page_my_proposals.py` Reads sibling Promo Calendar's `promo_data.py` via `importlib.util.spec_from_file_location` (unique module name `calendar_promo_data`). - Status groups: `🔧 Trebaju doradu` → `[🔧 preparing]`; `💡 U pregledu` → `[💡 idea]`; `✅ Odobreno` → `[✅ approved]`; `🟢 Live` → `[🟢 live]`; `📜 Sve` → all. - Filter: owner selectbox. - 4 KPI metrics (count per status). - Per-proposal card via `_render_proposal`: header, latest director comment, details expander with SKU table + audit log. - Actions on "Trebaju doradu": `🔁 Re-submit` → `update_promo({"status": "💡 idea"})` + log; `🗑️ Odustani` → `delete_promo`. ### `page_forecaster.py` **Filters**: SKU selectbox (label = `sku — name`), `outcome` selectbox (4 options). **Data**: `load_all()` (requires `sales_clean.csv` + `erp_promo_calendar.csv`). **Key calculations**: - Combines `past_promos_for_sku(sku)` (weight ×3) + sub-cat siblings (weight ×1, first 30). - `avg_uplift` = weighted average; `median_uplift` = `np.median`. - Outcome-anchored discount (when ≥2 historical depths): - "lagera" → `round(d_max)`; "ruc" → `round(d_min)`; "novi" → `round((d_avg + d_max) / 2)`; "traffic" → `round(d_avg)`. - No-history defaults: lagera 30, ruc 12, novi 22, traffic 25. - `rec_duration = 4` (CMs only run monthly AKCIJA). - `_pred_uplift_at(d)` = `avg_uplift × (1 + (d − 20)/100 × 0.4)` × band-shape ratio × price-disruptor multiplier; floor 1.0. - `_breakeven_at(d) = price / (price × (1 − d/100))`. - Breakeven hard-limit: walks discount DOWN in 1% steps until predicted ≥ breakeven; floored at historical min; `breakeven_unsatisfiable` flag if no clearing depth. - Volume forecast: `promo_qty = base_avg × rec_duration × pred_uplift_at_rec`. **Tables / metrics**: - 5-metric row: rec discount %, rec duration, predicted uplift, breakeven uplift, margin signal. - Price-zone success/info banner (disruptor zone). - 4-metric forecast: baseline/wk, forecast units, forecast revenue, forecast RUC. - Discount sensitivity dataframe (0..50 step 5; `pred = min(avg_uplift × (1 + (d−20)/100 × 0.4), 2.5)`; breakevens; ✅/❌ margin). - Backing-events dataframe (🟢 own / ⚪ sibling). ### `page_history.py` Standalone `_build_campaigns` from `erp_promo_calendar.csv`: groups by `(primary, year)` then splits weeks into contiguous blocks (gap ≤ 6). Retail+web only (no wholesale). - Per-SKU metrics: `avg_d = d_qty / d_n`, `avg_b = b_qty / b_n`, `uplift = avg_d / avg_b`. Revenue from `sales_clean × avg_ppp_retail`/`avg_ppp_webshop`. RUC from `ruc_retail + ruc_webshop`. `eff_price = d_rev / d_qty`. - `@st.cache_data(ttl=300)`. **Filters**: Year selectbox, search text input. **Charts / tables**: - Campaign picker selectbox (formatted with avg uplift + RUC). - 5-metric campaign header: SKUs, Total units, Revenue, RUC, Avg uplift. - SKU table sorted by Revenue (12 cols including Tier, Weeks, Uplift, Effective €/u, Revenue €, RUC €). - Weekly performance multiselect + Plotly chart: 4w pre + promo + 2w post, vrect highlights promo period, hline baseline in solo mode. - CSV download_button per campaign. ### `page_performance.py` **Source**: `../data/promo_performance.csv` (rebuilt weekly by `build_promo_performance.py`), `@st.cache_data(ttl=300)`. **Filters**: Category multiselect, Tier (oznaka) multiselect, `promo_start_yw` range slider, `max_weeks` slider (default 13, filters permanent-price runs). **Metrics**: - 5-metric row: Campaigns, SKUs, Median uplift (DURING/BEFORE), Median cannibalization (AFTER/BEFORE), Median net effect (= (during + 4w after) / (BEFORE × total_weeks)). - Category breakdown groupby table. - Plotly scatter `actual_uplift` vs `cannibalization` (size = `n_promo_weeks`, color = `oznaka`), reference hline/vline at 1.0. - Full campaigns table + CSV download. ### `page_marketing_history.py` **Source**: webshop coupon log (`load_coupons()` from `marketing_data.py`). - Builds campaigns via `build_marketing_campaigns(coupons, sales, name_map, cat_map, tier_map)`. **Filters**: year selectbox, search text input, "Prikaži trajne kodove" checkbox. **Metrics**: - 5-metric campaign row: SKUs, Units, Revenue (after), Discount given, Avg discount = `discount / rev_before × 100`. - Daily stacked-bar per coupon code (top 8 + "ostali" bucket). - Coupons table + SKUs table + CSV download per campaign. - `_render_permanent(coupons)` — F-type long-running codes, one row per coupon. ### Core data layer: `promo_data.py` Notable helpers: - `parse_flavor(name)` — first flavor token from `_FLAVOUR_TOKENS` minus `_SKIP`. - `flavor_compatibility(a, b)` — same→1.00, different known→0.40, missing→0.70. - `parse_unit_size(name)` — regex extract pack size; returns `(value_in_base_unit, "g"|"ml")`. - `category_price_benchmark(plan, prices, cat, unit)` — €/100u distribution within a category; returns `{p25, median, p75, min, max, n_skus, unit}` (only when ≥5 SKUs). - `price_disruptor_multiplier(promo_per100, bench)` — `(mult, zone_label)` based on ratio vs cat p25. - `discount_band_uplift_curve(discount_pct)` — piecewise non-linear curve calibrated against 3,107 historical events. - `upside_ratio_for_band(discount_pct)` — empirical p90/median uplift ratio per band. - `historical_sibling_drop_pct(...)` — median observed sibling retail+web drop during focus's past ERP promo weeks vs clean baseline. - `first_time_uplift_cap(n_history, discount_pct)` — adaptive ceiling. - `load_transactions()` — `data/sales_transactions.csv`, RCM+WSA-D rows only. - `detect_mechanic_from_transactions(tx, sku, start_yw, end_yw)` — infers mechanic from qty-per-doc distribution; 1+1/2+1/3+1 if dominant qty=2/3/4 has ≥40% share. - `guess_mechanic_from_discount(d)` — discount-band → mechanic label. - `effective_discount(mechanic, user_pct)` — see Planner. - `load_nc30()` — `{sku: nc30_price}` from `data/nc30.csv`. - `check_nc30(sku, planned_promo_price)` — OK if planned ≤ nc30 + 0.01. - `load_all()` — loads 10 CSVs and builds lookup maps. - `base_run_rate(...)` — mean of most recent `n_weeks=4` non-ERP-promo, non-zero weeks within `lookback=13`. - `_is_monthly_akcija(ptype, duration)` — duration 3 or 4 OR primary type contains AKCIJA / AKCIJE. - `promo_pattern_for_sku(...)` — analog method weekly qty pattern; LEVEL from baseline × suggest_uplift, SHAPE is generic ramp/peak/decline. - `_detect_shadow_promo_weeks(sales, sku, spike_mult=2.5)` — qty spike vs trailing-4-week median. - `past_promos_for_sku(...)` — list of past monthly AKCIJA events with OOS detection (zero-week OR < 40% of campaign median). - `suggest_uplift(sales, erp, sku, discount_pct, outcome="")` — own history → category → flat fallback; recency bias (60% recent + 40% median), linear discount sensitivity, band shape delta, price-disruptor multiplier; surfaces p90 upside. - `build_family_map(plan)` — `{family_key: [skus]}` keeping only multi-variant families (uses `_family_key` name-stem stripping). - `cw_to_label(year, week)` / `label_to_cw(label)` — `"CW20 (2026)"` ↔ `(2026, 20)`. - `horizon_weeks(n=26)` — current + n future `(year, week)` tuples. - `_read_promotions()` / `append_promotion(rows)` — `PromoTool/data/cm_promotions.csv`. - `detect_conflicts(sy, sw, ey, ew, skus)` — overlap check against `cm_promotions.csv` (yw arithmetic). Verbatim formulas: - `effective_discount`: 1+1 → 50.0, 2+1 → `round(100/3, 2)` = 33.33, 3+1 → 25.0, 4+1 → 20.0, else user. - `guess_mechanic_from_discount`: 47..53 → "1+1 gratis"; 30..36 → "2+1 gratis"; 22..28 → "3+1 gratis"; 18..21 → "4+1 gratis". - Discount-band uplift curve: - `d ≤ 15`: `0.85 + (d/15) × 0.20` - `d ≤ 25`: `1.05 + ((d−15)/10) × 0.45` - `d ≤ 35`: `1.50 + ((d−25)/10) × 0.60` - `d ≤ 45`: `2.10 + ((d−35)/10) × 0.40` - `d ≤ 60`: `2.50 + ((d−45)/15) × 1.00` - else: `min(5.00, 3.50 + (d−60)/40 × 1.50)` - p90 upside ratios by band: 0–15 → 2.00, 15–25 → 2.33, 25–35 → 2.84, 35–45 → 3.15, 45–60 → 1.84, >60 → 4.72. - Price-disruptor: ratio `promo_per100 / cat_p25`; ≥1.0 → 1.0; 0.7–1.0 → `1.0 + share × 0.5`; 0.5–0.7 → `3.0 + share × 1.0`; <0.5 → `4.0 + min(1, deficit) × 3.0`. - Linear discount scaling: `base_predict = 0.6 × recent_upl + 0.4 × median_upl`; `scale = max(0.3, 1.0 + delta_pp × 0.03)`. - OOS detection: week flagged if `q == 0` OR `q < 0.4 × median_q` (non-zero median). ### Core data layer: `marketing_data.py` **`classify_campaign(coupon_name, coupon_code)`** — verbatim regex labels (uppercased haystack `s`): - `"BF2025" or "BF 25" or "BF25" or "BLACK FRIDAY" in s` → `"BF 2025"` - `"XMAS2025" or "XMAS 2025" or "PROSINAC25" or "XMAS"` → `"XMAS 2025"` - `"BDAY2026" or "BD2026" or "PLAZA18" or "BOSS18"` → `"BDAY 2026"` - `"WOMEN'S WEEK" or "ZOEWW" or "EXTRA15WMN"` → `"Women's Week 2026"` - `"WINTER" or "WS26"` → `"Winter Sale 2026"` - `"01052026" or "1.5.2026"` → `"1. svibnja 2026"` - `"FLASH"` → `"Flash sale"` - `"POLLEO WEAR" or "VULE20"` → `"Polleo Wear"` - `"POLLEO15"` → `"POLLEO15 -15%"` **(must come BEFORE "SNACK" since description contains "Snacks & RTD")** - `"SNACK"` → `"Snacks -15%"` - `"ZOE"` → `"ZOE promo"` - else → `"Ostalo"` **`load_coupons()`** — reads `PromoTool/data/Detaljni report jedan red po orderu, kuponu i proizvodu..csv` (sep=`;`, encoding `cp1250`). Filters out `order_status == "08 - Otkazano"`. Parses `date_added`, drops NaT, adds ISO `year`/`week`/`date` cols, adds `campaign` via `classify_campaign`. Coerces numeric cols: `quantity, line_total_before_coupon, line_total_after_coupon, line_coupon_discount_total, coupon_discount_percent`. **`build_marketing_campaigns(coupons, sales, name_map, cat_map, tier_map)`** — filters to `coupon_type == "P"`. Groups by `campaign`. Returns dicts with campaign-level (`id, campaign, first, last, days, n_skus, n_coupons, n_orders, total_units, total_rev_before, total_rev_after, total_discount, avg_uplift, skus, coupons`) and per-SKU + per-coupon lists. Per-SKU `avg_base` from 4-week pre-campaign `qty_webshop` via `_baseline_qty`. Sorted by `first` desc. **`permanent_codes(coupons)`** — same shape but `coupon_type == "F"` (influencer/podcast/gift/compensation). **`daily_units_for_campaign(coupons, campaign)`** — daily groupby (`date`, `coupon_code`) → units/orders for P-type rows. ### `parent_map.py` Two-stage parent-SKU resolution: 1. **Primary**: `data/Polleo Help svi artikli.xlsx` sheet `Artikli`, cols `SKU`/`Naziv`. Same `Naziv` across SKUs = parent. Key = `XLSX::`. 2. **Heuristic fallback**: - SKU with ≥3 dash-segments (e.g. `VENUM-03813-449-M`) → parent = SKU minus last segment → key `SKU::`. - Else → key `NAME::` where stem is `_name_stem(name)` (lowercased, diacritics stripped, first N tokens until variant token; size tokens like `454g`, `2kg` kept). `_VARIANT_TOKENS`: apparel sizes (xs..xxxl, small/medium/large), EN + HR colors, EN + HR flavors. Lowercase, no-diacritic. `build_parent_map(plan_df, sku_col="sku", name_col="name")` returns `{parent_key: {"display": ..., "skus": [...]}}`. Display preference: Naziv (xlsx) > `SKU::prefix — name` (SKU-keyed) > title-cased stem (NAME-keyed). `_load_xlsx_parent_map()` is `lru_cache(maxsize=1)`. ### Auxiliary: `gath_to_nc30.py` - Input: most-recent `PromoTool/Pregled*nc30*.xlsx` (fallback `*nc30*.xlsx`). - Reads col A = SKU, col R (index 17) = "Najniža cijena 30 dana". - Drops rows where NC30 ≤ 0; for duplicate SKUs keeps LOWER NC30 (most conservative). - Output: `../data/nc30.csv` with cols `sku, nc30_price`. --- ## 6 · `PromoCalendar/` — unified promo calendar ### `app.py` — calendar dashboard, quick-add, import/export **Sidebar**: page radio (`📊 Calendar dashboard`, `➕ Quick-add`, `📥 Import / Export`); statuses multiselect (default all `STATUSES`). **Dashboard filters**: Sources multiselect (from `SOURCES`), Grupacije multiselect, SKU multiselect (restricted to SKUs in current promos, label `SKU — Name`). **Calendar nav**: view toggle `Mjesec` / `Tjedan`; department multiselect (`Nabava`, `Marketing`); prev/next/today buttons; HR month picker or date picker for week view. **Sources of promo data**: - `load_promos()` from `promo_data.py` → `PromoCalendar/data/promo_calendar.csv`. - `_load_cm_promotion_detail()` reads sibling `PromoTool/data/cm_promotions.csv` for per-SKU breakdown of selected promo (joined on `id`). - `_load_sku_grupacija_map()` reads `../data/sku_subcat_map.csv` (cols `sku, grup`), normalized via `_GRUP_CAT_MAP` (multi-language → canonical HR). - `_load_sku_name_map()` reads `../data/sku_subcat_map.csv` or `../data/sku_category_map.csv` for `{sku: name}`. - `_load_sku_prices()` reads `../data/sku_prices.csv` (price col fallback: `price, price_eur, retail_price, mp_price, cijena`). - `_synthesize_sku_breakdown(promo)` — deterministic dummy SKU projection when no PromoTool detail exists: `seed = md5(promo_id + sku)`; price fallback 5–30 €; jitter 0.7–1.3 × `units/n_skus`; discount 15–25%; margin 18–30%. **KPI metrics** (5 cols, scoped to current period): In this period, Pending review (idea+preparing), SKUs in promos, Conflicts, Total promo units. **Calendar rendering**: HTML grid (`pc-cal` CSS) using `monthdatescalendar` for month view or week strip for week view. Per-day chips colored by `SRC_COLOR`; max 4 chips per day in month view, 10 in week view. **Per-promo detail panel**: - `_promo_date_range(r)` → Monday-of-start to Sunday-of-end (ISO). - Conflicts grouped by "other promo"; resolution actions: "Vrati ovu predlagaču" / "Vrati drugu" / "Vrati oboje" (all → status `🔧 preparing` + `append_log`); "Acknowledge" → `acknowledge_conflict(id_a, id_b)`. - SKU breakdown: real (`cm_promotions.csv`) or synthesized. 5-metric row: SKUs, Proj. količina, Proj. prihod, Proj. RUC, RUC % = `t_ruc / t_rev × 100`. - Audit log expander; edit / delete expander. **Quick-add form**: name, source, type, outcome, SKUs, week range (parsed by `parse_week_range`), category, owner, units, status, notes → `add_promo(...)`. **Import / Export page**: Excel uploader → `import_excel(bytes)`; CSV + xlsx exports; "Wipe all promotions" with confirm checkbox. ### `promo_data.py` **`PROMO_COLS`** (full): ``` id, name, source, type, outcome, status, start_year, start_week, end_year, end_week, skus, units, category, owner, notes, approval_log, acknowledged_conflicts, created_at, updated_at ``` **`SOURCES`** (with colors): - `B2C — MP (retail)` → `#7C6FEE` - `B2C — WEB` → `#E8734A` - `B2B — FMCG` → `#34D399` - `B2B — FITNESS` → `#38BDF8` **`SOURCE_TO_DEPT`**: B2C—MP → `Nabava`; B2C—WEB → `Marketing`; B2B—FMCG → `Nabava`; B2B—FITNESS → `Nabava`. **`DEPTS = ["Nabava", "Marketing"]`** · **`ROLES = ["Category Manager", "Marketing", "Direktor nabave"]`**. **`ROLE_DEFAULT_SOURCE`**: Category Manager → B2C—MP; Marketing → B2C—WEB; Direktor nabave → B2C—MP. **`STATUSES = ["💡 idea", "✅ approved", "🔧 preparing", "🟢 live", "✓ done", "📊 analyzed"]`** **`OUTCOMES = ["📦 Rješavanje lagera", "💰 Veći RUC", "👥 Novi kupci", "🚀 Traffic driver"]`** **`TYPES = ["Univerzalna", "Kampanja", "Wholesale", "Web", "Loyalty", "Rok istek", "Partner / Passport", "Otvaranje", "Dani centra", "VISA"]`** **Functions**: - `append_log(promo_id, actor, action, comment="")` — appends `[ts] actor · action — comment` to `approval_log`; bumps `updated_at`. - `load_promos()` — reads `promo_calendar.csv` (cached `ttl=10`); fills missing `PROMO_COLS` with `""` (or `0` for year/week/units). - `save_promos(df)` — writes CSV + clears `load_promos` cache. - `add_promo(row)` — generates `uuid4().hex[:8]` id, seeds `approval_log` with creation line, defaults status `💡 idea`. - `update_promo(promo_id, fields)` — patches fields, bumps `updated_at`. - `delete_promo(promo_id)` — removes row. - `acknowledge_conflict(id_a, id_b)` — adds counterpart id to both promos' `acknowledged_conflicts` (comma-separated set, idempotent). - `exclude_skus_from_promo(promo_id, skus_to_exclude, cat_map=None)` — drops given SKUs; if promo is category-only, expands `cat_map[cat]` minus excluded. - `yw_to_int(year, week)` — `year × 100 + week`. - `parse_week_range(text)` — `"20-23" → (20, 23)`; `"21" → (21, 21)`. - `cw_label(year, week)` — `"CW{week}"`. - `horizon_weeks(weeks_back=4, weeks_fwd=13)` — list of `(year, week)` covering window around today. - `load_category_to_skus()` — reads `../data/sku_category_map.csv`; returns `{cat: set(sku)}`. - `_resolve_skus(row, cat_map)` — explicit SKUs win; otherwise expand category via `cat_map`. Returns `(sku_set, from_category_bool)`. - **`detect_conflicts(df)`** — pairs every (i` backup); `--append` mode concatenates with existing rows. --- ## 7 · Cross-cutting helpers (app.py) — data-loading semantics the new stack must replicate Not page functions, but every page depends on them: - **`load_sales_data()`** — wraps `sales_clean.csv`; provides `yw = year * 100 + week`, plus `xyz` decoration from `sku_plan_list.csv` (col `total_xyz`). - **`load_demand_plan()`** — reads `Polleo_Demand_Plan.xlsx` "Demand Planning" blocks; returns `{cws[], rows[{SKU, Category, baseline[], factors[]}]}`. Falls back to per-block reconstruction if `Demand Output - Total` is empty/uncached. - **`load_sku_prices()`** — `sku_prices.csv` → `dict[sku, avg_sell_price]`. - **`load_vp_mp_inputs()`** — `{vp, mp}` DataFrames from `vp_input.csv` / `mp_input.csv`. - **`load_consensus_snapshots()`** — reads `data/consensus/snapshot_*.json` files, returns list sorted by timestamp. - **`load_corrections()` / `save_corrections()`** — `data/corrections.json` (planner factor and VP/MP overrides keyed by SKU and CW). - **`compute_accuracy_metrics(sc)`** — wraps `_load_total_fa_data` + per-row FA derivation. - **`compute_exceptions(sc)`** — spike (>30% WoW) and zero-sales (3+ weeks at 0) flag rows. - **`compute_plan_revenue(plan, sku_prices)`** — `Σ baseline × factor × price` across the 13-week horizon. - **`write_forecast_for_supply()`** — projects `forecast_for_supply.csv` from latest plan + on-tops (consumed by Supply module). These are the seams the new FastAPI repositories must reproduce. Any new endpoint that surfaces "sales", "demand plan", "prices", or "accuracy" must produce numbers that match what these helpers produce against the same source CSVs.