Pharma Forecasting & Market Intelligence Automation
Sapnity implemented a unified forecasting and market intelligence engine that connects SAP sales, CRM activity, HCP behavior and secondary data into a governed, AI-assisted forecasting framework — replacing spreadsheet-heavy cycles with a reusable forecasting pattern for global, regional and country teams.
1. Business Problem — Forecasting on Excel
The client, a Top-20 global pharma, ran brand and country forecasts predominantly in Excel. Each country maintained its own templates, assumptions and macros.
- 20+ markets each using different forecast structures and definitions.
- No single source of truth for volume, TRx/NRx, or lag curves.
- Secondary data, HCP behavior and CRM activity rarely integrated.
- Consolidation to regional and global views took days each cycle.
- Forecast accuracy highly dependent on individual analysts.
The pain wasn’t just about lateness — it was that leadership didn’t trust the numbers in critical S&OP meetings.
2. Sapnity’s Mandate
Leadership asked Sapnity to:
- Design a governed forecasting pattern spanning all brands and regions.
- Introduce AI-powered baselines without losing expert judgment.
- Bring SAP sales, CRM activity and secondary data into one forecast view.
- Enable fast scenario planning (base / stretch / conservative).
- Establish full auditability and assumption transparency.
3. Before — Fragmented Forecasting Landscape
Before Sapnity, forecasting was a coordinated act of spreadsheet juggling. Every cycle meant chasing files, reconciling formulas and explaining variances line by line.
A. Fragmented Tools
200–300 MB Excel files emailed across affiliates, each with its own macros, tabs and hidden cells that only one analyst truly understood.
B. Manual Market Intelligence
HCP targeting, market share and uptake curves were calculated in separate sheets — rarely synchronized with the primary forecast.
C. Unpredictable Accuracy
MAPE and Bias were not tracked systematically; accuracy was discussed qualitatively, often when it was already too late to correct the quarter.
D. Zero Scenario Governance
“Best case / Worst case” lived in local copies; no controlled way to compare, replay or audit decisions made under pressure.
In short, forecasting was high-effort, low-trust — and the more countries you added, the less anyone believed the roll-up.
4. After — Sapnity Forecast Intelligence Engine
Sapnity helped the client move from Excel-driven cycles to a central forecasting intelligence engine:
- A unified Dataverse model for products, markets, channels and HCP segments.
- Python Prophet baselines generated automatically each cycle.
- Promotion, HCP and seasonal adjustments captured via a clear override layer with full audit history.
- Scenario engine for base / stretch / conservative views per brand and country.
- Power BI “Forecast Studio” consolidating country → region → global roll-ups.
Instead of emailing files, brand teams now open a single app, review AI baselines, adjust with documented assumptions and push a governed forecast into S&OP.
5. Implementation Story — From Templates to Pattern
Phase 1 — Discovery & Taxonomy
- Collected existing forecast templates from 22 markets and 5 regional teams.
- Defined a standard taxonomy for TRx/NRx, volume, price, channels and units.
- Identified 40+ sources of variance in methodology and assumptions.
Phase 2 — Forecast Data Model in Dataverse
- Designed tables for products, markets, time buckets, scenarios and assumptions.
- Separated baseline, override and approved layers for transparency.
- Embedded row-level security for country, regional and global views.
Phase 3 — AI Baseline & Accuracy Tracking
- Implemented Prophet-based models using 3+ years of SAP sales history.
- Added adjustment factors for launch curves, LOE, seasonality and events.
- Built automated MAPE, Bias and MAD tracking at brand and country level.
Phase 4 — Forecast App & Scenario Engine
- Created a model-driven app for brand teams to review and override baselines.
- Added workflows for scenario creation, submission and approval.
- Introduced an “assumption log” view for finance, marketing and medical.
Phase 5 — Analytics, S&OP & Governance
- Power BI “Forecast Studio” dashboards for country, region and global stakeholders.
- Monthly governance forum with rapid drill-down into deviations and drivers.
- Reusable pattern documented as the client’s internal “Forecast Playbook”.
6. Architecture Pattern — AI Forecasting Stack
The diagram below is Sapnity’s reusable AI Forecasting Pattern for pharma — now applied to other therapeutic areas and regions.
Data Ingestion Layer
SAP sales, CRM activity, secondary market data, HCP intelligence, promotion history.
Forecast Engine Layer
Prophet baselines, launch curves, seasonality patterns and event-based uplifts.
Override & Governance Layer
Brand overrides, approval workflows, scenario definitions and assumption logs.
Application Layer
Forecast app for brand, finance and regional teams; guided “review and submit” flows.
Analytics & S&OP Layer
Power BI Forecast Studio, accuracy dashboards, S&OP roll-ups and variance analysis.
This pattern is now being reused for demand planning, supply planning and salesforce sizing — not just for this one forecasting use case.
7. Outcomes & KPIs
| KPI | Before | After Sapnity |
|---|---|---|
| Forecast Cycle Time | 12–15 days per cycle | 3–5 days including approvals |
| Global Consolidation Effort | Manual Excel stitching | Fully automated roll-ups |
| Forecast Accuracy (MAPE) | 18–25% | 7–12% across key markets |
| Scenario Rebuild Time | 6–8 hours | Seconds to generate new scenarios |
| Auditability of Assumptions | Low, scattered notes | 100% traceable with change history |
More importantly, forecast conversations shifted from “Which file is right?” to “Which scenario should we commit to?” — a fundamental mindset change.
8. Sapnity Differentiators
- Pattern-first design: We delivered a reusable AI forecasting pattern, not a single-project build.
- AI + human judgment hybrid: Machines propose; experts approve, with explainable adjustments.
- Full-stack integration: SAP, CRM and secondary data stitched into one governed model.
- Built-in accuracy management: MAPE, Bias and MAD are first-class objects — not afterthoughts.
- S&OP ready from day one: Architected to feed existing S&OP governance rather than replace it.
For this client, Sapnity didn’t just modernize forecasting — we created a forecasting backbone that future brands, markets and business units can plug into with minimal friction.