The open to buy formula, with cash, lead time, and sell-through in the same sheet

Open to buy is a purchasing budget expressed as a formula: planned purchases = planned sales + planned end-of-month inventory − beginning inventory − inventory already on order. Four numbers, cost dollars, per category, per month. The output is the amount of new stock the plan can absorb — and the reason it's worth the twenty minutes is that it converts "this reorder looks justified" into "this reorder fits, and that one doesn't."

Most OTB explanations stop at that formula. The three things that make it survive a real buying cycle are less commonly covered: the money you commit is not the money you receive (lead time shifts it by months), the sales you plan are not a hope (they come from sell-through), and the budget is not the bank balance (cash sets the ceiling underneath it). Here is the whole method, worked with numbers.

The formula, and what each term is really doing

Planned purchases = planned sales + planned EOM inventory − beginning inventory − on order

• Planned sales at cost. What you expect to sell, valued at what it cost you. Derive it from sell-through, not ambition — more on this below.
• Planned end-of-month inventory at cost. The stock you intend to be holding at month end. This is the term that prevents over-buying, because it forces a number onto "how much is enough."
• Beginning inventory at cost. What you have when the month opens, carried forward from last month's actual close.
• On order at cost. POs already placed and not yet received. Forgetting this term is the classic error, and it's the one that produces double orders.

All four in the same unit — cost dollars — or the answer is fiction. Mixing retail into a cost worksheet doesn't produce an error message; it silently doubles the number. (OTB can be run at retail with markup planning built in, which is how department stores do it. At small-store scale, cost is the honest basis, because cost is what leaves your bank account.)

Step 1: derive planned sales from sell-through

Planned sales has to come from evidence, and the evidence is last season's sell-through: units or dollars sold ÷ units or dollars received.

Last year's knitwear buy, illustrative: $40,000 at cost received, $34,000 sold at cost through the season — 85% sell-through at cost, with $6,000 carried into clearance. If this year's planned buy is $44,000 at a similar assortment and season, expected sales at cost are 44,000 × 0.85 = $37,400. That number goes in the plan; the 85% is the assumption you write down next to it.

Two adjustments worth making explicitly rather than burying:

• Sell-through is a rate, not a verdict. 85% on knitwear may be excellent or poor depending on the category's norm. Compare against your own prior seasons before you treat it as a target.
• Split full-price from markdown sales. A season that sells 85% at cost but gives away the last 30% at half price is a different business from one that sells 70% at full price. The sell-through rate guide covers how to read the ratio without flattering it.

Step 2: convert purchases into receipts using lead time

This is the step the textbook formula assumes away. A purchase order placed in October with a 60-day lead time arrives in December. October's OTB therefore constrains December's receipts, not October's shelf, and a plan that confuses the two will over-commit in the month it feels flush and starve the month it needs stock.

The fix is to run OTB by receipt month — the month goods land — and track commitments separately:

Arithmetic for October: (22,400 + 47,000) − (51,600 + 6,800) = 69,400 − 58,400 = $11,000. December: (31,500 + 58,000) − (52,000 + 4,000) = 89,500 − 56,000 = $33,500.

Read the October number against the December number and the lead-time point becomes obvious: October's buying capacity is small because stock and inbound orders are already heavy, while December's is large because the season is selling down. If your 60-day supplier needs a decision in October, the relevant budget is December's — and if you book that commitment against October's OTB, you'll conclude you can't afford the holiday buy in the month you actually must place it.

A practical rule for the sheet: one column per month, an OTB row, and a committed row that subtracts POs the moment they're placed rather than when they land. Committing against the receipt month, and tracking commitment immediately, is what keeps the two views from drifting.

Step 3: apply the cash ceiling

OTB answers "how much buying does the plan support?" It does not answer "can I afford it?" That is the cash budget, and it sits underneath: total inventory at cost plus paid in-transit goods plus supplier deposits must stay below a ceiling computed from your COGS and the weeks of cover you must hold.

