# Margin Bridge v2 — Channel-Split Methodology

**Polleo Demand — Finance module, May 2026 upgrade**

## What changed from v1

v1 produced four headline effects (Volume / Price / Mix / COGS) computed on
blended-all-channel data. The "Price" line bundled three completely different
phenomena into one number:

- structural wholesale-vs-B2C pricing difference (wholesale sells at VPC ≈ 30%
  below B2C list — that's not a "pricing problem", it's normal channel mix)
- planned promo investment (intentional discount cost)
- unplanned price erosion (the actual signal CFOs want)

In April 2026 the v1 Price effect was −€1.22M; **investigation showed ~€676K
of that was structural wholesale pricing** that no one had decided to "fix".
The number was misleading the S&OP conversation.

v2 splits the bridge by channel and computes each effect on its own
channel-specific plan baseline.

## Period scope

April + May 2026 only. Those are the only months where:
- `backtest_results` has a committed forecast per (SKU, year, week)
- `erp_transactions` has actual sales with per-line `purchase_value` and
  `tax_base`
- `promo_policy_items` has matching promo policies

Earlier months either lack the forecast or lack rekapitulacija detail.

## The four data sources

| Input | Source | Loaded by |
|---|---|---|
| **Plan qty (total)** | `backtest_results.forecast` ← `data/backtest_fa.csv` | `db/migrate_remaining.py:_load_backtest` |
| **Plan qty per channel** | `backtest_results.forecast_wholesale` + `backtest_results.forecast_retail` (retail includes webshop) | Same loader, new columns added by `db/migrations/bridge_v2_per_channel_forecasts.sql` |
| **Plan price (wholesale)** | `sku_planning.vpc` (manual KAM input) | `db/migrate_dimensions.py` ← `data/sku_plan_list.csv` |
| **Plan price (B2C)** | Per-SKU weighted blend of `erp_prices.normal_retail_ppp` × actual_qty_retail + `erp_prices.normal_webshop_ppp` × actual_qty_webshop | `update_sales.py:compute_recent_normal_ppp` |
| **Plan cost** | **Trailing prior-3-month avg of booked `purchase_value/quantity`** (NEW — replaces NabavneCijene receipts as primary source) | Derived inside `report_bridge_monthly()` from `erp_transactions` |
| **Actual qty per channel** | `SUM(erp_transactions.quantity)` per channel grouped on `lookup_channel_map.channel` | erp_transactions ← rekapitulacija |
| **Actual price** | `SUM(tax_base)/SUM(quantity)` per channel per (sku, month) | erp_transactions |
| **Actual cost** | `SUM(purchase_value)/SUM(quantity)` per (sku, month) — "Nabavna vrijednost €" from rekapitulacija | erp_transactions |

### Channel mapping

`lookup_channel_map.channel` defines:
- `wholesale` — RCM, TRC, VPT, VPB, RIZ, **RAC** (RAC is "B2B-ish web orders"
  but operationally KAM-managed; user confirmed it stays in wholesale)
- `retail` — RCM (POS retail / maloprodaja)
- `webshop` — WSA, WSB, WSC, WSD

The bridge groups `retail` + `webshop` as **B2C**; `wholesale` stays alone.

## The bridge — six headline lines

For each month with both plan and actuals:

```
plan_margin_total = plan_qty_ws × (vpc - plan_cost)
                  + plan_qty_b2c × (b2c_list - plan_cost)
actual_margin_total = Σ(actual_qty × (actual_price - actual_cost))   per (sku, channel)
total_variance = actual_margin_total − plan_margin_total
```

Decomposed into:

### 1. Wholesale total variance
Aggregate of WS Volume + WS Price + WS Mix + WS COGS:

- **WS Volume** = `(Σactual_qty_ws − Σplan_qty_ws) × ws_plan_avg_margin`
- **WS Price (VPC drift)** = `Σ((actual_unit_price_ws − VPC) × actual_qty_ws)` per SKU
  Captures: (a) discounts below VPC negotiated per customer, (b) channel
  mix among different wholesale doc types (TRC vs VPB etc.)
- **WS COGS** = `−Σ((actual_unit_cost_ws − plan_cost) × actual_qty_ws)` per SKU
- **WS Mix** = WS total variance − WS Volume − WS Price − WS COGS (residual)

### 2. B2C total variance
Aggregate of B2C Volume + B2C Price + B2C Mix + B2C COGS:

- **B2C Volume** = `(Σactual_qty_b2c − Σplan_qty_b2c) × b2c_plan_avg_margin`
- **B2C Price** = `Σ((actual_unit_price_b2c − b2c_list) × actual_qty_b2c)` per SKU
  Catalog list = `(qty_retail × normal_retail_ppp + qty_webshop × normal_webshop_ppp) / qty_b2c`
