# Margin Bridge — Methodology and Data Audit

**Purpose of this document**: explain exactly what the monthly margin
bridge in the Polleo Demand app is calculating, what data sources it
uses, and where the methodology is solid vs where it relies on proxies.
Drop this into a Claude / ChatGPT chat to interrogate the assumptions
or compare against a different bridge design.

Anchor week when this was written: **2026W21**. Numbers below are live
from the Postgres DB at that moment.

---

## TL;DR

- Bridge runs for **April 2026 and May 2026 only**. Earlier months
  excluded because Polleo only started running a real demand forecast
  in April 2026 — anything before that has no genuine plan baseline
  to compare against.
- Both months are **FULL tier** — real plan (backtest forecast), real
  prices (erp_transactions), real costs (NabavneCijene receipts).
- **Day-fraction splitting**: ISO weeks that straddle a month
  boundary are split pro-rata by day count (e.g. CW18 Apr 27 - May 3
  contributes 4/7 to April and 3/7 to May). This is mass-preserving —
  no double-counting and no week falls into the wrong month.
- April 2026 shows 100% coverage (30/30 days). May 2026 shows partial
  coverage (17/31 days) because actuals only extend to CW20 and
  backtest plan only extends to CW19. The UI surfaces this with a
  "days observed / days in month" badge so partial months aren't
  mistaken for full ones.
- **Both months are dominated by a large negative Price effect** —
  heavy promo discounting pulling realized prices below the catalog
  price baseline. Volume favorable, Mix and COGS small.

---

## 1. What the bridge does

For April and May 2026, the bridge decomposes the difference between
**plan margin** and **actual margin** into four causes:

| Effect | Question it answers | Accountable team |
|---|---|---|
| **Volume** | Did we sell more or fewer units than planned, at plan margins? | Demand Planning + Sales |
| **Price** | Did we sell at higher or lower prices than current baseline? | Commercial / KAM / Trade Marketing |
| **Mix** | Did the SKU proportion shift toward higher- or lower-margin products? | Category Management |
| **COGS** | Did our input costs from suppliers move month-over-month? | Procurement / Sourcing |

The four effects sum to the total margin variance for that month:
`Volume + Price + Mix + COGS = Actual margin − Plan margin`

---

## 2. The four components — formulas and intuition

### 2.1 Volume effect (aggregate, at plan-average margin)

```
plan_avg_margin = total_plan_margin / total_plan_qty       # €/unit, plan mix
volume_effect   = (total_actual_qty − total_plan_qty) × plan_avg_margin
```

**Intuition**: "If we had sold the actual total quantity at the
plan-mix average margin, what would have happened?" Isolates the
quantity miss from the mix shift.

### 2.2 Price effect (per-SKU sum)

```
price_effect = Σ_sku [ (actual_unit_price − plan_unit_price) × actual_qty ]
```

**Intuition**: "Per SKU, what did selling at the realized price
instead of the plan price do to margin?" Volume held constant at
actual.

### 2.3 COGS effect (per-SKU sum)

```
cogs_effect = − Σ_sku [ (actual_unit_cost − plan_unit_cost) × actual_qty ]
```

Sign convention: cost increase is **negative** margin impact.

**Intuition**: "Per SKU, what did the change in unit cost do to
margin, on actual volumes?"

### 2.4 Mix effect (residual)

```
plan_margin_total   = Σ_sku [ plan_qty   × (plan_price   − plan_cost)   ]
actual_margin_total = Σ_sku [ actual_qty × (actual_price − actual_cost) ]
total_variance      = actual_margin_total − plan_margin_total

mix_effect = total_variance − volume_effect − price_effect − cogs_effect
```

**Intuition**: Mix is what's left after V + P + C is accounted for. It
represents the shift in **which SKUs** sold — not how many, not at
what price, not at what cost. Pure proportion change.

**Worked toy example for Mix:**

| | Plan | Plan margin/unit | Plan total | Actual | Actual total |
|---|---|---|---|---|---|
| SKU A (premium) | 100 | €10 | €1,000 | 50 | €500 |
| SKU B (discount) | 100 | €2 | €200 | 150 | €300 |
| Total | 200 | avg €6 | €1,200 | 200 | €800 |

- Volume effect: 0 (total qty matches plan exactly)
- Price effect: 0 (each SKU sold at its plan price)
- COGS effect: 0 (no cost change)
- Total variance: −€400 ← **all of it is MIX**

