One of the more fascinating and challenging subjects in FP&A, in the retail space, is Open-to-Buy (OTB) planning. This concept is uniquely pertinent to the retail sector and calculating it can be quite intricate.
OTB is a forward-looking metric based on future projections. In retail, it involves assessing current stock and estimating the exact amount of stock needed to achieve a predetermined target. It’s a blend of sales forecasting and stock planning, requiring accurate data and meticulous calculations.
Firstly, consider the concept of Forward Weeks (FW) and Target Weeks (TW). FW indicates how long your current closing stock would last if no new stock was purchased. On the other hand, TW denotes the desired duration of time your stock should last. For instance, you might target 12 weeks of stock on hand.
The calculation process starts by calculating the Forward Weeks for each month. If stock lasts through February, which has 4 weeks, and it extends to March with 5 weeks, the cumulative effect—9 weeks—is key. The same approach applies for Target Weeks, except we’re aiming to reach the target stock duration.
The critical elements here are accurate sales forecasts and lead-time planning for stock replenishment. Retailers usually break down months into weeks, with a predetermined number of weeks per quarter. This uniformity helps in calculating stock requirements effectively.
At this point, the essential metric emerges: Open To Buy (OTB). This showcases the stock difference between your target and your actual stock. If stock falls short of the target, it’s time to buy more; if stock exceeds the target, it might be time to think of promotions to boost turnover. The capabilities of our technology, PowerExcel, shine here. We can visualize these calculations without manually performing them in Excel. It’s important to note that PowerExcel’s free version can handle up to 500 categories or stores.
With calculations shown for one store and one category we can also easily apply these calculations to different stores and categories. This is a powerful way to ensure consistent stock management across a retail business. Moreover, we can generate reports that highlight exceptions. For instance, using color codes like green for excess stock, orange for the right stock levels, and red for stock shortages, we can swiftly spot and address issues.
To summarize, retail OTB planning involves intricate calculations based on sales forecasts, stock targets, and lead times. PowerExcel can significantly simplify this process by centralizing calculations and allowing for easy visualization of results.
Whether you’re running a small store or a larger retail chain, mastering OTB can enhance your stock management strategies and overall business efficiency. If you’re intrigued, please sign up for our waitlist for the freemium version, PowerExcel Personal or consider our more comprehensive paid subscriptions with the support of our experienced team.
