Inventory stocking is one of those rabbit-holes you can go further and further into if you want to.  Improved management of your inventory almost always has a financial benefit such as decreased capital tied up in inventory or increased productivity or sales due to item availability.

We’ll start with the most basic level of inventory stocking sheet that I learned when I was an assistant department manager of a grocery store.  It was good enough for a multi-million dollar grocery chain, so this method is probably good enough for a comparable business.  There are a few specific cases such as long lead times, high inventory carrying costs, high cost of out-of-stock or highly seasonal businesses which might drive you to a more complex system.

Let’s Do It

I created a simple template which lets you set a Stocking Target for each product.  When I was using this we always adjusted the targets based on trial and error, but for you, I have created a calculator to help you set initial targets.  The template will also create an order list for each of your vendors in order to make ordering a cinch.


Basic Inventory Stocking Targets

Choose the Items that Matter

First of all, you have to choose what items are critical enough to actively manage inventory at this level.  You should list those items on this sheet, the part numbers and the vendors who sell them to you.  I’d recommend you do these in the order that you plan to do your inventory in.

There is a vendor master list on the second sheet that you should put each vendor name onto.  This is so that you can create a vendor-specific order list later on and so that you don’t have multiple names for the same vendor and miss an order!  ex: Franks Beans vs Frank’s Beans.

Setup Stock Targets

This is probably the most difficult part of this method.  You need to set up your stocking targets based on how many units you anticipate selling in an order period.  When I had to start filling this out for stocking oil filters in my shop I made a LOT of mistakes.  Luckily for me, I had great suppliers who could get me a replacement part within an hour. I was also starting from zero where I didn’t even carry inventory, so anything was better than the nothing I had! Keep in mind as you’re doing this that it costs you money to keep things in inventory.  You have to shell out that cash to stock up, so don’t be TOO conservative on this stuff!

Just because I know that many people aren’t in that ideal of a supplier situation, I created a tool that I hope will help.  I’ll cover that in the “Stock Target Calculator” section.

Take Inventory

When you have your stock targets for each item: go through and take inventory.  If you have previously taken inventory on this sheet you should delete all of the values from the inventory row.  I highlighted this row in Yellow to make it clear which row you’ll be editing regularly.

You should make sure you do this on a consistent timeline.  The method we’re using only works when you use it consistently.  I usually picked two mornings of the week that I knew were generally slow which would allow me to actually use this method.  When my business got moving I had an employee who took inventory for me since this method was running like a well-oiled machine.  Ha!

Place Your Orders

One interesting part of this is if you have to order parts in cases.  Which way do you round when you need to order 4 bottles of wine but you can only order in cases of 12?  You can use the “Case Order Rounding Strategy:”drop-down to choose which method you want to use.  This should be based on the impact of being wrong.  How bad is it if you order too much?  How bad is it if you don’t order enough?

When you’ve taken all of your inventory, go to the “VendorOrderSheet” shown above and use the drop-down to select a vendor you’d like to order from.  If you see an item that is blank in the order report, then you missed that item on the inventory sheet.  The workbook is nice enough to show you which vendors you need to place an order within the “Cases Needed” column.

Adjust and Tweak With Growth

Remember how I originally had to make some adjustments to this sheet to get it to work in the first place?  Well, as my business grew and my customer base changed, my inventory stocking strategy had to change with it.  I usually made adjustments when I ran out of stock.  If that happened then I’d increase the Stock Target to ensure that I increased inventory to avoid that happening again.  If you are in a situation where out of stock situations have more severe consequences, you might set a higher trigger point for adjusting your stocking targets.  You can occasionally decrease stocking targets if you notice that you’re not selling an item like you used to.

Stock Target Calculator

I created a Stock Target Calculator that will let you paste in 90 days of sales of an item to calculate what your levels should be.  Keep in mind that historical data may not always indicate what future sales will be.  Enter item sales in the “StockTargetCalculator” sheet along with information about your ordering situation.

The sheet tries to predict what your average sales per order period will be and add in some statistical stack-up of variance over the period.  You can see on the right that there is a matrix of options to choose from.  Your choice will depend on how conservative you need to be in your inventory and the cost impact of maintaining higher levels of inventory.  You can set a “Safety Factor” and a “Minimum Inventory” level and see how that effects the additional rows in the matrix as you desire.

Most likely you can choose the 95% coverage option with a small safety factor and be well covered.  Statistically, that means you should have the item in stock more than 95% of the time someone tries to purchase that item from you (if your business is not growing).  If your business is growing, you should use the Safety Factor to help accommodate your growth over the sales period.  You can use Forecasting Based on Historical Data to help you determine your growth rate if you need to get that detailed.

You probably don’t want to do this for every single item on your inventory sheet.  I’d recommend you create a report of all your items sorted from most sales to least sales.  You can do this in a Pareto fashion if it works for you.  Then I’d do this analysis on the top few items and then just do it for some items spaced out down that list.  Use your best judgment, you can probably scale your Stock Targets based on the total units sold and be fairly close.  Just be prepared to make adjustments as you go.