Savings Audit Math¶

Savings/audit math must be reproducible. No savings number should appear because a workbook formula, AI response, or Domo card made it look plausible. Every number must trace back to a scoped run, a source table, a formula, and a validation status.

Scope Before Math¶

Every calculation starts with a resolved RunSelection:

Field	Meaning
`itr`	Backend run id / ITR number used to scope automation tables.
`pbm`	Canonical PBM for the run.
`tpa`	Canonical TPA/broker for the run.
`group_name`	Optional exact group filter.
`start_date`, `end_date`	Expected analysis window.
`resolved_by`	How the backend resolved the run.

The SQL filter is intentionally narrow:

WHERE itr_num = ?
  AND LOWER(LTRIM(RTRIM(COALESCE(group_name, '')))) = LOWER(?)

The group_name predicate is included only when a group is selected. This prevents multi-group totals from leaking into exact group outputs.

Current Source Tables¶

Table	Used for
`automation.audit_kpidrugs`	total drug spend, specialty spend, utilizers, unique drugs, top drugs, therapeutic classes
`automation.audit_groupsrepresented`	group count, total claims, fill-date range, group-level claim counts
`automation.audit_segmentation`	channel/bucket claim counts, AWP, ingredient cost, dispensing fee, source discount
`automation.audit_specialtydrugs`	specialty/high-cost drug rankings by ingredient cost
governed pricing registry	contract discount and rebate inputs when exact coverage exists
governed savings packet	PBM alternative matrix and net-employer-paid scenarios when exact packet match exists

Report Payload Formulas¶

These formulas are implemented in LeafEnterprise/client_preview.py.

Metric	Formula
`total_drug_spend`	`SUM(drug_spend)` from `audit_kpidrugs` for the scoped `itr_num` and optional group
`specialty_drug_spend`	`SUM(drug_spend WHERE UPPER(LH_specialty) = 'Y')`
`total_utilizers`	`SUM(utilizers)`
`unique_drugs`	`COUNT(DISTINCT drug_name)`
`group_count`	`COUNT(DISTINCT group_name)` from `audit_groupsrepresented`
`total_claims`	`SUM(num_claims)` from `audit_groupsrepresented`
`min_fill_date`	`MIN(min_fill_date)` from `audit_groupsrepresented`
`max_fill_date`	`MAX(max_fill_date)` from `audit_groupsrepresented`
`specialty_share`	`specialty_drug_spend / total_drug_spend`, or `0` when total spend is zero

Top lists are deterministic rankings:

Output	Ranking
`channel_segmentation`	grouped by bucket, ordered by `SUM(ingredient_cost) DESC`
`top_drugs`	grouped by drug and therapeutic class, ordered by `SUM(drug_spend) DESC`
`specialty_high_cost`	grouped by specialty drug name, ordered by `SUM(ingredient_cost) DESC`
`groups`	grouped by group name, ordered by `SUM(num_claims) DESC`

Channel And Workbook Math¶

These formulas are implemented in LeafEnterprise/report_workbook.py.

For each channel bucket:

Metric	Formula
claims	`SUM(num_claims)` from `audit_segmentation`
AWP	`SUM(AWP)`
ingredient cost	`SUM(ingredient_cost)`
dispensing fee	`SUM(disp_fee)`
discount	`AVG(discount)` from the source segmentation table

Important: the current renderer does not silently recompute source discount as 1 - ingredient_cost / AWP. It uses the source discount field from audit_segmentation. If the business wants weighted effective discount as the canonical value, that must be added as a named formula and regression-tested before replacing the current field.

Contract Guarantee Math¶

Contract discount and rebate cells are not guessed.

The renderer writes contract discount/rebate inputs only when the active scope resolves to exact governed pricing lineage. Otherwise those cells stay blank and the workbook carries an explicit note.

Current row-to-term mapping includes retail, mail, and specialty discount/rebate fields, for example:

