Connected Pharma Quota Planning Prototype

This repository contains a formula-driven Excel planning model that turns a national revenue target into explainable territory quotas for a fictional mid-size pharmaceutical company. It uses a DISCO-style separation of Data, System, Calculation, Input, Output, and Audit responsibilities to demonstrate the architecture and controls that should be designed before a production planning implementation.

Honest portfolio positioning: this is an independent Excel-based design exercise informed by Anaplan Academy coursework. It was not built in an Anaplan workspace, is not an Anaplan export, and should not be presented as an Anaplan implementation or certification project.

Business problem

Finance sets one annual national revenue target, while Sales needs defensible quotas for every Territory and Sales Rep. The allocation must reflect prior-year performance and market potential, reconcile a bottom-up build with the top-down target, protect territories through a quota floor, and make exceptions visible.

The prototype provides a connected calculation path:

Prior-year sales
    + Product x Tier growth assumptions
    + Territory tier weight
    = Bottom-up raw quota
    -> proportional top-down reconciliation
    -> 90% prior-year floor
    -> approval-gated management override
    -> Working or frozen Approved result
    -> monthly phasing, diagnostics, dashboards, and audit controls

What is in the current workbook

The advanced workbook contains 21 purpose-specific sheets. The eight modules in the original brief retain their exact names; thirteen extensions add navigation, scenarios, a simulated workflow, monthly phasing, diagnostics, and controls.

Layer	Sheets	What they do
Guide	START_HERE	Explains the use case, navigation, color conventions, and platform boundary.
Data	DAT01_HistoricalSales, DAT02_AccountTiering, DAT03_RefreshControl, DAT04_ApprovedQuotaSnapshot	Stage inputs, check source quality, record refresh metadata, and preserve an illustrative static Approved snapshot.
System	SYS01_TerritoryHierarchy, SYS02_PlanningLists, SYS03_TimeCalendar	Centralize the geography, ownership mappings, validation lists, and FY2027 calendar.
Calculation / controls	CALC01_GrowthAssumptions	Holds Scenario x Product x Tier growth, targets, floor policy, tier weights, review thresholds, seasonality, and active Scenario/Version selectors.
Calculation	CALC02_QuotaEngine through CALC06_FairnessAnalytics	Build raw quota, reconcile to target, compare scenarios, phase monthly, select Working/Approved results, and calculate review indicators.
Planning input	INP01_ManagementOverrides, INP02_RegionalWorkflow	Simulate approval-gated overrides and regional Submit/Review/Approve status with prerequisite checks.
Output	OUT01_QuotaDashboard through OUT04_WorkflowDashboard	Serve national, Rep, regional, monthly, exception, and workflow views.
Audit	AUD01_ModelControlCenter	Runs terminal checks without feeding a calculation or output.

See the model blueprint for every list, module, dimension, and line-item group.

Architecture

flowchart LR
    DAT[DAT01-DAT04\nSource and snapshots] --> CALC[CALC01-CALC06\nDrivers and calculations]
    SYS[SYS01-SYS03\nLists, mappings, calendar] --> CALC
    CALC --> INP[INP01-INP02\nOverrides and workflow]
    INP --> CALC
    CALC --> OUT[OUT01-OUT04\nRole-oriented outputs]
    DAT --> AUD[AUD01\nTerminal controls]
    SYS --> AUD
    CALC --> AUD
    INP --> AUD
    OUT --> AUD

Loading

AUD01_ModelControlCenter is deliberately terminal: it observes upstream modules but no business calculation depends on it. This prevents a control from masking or changing the result it is checking.

Core assumptions

Assumption	Base value
National next-FY target	$2,500,000
Base growth	8.0% YoY
Tier weights	A = 1.25x; B = 1.00x; C = 0.80x
Quota floor	90% of prior-year actual
Products	Cardio, Oncology, Immunology
Geography	Region -> Area -> Territory
Planning calendar	Calendar FY2027, monthly
Scenarios	Base, Upside, Downside
Excel versions	Working and Approved selector

