An open to buy worksheet is a tool that retailers use to plan their inventory purchases for a specific period of time. It helps them balance their stock levels, sales goals, and cash flow by showing how much they need to buy and when.

In this article, we will explain what an open to buy worksheet is, how it works, and how you can create one for your retail store.

An open to buy worksheet is a spreadsheet that calculates the amount of inventory that a retailer needs to order for each product category based on the following factors:

• Expected sales: The projected revenue from selling the products in a given period.
• Planned markdowns: The discounts or price reductions that the retailer plans to offer on certain products to clear excess inventory or stimulate demand.
• Beginning inventory: The amount of inventory that the retailer has on hand at the start of the period.
• Ending inventory: The amount of inventory that the retailer wants to have on hand at the end of the period.

The open to buy worksheet uses a simple formula to calculate the amount of inventory that the retailer needs to order for each product category:

Opening inventory + Purchases - Sales - Markdowns = Ending inventory

The purchases are the variable that the retailer needs to solve for. By rearranging the formula, we get:

Purchases = Sales + Markdowns + Ending inventory - Opening inventory

This formula tells us how much inventory we need to buy to achieve our sales and inventory goals. We can apply this formula to each product category and sum up the results to get the total budget for inventory purchases for the period.

To create an open to buy worksheet, you will need some data and assumptions about your sales, markdowns, and inventory levels. You can use historical data, industry benchmarks, or your own estimates based on your experience and market research.

Here are the steps to create an open to buy worksheet:

1. Create a spreadsheet with columns for product categories, expected sales, planned markdowns, beginning inventory, ending inventory, and purchases.
2. List your product categories in rows. You can use broad categories (e.g., clothing, accessories, footwear) or more specific ones (e.g., shirts, pants, dresses) depending on your preference and level of detail.
3. Enter your expected sales for each product category in the corresponding column. You can use monthly, quarterly, or seasonal data depending on your planning cycle. You can also break down your sales by channels (e.g., online, in-store) if you want more granularity.
4. Enter your planned markdowns for each product category in the corresponding column. You can use a percentage of sales or a dollar amount depending on how you plan your promotions. You can also vary your markdowns by product category depending on their demand and profitability.
5. Enter your beginning inventory for each product category in the corresponding column. This is the amount of inventory that you have on hand at the start of the period. You can use your actual inventory count or your inventory system data.
6. Enter your ending inventory for each product category in the corresponding column. This is the amount of inventory that you want to have on hand at the end of the period. You can use a target inventory turnover ratio or a target number of days of supply depending on how you measure your inventory performance.
7. Use the formula Purchases = Sales + Markdowns + Ending inventory - Opening inventory to calculate the amount of inventory that you need to order for each product category and enter it in the corresponding column.
8. Add up the purchases for all product categories and enter it in a separate cell. This is your total budget for inventory purchases for the period.

You can use this open to buy worksheet as a guide for placing your orders with your suppliers. You can also update it regularly with actual sales, markdowns, and inventory data to monitor your performance and adjust your plans accordingly.

Using