How to Use an Open to Buy Spreadsheet for Retail Planning
An open to buy spreadsheet is a tool that retailers use to budget for their future inventory purchases. It helps them to stock the right amount of the right products at the right time by showing the difference between how much inventory they need and how much they have available.
In this article, we will explain what an open to buy spreadsheet is, why it is important, and how to create one using a simple Excel template.
What is an Open to Buy Spreadsheet?
An open to buy spreadsheet is a document that shows the planned inventory levels, sales, and purchases for a specific period, usually six months. It is calculated at cost and assigned to different product categories based on each category’s contribution to total sales mix.
The basic formula for an open to buy spreadsheet is:
This formula accounts for markdowns by calculating sales at cost (COGS). Opening stocks, intakes, and closing stocks are also calculated at cost value, not retail value.
Why is an Open to Buy Spreadsheet Important?
An open to buy spreadsheet is important because it helps retailers to achieve their growth, profitability, and cash flow targets. It also helps them to avoid overstocking or understocking issues that can affect their sales performance and customer satisfaction.
Some of the benefits of using an open to buy spreadsheet are:
It helps retailers to align their inventory purchases with their sales forecasts and demand trends.
It helps retailers to optimize their inventory turnover and reduce their carrying costs.
It helps retailers to plan for seasonal variations and promotional events.
It helps retailers to monitor their actual performance against their plans and make adjustments as needed.
It helps retailers to maintain a centralized source of truth for their plans and actuals.
How to Create an Open to Buy Spreadsheet?
To create an open to buy spreadsheet, you will need some data and a template. The data you will need are:
Your sales budget or forecast for the next six months.
Your desired gross margin percentage for each product category.
Your current inventory levels and values for each product category.
Your planned markdowns and inventory adjustments for each product category.
The template you will need is a simple Excel spreadsheet that has the following columns:
Product Category: The name of the product category (e.g., tops, bottoms, accessories, etc.).
Sales Plan: The planned sales at retail value for each month.
Sales Plan at Cost: The planned sales at cost value for each month. This is calculated by multiplying the sales plan by (1 – gross margin percentage).
Opening Stock: The inventory level at cost value at the beginning of each month.
Closing Stock: The inventory level at cost value at the end of each month. This is calculated by adding the opening stock and the intakes (purchases) and subtracting the sales plan at cost.
Stock Cover: The number of months of sales that the closing stock can cover. This is calculated by dividing the closing stock by the sales plan at cost.
Intakes (Purchases): The amount of inventory that needs to be purchased at cost value for each month. This is calculated by subtracting the opening stock and the sales plan at cost from the desired closing stock.
To create an open to buy spreadsheet using this template, follow these steps:
Build a sales plan for each product category based on your sales budget or forecast. You can use historical data, market trends, or customer feedback to estimate your sales potential.
Create an inventory control plan for each product category based on your desired stock cover. Stock cover is the number of months of sales that your inventory can cover. A higher stock cover means more inventory on hand, which can reduce stockouts but increase carrying costs. A lower stock cover means less inventory on hand, which can reduce carrying costs but increase stockouts. You