The wrong SKUs sold. High-margin under-delivered; low-margin
over-delivered. Same total volume.

---

## 3. Why only April + May 2026?

Polleo started running a structured forecast (StatsForecast pipeline,
~489 planned SKUs) **in April 2026**. Before that:

- No formal forecast was published per SKU per week
- Any "plan baseline" we'd build for older months is a back-cast — a
  trailing-average reconstruction of "what the plan would have looked
  like" — not the literal plan
- Variance vs a reconstruction doesn't tell us anything about planning
  accuracy or commercial discipline; it just compares two views of
  history

So the bridge enforces a hard cutoff:

```python
FORECAST_START_YEAR  = 2026
FORECAST_START_MONTH = 4
# month_keys filtered to >= 202604 only
```

Result: only **April 2026** and **May 2026** appear in the bridge.
Earlier months (Jan 2025 - Mar 2026) are excluded entirely.

For May 2026 the bridge sees a partial month — backtest forecast
covers CW19 only (Thursday May 7). CW20 (May 11-17) and CW21 (May
18-24) fall back to the 12w trailing-non-promo proxy. This is noted
in the limitations section.

---

## 4. Data sources — what feeds each component

### 4.1 Plan side

| Field | Source | Coverage |
|---|---|---|
| `plan_qty` | `backtest_fa.csv` (column `forecast`) where it exists for that SKU-week, else **trailing 12-week non-promo average × number of weeks in the month** | backtest_fa covers CW12-CW19 2026 (8 weeks); after the April-cutoff filter this means full coverage for April 2026 and only CW19 for May 2026 |
| `plan_price` | `erp_prices.avg_sell_price` — **current snapshot** (most recent valid_from) | 9,869 SKUs |
| `plan_cost` | qty-weighted avg of `NabavneCijene` receipt unit cost over the **3 months prior to the period** | 3,773 SKUs with receipt history |

### 4.2 Actual side

| Field | Source | Coverage |
|---|---|---|
| `actual_qty` | `v_sales_weekly_full.qty_total` summed across ISO weeks in the calendar month | Full coverage for Apr/May 2026 |
| `actual_price` | `erp_transactions.tax_base` (net of VAT) ÷ `erp_transactions.quantity` summed per (sku, month) | erp_transactions covers 2026-03-02 → 2026-05-17 — full April, partial May |
| `actual_cost` | qty-weighted avg of `NabavneCijene` receipt unit cost during the period | Full coverage Apr + May 2026 |

### 4.3 ISO week → calendar month mapping (day-fraction splitting)

Every weekly value (`plan_qty`, `actual_qty`, etc.) is split across
the calendar month(s) it touches by **day count**:

```
week_value × (days_in_month_X / 7) → contributes to month X
```

Most weeks fall entirely inside one month and produce a single
contribution at 7/7. Straddle weeks produce two contributions, e.g.
**CW18 2026 (Apr 27 - May 3)**:
- 4 days in April (Apr 27, 28, 29, 30) → April gets 4/7 of the value
- 3 days in May (May 1, 2, 3) → May gets 3/7 of the value

This is mass-preserving (the two fractions sum to 1.0) and removes
the "snap to one month" error that the older Monday/Thursday rules
imposed.

Helper: [backend/services/time_utils.py](backend/services/time_utils.py)
exports `split_iso_week_across_months(year, week)` which returns
`[(cal_year, cal_month, fraction), …]`. The bridge, NPL report monthly
view and revenue forecast plan distribution all use it.

For April 2026 (full month):
- CW14 (Mar 30 - Apr 5): 2 days March, 5 days April → April gets 5/7 of CW14
- CW15 (Apr 6 - 12): all in April → 7/7
- CW16 (Apr 13 - 19): all in April → 7/7
- CW17 (Apr 20 - 26): all in April → 7/7
- CW18 (Apr 27 - May 3): 4 days April → 4/7
- April total = 5 + 7 + 7 + 7 + 4 = 30 days = full month ✓

For May 2026 (currently partial because actuals stop at CW20):
- CW18 (Apr 27 - May 3): 3 days May → 3/7
- CW19 (May 4 - 10): all May → 7/7
- CW20 (May 11 - 17): all May → 7/7
- May observed = 3 + 7 + 7 = **17 days of 31** = 55% coverage

