Procurement & Inventory Management
All-Units vs Incremental Discount EOQ Formula
Compare All-Units Price-Break EOQ (step-up to Q_brk) against Incremental Marginal-Tier Discount EOQ (effective setup S + (p_0 - p_1)*Q_brk) on total annual landed cost.
Never confuse an All-Units supplier price break with an Incremental marginal-tier discount!
Suppliers quote volume price breaks (base price p_0 below Q_brk, discounted price p_1 < p_0 at or above Q_brk) in TWO fundamentally different ways! Under an All-Units Discount, ordering Q >= Q_brk reprices EVERY unit from 1 to Q down to p_1 — creating a downward cost cliff at Q_brk where optimal tier quantity is Q_AU = MAX(Q_brk, SQRT(2*D*S / (h*p_1)))! Under an Incremental (Marginal) Discount, the first Q_brk units in EVERY order STILL cost p_0 and only units above Q_brk cost p_1 — adding fixed per-order surcharge (p_0 - p_1) * Q_brk onto setup cost S_inc = S + (p_0 - p_1) * Q_brk (meaning you NEVER stop right at Q_brk; optimal tier order jumps to EOQ_inc = SQRT(2 * D * S_inc / (h * p_1))!)!
Verify with exact integers! Annual_Demand_D = 3,600 (B2), Order_Setup_S = 100 (B3), Holding_Rate_h = 0.20 (B4), Base_Price_p0 = 10.00 (B5), Break_Qty_Qbrk = 900 (B6), and Discount_Price_p1 = 9.00 (B7): (1) Base EOQ_0 = SQRT(720,000 / 2.00) = 600 units -> TC_base = 36,000 + 600 + 600 = 37,200.00; (2) All-Units steps to Q_AU = MAX(900, 632.46) = 900 units -> TC_AllUnits = 32,400 + 400 + 810 = 33,610.00 (saves 3,590.00/yr!); (3) Incremental effective setup is S_inc = 100 + 1.00 * 900 = 1,000 -> Q_Inc = SQRT(7,200,000 / 1.80) = 2,000 units -> TC_Incremental = 32,400 + 1,800 + 1,800 = 36,000.00!
| Supplier Contract Structure (D=3,600, S=100, h=20%, Q_brk=900) | Optimal Order Quantity Q* | Minimum Total Annual Cost |
|---|---|---|
| No-Discount Base Tier (p_0 = 10.00) | EOQ_0 = 600 units | 37,200.00 / yr |
| All-Units Price Break (All Q >= 900 at 9.00) | Q_AU* = 900 units (At Break!) | 33,610.00 / yr (-3,590 Saved!) |
| Incremental Tier Break (Units > 900 at 9.00) | EOQ_inc* = 2,000 units (Never at Break!) | 36,000.00 / yr (-1,200 Saved) |
Minimum Total Annual Cost Under All-Units Price Break Formula
Compare optimal Base-Tier cost against All-Units Discount-Tier cost at Q_AU = MAX(Q_brk, EOQ_1).
=LET(d_yr, B2, s_ord, B3, h_rt, B4, p_0, B5, q_bk, B6, p_1, B7, IF(OR(d_yr <= 0, s_ord <= 0, h_rt <= 0, p_1 <= 0, p_0 <= p_1, q_bk <= 0), "Invalid Discount EOQ Input", LET(q_0, MIN(q_bk - 1, SQRT((2 * d_yr * s_ord) / (h_rt * p_0))), tc_0, d_yr * p_0 + (d_yr / q_0) * s_ord + 0.5 * q_0 * h_rt * p_0, q_au, MAX(q_bk, SQRT((2 * d_yr * s_ord) / (h_rt * p_1))), tc_au, d_yr * p_1 + (d_yr / q_au) * s_ord + 0.5 * q_au * h_rt * p_1, ROUND(MIN(tc_0, tc_au), 2))))Minimum Total Annual Cost Under Incremental Marginal Discount Formula
Absorb (p_0 - p_1) * Q_brk into effective setup S_inc = S + (p_0 - p_1) * Q_brk when Q >= Q_brk.
=LET(d_yr, B2, s_ord, B3, h_rt, B4, p_0, B5, q_bk, B6, p_1, B7, IF(OR(d_yr <= 0, s_ord <= 0, h_rt <= 0, p_1 <= 0, p_0 <= p_1, q_bk <= 0), "Invalid Discount EOQ Input", LET(q_0, MIN(q_bk, SQRT((2 * d_yr * s_ord) / (h_rt * p_0))), tc_0, d_yr * p_0 + (d_yr / q_0) * s_ord + 0.5 * q_0 * h_rt * p_0, s_inc, s_ord + (p_0 - p_1) * q_bk, q_in, MAX(q_bk, SQRT((2 * d_yr * s_inc) / (h_rt * p_1))), tc_in, d_yr * p_1 + (d_yr / q_in) * s_inc + 0.5 * q_in * h_rt * p_1, ROUND(MIN(tc_0, tc_in), 2))))Try the example
Use the original values or change them to test your understanding.