Ppa financial model

ppa-financial-model

Full financial modeling of PPA initiatives: SKU-level P&L, MNL cannibalization matrix, Monte Carlo sensitivity, and competitor response scenarios. Always dashboard-first.

---

Plain Language

"¿Cuánto gana/pierde la empresa si lanzamos el 600g?"  → sku_pl_model.py
"¿El 600g nuevo canibaliza el 500g existente?"          → cannibalization_matrix.py
"¿Qué tan sensible es el resultado a la inflación?"     → sensitivity_analysis.py
"¿Qué pasa si el competidor baja precio 10%?"           → competitor_response.py
"Dame el resumen ejecutivo financiero"                   → financial_summary_report.py

Core insight (EY PPA p.6 + Polestar 7-step model): A PPA change that looks positive in isolation often destroys value through cannibalization of existing SKUs. The full picture requires modeling incremental revenue — not total revenue — and net margin after accounting for substitution effects within the portfolio.

---

Core Equations

SKU P&L model

# Revenue:
revenue = volume × price

# COGS:
cogs = volume × cost_per_unit   # cost_per_unit = cost_per_g × pack_size_g

# Gross profit:
gross_profit = revenue - cogs
gross_margin = gross_profit / revenue

# Operating profit:
operating_profit = gross_profit - marketing_spend - trade_spend

# Volume (elasticity-adjusted):
volume_new = volume_base × (1 + elasticity × pct_price_change)

MNL Cannibalization matrix (Multinomial Logit)

# Market share for SKU i (MNL):
share_i = exp(V_i) / Σ_j exp(V_j)
V_i = α_i + β × price_i   # utility function

# Diversion ratio from SKU i to SKU j (when SKU i removed/changed):
diversion_i→j = (∂share_j/∂price_i) / (∂share_i/∂price_i)
             = share_j × (1 - share_j) / share_i   (simplified MNL)

# Cannibalization rate: % of new SKU volume coming from own portfolio
cannib_rate = Σ_j diversion_new→j × share_j_baseline

# Net incremental volume:
vol_incremental = vol_new_sku × (1 - cannib_rate)

Sensitivity analysis (Monte Carlo)

# For each parameter θ (elasticity, cost, competitor_price):
# Sample θ ~ N(μ_θ, σ_θ)
# Compute profit(θ) → distribution of outcomes

# Key sensitivities:
sensitivity_i = (∂profit / ∂θ_i) × (σ_θ_i / profit_base)

Competitor response model

# Scenario A: No response
vol_A = vol_base × (1 + E_own × pct_own_price_change)

# Scenario B: Symmetric response (competitor matches % change)
# Own volume reverts partially — net effect ≈ 0.3–0.5 of Scenario A

# Scenario C: Asymmetric (competitor responds only in affected channel)
# Model as weighted average of A and B by channel exposure

---

Workflow

Step 1 — Data validation

# data-intake-normalizer: Required: sku_name, price, cost_per_unit, volume
# Optional: marketing_spend, trade_spend, competitor_prices, elasticity

Step 2 — SKU P&L

python scripts/sku_pl_model.py \
    --skus /mnt/user-data/uploads/sku_data.xlsx \
    --scenario-prices "18.50,20.00,21.00" \
    --elasticity -1.42 \
    --output results/pl_model.json

Step 3 — Cannibalization matrix

python scripts/cannibalization_matrix.py \
    --skus /mnt/user-data/uploads/sku_data.xlsx \
    --new-sku '{"name":"RefrescoCo 600ml","price":18.50,"size_g":600}' \
    --output results/cannibalization.json

Step 4 — Sensitivity analysis

python scripts/sensitivity_analysis.py \
    --pl-model results/pl_model.json \
    --params '{"elasticity":{"mean":-1.42,"std":0.3},"cost_pct":{"mean":0,"std":0.10}}' \
    --n-simulations 5000 \
    --output results/sensitivity.json

Step 5 — Competitor response

python scripts/competitor_response.py \
    --base-volume 1000 \
    --own-price-change 0.08 \
    --elasticity-own -1.42 \
    --elasticity-cross 0.42 \
    --output results/competitor.json