- **B2C COGS** = `−Σ((actual_unit_cost_b2c − plan_cost) × actual_qty_b2c)` per SKU
- **B2C Mix** = B2C total variance − B2C Volume − B2C Price − B2C COGS (residual)

### 3. Channel mix effect
`channel_mix = total_variance − WS_total_variance − B2C_total_variance`

This residual captures the structural margin impact of wholesale-vs-B2C share
shifting between plan and actual. When the actual mix sells more wholesale
(lower-margin channel) than the plan assumed, channel_mix is negative.

## ROI overlays (on top of the channel-split bridge)

The three ROI overlay lines are **slices of the actual transactions** that
explain pieces of the WS / B2C totals — they don't add to the reconciliation,
they decompose pieces of it.

### Promo ROI (B2C only)

```
Slice:    erp_transactions WHERE (product_id, transaction_date) matches an
          active promo_policy_items window AND channel IN ('retail','webshop')
          — wholesale gets no promo treatment per Polleo policy

list_value   = Σ(qty × channel_appropriate_list)   ← normal_retail_ppp or normal_webshop_ppp
                                                     blended by actual retail/webshop mix
promo_uplift     = Σ(qty × plan_margin_per_unit)
promo_investment = -(list_value − actual_revenue)
promo_roi        = promo_uplift + promo_investment
```

`avg_sell_price` is NOT used here — it contains baked-in past promo
discounts which would understate the depth of the current promo.

### Loyalty impact (B2C)

```
Slice:    erp_transactions WHERE has_loyalty = TRUE
          (`has_loyalty` derived at load time from "Loyalty kartica" column:
           digit string = loyalty card used, "nonLoyalty"/empty = no card)

loyalty_impact = -(list_value − actual_revenue)
```

Always negative — loyalty cards give a discount, never raise price.

### New-listing ROI (wholesale only)

```
Slice:    erp_transactions WHERE channel = 'wholesale' AND
          (product_id, partner_id) appears in our entire history for the
          first time on this transaction's date (52w lookback intent;
          using "all history we have" in practice ≈ 60w)

new_listing_volume     = Σ(qty × plan_margin_per_unit)   ← incremental volume at standard margin
new_listing_investment = -(Σ(qty × avg_sell_price) − actual_revenue)
new_listing_roi        = volume + investment
```

KAM-driven onboarding of new customers / new SKUs at concessional pricing.
Positive ROI = volume gain at standard margin exceeds the discount cost.

## What changed methodologically (v1 → v2)

| Area | v1 | v2 |
|---|---|---|
| Channels | Blended | WS / B2C / channel_mix split |
| Plan price wholesale | (no separate plan price) | `sku_planning.vpc` |
| Plan price B2C | `erp_prices.avg_sell_price` (12w blended realized, retail+webshop only) | Per-SKU weighted blend of `normal_retail_ppp` + `normal_webshop_ppp` by actual qty mix |
| Plan cost | NabavneCijene 3-month prior receipt avg | Trailing 3-month avg of booked `purchase_value/qty` (consistent with actual_cost source) |
| Actual cost | Booked `purchase_value/qty` → fallback NabavneCijene → Sifrarnik | Same cascade (already in v1) |
| Promo investment list | `avg_sell_price` | Channel-appropriate `normal_*_ppp` |
| Promo channel scope | All channels matched on policy date window | B2C only (retail + webshop) — wholesale excluded |
| Reconciliation | V + P + M + COGS = total_variance | WS_var + B2C_var + channel_mix = total_variance |

## Sample results — April + May 2026

### Apr 2026 (total variance −€750K)

| Line | Value |
|---|---|
| Wholesale total variance | **−€156K** |
| └ WS Volume | +€247K |
| └ WS Price (VPC drift) | −€467K |
| └ WS Mix | +€66K |
| └ WS COGS | −€3K |
| B2C total variance | **−€498K** |
| └ B2C Volume | −€1K |
| └ B2C Price | −€380K |
| └ B2C Mix | −€115K |
| └ B2C COGS | −€2K |
| Channel mix effect | **−€95K** |
| **Reconciles to total** | **−€750K ✓** |
| Promo ROI (B2C) | €0 (no policies active in April) |
| Loyalty impact (B2C) | −€0.4K |
| New-listing ROI (wholesale) | +€82K |

### May 2026 (total variance −€378K)

| Line | Value |
|---|---|
| Wholesale total variance | **−€75K** |
| └ WS Volume | +€151K |
| └ WS Price (VPC drift) | −€280K |
| └ WS Mix | −€42K |
| └ WS COGS | +€96K |
| B2C total variance | **−€86K** |
| └ B2C Volume | +€453K |
| └ B2C Price | −€789K |
| └ B2C Mix | +€61K |
| └ B2C COGS | +€189K |
| Channel mix effect | **−€217K** |
| **Reconciles to total** | **−€378K ✓** |
| Promo ROI (B2C, channel-list pricing) | +€66K |
| Loyalty impact (B2C) | −€1.2K |
| New-listing ROI (wholesale) | +€13K |

