Understanding and Leveraging Open to Buy to Master Your Retail Inventory
Managing inventory can make or break a retail business. Buy too much, and your cash
is tied up in products that may not sell. Buy too little, and you miss sales opportunities.
That’s where Open to Buy (OTB) comes in—a simple, strategic tool that helps retailers
plan their inventory purchases with confidence.
What Is Open to Buy?
Open to Buy (OTB) is a retail inventory planning system. It helps you determine how much inventory you can and should purchase during a specific period—usually a month or a season—based on your sales plan, inventory levels, and desired stock levels.
Combine this with lead times and you’ll know just what to order and when.
In plain terms, OTB helps you answer the critical question:
How much inventory should I order this month without overspending or understocking?
Why Is OTB So Useful?
OTB isn’t just for big retailers with complex systems. It’s valuable for anyone who needs to stay on top of inventory and cash flow. Here’s why:
Avoid Overbuying: No more shelves full of dead stock.
Prevent Stockouts: Stay in-stock on the items that drive your revenue.
Free Up Cash Flow: Know when to hold back on purchasing to avoid tying up
your money.
Make Smarter Buying Decisions: Align inventory purchases with sales
forecasts.
Quick Visibility: With just a few inputs, you get a clear, at-a-glance picture of
your inventory situation.
Who Should Use OTB?
Open to Buy is ideal for:
Brick-and-mortar retail owners (boutiques, specialty shops, etc.)
Ecommerce store owners
Inventory managers and buyers
Pop-up or seasonal sellers
Any retailer who orders inventory regularly
Even if you’re running a small operation, OTB can bring much-needed structure and foresight to your purchasing decisions. Generally speaking, OTB makes the most sense for retailers with a large number of unique items (SKUs) that need to be managed and ordered.
How to Calculate OTB
You can calculate Open to Buy for a product or location using a very simple formula:
Open to Buy = Projected Sales + Planned Ending Inventory – Beginning Inventory –
Already On Order
Planned Sales: How much you expect to sell during the month or period.
Ending Inventory: How much inventory you want to have left at the end of the
period.
Beginning Inventory: The amount of inventory you have at the beginning of the
period.
Already On Order: The amount of inventory you have already ordered and
expect to receive during that month or period.
Sounds pretty simple right? It can be, but putting it into practice can also get confusing quickly. That’s why we’ve created the how-to-guide below which will show you how to build your own spreadsheet for calculating OTB at your business.
Make Your Own OTB Spreadsheet
Inventory, sales, data, calculations… this sounds like a job for Microsoft Excel! Below, we’ll walk through how to create your own spreadsheet, but first it’s worth asking upfront if this really is a job for Excel or not. For example, if you manage 3 store locations with 100 products each, you could be looking at anywhere between 50,000 to 250,000 calculations.
The good news is that, either way, we’ve got you covered! Below we’ll show you how to build a spreadsheet, but if you need something more robust, you can click here to talk to one of our specialists about a custom OTB tool that still uses Excel on the front end, but that has a much more powerful engine for crunching numbers and managing relationships between parameters. Instead of having 20 spreadsheets with 400 tabs, you can have a single spreadsheet that does everything you need in one place.
Now, onto the OTB spreadsheet tutorial….
Month – enter the months for the duration of your analysis. Typically, sales should be projected 24 months into the future if possible.
Projected Sales – enter the projected sales for each month.
COGS – calculate your cost of goods sold (COGS) with this formula: =B5*(1-0.45) and replace the 0.45 with your gross profit margin or discount from the supplier. This formula can be copied all the way down.
Ending Inventory – this setup is designed to work around a target Inventory Turnover (how often a company sells through its inventory over a given period—usually a year). If your target inventory turnover is 4 times per year, that would be once every 3 months. In that case the formula in cell D5 would be =SUM(C6:C8) which is the sum of COGS over the next 3 months. If your target turnover is 2, the formula would sum the following 6 months of COGS. The copy can then be copied down.
Beginning Inventory – In cell E5 of the example, you should manually type in the beginning inventory value.
Note: this must be expressed at cost, not retail value. Then in cell E6 use the formula =D5 and copy it down the table in that column. The beginning inventory is always the prior period’s Ending Inventory.
On Order – This column allows you to record inventory that is currently on order that has not been received yet. There are a couple things to keep in mind when filling out this column: (1) be sure to enter the order value at cost, (2) the order amount should be entered in the period (e.g., month) when the items are expected to arrive, not when they were ordered.
Purchases (OTB) – In cell G5 of the example, use the formula =C5+D5-E5-F5 to
calculate the Open to Buy value for the month. This is the same formula that was
discussed earlier in this article: Open to Buy = Projected Sales + Planned Ending
Inventory – Beginning Inventory – Already On Order. This formula can also be copied down the table.
See the completed example below.
If you have any trouble setting up your Open to Buy calculator, or need help with a more advanced solution, click the button below to speak with someone from our team.
Future Weeks of Stock (FWOS)
Our example above used a target turnover rate. However, some businesses prefer to instead target Forward Weeks of Stock (FWOS) which is more granular and helps you understand how long your current inventory will last based on your future sales projections.
FWOS = Current Inventory ÷ Average Weekly Forecasted Sales
Using FWOS instead of turnover rate adds a more granular time dimension to your inventory picture, and one that can be varied seasonally as needed. Rather than using a projected inventory turnover, you stipulate how many weeks the product needs to last which may be driven by other factors such as re-order windows, minimum orders from suppliers or supplier lead times.
Example:
If you have 300 units in stock and expect to sell 100 units per week, your FWOS is 3 weeks. That tells you you’ll run out in three weeks unless you restock. If that item has a Target Weeks of Stock (TWOS) of 4 weeks, then you are understocked and the calculator would show the Open to Buy amount needed to bring the projected FWOS into alignment with your target weeks of stock (TWOS).
Use FWOS to prioritize your buying within OTB: items with low FWOS and strong sales should be ordered first. Setting up a calculator this way requires a more advanced financial model and is beyond the scope of this post. If you need help setting up this style OTB, you can book a free call with someone from our team using the button below.
OTB-Driven Order Reports
A big practical benefit of OTB planning is that it naturally leads to a targeted order
report. Once you;ve calculated how much you're open to buy (per category or vendor), your spreadsheet or reporting tool can:
List exactly which items need to be reordered
Rank them by urgency based on stock levels or sales velocity
Filter out overstocked items, so you don’t waste budget replenishing the wrong products
Instead of guessing or reacting to out-of-stocks, you can make data-driven purchasing decisions that align with your sales goals and inventory targets. This turns your spreadsheet into a daily or weekly command center for purchasing.
This functionality is best accomplished by writing VBA scripts to pull and summarize the information the way you want to see it. You may also want to see overstocked items so that you can run discounts, promotions or giveaways, and this style reporting is perfect for identifying those items as well. If you need help automating this style reporting, click here to speak with someone from our team.
Rolling Forecasts & Regular Updates
OTB works best when it’s updated regularly. Markets shift, trends change, and sales can surprise you—for better or worse. By reviewing your OTB and FWOS monthly (or even weekly), you can adjust your plan to stay on track.
Keep in mind:
- Sales plans may need tweaking based on real-world results
- Inventory data must be accurate—OTB is only as good as your numbers
- Seasonality and promotions can temporarily throw off your metrics
Importing Sales & POS Data
As wonderful as the Open to Buy (OTB) spreadsheet is at helping you manage your retail inventory efficiently, it will only work well if it is supported by good data. Sometimes, that means a LOT of data.
Manually entering or even copying and pasting the sales, PO and inventory data into your spreadsheet can be a major hassle and exhaustively time consuming. That’s why we recommend setting up some reliable automation for getting data into your OTB calculator spreadsheet.
There are 3 main ways of accomplishing this, depending on where the data is stored (i.e., what software you are using for POS and inventory management):
Export-Import: Most modern software can export any and all data you need to an excel or csv (comma separated value) document. You can then set up an import
tool in your OTB spreadsheet using vba programming that ‘consumes’ the export
file, importing it into your spreadsheet and performing any necessary data
transformations in the process.
PowerQuery: Excel’s built-in data-import tool is called PowerQuery. It can be
used to import data from an export file without the need for vba programming. In
some cases it can also be used to form direct connections that import the data
automatically when refreshed.
API: Larger software platforms have APIs (application programming interfaces).
Using VBA programming in Excel, code can be written that pulls information into
the spreadsheet directly from the source program using their secure API tokens.
Can Excel Handle It?
As we mentioned upfront, for larger-scale operations and businesses with hundreds of items or multiple locations, Excel may not be the best fit. The reasons for this are primarily:
Speed – massive amounts of data-crunching may overload Excel’s calculation
engine resulting in slow performance or frequent crashes.
Ease of Maintenance – maintaining all of this data, the relationships and the
calculations in Excel may grow excessively cumbersome past a certain point.
Potential for Errors – when formulas are used in Excel, there is always the
possibility that a user will accidentally delete or edit a formula, leading to errors
that are extremely challenging to detect or chase down once discovered.
Push-Pull Capability – in Excel, each cell is either a formula for calculation, or a
cell for data entry. You can push data in or pull out a result by formula, but no cell
can do both. This means if you want to edit a result and see how it effects the
model, there’s no easy way of doing so.
To better serve the needs of clients where the considerations above are a priority, we have partnered with a technology group that creates fully-customized solutions with Excel as the user-interface, but with a much more robust calculation engine operating in the background, which solves all of the problems listed above. To find out more about this solution, send us a message or book a call with one of our specialists to discuss.
