Open To Buy Excel Spreadsheet

Open-to-buy (OTB) is an inventory management strategy and formula businesses use to create buying budgets for specific periods of time. It takes into account expected beginning-of-month and end-of-month inventory, planned sales, and planned markdowns. Basically, an open-to-buy budget tells retailers how much they can spend on inventory at some future date, whether it be the holiday season or the month of May.

With open-to-buy planning, retailers can forecast and spend proportionately to sales, meet demands for popular products, and prepare for seasonal surges. By helping retailers keep track of on-hand inventory compared to what is actually needed, open-to-buy planning reduces excessive spending and minimizes waste.

Say you want to create an open-to-buy plan for the holiday season (November to January) at your clothing store. You anticipate heading into the season with $7,000 of inventory. Last year you made $78,000 in sales during this time. This year, however, your customer base has grown and your business is making slightly more, so you anticipate making closer to $90,000. Additionally, you plan to mark down summer items valued at $3,000 by 20% ($600), and you anticipate having about $5,000 of inventory in stock once the holiday period is complete. With all this, your OTB calculation would be:

While larger businesses and corporations make longer-term open-to-buy plans (quarterly, semi-annually, etc.), those running smaller operations with tighter budgets should use shorter-term plans.

For businesses that see large seasonal spikes, create open-to-buy plans for each week. This will ensure you stay on top of seasonal flows and also help your buyers understand which products need to be ordered at higher volumes more frequently during busy periods.

Understanding your inventory and making accurate projections is a key part of open-to-buy planning, and it all comes down to good inventory management. Make your life easier with a fully integrated POS system with inventory controls, so that you can get automated reports, insights, and real-time data on your inventory.

First, you need to pull last year's sales and inventory data on a month-over-month basis from your systems of record. Sales data is typically exported from a POS system (e.g. Shopify or Demandware) and inventory from an ERP system (e.g. NetSuite or Microsoft Dynamics). Some companies may also store inventory information in spreadsheets or an accounting software like QuickBooks.

OTB calculation is one of the most important tasks to master when starting a retail business. Failing to calculate the open to buy budget can be detrimental for the business, due to stock problems that will soon arise from improper planning. In fact, a lot of retail & ecommerce startups fail, mainly due to cash flow problems created by poor inventory management.

If you were budgeting for a period that starts tomorrow, this would have been your current stock value (at cost). However; since we always budget for a period well in advance (usually 6 months ahead) this would mean calculating your opening stock well in advance.

Once you have defined your sales, margins and opening stocks in the previous steps, you will plug those numbers in one of the Open to Buy tools we discuss below, to calculate your total buying budget.

You can use this excel sheet to create a 6 month merchandise plan, 6 months in advance. The total months on the sheet are 18 months, and we show you exactly how to fill it in the PDF attached to it. In the members area, we also provide separate demo sales, margin & inventory data for you to practice using it.

You learn, for example, how to work with the Accessibility Checker to tackle accessibility issues while you're creating your spreadsheet. You'll also learn how to add alt texts to images so that people using screen readers are able to listen to what the image is all about. You can also read about how to create accessible tables and how to use templates, fonts, and colors to maximize the inclusiveness of your spreadsheets before sharing them with others.

To manually launch the Accessibility Checker, select Review > Check Accessibility. The Accessibility pane and the Accessibility ribbon open, and you can now review and fix accessibility issues. The Accessibility ribbon contains all the tools you need to create accessible spreadsheets in one place. For more info, go to Accessibility Ribbon and Improve accessibility with the Accessibility Checker.

Use one of the accessible Excel templates to make sure that your spreadsheet design, colors, contrast, and fonts are accessible for all audiences. The templates are also designed so that screen readers can more easily read the spreadsheet content.

An accessible font doesn't exclude or slow down the reading speed of anyone reading a spreadsheet, including people with low vision or reading disability or people who are blind. The right font improves the legibility and readability of the spreadsheet.

The text in your spreadsheet should be readable in a high contrast mode. For example, use bright colors or high-contrast color schemes on opposite ends of the color spectrum. White and black schemes make it easier for people who are colorblind to distinguish text and shapes.

Use the Accessibility Checker to analyze the spreadsheet and find insufficient color contrast. The tool now checks the documents for text color against page color, table cell backgrounds, highlight, textbox fill color, paragraph shading, shape and SmartArt fills, headers and footers, and links.

Name cells and ranges so that screen reader users can quickly identify the purpose of cells and ranges in Excel worksheets. Users can use the Go To command (Ctrl+G) to open up a dialog box which lists all the defined names. By selecting a name, a user can quickly jump to the named location.

When your spreadsheet is ready and you've run the Accessibility Checker to make sure it is inclusive, you can try navigating the spreadsheet using a screen reader, for example, Narrator. Narrator comes with Windows, so there's no need to install anything. This is one additional way to spot issues in the navigation, for example.

Name cells and ranges so that screen reader users can quickly identify the purpose of cells and ranges in Excel worksheets. Users can use the Go To command (Control+G) to open up a dialog box which lists all the defined names. By selecting a name, a user can quickly jump to the named location.

When your spreadsheet is ready and you've run the Accessibility Checker to make sure it is inclusive, you can try navigating the spreadsheet using VoiceOver, the built-in macOS screen reader. This is one additional way to spot issues in the navigation, for example.

To launch the Accessibility Checker, select Review > Check Accessibility. The Accessibility pane opens, and you can now review and fix accessibility issues. For more info, go to Improve accessibility with the Accessibility Checker. 781b155fdc