## Known limits

1. **Channel mix is a residual.** It captures both structural share shift
   AND any computation gap between the aggregate and per-channel V/P/M/COGS
   decompositions. For Apr (−€95K, 13% of variance) and May (−€217K, 57% of
   variance) it's a real, large structural number — but we can't yet
   attribute it cleanly to "more wholesale" vs "more retail" without an
   explicit plan channel-mix target.

2. **Per-channel plan_qty assumes engine's split is correct.** When
   `backtest_results.forecast_wholesale` / `forecast_retail` are both 0 (the
   engine didn't produce a per-channel split for that SKU), the channel
   plan_qty is 0 and the bridge attributes all variance for that SKU to
   channel_mix. This is the same problem v1 had, just made visible.

3. **NabavneCijene fallback still exists** for SKUs without booked
   transaction history. ~67% of SKUs in May fall back to receipt-based or
   Sifrarnik cost — for those SKUs, plan_cost and actual_cost are computed
   from the same source so COGS effect is 0 by construction.

4. **Promo qty in May with B2C filter is 8,410** (vs the v1 number that
   included all channels at 39,411). The drop is because wholesale
   transactions during a promo window were being miscounted — they're not
   actually promo, they just happened during the date range.

## File references

```
backend/services/finance_service.py
    report_bridge_monthly()       ← THE function, ~620 lines (was 350 in v1)

backend/api/routers/finance.py
    GET /api/finance/bridge/monthly   ← endpoint (returns dict, no schema enforcement)

frontend/src/pages/finance/Bridge.tsx
    channelView state             ← 'all' / 'wholesale' / 'b2c' tabs
    Per-channel V/P/M/COGS card grids
    Channel mix banner

db/migrations/bridge_v2_per_channel_forecasts.sql
    Adds forecast_retail / forecast_wholesale / actual_retail / actual_wholesale / ws_share
    to backtest_results
    Adds forecast_retail / forecast_wholesale to forecasts

db/migrate_remaining.py
    _load_backtest()              ← updated to populate new per-channel columns
    _load_forecast_log()          ← same

data/backtest_fa.csv              ← engine output, already had per-channel columns
data/forecast_log.csv             ← engine output, already had per-channel columns
```

## How to refresh data

1. Sales upload — `update_sales.py` reads new rekapitulacija files
2. Re-run forecast — `forecast_engine.py` (already produces per-channel)
3. Reload DB — `python db/migrate_remaining.py` (will TRUNCATE + INSERT
   `backtest_results` and `forecasts` with per-channel columns)
4. Bridge auto-picks up new data on next page load — no caching

## Q&A vs other Claude session

Anticipating questions you'd ask:

**Q: Why does May B2C Price effect = −€789K? Did we discount more aggressively in B2C?**
A: Yes, partly. Promo investment alone is around −€18K (in the corrected B2C-list-priced calc, was −€47K with the v1 avg_sell_price approach). The remaining −€770K is mostly unplanned retail markdowns (B2C Price minus Promo Investment ≈ unplanned price erosion). Worth digging into which SKUs by mode-based normal_retail_ppp moved most.

**Q: Channel mix −€217K in May — what does that actually represent?**
A: It's the residual after WS variance + B2C variance. In May:
  - Plan ratio (from backtest): WS plan_qty=60K / B2C plan_qty=40K → 60% WS  
  - Actual ratio: WS actual_qty=128K / B2C actual_qty=90K → 58% WS
  - But the engine's plan_qty per channel may not be fully reliable for all 'total'-mode SKUs (those without an explicit split). Some of the −€217K is "channel mix" and some is "we don't actually know the channel plan for this SKU".

**Q: Should the COGS line tell us if suppliers raised prices?**
A: Now yes, because plan_cost is "trailing 3-month avg of booked cost" rather than NabavneCijene receipts. COGS effect = (booked actual_cost − trailing_3m_booked_cost) × actual_qty. Positive COGS effect = we sold at LOWER booked cost than the prior-3-month avg (supplier deflation or selling old cheaper FIFO stock). Negative COGS effect = supplier cost rose vs trailing.

**Q: What's the difference between the bridge's Price line and "Promo Investment"?**
A: B2C Price captures EVERY price gap on EVERY B2C transaction. Promo Investment is the subset of that gap that was attributable to an active promo policy. The residual ("unplanned price" component, computable as B2C Price − Promo Investment − Loyalty Impact) is the truly unplanned markdowns.