How do I set up an open-to-buy system to forecast demand by category in a department store chain?

An open-to-buy (OTB) system for a department store chain is basically a category-level demand and inventory planning process that tells you:

• how much you can buy,
• when you should buy it,
• and where to allocate budget across categories, stores, and time periods

so you can meet demand without overstocking.

Here’s a practical way to set it up.

---

1) Define the planning structure

Start by deciding the planning hierarchy.

Typical hierarchy:

• Company
• Division / department
• Category
• Subcategory / class
• Store cluster / region / channel
• Time period: week or month

For most department store chains, a good setup is:

• Monthly OTB at category level
• Weekly execution at class/store cluster level

This gives enough control without making the model too noisy.

---

2) Decide what OTB will forecast

OTB should forecast and connect these key elements:

• Sales forecast
• Gross margin
• Inventory on hand
• In-transit inventory
• Committed/open orders
• Planned receipts
• Markdowns
• Shrink/returns
• Target stock levels / weeks of supply

Core OTB equation:

Open-to-buy = Planned net sales + Planned markdowns + Desired ending inventory - Beginning inventory - Open orders - Receipts already committed

In practice, many retailers simplify to:

OTB = Budgeted sales + Desired ending stock - Beginning stock - On-order inventory

Then adjust for markdowns, returns, and other factors.

---

3) Build a demand forecast by category

OTB depends on a reliable category demand forecast.

Inputs to use

At minimum, use:

• Historical sales
• Units sold
• Average unit retail price
• Gross margin
• Promotions
• Seasonality
• Holiday timing
• Markdown history
• Stockouts / lost sales
• Product lifecycle stage
• Weather or local events if relevant

Forecast at the right level

A good approach is:

1. Forecast total category demand
2. Reconcile forecasts down to subcategories/classes
3. Allocate to stores or store clusters

That avoids overfitting at the store-SKU level early on.

Forecast methods

Use a mix:

• Baseline statistical forecast
  • moving average, exponential smoothing, ARIMA, Prophet, etc.
• Driver-based adjustments
  • promotions, pricing, holidays, seasonality, events
• Judgmental overrides
  • merchant input for new launches, fashion trends, etc.

For department stores, category demand is often strongly affected by:

• seasonality,
• promotions,
• fashion cycles,
• weather,
• holidays,
• price changes.

So a pure time-series model is usually not enough.

---

4) Create the merchandise financial plan

You need a monthly or weekly plan for each category:

• Planned sales
• Planned gross margin %
• Planned markdown %
• Planned receipts
• Planned inventory
• Planned turns / weeks of supply

Example:

• Sales target: $500k
• Gross margin target: 42%
• Ending inventory target: $300k
• Beginning inventory: $250k
• Open orders: $75k

Then OTB tells you how much additional buying room remains.

---

5) Incorporate inventory dynamics

OTB must account for how inventory flows.

Inventory equation

Ending inventory = Beginning inventory + Receipts - Sales - Markdown units - Shrink + Returns

For planning purposes:

• Receipts are what you expect to get from suppliers
• Sales are forecast demand
• Returns may matter in apparel and soft goods
• Shrink should be modeled if material

You need to maintain a target inventory level based on:

• lead times,
• demand volatility,
• service level goals,
• replenishment frequency.

A common approach is to target: Weeks of supply = Lead time + review period + safety stock buffer

---

6) Segment categories by behavior

Not all categories should be planned the same way.

Segment categories into groups such as:

• Stable/basic replenishment
• Seasonal
• Fashion/discretionary
• Promotional
• New product / launch
• Long-tail / slow-moving

Each needs different OTB logic:

• Stable basics: forecast from history and target weeks of supply
• Seasonal: use last year + season curve + timing shifts
• Fashion: more merchant input and trend adjustment
• Promotional: build event-based uplift models
• New items: analogs/similar item mapping

---

7) Set planning assumptions

You need standard assumptions at each category level:

• Lead times by vendor
• Minimum order quantities
• Pack sizes
• Fill rates / service levels
• Markdown cadence
• Replenishment frequency
• Carrier delays
• Return rates
• Shrink rates

Without standardized assumptions, OTB becomes inconsistent across buyers and categories.

---

8) Build the actual OTB model

A useful monthly model includes these lines:

Starting point

• Beginning inventory at retail
• Add: planned receipts
• Less: forecast sales
• Less: markdowns
• Less: shrink
• Add/Subtract: adjustments

