FMCG analytics: sell-in, sell-out, trade spend and service in one reconciled view

An FMCG supplier does not own the shelf, the checkout or the customer record. Every number that matters to the business either arrives from somebody else's system or has to be argued out against somebody else's system. That single structural fact is what FMCG analytics exists to deal with, and it is why data analytics for fast moving consumer goods looks nothing like reporting in a business that sells direct.

This page is written for the person who has to carry the number into the room: a commercial or national account manager preparing for a range review, a sales director who has to say whether the last catalogue period paid for itself, a demand planner defending a forecast, or a finance manager who needs customer margin after trade before the board pack goes out. If you have ever had four versions of last week's sales sitting in four tabs and no defensible way to say which one is right, this page is about that.

What it settles is narrow and specific: which sources a supplier's reporting has to draw on, how the core measures are actually calculated, why the calculation goes wrong when it is done casually, and what a reporting set has to reconcile before anyone can trust a chart on it. There are no client names and no percentages here, because we do not publish outcomes we cannot attribute. There is a lot of mechanics, because that is the part nobody else writes down.

What FMCG analytics has to answer

Strip away the dashboards and the reporting exists to settle five questions. They get asked in almost exactly these words.

"Did that promotion actually make money?" Not did it move volume — it always moves volume. Did the incremental margin cover the scan-back, the off-invoice, the co-op advertising, the gondola end and the deductions that turned up nine weeks later against an unrelated invoice.

"Why is our number different from the retailer's number?" Usually asked about service. Sometimes asked about sales. Always the same underlying problem: two parties measuring the same event against different denominators, on different clocks, at different units of measure.

"Whose forecast was wrong?" Asked when there is a truckload of short-dated stock in the distribution centre, or when a promotion sold out in week one and the retailer has a gap on shelf and a long memory.

"What is this customer actually worth to us?" Gross sales by customer is easy and nearly useless. Net realised price after every rebate, deduction and freight line, minus cost to serve, is the number that changes decisions, and most suppliers cannot produce it on demand.

"Are we ranged where it counts, and is it selling once we are there?" Distribution gains that look good on a store count and disappear on a weighted measure. Rate of sale that cannot be compared between two SKUs sitting on different distribution.

Everything below is in service of those five.

The data, and where it comes from

Six layers, each arriving in a different shape, on a different cadence, keyed differently. Nothing joins to anything else without deliberate work.

Underneath all of it sits a master data problem that causes more reporting failures in Australian FMCG than any analytical mistake: the supplier's item master, the GS1 Australia National Product Catalogue record used with Woolworths, Coles and Metcash, each retailer's own item code and the syndicated provider's product hierarchy are four different keys for the same carton. Nobody owns the mapping between them, so it gets rebuilt by hand every time somebody needs it.

The KPIs, defined properly

Formulas, not benchmarks. We do not publish target values for these because there is no citable Australian benchmark for any of them.

Why this is hard in a spreadsheet

Not because spreadsheets are bad. Because three specific joins are beyond what a workbook can hold together reliably.

Joining scan data to your own sales. Scan is at the retailer's item and store level, on the retailer's week. Your sales are at your item, customer and invoice level, on your period calendar. Syndicated data is at the provider's product hierarchy and market definition. Reconciling them needs a maintained four-way mapping between your item master, the National Product Catalogue GTIN, each retailer's item code and the provider's product code — plus a calendar that reconciles retail weeks to your accounting periods. In a workbook this is a VLOOKUP against a file that lives somewhere else and is updated by whoever remembered. The join silently drops the new SKUs first, which are the ones everyone is watching.

Rebate accrual timing. The deal is agreed in one month, the volume ships in another, the scan-back is claimed six to ten weeks later, and it arrives as a deduction against an unrelated invoice carrying a claim number that does not match any promotion code you use internally. The commercial spreadsheet tracks planned spend; the ledger tracks actual deductions; nothing links a row in one to a row in the other. That is why the promotion argument has no resolution mechanism and gets settled by seniority. The fix is not cleverer analysis — it is giving every promotion one identifier that survives from the trading agreement into the ERP into the deduction, so the post-analysis assembles itself.

