Before determining how to plan for sporadic items, let’s first define a sporadic item.
A sporadic item is, first and foremost, stockable. You want to keep it on the shelf, even if you don’t have a live customer order for the item. Sporadic items have irregular sales patterns, meaning they sell less than 8 months of the year.
Typically, 70 – 90% of a distributors’ items are sporadic. That’s a large majority of a distributors’ products, which means planning accuracy and customer service are going to be crucial. Because you may have several months without product usage, you need to approach sporadic items differently than items with consistent patterns.
So, how do you determine what a reasonable amount of stock is when demand for an item is sporadic and, frankly, all over the place?
Planning for sporadic items
The key to planning for sporadic items is to ensure you have enough on hand when the customers need it, but not so much that you’ve tied up cash that could be used for more consistent sellers. Remember, if a customer needs 10 of an item and you have 4, you’ve done nothing.
So, how do you make sure you have the right amount of product on hand at any given time? We have found consistent success with a method laid out by Jon Schreibfeder.
Establishing the normal usage quantity
First, we need to know how many we sell in the months we sell it. To calculate, we’re going back to math class.
We want the greater of:
Mean = average
Median = the middle value
Mode = most commonly occurring value
A sporadic item sells 5 months out of the year:
| Jan | Feb | Mar | Apr | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|
| 12 | 12 | 16 | 7 | 8 |
Mean = (12 + 12 + 16 + 7 + 8 ) / 5
55 / 5 = 11
The median is 12
The mode is 12
Typical usage quantity = 12
Since the larger of these 3 numbers is 12, all stock calculations will be based on some multiple of 12.
Establishing frequency and lead time
We know how many months of the year the item sells, but we also need to know how long it takes to get to us.
Once we have that information, we can multiply the typical usage quantity (in our case, 12) by the number in our matrix to get the “target stock quantity.”
| 1-2 | 3 | 4-5 | 6 | 7 | |
|---|---|---|---|---|---|
| 0-15 | 1.0 | 1.0 | 2.0 | 2.0 | 2.0 |
| 16-30 | 1.0 | 1.0 | 2.0 | 2.0 | 3.0 |
| 31-60 | 1.0 | 2.0 | 3.0 | 3.0 | 3.0 |
| 61-90 | 1.0 | 2.0 | 2.0 | 3.0 | 4.0 |
| 91+ | 2.0 | 3.0 | 2.0 | 3.0 | 4.0 |
For example, if our product has a 30-day lead time and 5 months with sales, our stock target is 24. 12 x 2 + 24
Based on a target stock of 24, if we look back at our sales chart, having 24 units will satisfy every order that occurred over a 60-day period that year.
The Matrix
The matrix above is an example and can be customized to your organization. The idea is to have multipliers that go up as frequency and lead time increase. You want to make sure you’re carrying enough inventory to sell while waiting on replenishment from the supplier.
If you run lean inventory, you will probably want to decrease the values, but if you are weighted toward higher customer service and making a larger inventory investment, you could move them higher.
Depending on your preferences and how general or granular you want to be, you may choose to use 1 lead/sell matrix company-wide or have multiple charts for various product lines. We’d advise, however, against having such granular data that it’s impossible to maintain.
Atlas Precision Consulting recommends conducting a settings review annually. Your business will evolve and it’s important to take a step back and get a high-level overview.
Calculate the Target
Let’s look at another example. Here we have 3 months of sales on a product with a lead time of 63 days.
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 12 | 7 | 9 |
Mean = (12 + 7 + 9) / 3 = 9.3 Mode = (nothing here, because no value occurs twice) Median = 9 Take the larger of the 3 = 9.3 Going back to our chart, our multiplier will be 2. 9.3 x 2 = 18.6Having 19 units would have filled every order over a 90-day period in the year above.
Put it into practice
Although having a system to do this all for you is ideal, you can accomplish the same goal with a spreadsheet. You’ll need:
- Item ID
- Sales in each month for the last 12 months
- Lead Time
- Use COUNT or COUNTIF to get the number of months
- Use the Mean, Mode, and Median functions for usage quantity
- Use If statements and VLOOKUPs to get your multiplier value
SQL will make life a little simpler.
- Build the matrix as a table
- Use SQL to calculate the usage quantity and number of months
- Median and Average are built in, getting Mode is doable
- Also use SQL to either read the lead time, or calculate it yourself
Applying the concept to your inventory system
Set Replenishment for Min/Max If you’re a conservative demand planner, make the stock target the max and the stock target -1 usage quantity as your minimum. Example: Usage qty 4, Stock target 8 = Min:4, Max:8 If you’re a little more aggressive, you can make the stock target your minimum and the stock target +1 usage quantity as the max. Example: Usage qty 4, Stock target 8 = Min: 8, Max: 12 Alternatively, you can set the stock target to the order point (OP), and the vendor minimum order quantity (or fraction of the bin capacity) as the order quantity (OQ). This is good in cases where you are space-constrained or your vendor has a minimum that is much higher than you would typically need to buy. Knowing the majority of your items are sporadic, we hope this gives you a good idea of how to plan out your order quantities to keep customers and the bottom line happy.