The two disagree in a specific and useful way. If OTB says $11,000 and your open cash after reserves is $4,000, the plan is unaffordable this month — and the response is not to ignore the budget but to choose deliberately: shrink the planned EOM target (hold less), push receipts into the next month, or fund the difference. The inventory budget guide works the ceiling calculation; OTB is the plan that spends under it.

Worked month, end to end

A 240-SKU accessories store, one category, illustrative:

1. Sell-through input: last year's buy sold 82% at cost; this year's planned buy is $38,000 at cost → planned sales $31,160, round to $31,000.
2. EOM target: cover for the first three weeks of next month plus a 15% buffer → $46,000 at cost.
3. Beginning inventory: last month's actual close, $49,500.
4. On order landing this month: $7,200.
5. OTB: (31,000 + 46,000) − (49,500 + 7,200) = 77,000 − 56,700 = $20,300.
6. Cash check: open cash after reserves is $18,000 → the binding constraint is cash, not the plan. Spend $18,000 and carry $2,300 of intent into next month, or trim the EOM target by $2,300 (holding less is legitimate if the season supports it).
7. Priorities under the constraint: bestsellers below their cover target first, then core sellers inside the cycle, then new-product tests capped at a fixed share (10% of open cash is a workable convention), then depth on the tail.

The number in step 5 is not the answer to "what should I buy" — it's the ceiling on the answer. The lines underneath come from per-SKU logic, and those two systems have to talk to each other: how much inventory to order produces the line items; OTB says how many of them fit.

Four ways OTB fails

• It runs annually and is never re-forecast. A plan made in January is a fantasy by April. Recompute monthly; the worksheet takes minutes once the columns exist.
• Planned sales are aspiration. An optimistic sales plan authorizes over-buying with total confidence. Sanity-check against sell-through and, if the plan is above last year's actual, write down why.
• It's run at SKU level. OTB is a category budget; per-SKU OTB is maintenance for its own sake at any catalog size. Keep the budget at category level and the quantities at SKU level.
• Retail and cost get mixed in one column. The single most common structural error, and the one that produces plausible-looking numbers that are wrong by the markup.

The tooling, honestly

At small-store scale OTB lives in a spreadsheet, and that is a reasonable place for it — four rows per category per month is not a software problem. What doesn't exist yet, at the time of writing, is a widely adopted small-merchant OTB budgeter in the Shopify app market; the enterprise planning suites that automate it are priced for retailers with a planning department. Our own InventoryX is being built as a planning layer that includes purchase planning and open-to-buy, and it is in development — not a live app today, so the sheet above is the recommendation rather than a stopgap. ClearStock, our live app, works on the other end of the lifecycle: finding slow and dead stock and applying discount rules to clear it.

Common questions

What is the open to buy formula? Planned purchases = planned sales + planned end-of-month inventory − beginning inventory − inventory on order. Keep every term in cost dollars. The output is the dollar amount of new buying the month's plan can absorb.

Should open to buy be calculated at cost or at retail? At cost for a small store: it matches what you actually spend, what supplier invoices total, and what your cash position feels. Larger retailers run OTB at retail with markup planning built in, which is more machinery than a Shopify store with a few hundred SKUs needs.

How does lead time change the calculation? It changes which month's budget a commitment belongs to. Goods ordered in October with a 60-day lead time land in December, so they consume December's OTB — not October's. Run the worksheet by receipt month and subtract commitments the moment a PO is placed.

How do I calculate open to buy in a spreadsheet? One row per month per category: planned sales, planned EOM inventory, beginning inventory, on order, then =(B1+B2)-(B3+B4). Roll each month's actual closing stock into the next month's beginning inventory, and add a committed row that updates as POs go out.

What's the difference between open to buy and an inventory budget? OTB is a purchasing plan derived from sales and stock targets. The inventory budget is the liquidity ceiling underneath it — the most cash you can have locked in stock, paid transit, and deposits without breaking payroll or tax reserves. When they disagree, the cash ceiling wins and the plan gets revised.

Next step

Build the four-row worksheet for your biggest category, this month and next, using last year's sell-through for the planned-sales line rather than a round number. Then run your intended reorders through it. If they exceed the OTB, you've caught an over-buy before it happened — which is the entire return on twenty minutes of arithmetic.