DIFOT reconciliation, where two parties measure differently. The gap between your 98% and the retailer's 91% is enumerable, and each line of it is a different data problem. The retailer measures against the original purchase order; you measure against the confirmed quantity after the order was trimmed to available stock — same delivery, two denominators. You count lines, they count orders. You timestamp despatch, they timestamp receipt into the DC or the booked window, so the time your truck spent queuing belongs to whoever's clock you use. The despatch advice says what was sent, the receipt scan says what was booked in, and pallet-level versus carton-level receipting produces different shortfalls. Then there are non-conformances recorded in the retailer's system that never flow back into yours. Rebuilding that reconciliation means one row per delivery carrying order, confirmation, despatch, advice, receipt and non-conformance together — which is a data model, not a pivot table.

There is a fourth, quieter one. Forecast accuracy cannot be measured at all if no forecast version was kept. If the only forecast in the system is the current one, nobody can answer what was known on the day the production run was committed, and the argument is unwinnable by design.

What a working FMCG dashboard shows

Four pages, each with an owner and a question it settles.

Promotion post-analysis. Every promotion as one row, keyed on a single promotion identifier: planned spend, actual claimed spend, uplift against a stated baseline method, cannibalisation of your own lines, the four weeks after for pantry loading, and incremental margin against investment. Read by the national account manager before the next range or promotional plan goes in, and by finance when the accrual is released. It ends the argument described above by making the two ledgers the same ledger.

Customer and channel margin. The gross-to-net bridge, per customer, per period, with every deduction type named and traceable back to a claim. Then cost to serve — freight, 3PL handling, case pick versus full pallet, rebooking, short-life credits, pallet hire — down to contribution. Read by the commercial lead and by finance. This is the page that changes what gets negotiated.

Service level. DIFOT rebuilt from your own data at the same grain the retailer scorecards it, with the variance broken into its causes rather than shown as a single percentage. Read by supply chain and by whoever fronts the retailer's service review, who needs to arrive with a list of specific deliveries rather than a disagreement.

Forecast accuracy and bias. WMAPE and bias by category, brand and customer, measured against snapshotted forecast versions at the lag decisions are actually made at. Read by demand planning and S&OP. It converts "whose forecast was wrong" into "we are consistently 6% long on this brand at eight weeks", which is an actionable statement rather than an accusation.

A fifth page — distribution, share and rate of sale from syndicated and scan data — earns its place if the business subscribes to the data, and carries most of the load during a category review.

Sales dashboards in Power BI

If you are searching for an FMCG sales dashboard in Power BI, the useful question is not which visuals to use. It is what the model underneath has to reconcile before any visual is honest.

A model that works has scan and shipments as two separate fact tables at their own grain, joined through conformed dimensions rather than flattened into one table. Product resolves through a bridge that maps your item code, the GTIN, the retailer item code and the syndicated product code. Date resolves through a calendar that carries both retail weeks and your accounting periods, so a four-week retail period and a calendar month can sit on the same page without lying to each other. Customer resolves through a hierarchy that survives a banner restructure.

Promotion is its own dimension, keyed on the identifier that also appears on the ERP transaction and on the deduction. Without that, promotional ROI in Power BI is a chart of two unrelated numbers placed near each other.

Beyond that: row-level security so an account team sees its own customers, incremental refresh tied to the weekly scan drop rather than a nightly full reload, measures written once in DAX with the definition documented on the page, and a deliberate decision about whether a card labelled "sales" means shipments or scan — stated on the dashboard, not assumed. Most FMCG dashboards that lose trust lose it there.

How we work

Roar Data is operator-led and Brisbane-based, serving Queensland and suppliers across Australia. The founder spent 20-plus years in operations across manufacturing, pharmaceutical, glass packaging, FMCG, healthcare, aged care, finance and construction before building reporting for a living, which is why this page reads the way it does.

We start with the Reporting Clarity Diagnostic, fixed at A$1,950. It maps what data you actually have, where the definitions conflict, which joins are broken, and what a working reporting set would need to hold — and you own the output whether or not you build anything with us. Builds are quoted after that, never sold from a page, because a supplier on one retailer with a clean ERP and a supplier on four retailers with a 3PL and a syndicated subscription are not the same job.

Delivery is remote for most of it, on site in Brisbane and South East Queensland where that is useful. Every build is handed over with training and documentation: measure definitions written down, refresh schedules explained, and your team able to change things without calling us. We are rated 5.0 on Google.