Workbook row family	Contract term candidates
Retail 30 brand	`retail_30_brand_discount`, `retail_brand_discount`, `retail_30_brand_rebate`, `retail_brand_rebate`
Retail 30 generic	`retail_30_generic_discount`, `retail_generic_discount`
Retail 90 brand	`retail_90_brand_discount`, `retail_90_brand_rebate`
Mail brand	`mail_brand_discount`, `mail_brand_rebate`
Specialty brand	`specialty_brand_discount`, `specialty_discount_exclusive`, `specialty_brand_rebate`, `specialty_retail_rebate`, `specialty_mail_rebate`

If exact contract evidence is missing, final guarantee-backed savings/audit conclusions are blocked. Domo pricing rows can only help identify candidate evidence.

Optimization Projection Math¶

These formulas are implemented in LeafEnterprise/pocket_consultant.py.

Governed PBM Switch¶

When an exact governed savings packet matches the active scope, LeafEnterprise parses the workbook packet's alternative_pbm_matrix.ranked_analysis.

Required rows:

Net Employer Paid
Savings Amount
Savings %

Supporting component rows when present:

Gross Employer Paid
Plus Administrative Fees
Specialty Drug Alternative funding
Impact to Rebate from Alternative Funding
Less Rebates
PEPM Credit
Net Employer Paid per Script

Formula:

baseline_net_employer_paid = incumbent Net Employer Paid
target_net_employer_paid = challenger Net Employer Paid
savings_amount = packet Savings Amount
fallback_savings_amount = max(baseline_net_employer_paid - target_net_employer_paid, 0)
savings_pct = savings_amount / baseline_net_employer_paid

Execution-preview formula:

projected_savings = max(baseline_net_employer_paid - target_net_employer_paid, 0)
projected_total = target_net_employer_paid
projection_basis = Net Employer Paid

This is the strongest recommendation class because it is grounded in an exact governed packet rather than generic benchmark modeling.

Specialty Rebate Lift¶

Allowed only when evidence says rebate-backed recommendations are safe.

projected_savings = specialty_drug_spend * improvement_pct
default improvement_pct = 0.05
projected_total = total_drug_spend - projected_savings

If exact rebate evidence is not linked, this class is blocked.

Top Specialty Cost Reduction¶

projected_savings = top_specialty_drug_ingredient_cost * improvement_pct
default improvement_pct = 0.08
projected_total = total_drug_spend - projected_savings

This is a modeled scenario unless exact governed evidence upgrades the recommendation posture.

Channel Cost Reduction¶

projected_savings = top_channel_ingredient_cost * improvement_pct
default improvement_pct = 0.04
projected_total = total_drug_spend - projected_savings

PBM Benchmark Switch¶

projected_savings = max(total_drug_spend - benchmark_avg_total_drug_spend, 0)
projected_total = total_drug_spend - projected_savings

This is a benchmark scenario, not final savings proof.

Reproducibility Contract¶

Every savings/audit output should be reproducible from these artifacts:

Artifact	Requirement
scope	exact run id plus PBM/TPA/group/date window
source rows	SQL tables and filters used for each metric
formula	named calculation and versioned implementation path
evidence	contract, rebate, pricing, packet, or blocked evidence state
output	report payload, workbook, PDF, or action preview using the same scope
validation	pytest, smoke, workbook parity, or explicit blocked reason

What Must Never Happen¶

Do not copy stale workbook formulas as system truth.
Do not use AI text as a calculation source.
Do not treat Domo pricing as final contract evidence.
Do not show rebate-backed savings without exact rebate evidence.
Do not hide missing contract terms by using zero, actuals, or prior-year values.
Do not compare scopes with different PBM, TPA, group, or date windows without marking the mismatch.

Test Targets¶

Use the smallest test set that covers the changed math:

python -m pytest tests/test_report_workbook.py tests/test_pocket_consultant.py

For route-level validation:

python scripts\smoke_client_portal.py --base-url http://127.0.0.1:8787