Margin and stockout analytics on a medallion data platform for an NDIS distributor.

The brief

A healthcare-supplies provider serving NDIS, home-care, and aged-care channels through two storefronts (B2C and a custom B2B store) operating on a recently migrated Odoo ERP.

The business had migrated from Cin7 to Odoo, but reporting was still produced through finance manually consolidating CSV exports each month. Margin per SKU and per channel required approximately half a week to compute and was not fully trusted. Stockouts were typically identified by customers rather than by the supplier.

What we found

• Margin calculation resided in spreadsheets. Multiple versions existed across analysts, with figures that were out of date within days of preparation.
• Stockouts were identified by customers. The backorder queue grew before operations had visibility of the shortfall.
• Channel performance had no single definition. The choice between gross and net measurement, and the treatment of NDIS plan-manager rebates, varied between reports.
• Data lineage was not maintained. When figures changed between meetings, the cause could not be explained.

What we did

Implemented a medallion data platform on Microsoft Fabric. Bronze layer ingests Odoo, storefront events, payment provider data, and shipping carrier records; the silver layer applies conformance through a single SKU, channel, and customer dimensional model; gold layer marts are certified by finance.

A margin model was agreed with the CFO — gross, net, and net-of-rebates measurements are explicitly defined rather than implicit. Every Power BI report references the same model.

A stockout signal combines bronze inventory data with silver-conformed reorder cadence to produce a next-morning alert delivered through a Power BI app, replacing the prior end-of-month identification of shortfalls.

Channel performance dashboards — B2C, B2B, NDIS plan-managed, home care, aged care, and hospital — each operate on a dedicated slice of the same gold layer.

dbt models are deployed with documented contracts and tests; lineage is maintained end-to-end; and refresh schedules are predictable and observable.

The phased rollout

Every engagement is delivered using The Ascent Method — Map, Architect, Build, Operate.

1. P1
   Map (weeks 1–3). Worked alongside finance, operations, and channel buyers. Documented every margin question that is regularly asked, every parallel spreadsheet, and every conditional answer.
2. P2
   Architect + Build bronze (weeks 2–8). Ingestion from Odoo, storefronts, Stripe, and carriers; lineage and audit logging built in from the outset.
3. P3
   Build silver (weeks 6–16). Conformed SKU, channel, and customer dimensions; data quality gates; and reconciliation against the finance month-end position.
4. P4
   Build gold + Power BI (weeks 12–28). Margin, stockout, and channel performance — formally certified by the CFO and COO prior to go-live.
5. P5
   Operate (weeks 24–32). Handover, runbooks, training, and a four-week warranty period.

Outcomes

Finance can retrieve margin per SKU and per channel on any day of the month, rather than being constrained to the Wednesday following close.

Stockout alerts are delivered the following morning, enabling operations to reorder before customers raise backorder enquiries.

Channel performance dashboards reconcile precisely against the general ledger. The board pack is now a Power BI report rather than a manually consolidated spreadsheet.

When a figure changes, lineage records explain the cause; debate over which figure is correct has been eliminated.