Open-to-buy (OTB) by category and month
One row per category and month: sales plan, price reductions, weeks of cover, opening stock and orders. The output is OTB at retail and at cost.
What the template is for
The monthly OTB refresh and an over-buying check by category.
- What one row is
- one row = category × month
Columns
| Column | Code | Type | Required | Validation rule |
|---|---|---|---|---|
| Month | month | Text | yes | month as YYYY-MM |
| Category | category | List value | yes | a value from the category reference |
| Planned sales, thousand, excl. VAT | sales | Number | yes | a number not below 0 |
| Planned price reductions, thousand | markdown | Number | yes | a number not below 0 |
| Target weeks of cover at the end | cover | Number | yes | a number not below 0 |
| Closing stock, thousand (retail) | end | Number | yes | calculated: sales × weeks of cover ÷ 4.3 |
| Opening stock, thousand (retail) | begin | Number | yes | a number not below 0 |
| On order, not received, thousand (retail) | onorder | Number | yes | a number not below 0 |
| OTB, thousand (retail) | otb | Number | yes | calculated: sales + reductions + closing stock − opening stock − orders; a negative value means over-buying |
| Initial markup margin IMU, % | imu | Number | yes | from 0 to 100 |
| OTB, thousand (at cost) | otbCost | Number | yes | calculated: OTB × (1 − IMU ÷ 100) |
Example rows
| Month | Category | Planned sales, thousand, excl. VAT | Planned price reductions, thousand | Target weeks of cover at the end | Closing stock, thousand (retail) | Opening stock, thousand (retail) | On order, not received, thousand (retail) | OTB, thousand (retail) | Initial markup margin IMU, % | OTB, thousand (at cost) |
|---|---|---|---|---|---|---|---|---|---|---|
| 2027-02 | women | 9,800 | 1,200 | 8 | 18,233 | 21,000 | 6,500 | 1,733 | 58 | 728 |
| 2027-02 | men | 4,400 | 450 | 8 | 8,186 | 8,800 | 3,000 | 1,236 | 57 | 531 |
| 2027-02 | kids | 2,100 | 300 | 6 | 2,930 | 3,300 | 1,500 | 530 | 55 | 238 |
| 2027-03 | women | 12,400 | 1,500 | 8 | 23,070 | 17,500 | 5,200 | 14,270 | 58 | 5,993 |
| 2027-03 | men | 5,600 | 600 | 8 | 10,419 | 7,900 | 2,400 | 6,319 | 57 | 2,717 |
The example data is illustrative: an exercise, replace it with your own.
Note
Use the IMU of each category, not a chain average. Convert opening stock and orders to one base (retail) before the calculation. The example data is illustrative: an exercise, not a norm or an industry benchmark.
How to open the file
The file is UTF-8 with a BOM, comma separated, with a decimal point. Set the text format for code columns (SKU, article, GTIN) so that long numbers and leading zeros are not distorted. The validation rules are suggested defaults, not norms: your systems and contracts may require something else.
Calculations that use these fields
- Open-to-buy (OTB) for the season
- Buying budget by category and weeks of supply
- Opening stock: stock-to-sales and the monthly buying budget
- Buying plan adherence: receipts, sales and stock
- Reorder from early sales
- Season sell-through pace and leftover risk
- Average stock for the year and turnover
- Brand scorecard for a buyer: weighted criteria, stop criteria and a decision by thresholds
Documents that need this data
- Open-to-buy budget (OTB)
- Sales plan
- Merchandise stock balance
- A buyer session: the brand scorecard, the agenda and the order terms
- Label launch: from an idea to the first sales — stages, documents, calculations in order and readiness criteria
- Optimization of range and allocation: knapsack by budget and shelf, marginal stock allocation, the transport problem
- Job profiles: merchandiser and collection product manager — what they count, which documents they make, how the week works
- Safety stock, service level and newsvendor: how much to hold and how much to order
- The six-month merchandise plan and model stock: sales, coverage, safety stock, purchases by month
- S&OP: cycle and documents
- Excel for retail, part II: ABC analysis and the open-to-buy (OTB) budget in a spreadsheet