The supplied source brief contained Territory totals, not Product-level actuals. To demonstrate a Territory x Product model while preserving every supplied total, the workbook transparently allocates history 45% to Cardio, 35% to Oncology, and 20% to Immunology. A production build should load actual source-system detail instead.

Formula logic

Growth Amount          = Prior Year Actual x Growth %
Growth-Adjusted Quota  = Prior Year Actual + Growth Amount
Tier Adjustment        = Growth-Adjusted Quota x (Tier Weight - 1)
Raw Quota              = Growth-Adjusted Quota x Tier Weight
Scale Factor           = National Target / Sum of Raw Quotas
Scaled Quota           = Territory Raw Quota x Scale Factor
Floor Value            = Territory Prior Year Actual x Floor Retention
Calculated Quota       = MAX(Scaled Quota, Floor Value)
Effective Working      = Approved valid override, otherwise Calculated Quota
Normalized Seasonality = Monthly Index / Product annual index total
Monthly Quota          = Selected Annual Product Quota x Normalized Seasonality

The floor is applied after proportional scaling, so it can create a policy-driven overage. The model reports that difference explicitly. It does not insert an undisclosed balancing plug.

Default case behavior

With Base / Working selected, the unoverridden calculation starts from $2,115,000 of prior-year sales, produces a $2,513,160 raw-quota pool, and uses a scale factor of approximately 0.9948x. T-South-02 activates the 90% floor, so the calculated national quota is approximately $2,507,700 rather than exactly $2,500,000.

The advanced sample contains two illustrative Submitted override requests, for T-West-01 and T-South-02. Neither affects the published Working baseline because neither is Approved. The bridge, workflow dashboard, and control center expose both the pending decisions and the zero applied-override delta. A disposable recalculation test proves that a complete request changes Working quota only after its status, approver, and approval date satisfy the approval gate.

How to review it

1. Open the Excel workbook and begin on START_HERE.
2. On CALC01_GrowthAssumptions, select an Active Scenario and Active Version. Yellow cells with blue text are editable inputs.
3. Follow one record through DAT01_HistoricalSales -> CALC02_QuotaEngine -> CALC03_Reconciliation.
4. Review the approval logic in INP01_ManagementOverrides and the simulated regional process in INP02_RegionalWorkflow.
5. Confirm annual-to-month reconciliation in CALC04_MonthlyPhasing and review allocation indicators in CALC06_FairnessAnalytics.
6. Present OUT01_QuotaDashboard, OUT02_RepScorecard, OUT03_RegionalReview, and OUT04_WorkflowDashboard.
7. Finish on AUD01_ModelControlCenter and distinguish expected policy warnings from critical failures.

For a portfolio presentation, follow the demo script.

Screenshots

National dashboard	Regional review

Quota engine	Model control center

Additional views: reconciliation, Rep scorecard, monthly phasing, and workflow dashboard.

Evidence of Anaplan-aware design

Concept	Working evidence in Excel	What still requires the platform
DISCO-style architecture	Dedicated DAT, SYS, CALC, OUT modules, plus scoped INP and terminal AUD sheets	Model objects, functional areas, saved views, and UX pages
Lists and hierarchy	Governed validation members and Region -> Area -> Territory mappings	Coded lists, parent relationships, subsets, and production-list settings
Dimensionality	Territory x Product engine; Territory reconciliation; Scenario x Product x Tier assumptions; Territory x Product x Month phasing	Blueprint Applies To, native Time, summaries, and engine-specific qualification
System modules	Reusable hierarchy, rep, calendar, and list mappings	List-formatted line items and compiled direct-reference/LOOKUP patterns
Scenarios and versions	Formula-driven Base/Upside/Downside selector plus Working/static Approved selection	Native Versions or an approved custom-version design and controlled promotion
Workflow and overrides	Manual status, prerequisite, approval, effective-value, and visual-lock formulas	Notifications, actions/processes, user authorization, and Dynamic Cell Access
Security	Conceptual access matrix in the governance document	Model roles, Selective Access, named-user tests, and administrator controls
ALM	Development/Test/Production and release design in documentation	Revision tags, Compare and Synchronize, Deployed mode, and real release evidence
Data integration	Refresh contract, row acceptance, rejected-record logic, and control totals	Governed source connections, imports/actions, schedules, and production monitoring