Step 6 — Financial summary + dashboard

python scripts/financial_summary_report.py \
    --pl results/pl_model.json \
    --cannib results/cannibalization.json \
    --sensitivity results/sensitivity.json \
    --competitor results/competitor.json \
    --output dashboard_data.json

Output sequence:

1. [bash_tool] All scripts
2. [web_search] "PPA financial modeling ROI [category] CPG [year]" +
                "cannibalization rate new SKU launch FMCG benchmark [year]"
3. [TomTom MCP] If zone data → P&L by zone / region
4. [show_widget] Complete inline dashboard
5. [text] NBA + SKU/category/market context
6. [text] Caveats

---

Dashboard Panels (all visible inline)

1. KPI bar — incremental revenue, net margin, cannibalization rate, worst-case downside (P10), best-case upside (P90), competitor response impact
2. P&L waterfall — revenue → gross profit → operating profit by scenario
3. Cannibalization matrix — heatmap of diversion ratios between own SKUs
4. Sensitivity tornado — top 5 parameters ranked by impact on profit (±1σ)
5. Scenario comparison table — base / optimistic / pessimistic / competitor response
6. Monte Carlo distribution — profit distribution with P10/P50/P90 markers
7. TomTom map — P&L by region/zone when geographic data present

---

Marketer Insights Layer (MANDATORY)

Search before benchmarking

web_search: "cannibalization rate new SKU FMCG [category] benchmark [year]"
web_search: "competitor response price change CPG [market] [year]"
web_search: "PPA ROI consumer goods [category] LATAM [year]"

Translate to business language

Technical	Business meaning
incremental_revenue = $42K/mo	"After cannibalization, the new SKU adds $42K net — not $80K gross"
cannib_rate = 38%	"38% of new SKU buyers come from existing own SKUs — only 62% are truly incremental"
P10 = -$12K	"In 10% of scenarios the initiative loses $12K/mo — board needs to know this"
diversion_600ml→500ml = 0.45	"45% of buyers who switch to 600ml were buying our 500ml — half the volume is cannibalized"
competitor_response = -8% net	"If competitor matches our price cut, net benefit drops 60% — our move may not be worth it"

NBA

• Incremental framing: "Always present incremental P&L, not gross — $80K revenue looks great until you subtract $30K cannibalization"
• Cannibalization threshold: "If cannib rate > 40%, reconsider the launch — you're mainly redistributing existing volume, not growing the pie"
• Sensitivity priority: "Elasticity uncertainty drives 45% of profit variance — validate with A/B test before full rollout"
• Competitor pre-emption: "If competitor is likely to respond symmetrically, net benefit drops to $[X] — model this scenario explicitly for CFO"
• SKU rationalization: "High diversion (>0.6) from SKU A to B = A is a candidate for discontinuation — simplify portfolio"
• Launch sequencing: "Launch new pack in [low-cannibalization channel] first — TT before MT minimizes overlap with existing SKU distribution"

---

Key Caveats

• MNL independence: MNL assumes Independence of Irrelevant Alternatives — may overestimate diversion between close substitutes
• Cannibalization lag: Consumer switching takes 4–12 weeks to stabilize — early data overestimates incrementality
• Monte Carlo assumptions: Normal distribution for parameters may understate tail risk in high-inflation environments
• Competitor response timing: Models assume instantaneous response — actual response lag is typically 4–16 weeks in FMCG
• Trade spend omission: P&L without trade spend overstates margin — always include listing fees for new SKU launches

---

Integration with OS

Skill	Handoff
data-intake-normalizer	Validate SKU / P&L input
price-elasticity-modeling	Elasticity β feeds volume projection
ppa-pack-size-leverage	Cash margin delta from pack change → P&L model
price-demand-optimization	p_opt feeds revenue line
margin-simulation	Agency margin OS → cross-reference with client P&L
trade-promotion-roi	Trade spend line feeds operating profit
measurement-incrementality	A/B test validates cannibalization rate ex-post