Commercial Analytics

Building a Pharma Sales & Rebate Engine

Territory sales, GPO rebates and profitability for an oncology drug

Pharma Analytics
Excel Modeling
Contract & Rebate Logic

See the work

The Problem

A pharma manufacturer selling a blood-cancer drug through Group Purchasing Organizations (GPOs) had closed its quarter and needed three answers: how each territory performed, what rebate each hospital system had earned under its GPO or special contract, and how much of the quarter's profit those rebates would eat. The data was spread across a ~10.9K-row sales master, account hierarchies, a ZIP-to-territory alignment, a sales crediting rule and a tiered rebate contract sheet.

Strategy & Solution

Audited the master data and resolved 6 discrepancies, including blank GPOs, unmappable accounts, a 340B flag that contradicted account type in 262 rows, and a system split between two contracts.
Rolled child accounts up to hospital systems and ZIPs to 9 territories, applying weighted-sales crediting (340B at 0.2x, no-contract hospitals at 1.5x).
Built the rebate engine: system-level sales by GPO, tier thresholds from the contract sheet, and a pooled special contract where the whole group's volume sets everyone's tier.

Impact

Showed weighted national sales up 47% over three quarters, with the Northeast and Southeast driving a third or more of volume.
Calculated $7.25M in quarterly rebates; one GPO alone took nearly 60% of the total.
Put rebates against $14.6M in quarterly profit: they absorb close to half, so the next quarter's payouts need to be planned on the growth curve.

Interested in working together?

Get in touch