Then compare to target ending inventory

• Target ending inventory based on weeks of supply or sales plan

Result

• Open-to-buy dollars
• Open-to-buy units
• Suggested order quantities

You can also calculate OTB by both:

• Retail value
• Cost value

Retail is useful for merchandising control; cost is useful for purchase budgeting.

---

9) Reconcile top-down and bottom-up planning

Best practice is to use both:

Top-down

• Finance sets sales, margin, and inventory targets

Bottom-up

• Merchants propose forecasts based on category knowledge

Then reconcile them:

• If bottom-up sales > top-down target, challenge the assumptions
• If bottom-up inventory is too high, identify excess coverage
• Use exception-based review rather than manual review of everything

This is often called plan alignment.

---

10) Add scenario planning

OTB should support scenarios such as:

• Base case
• High demand case
• Low demand case
• Promotional upside
• Supplier delay case
• Clearance/markdown case

This is critical in department stores because demand can shift quickly.

Useful questions:

• What if sales run 10% below plan?
• What if a vendor delivery slips by 3 weeks?
• What if a category must be marked down earlier?
• What if weather shifts seasonal demand?

---

11) Set up the workflow and governance

An OTB system is not just a model; it’s a process.

Monthly cycle

1. Forecast demand
2. Update inventory position
3. Calculate OTB
4. Review merchant exceptions
5. Approve buys
6. Place orders
7. Track actuals vs plan
8. Reforecast next period

Governance roles

• Merchandising: owns category plan and buys
• Planning/forecasting: builds demand forecasts and OTB
• Finance: validates sales/margin/inventory targets
• Supply chain: confirms lead times and receipts
• Store ops: provides store execution feedback

---

12) Track KPIs to improve the system

Important KPIs:

• Forecast accuracy by category/class/store
• Bias
• Gross margin %
• Inventory turns
• Weeks of supply
• Sell-through rate
• Stockout rate
• Markdown rate
• Open-to-buy utilization
• Aged inventory %

If the system is working, you should see:

• better forecast accuracy,
• lower excess inventory,
• fewer stockouts,
• improved margin,
• faster reaction to demand changes.

---

13) Recommended data model

At minimum, your data model should include:

• Item, class, category hierarchy
• Store and store cluster
• Date/calendar and seasonality markers
• Sales history
• Inventory snapshots
• Receipts and open orders
• Purchase orders
• Price and markdown history
• Promotion calendar
• Vendor lead times
• Product attributes
• Returns/shrink data

A data warehouse or planning cube works well here.

---

14) Practical implementation approach

Phase 1: Build a simple version

• Monthly category forecast
• Inventory and receipts data
• OTB calculation in Excel/BI tool

Phase 2: Automate forecasts

• Add statistical forecast models
• Add seasonality and promo adjustments

Phase 3: Add allocation and exception management

• Store cluster planning
• Item-level replenishment logic
• Scenario planning and alerts

Phase 4: Integrate with ERP/planning tools

• Connect to purchasing, inventory, and finance systems
• Automate PO recommendations

---

15) Example OTB calculation

Suppose for a category in next month:

• Beginning inventory: $200,000
• Planned sales: $300,000
• Planned markdowns: $20,000
• Target ending inventory: $250,000
• Open orders already placed: $50,000

Then:

OTB = Planned sales + Markdowns + Target ending inventory - Beginning inventory - Open orders

OTB = 300,000 + 20,000 + 250,000 - 200,000 - 50,000 = 320,000

So you have $320,000 open-to-buy.

If you want a more conservative plan, reduce target inventory or update sales if demand weakens.

---

16) Common pitfalls

Avoid these:

• Forecasting only at total chain level
• Ignoring stockouts in historical sales
• Using one method for all categories
• Not accounting for markdowns
• Failing to update open orders
• Planning in retail dollars only
• Not reconciling finance and merchandising assumptions
• Overusing manual overrides without tracking accuracy

---

17) Best-practice design summary

A strong department store OTB system should:

• forecast demand by category and store cluster,
• translate demand into inventory and buying needs,
• align with financial plans,
• account for lead times and open orders,
• support scenario planning,
• and be reviewed on a regular merchandising cadence.

---

If you want, I can also give you:

1. a template OTB spreadsheet structure,
2. a sample data schema, or
3. a step-by-step forecasting formula set for Excel or Power BI.