---

## 5. Tier classification

Both months come back as **FULL tier** — the most complete data state
the bridge supports:

| Tier | Conditions | What it means |
|---|---|---|
| **FULL** | `erp_transactions` + `NabavneCijene` both present | All 4 effects (V/P/M/COGS) are real |
| ~~COST_ONLY~~ | (not relevant after the April cutoff) | n/a |
| ~~VOLUME_ONLY~~ | (not relevant after the April cutoff) | n/a |

The codebase still has the three tiers defined so it can handle
future months that fall back to partial data, but right now both
displayed months are FULL.

---

## 6. Coverage matrix — what data exists per source (post-cutoff scope)

### 6.1 `backtest_fa.csv` (plan-side forecasts)

| | |
|---|---|
| Rows | 3,809 |
| SKUs | 503 |
| Span | CW12-CW19 2026 (8 weeks) |
| Useful weeks after April cutoff | CW14-CW19 (April CW14-CW18 + May CW19) |
| Coverage Apr 2026 | 5 of 5 weeks (full) |
| Coverage May 2026 | 1 of 3 weeks (only CW19, the rest falls to trailing-avg proxy) |
| Used as | `plan_qty` per (sku, week) where available |

### 6.2 `v_sales_weekly_full` (actual volume)

| | |
|---|---|
| Rows | 116,197 |
| SKUs | 9,951 |
| Span | CW01 2025 - CW20 2026 (62 weeks) |
| Apr 2026 weeks present | CW14-CW18 (full month) |
| May 2026 weeks present | CW19, CW20 (CW21 not closed yet) |
| Used as | `actual_qty` |

### 6.3 `erp_transactions` (actual price + sales-channel data)

| | |
|---|---|
| Rows | 283,612 |
| SKUs | 4,165 |
| Span | 2026-03-02 → 2026-05-17 |
| Apr 2026 coverage | Full (Mar 30 - May 3 captured) |
| May 2026 coverage | Partial (only May 1-17, then truncated) |
| Used as | `actual_price` |

### 6.4 `NabavneCijene.xlsx` (receipt cost — canonical COGS source)

| | |
|---|---|
| Rows | 6,674 receipts |
| SKUs | 3,773 |
| Span | 2025-05-26 → 2026-05-21 (13 months) |
| Per-row fields | date, qty, Nabavna vrijednost € |
| Aggregation | qty-weighted avg unit cost per (sku, year, month) |

#### Example of real cost drift visible in NabavneCijene

**POL09740** (Polleo 1st Whey 908g Švicarska Čokolada):

| Receipt date | Qty | Unit cost € |
|---|---|---|
| 2025-08-14 | 1,258 | 14.61 |
| 2025-10-29 | 412 | 14.61 |
| 2025-11-27 | 252 | 14.58 |
| 2025-12-08 | 504 | 14.91 |
| 2026-01-23 | 1,695 | 14.41 |
| 2026-03-02 | 840 | 15.34 |
| 2026-04-28 | 1,048 | 15.61 |

+6.8% cost inflation across 8 months. The bridge picks this up as
unfavourable COGS effect in the months where new receipts arrive at
the higher cost.

---

## 7. Live numbers (anchor 2026W21) — after day-fraction splitting

Only two months, both FULL tier. All values in EUR. Coverage column
shows how much of the calendar month is observed (full vs partial).

| Month | Tier | Coverage | SKUs | Plan margin | Actual margin | **Variance** | Volume | Price | Mix | COGS |
|---|---|---|---|---|---|---|---|---|---|---|
| **Apr 2026** | FULL | 30/30 (100%) | 2,947 | 1,892,107 | 1,138,740 | **−753,367** | +543,437 | **−1,220,313** | −66,175 | −10,316 |
| **May 2026** | FULL | 17/31 (55%) | 2,257 | 774,270 | 57,182 | **−717,088** | +561,405 | **−1,224,185** | −53,595 | −713 |

### Per-day normalization (so the partial month is comparable)

| | Apr 2026 | May 2026 (so far) |
|---|---|---|
| Plan margin / day | €63,070 | €45,545 |
| Actual margin / day | €37,958 | €3,364 |
| **Variance / day** | **−€25,112** | **−€42,182** |
| Volume / day | +€18,114 | +€33,024 |
| Price / day | −€40,677 | −€72,011 |
| Mix / day | −€2,206 | −€3,152 |
| COGS / day | −€344 | −€42 |

