Retail Merchandising & Revenue Management
Retail Clearance Markdown & Salvage Profit Formula
Calculate Constant-Elasticity Clearance Units Sold MIN(I_rem, v_0 * (1 - d)^epsilon * W_rem), Hold-Full-Price vs Markdown Gross Margin (with end-of-season liquidator salvage), and Net Markdown Profit Lift.
Optimize end-of-season clearance depth before unsold stock hits jobbers at salvage pennies!
Apparel and seasonal electronics buyers holding Remaining Inventory I_rem with only W_rem weeks left before season changeover face a concrete dynamic tradeoff: holding Full Ticket Price P_0 sells MIN(I_rem, v_0 * W_rem) units and dumps leftover stock to off-price liquidators at Salvage Value P_salv < Unit_COGS; whereas taking a Markdown Discount d_md in (0, 1) lowers selling price to P_md = P_0 * (1 - d_md) while accelerating weekly velocity under Price Elasticity epsilon < 0 to v_md = v_0 * (1 - d_md)^epsilon, selling Q_md = MIN(I_rem, v_md * W_rem) in-season and salvaging only (I_rem - Q_md)! Notice the stock-out over-discounting trap: once v_md * W_rem exceeds I_rem, deeper markdowns cut price without adding a single extra unit sold!
Verify on Remaining_Inventory I_rem = 500 units (B2), Unit_COGS = $40.00 (B3 -> $20,000 sunk inventory cost), Full_Ticket_Price P_0 = $100.00 (B4), Baseline_Velocity v_0 = 32 units/wk (B5), Weeks_Remaining W_rem = 6 weeks (B6), Markdown_Depth d_md = 0.20 (20% off -> $80.00 clearance price, B7), Price_Elasticity epsilon = -3.0 (B8 -> (0.80)^(-3) = 1.953125x velocity = 62.50 units/wk!), and Salvage_Liquidator_Price P_salv = $20.00 (B9): (1) Markdown In-Season Units Sold is MIN(500, 62.50 * 6) = 375.00 units (E2, vs 192 units at full price!); (2) Hold-Full-Price Gross Margin is (192 * $100 + 308 * $20) - $20,000 = $5,360.00 (E3); (3) 20%-Markdown Gross Margin jumps to (375 * $80 + 125 * $20) - $20,000 = $12,500.00 (E4); yielding +$7,140.00 Net Markdown Profit Lift (E5)! Now test excessive 50% discounting (d_md = 0.50 -> $50 price, capping at 500 units): Margin collapses to $5,000.00 (-$360.00 worse than holding full price)!
| Seasonal Clearance Metric (500 Units, COGS=$40, P0=$100, v0=32/wk, 6 Wks, d=20%, eps=-3) | Formula | Verified Output |
|---|---|---|
| Markdown In-Season Units Sold | MIN(I_rem, v_0 * (1 - d_md)^eps * W_rem) | 375.00 units (vs 192 Full) |
| Hold-Full-Price Gross Margin ($) | Q_full*P_0 + (I_rem - Q_full)*P_salv - I*COGS | $5,360.00 (308 Salvaged) |
| 20%-Markdown Gross Margin ($) | Q_md*P_md + (I_rem - Q_md)*P_salv - I*COGS | $12,500.00 (125 Salvaged) |
| Net Markdown Profit Lift ($) | GM_Markdown - GM_Hold_Full | +$7,140.00 (-$360 @ d=50%) |
Inventory-Capped Markdown Gross Margin Formula
Validate I_rem > 0, COGS >= 0, P_0 > P_salv >= 0, v_0 > 0, W_rem > 0, 0 <= d_md < 1, and epsilon < 0!
=LET(i_rm, B2, cg_u, B3, p_fl, B4, v_bs, B5, w_rm, B6, d_md, B7, ep_s, B8, p_sv, B9, IF(OR(i_rm <= 0, cg_u < 0, p_sv < 0, p_fl <= p_sv, v_bs <= 0, w_rm <= 0, d_md < 0, d_md >= 1, ep_s >= 0), "Invalid Clearance Plan", LET(q_md, MIN(i_rm, v_bs * ((1 - d_md) ^ ep_s) * w_rm), p_md, p_fl * (1 - d_md), ROUND(q_md * p_md + (i_rm - q_md) * p_sv - i_rm * cg_u, 2))))Net Markdown Profit Gain vs Hold-Full-Price Formula
Check d_md = 0: Markdown Margin equals Hold-Full-Price Margin and Net Lift equals $0.00 exact!
=IF(ISTEXT(E4), E4, ROUND(E4 - E3, 2))Try the example
Use the original values or change them to test your understanding.