Financial
Retail Open-to-Buy (OTB) formula: merchandise budget at retail and cost
Calculate monthly Open-to-Buy purchasing capacity OTB = PlannedSales + Markdowns + PlannedEOM - BOM - OnOrder at both retail value and wholesale cost.
Prevent bloated seasonal inventory with the Retail Open-to-Buy equation!
This monthly planning worksheet uses a consistent retail-value basis: planned sales plus planned markdowns/shrink and target ending stock, minus beginning stock and outstanding receipts expected in the same month. Do not mix on-order cost values with retail-valued sales and inventory or subtract receipts already included in beginning stock.
The cost estimate multiplies positive retail OTB by a supplied cost-to-retail complement. F2 is initial markup as a fraction of retail selling price, not markup on cost: (retail-cost)/retail. A 60% retail-basis markup implies a 40% complement. This is an estimate when product mix and markdowns vary; negative OTB means commitments exceed this plan, not proof that inventory should be cancelled.
| Monthly OTB Component (60% Initial Markup) | Amount ($) | Impact on OTB |
|---|---|---|
| Planned Sales ($180k) + Markdowns ($15k) | $195,000 | + Inventory outflows to replace |
| Planned EOM Target Stock | $240,000 | + Total stock needed ($435k) |
| Less: Actual BOM ($210k) + On-Order ($95k) | -$305,000 | = $130,000 OTB at Retail! |
| OTB at Wholesale Cost (1 - 60% = 40%) | $130,000 * 0.40 | = $52,000 Wholesale PO Budget |
Open-to-Buy at Retail ($)
A2=PlannedSales, B2=PlannedMarkdowns, C2=PlannedEOM, D2=ActualBOM, E2=OnOrder.
=IF(OR(COUNT(A2:E2)<>5,MIN(A2:E2)<0),"Invalid inputs",A2 + B2 + C2 - D2 - E2)Open-to-Buy at Wholesale Cost ($) with Overbought check
Use F2 between 0 inclusive and 1 exclusive for this positive-cost scenario. Keep all monetary inputs nonnegative and in the same currency, period and retail basis. Outstanding orders must be those expected to arrive in this planning period.
=IF(OR(COUNT(A2:F2)<>6,MIN(A2:E2)<0,F2<0,F2>=1),"Invalid inputs",LET(otb, A2 + B2 + C2 - D2 - E2, IF(otb < 0, "Overbought", ROUND(otb * (1 - F2), 2))))Try the example
Use the original values or change them to test your understanding.