May is running **worse per day than April** (−€42k vs −€25k variance
per day). The price drag is also harsher per day (−€72k vs −€41k).

### Story

The pattern in both months is identical and the per-day numbers
confirm it gets worse in May, not better:

1. **Volume is favourable** in both months (+€18k/day April,
   +€33k/day May) — we sold more units than the forecast called for.
   Demand engine on the conservative side.

2. **Price is the killer** (−€41k/day April, −€72k/day May) —
   realized selling prices came in well below the catalog
   `avg_sell_price` baseline. This is **heavy promo discounting**:
   volume came at the cost of margin per unit. Commercial / KAM
   accountability.

3. **Mix is mildly negative** (−€2k/day in both months) — slight
   skew toward lower-margin SKUs (wholesale channel rising,
   cheaper variants selling better).

4. **COGS is small** (under €1k/day either month) — minor
   procurement variance. Cost inflation is real (see POL09740) but
   at the bridge total it's a rounding error compared to the price
   drag.

**Net read**: we're winning volume but giving back margin via promo
prices. The story is consistent across both months — this isn't a
one-off, it's the operating pattern. **Promo depth is the lever
that needs commercial review.**

---

## 8. Known limitations

### 8.1 Plan price is a single current snapshot

`erp_prices.avg_sell_price` only has one row per SKU (the most recent
`valid_from`). For both Apr 2026 and May 2026 the bridge uses today's
avg_sell_price as the plan baseline. Since prices haven't moved
dramatically in the last 2 months this is probably OK, but if any
re-pricing happened in those weeks the Price effect is mis-attributed.

**Fix**: snapshot `erp_prices` weekly into a `erp_prices_history`
table so the bridge can pick the right plan baseline per period.

### 8.2 May 2026 is half-observed

