Retail Merchandising & Trade Promotion
Retail Promo Cannibalization, Halo & Net Margin Formula
Decompose Trade Promotion ROI across Naive Promoted-SKU Margin Lift, Sister-SKU Cannibalization Loss, Cross-Category Basket Halo Profit, and True Net Incremental Category Margin.
Catch money-losing trade promotions masked by single-SKU volume spikes!
Brand managers celebrating a 2.2x unit spike on a discounted endcap SKU frequently lose money at the category P&L level because single-SKU reporting ignores three counter-forces: (1) Margin Subsidy on Baseline Buyers Q_0 * (m_reg - m_promo) (shoppers who would have bought at full margin m_reg anyway!); (2) Sister-SKU Cannibalization -(Q_promo - Q_0) * gamma_can * m_sister (incremental promo units stolen from higher-margin pack sizes in the same aisle); and (3) Complementary Basket Halo +(Q_promo - Q_0) * h_halo (cross-category margin on chips/buns/accessories attached to incremental trips)! Summing all components gives True Net Incremental Profit = (Q_promo * m_promo - Q_0 * m_reg) - (Q_promo - Q_0) * gamma_can * m_sister + (Q_promo - Q_0) * h_halo!
Verify on Baseline_Units Q_0 = 1,000 (B2), Regular_Unit_Margin m_reg = $8.00 ($8,000 baseline profit, B3), Promoted_Units Q_promo = 2,200 (+1,200 gross lifted units, B4), Promo_Unit_Margin m_promo = $5.00 ($11,000 gross promo SKU profit, B5), Cannibalization_Rate gamma_can = 0.35 (35% of lifted units = 420 units diverted, B6), Sister_SKU_Margin m_sister = $9.00 (B7), and Basket_Halo_Margin_Per_Lifted_Unit h_halo = $1.50 (B8): Naive Single-SKU Margin Lift looks like +$3,000.00 (E2), Sister-SKU Cannibalization destroys -$3,780.00 (E3—meaning without basket halo, the promotion LOSES -$780.00!), Basket Halo adds +$1,800.00, and True Net Incremental Category Profit is +$1,020.00 (+0.1275 = +12.75% True Lift in E5 vs +37.50% naive)! Zero out Halo h_halo = 0 in B8 to expose the -$780.00 category loss!
| Trade Promotion Audit Metric (1,000->2,200 Units, $8->$5 Margin, 35% Cannibal @ $9, $1.50 Halo) | Formula | Verified Output |
|---|---|---|
| Naive Single-SKU Margin Lift ($) | Q_promo * m_promo - Q_0 * m_reg | +$3,000.00 (+37.50% Naive) |
| Sister-SKU Cannibalization Drag ($) | -(Q_promo - Q_0) * gamma_can * m_sister | -$3,780.00 (420 Units Diverted) |
| True Net Incremental Profit ($) | Naive_Lift + Cannibal_Drag + (Q_p - Q_0)*h_halo | +$1,020.00 (-$780 if Halo = $0) |
| True Category Profit Lift (%) | True_Net_Incremental / (Q_0 * m_reg) | +0.1275 (+12.75% Net Lift) |
True Net Incremental Category Profit Formula
Validate Q_0 > 0, m_reg > 0, Q_promo >= Q_0, m_promo >= 0, 0 <= gamma_can <= 1, m_sister >= 0, and h_halo >= 0!
=LET(q_bs, B2, m_rg, B3, q_pr, B4, m_pr, B5, gm_c, B6, m_ss, B7, hl_m, B8, IF(OR(q_bs <= 0, m_rg <= 0, q_pr < q_bs, m_pr < 0, gm_c < 0, gm_c > 1, m_ss < 0, hl_m < 0), "Invalid Promo Audit", LET(lf_u, q_pr - q_bs, ROUND((q_pr * m_pr - q_bs * m_rg) - lf_u * gm_c * m_ss + lf_u * hl_m, 2))))True Category Incremental Profit Lift Ratio Formula
Divide True Net Incremental Profit (E4) by Baseline Profit Q_0 * m_reg so category captains rank promotions by net category dollars rather than raw endcap cases!
=IF(ISTEXT(E4), E4, ROUND(E4 / (B2 * B3), 4))Try the example
Use the original values or change them to test your understanding.