The formula crosswalk maps Excel logic to likely Anaplan patterns such as direct references, LOOKUP, SUM, Boolean flags, narrow Applies To, and appropriate summary methods. Every platform formula remains illustrative until compiled and tested in a workspace.

Quality and testing

The generation script includes formula-error scans, workbook reopen checks, scenario-switch recalculation, source immutability, and approval-gated override tests. AUD01_ModelControlCenter adds 21 in-workbook controls for data, mappings, assumptions, reconciliation, versions, phasing, outputs, fairness, and workflow. A Python standard-library validator inspects the XLSX package without requiring Excel, and the same validation has completed successfully in GitHub Actions; the exact run is linked in the testing evidence.

The testing evidence records the final workbook hash, reconciliation values, structural counts, cascade tests, renders, compatibility boundaries, and the successful hosted validation run. A control warning can be intentional—for example, floor uplift—while a critical FAIL means the workbook is not ready.

Repository map

Path	Purpose
outputs/anaplan-style-quota-model/pharma-quota-planning-prototype.xlsx	Deliverable Excel / Google Sheets-ready prototype
outputs/anaplan-style-quota-model/product-requirements-document.md	Scope, requirements, acceptance criteria, roadmap, and effort estimate
outputs/anaplan-style-quota-model/model-blueprint.md	Lists, modules, dimensions, line items, and dependencies
outputs/anaplan-style-quota-model/business-case.md	Problem, assumptions, and calculation logic in plain English
data/	Fictional public CSV source templates plus field/grain notes
scripts/build_workbook.mjs	Reproducible workbook build, render, export, and test script
scripts/validate_xlsx.py	Dependency-free structural XLSX validator
.github/workflows/validate-workbook.yml	Hosted validation definition; run status must be verified after publication
docs/anaplan-formula-crosswalk.md	Excel-to-Anaplan conceptual implementation crosswalk
docs/governance-and-alm.md	Security, data governance, ALM, and performance design
docs/demo-script.md	Portfolio and interview walkthrough
docs/testing-evidence.md	Final QA evidence register

Estimated build effort

A polished project of this scope is approximately 95-120 hours from scratch, with a planning midpoint of 112 hours for one person. That estimate includes requirements, modeling, advanced scenarios and phasing, workflow simulation, controls, QA, documentation, screenshots, and portfolio presentation—not only spreadsheet formulas. See the PRD estimate for the workstream breakdown.

Interview framing

I did not have access to an Anaplan workspace, so I translated the business process into lists, hierarchies, dimensional grains, line items, modular dependencies, reconciliation rules, workflow states, controls, and role-oriented outputs in Excel. The working spreadsheet demonstrates the business logic and systems thinking. The supporting documents explain how I would validate and implement the equivalent design in Anaplan without claiming platform experience I have not yet had.

Disclaimer

This is an Excel-based design exercise simulating Anaplan-style connected-planning and DISCO modeling architecture, based on concepts learned through Anaplan Academy coursework. It was not built in the Anaplan platform, does not use an Anaplan workspace, and is not an Anaplan export or production implementation. Native platform capabilities—including model roles, Selective Access, Dynamic Cell Access, actions and processes, native Time and Versions, UX pages, production integrations, calculation-engine testing, and ALM promotion—remain conceptual until implemented and validated in an authorized workspace.