May 2026 only sees CW18 (partially, 3/7 of the days), CW19 and CW20
in actuals (v_sales_weekly_full doesn't have CW21+ yet). Total
observed = 17 of 31 days = 55% coverage. The bridge surfaces this
in the UI with a `days observed / days in month` badge so the
partial month is obvious.

Backtest plan ends at CW19, so plan_qty for CW20 falls back to the
trailing 12w non-promo proxy. About 60% of May's plan_qty is real
backtest forecast (CW18 + CW19), 40% is the trailing proxy (CW20).

**Fix**: when May closes and a new backtest is published, the next
run will show 31/31 (100%) coverage and the proxy disappears.

### 8.3 erp_transactions truncates at 2026-05-17

May 2026 actuals after May 17 aren't reflected in price detail. So
Price effect for May is biased toward the first half of the month.

**Fix**: re-extract `Rekapitulacija` files when May closes and reload
via `update_sales.py`.

### 8.4 Receipt cost vs sold cost

`actual_cost` from NabavneCijene is the **receipt cost** (what we
paid for inbound goods). `erp_transactions.purchase_value / quantity`
would be the **deducted cost** (what was charged to the sale from
inventory under whatever method the ERP uses — FIFO/standard).

For Polleo with relatively short stock cycles, the two should be
close. The bridge currently uses receipt cost on both plan and actual
sides — internally consistent (apples to apples) but not the same as
**accounting COGS** which uses deducted cost.

For a true accounting bridge:
- `actual_cost` = erp_transactions.purchase_value / quantity
- `plan_cost` = same metric from prior period
- Captures procurement variance AND inventory aging / FIFO timing

### 8.5 Mix effect is a residual

Mix is computed as `total_variance − V − P − C`. Standard textbook
approach but it means **any error in V, P or C ends up inside Mix**.
If plan_cost is wrong by €10k, that error gets pushed into Mix not
COGS. Worth keeping in mind when interpreting Mix movements.

### 8.6 Dormant SKU exclusion happens BEFORE the bridge

The bridge inherits the dormant-SKU exclusion from the Finance
context (SKUs with no stock + not newly listed + no sales in 26
weeks are removed entirely). For the bridge this is correct — those
SKUs would contribute zero anyway. Just be aware that the SKU count
per month (2,947 April, 2,257 May) reflects the cleaned universe.

### 8.7 Pseudo-SKUs filtered out

SKUs starting with `MKT`, `OST`, `USL`, `POPUST`, `PROMO_` are
treated as pseudo-products (coupons, marketing line items, services)
and excluded. They polluted the lost-sales top 5 with €99k "Visa
Marketing production" — clearly not a real product.

---

## 9. Open questions to discuss

1. **Is "Volume favorable + Price unfavorable" the right diagnosis?**
   Currently the bridge says "we sold more units than forecast but at
   lower realized prices". Alternative read: the forecast itself
   over-counted what we'd sell at full price and the engine should
   have already adjusted for the promo plan. If true, the Volume
   effect is also distorted.

2. **Should plan_price be the catalog price (avg_sell_price) or the
   blended plan-mix-of-promo+regular?** Currently it's the catalog
   price. Most price effect would disappear if plan_price already
   incorporated planned promo periods.

3. **Should the bridge use receipt cost (procurement view) or deducted
   cost (accounting view) for `actual_cost`?** Currently uses receipt
   cost. The CFO might want accounting view to reconcile to P&L.

4. **Are the channel definitions in `lookup_channel_map` correct?**
   Mix effect is sensitive to which transactions count as retail /
   webshop / wholesale.

5. **Should Mix be decomposed further?** Right now Mix lumps channel
   mix + sub-category mix + premium-vs-discount mix. For a richer
   narrative we could split, but it needs more dimensions in the
   bridge model.

6. **Is 3-month-prior baseline for plan_cost the right window?**
   Shorter (1-month) is more responsive to recent cost changes;
   longer (6-month) is more stable. Currently 3-month.

7. **Forecast scope is 489 planned SKUs but the bridge runs over all
   SKUs with sales (~2,900 in April).** The 2,400+ non-planned SKUs
   get plan_qty from the trailing-avg proxy. Should the bridge be
   limited to the 489 planned-only scope (cleaner comparison to what
   the planning team owns), or kept broad (full P&L view but mixes
   real-plan and proxy)? Currently broad.

---

## 10. Where the code lives

- **Service**: [backend/services/finance_service.py](backend/services/finance_service.py)
  - `FORECAST_START_YEAR` + `FORECAST_START_MONTH` constants — the cutoff
  - `FinanceContext.__init__` — loads NabavneCijene + 12w non-promo avg
  - `report_bridge_monthly()` — the calculation itself, filters months >= cutoff
- **Schemas**: [backend/schemas/finance.py](backend/schemas/finance.py)
- **API**: [backend/api/routers/finance.py](backend/api/routers/finance.py)
  - `GET /api/finance/bridge/monthly` (JSON)
  - `GET /api/finance/bridge/monthly/export` (Excel, multi-sheet)
- **UI**: [frontend/src/pages/finance/Bridge.tsx](frontend/src/pages/finance/Bridge.tsx)
- **Standalone script**: [scripts/cfo_audit.py](scripts/cfo_audit.py)
  outputs CSV / JSON files into `data/cfo_audit_outputs/`

---

## 11. Output files (in `data/cfo_audit_outputs/` after running the script)

After the April-cutoff filter, all bridge files contain Apr 2026 +
May 2026 only:

| File | Content | Current size |
|---|---|---|
| `bridge_by_month.csv` | Every SKU × every month with plan_qty, actual_qty, V/P/COGS per SKU | 579 KB (~5,200 rows) |
| `bridge_summary_by_month.csv` | One row per month: tier, plan, actual, V/P/M/COGS | 0.3 KB (2 rows) |
| `bridge_summary_by_category.csv` | One row per (month, category): V/P/M/COGS | 1.3 KB |
| `bridge_top_movers.csv` | Top 10 positive + top 10 negative SKUs per effect per month | 8 KB |
| `bridge_waterfall_data.json` | All of the above in a structure ready for visualization, including YTD rollup | 9 KB |

---

## 12. How to change the cutoff later

When you eventually want to include earlier or later months (e.g.
once a March 2026 forecast snapshot is reconstructed, or to push
into June 2026):

Edit two constants in [backend/services/finance_service.py](backend/services/finance_service.py):

```python
FORECAST_START_YEAR  = 2026
FORECAST_START_MONTH = 4
```

Both the live API and the `cfo_audit.py` script pick up the change
automatically. No other code changes needed.

---

*Document updated 2026-05-21 — bridge scope clipped to ≥ April 2026
(real forecast start). Anchored to data state at CW21 2026.*