How to Use Excel for Item Planning and Replenishment: Free Template + Guide

Do you have a system to effectively manage your core items so they never go out of stock? 

Most retailers have a set of products (such as white t-shirts, underwear or that brand's signature products) that are never meant to go out of stock. Retailers use various names interchangeably to describe such products. Basics, core, continuity, never-out-of-stock (aka NOS), and ever-green products are just a few of the names used in the industry.

Here at Toolio we call them core products, given their importance to the business and the high impact on a retailer's top-line and gross-margin. It is typically the seasonal products that attract customers into stores, but customers often end up leaving the stores with the core products. Given the importance of core products, it is critical to manage them closely and make sure that they never go out of stock.

Because core products are on the floor for much longer than seasonal products, it is relatively easier to forecast demand for these products and replenish  them on a weekly or monthly basis to make sure they never run out. At Toolio, Item Planning or inventory planning is the process of forecasting demand for core products and automating replenishment activities.

How can you manage core items effectively?

While managing inventory for core items, there are two objectives to keep in mind. One is to keep the out-of-stock-rate as close to 0 as possible. The other one is to minimize the cash tied to inventory, i.e. to keep the weeks-of-supply as low as possible. Unfortunately, these are conflicting initiatives. The lower weeks-of-supply a retailer keeps on a particular product (SKU), the more likely it is to run out of stock on that SKU.

While juggling between these two initiatives, the key to determine how much inventory to keep will be the lead-time. Generally, the lower the lead time is, the less need to keep high weeks-of-supply will be. Other factors that influence this decision are anticipated fluctuations in demand, disruptions in supply and how frequently the retailer wants to replenish, i.e. the order cycle.

Once a retailer has determined the minimum weeks-of-supply target, which is also known as safety-stock target, the lead time, minimum order quantity and order cycle, the core item management process is a matter of calculating sales velocities on a SKU level, calculating the weeks-of-supply available for each SKU and calculating whether or not a new purchase order should be created in that order cycle.

Toolio Item Planning Template

To help retailers manage their core item replenishment process, we have created a free Excel template. The template allows retailers to put in:

  • Sales information for the trailing N weeks
  • Current inventory on-hand
  • Inventory on order
  • Product information (such as safety-stock targets, MOQs and lead times)
  • Configuration (look-back period to calculate sales velocity and order-cycle in weeks)

Given all these inputs, the Toolio inventory planning Excel Template automatically calculates when you should be creating a particular SKU and how many units you should be buying.

You can access Toolio’s Free Item Planning Template here. We are hope it will help you to better plan and manage your inventory!

Out-grown spreadsheets to manage your inventory?

Toolio's Item Planning Template will help many small retailers to figure out when to replenish core items. As a retailer grows, so will the complexity around managing core items. There will be many more SKUs to keep track of, different systems to pull sales and inventory information from and different suppliers each providing different lead times and MOQs. This will make item planning a much more complex activity, and spreadhseets will no longer cut it.

That’s why we’ve designed Toolio to automate this planning process. Toolio's Item Planning module automatically pulls in sales and inventory data, allows you to specify supplier or item level replenishment information such as MOQ, lead-time and safety-stock, and automatically creates purchase orders, to keep your inventory level at the optimal level. If you’re ready to see how we can make your merchandising better, request a demo of Toolio.

Eytan Daniyalzade
CEO & Co Founder