Cellwise Start free

Excel guide · 4 min read

How to calculate open-to-buy in Excel

Quick answerOpen-to-buy = planned sales + planned markdowns + planned end-of-month inventory - beginning-of-month inventory - stock on order. In Excel, with those in rows 2 to 5 and on order in row 7 of a month's column: =B2+B3+B4-B5-B7. A negative result means you've already ordered more than the plan allows for that month.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGH
1Plan at retailMar 1, 2027Apr 1, 2027May 1, 2027Jun 1, 2027SeasonInitial markup %0.52
2Planned sales42000460005100044000183000
3Planned markdowns350028004200610016600
4Planned EOM inventory1180001250001120009800098000
5BOM inventory112000118000125000112000112000
6Planned purchases51500558004220036100185600
7On order520003100012000095000
8Open-to-buy-50024800302003610090600
9StatusOver-boughtOKOKOKOK
10OTB at cost-24011904144961732843488

Download this sheet as an Excel practice file (.xlsx)

The layout: one column per month, all at retail

Open-to-buy (OTB) is the budget a buyer still has for a month: how much more stock can be ordered without ending the month above plan. It comes from the merchandise plan, so the inputs are planned figures: planned sales, planned markdowns, and the stock you plan to hold at the end of each month.

Pick one valuation and keep every row on it. This sheet works at retail (selling price), which is the common way to plan; working entirely at cost is fine too. What breaks the plan is mixing them, for example markdowns at retail with on-order at cost. The months run across B:E (March to June 2027), the rows down column A, and the season total sits in F.

Only two things are typed besides the plan: the opening inventory for the first month (B5, the actual stock you start with) and what's already on order (row 7). Everything else is a formula.

Planned purchases: what the month needs in total

Stock leaves the shelf as sales and as markdowns (a markdown takes retail value off the stock without anything being sold), and you want to finish the month with the planned end-of-month (EOM) stock. Whatever of that isn't already covered by the beginning-of-month (BOM) stock has to arrive during the month. So planned purchases = sales + markdowns + EOM - BOM.

For March: 42,000 + 3,500 + 118,000 - 112,000 = 51,500 of stock at retail needs to arrive. If you also plan for shrinkage (theft, damage, admin errors), add it as one more row next to markdowns; it reduces stock the same way.

=B2+B3+B4-B5

Result: 51500

51,500 of planned purchases at retail for March.

How does next month's opening stock work?

Link it to this month's planned end: C5 is =B4, and so on across. Don't type the BOM for later months, or the months stop agreeing with each other when the plan changes.

=C5

Result: 118000

118,000: April opens with March's planned end-of-month stock.

Open-to-buy: subtract what's already on order

Purchase orders already placed for delivery in the month use up part of that budget. Subtract them and what's left is the open-to-buy. Put on-order in the month it's due to arrive, not the month the order was placed, since it's the arrival that changes the stock.

March has 52,000 on order against 51,500 of planned purchases, so the OTB is -500. April has 24,800 left to spend at retail, May 30,200 and June 36,100.

=B6-B7

Result: -500

-500: March is already over-bought by 500 at retail.

How do I flag over-bought months?

Test for a negative OTB with IF. Add conditional formatting (Home > Conditional Formatting > Highlight Cells Rules > Less Than, with 0) to colour the OTB row too.

=IF(B8<0,"Over-bought","OK")

Result: Over-bought

Over-bought for March.

Convert OTB at retail to cost

Buyers place orders at cost, so the retail budget is usually converted. At retail, OTB x (1 - initial markup %) gives the cost value, with the planned initial markup (as a percentage of retail) in an input cell, 52% in H1. April's 24,800 at retail is 11,904 at cost.

This is an approximation that assumes everything you buy carries that markup. If categories have very different markups, plan each category in its own block and convert with its own markup.

=C8*(1-$H$1)

Result: 11904

11,904: April's open-to-buy at cost, from 24,800 at retail and a 52% initial markup.

The season total, and why it isn't enough

Column F rolls the plan up: total sales and markdowns, the first month's BOM (=B5) and the last month's EOM (=E4). Planned purchases for the season with those inputs equal the sum of the monthly figures, because each month's end is the next month's beginning and they cancel out. That makes a neat check cell.

The season shows 90,600 left to buy, which looks comfortable, but March is already over by 500. Season totals net months against each other, so always read the monthly row. If March's extra stock arrives and sales only hit plan, March ends above its planned EOM, and you can either reduce April's OTB to match or accept a higher April stock. That's a planning choice, so make it visible rather than burying it in the totals.

=F6-SUM(B6:E6)

Result: 0

0: the season's planned purchases (185,600) equal the four months added together.

Common mistakes

Mixing cost and retail

Keep every row at retail or every row at cost. Convert the final OTB, not the inputs.

Typing each month's opening stock

Link BOM to the previous month's planned EOM so the plan stays consistent when you change it.

On-order by order date

Put orders in the month they're due to arrive. That's when they add to stock.

Only checking the season total

A negative month can hide inside a positive season. Flag each month.

FAQ

What is the open-to-buy formula?

Planned sales + planned markdowns + planned end-of-month inventory - beginning-of-month inventory - on order. Everything at retail or everything at cost.

What does a negative open-to-buy mean?

You've already ordered more than the plan needs for that month, so the month is over-bought. Whether to cancel, delay or accept it is a buying decision; the sheet just makes it visible.

How do I calculate open-to-buy at cost?

Multiply OTB at retail by (1 - initial markup %), with the markup as a percentage of retail. 24,800 at retail with a 52% markup is 11,904 at cost.

Where does planned end-of-month inventory come from?

Often from next month's planned sales and a planned stock-to-sales ratio, because next month's BOM is this month's EOM. See stock-to-sales ratio in Excel.

Functions used

Related guides

Updated