Multi-Country Margin Tracker: From List Price to Net-Net, Week by Week

Challenge

A food and beverage manufacturer wanted one view of how its prices move from the list tariff to the final net price. The view needed to show every trade condition along the way, what shoppers actually pay on the shelf, and how the margin splits between manufacturer and retailer. It had to work per product, per customer and per week, for several European countries, and also as a European total.

The data sat in many places, and each country got it from different systems and providers. The ERP held invoices, tariffs and taxes. A planning tool held the forecast. A retail panel provided weekly sell-out, a promotion-planning tool held the trade terms, and a monitoring service tracked shelf prices. Every source had its own codes and its own granularity: daily invoices, monthly forecasts, panel weeks that don't line up with calendar weeks, and promotions agreed with a buying group rather than with each store.

The hardest part was the discount logic. Trade conditions apply in a cascade of up to five steps. Each one is a percentage or a fixed amount, and is applied to a specific intermediate price. Some are promotional, and some are visible to the retailer and some are not. A single country had a previous version of the tracker. Scaling it to more countries meant the logic could no longer be hard-coded. On top of that, the countries ran on different generations of the data platform and needed to migrate at their own pace.

Approach

We split the solution into two layers with a clear contract between them.

• A country layer, owned by each country. Every source is brought to the same grain: week × product × customer. A product-mapping tool links panel and shelf-price products to ERP materials. Master data services manage customers, code mappings and access rights. The tricky parts are handled explicitly:
  • Monthly forecasts are spread over weeks in line with invoiced volumes.
  • Panel sell-out is allocated from product groups to individual materials using a rolling 52-week sales mix.
  • Prices that change mid-week are weighted by the units sold under each price.
  • Every volume and price is converted to sales units and kilograms.
• Business-maintained trade-term rules. Each promotion mechanism is classified once, in master data: which price it applies to, which cascade step it produces, whether it is a percentage or an amount, and whether the retailer sees it. Business users maintain these rules, not developers.
• Incremental by design. Master data is compared with yesterday's snapshot, and only the affected products, customers and weeks are recalculated. Deletions are kept as flags, so they travel cleanly downstream. The full daily run takes around a quarter of an hour.
• A data-lake hand-off. A control table drives incremental Parquet exports per country and table, and merges them into the common layer. New columns are added automatically, so a new condition type or a country-specific field flows through without code changes. Because the hand-off is file-based, each country can move to the next platform generation independently.
• A common layer, built once for everyone.
  • The net-price engine walks the cascade from tariff to Net 1 … Net 5.
  • It produces four views of the same price: manufacturer or retailer perspective, with or without promotions.
  • It creates one column per condition type dynamically.
  • It derives European totals using volume-weighted averages.
• Generated reporting. The Power BI model has more than 560 measures, and none are written by hand. A small Python generator combines a library of DAX templates with a configuration file and writes the measures straight into the model's source files. Every price is therefore available per unit, per kilogram and in total, as a period average or as the latest value. Margins, profit-pool shares and year-on-year comparisons follow one consistent pattern. Row-level security by country and by customer is maintained in master data.
• One orchestrated pipeline. A single scheduled workflow runs ingestion, transformations, the hand-off, the net-price engine and the report refresh, in dependency order, with failure alerts.