This is the second article in a series on measuring demand variation.
Estimating Demand Variation
There are only 2 reasons of you should ever choose to estimate demand instead of quantifying it:
- You can’t quantify, and your last resort is to estimate.
- You already know that variation is low.
Outside of these exceptions, your time will be better spent calculating demand variation.
When You Can’t Quantify Demand Variation
You may find yourself incapable of calculating standard deviations because you simply lack sufficient accurate data. In this case, estimating is the best you can do. However, realize that you must fix that issue. If you truly lack historical demand data, you should consider that a deadly strategic and operational failure.
Do You Lack Any Demand Data?
Perfect data is ideal, but rare. Unfortunately, perfectionists are abundant. A common mistake managers make is assuming that “insufficient data” means that anything less than 100% of their items have totally up-to-date, complete, accurate, and validated data. Recognize, that is an extreme perspective. In most cases, you’ll need to take what demand data you have (which is probably more than you think) and calculate accordingly.
For example, if you manage 5,000 items, but lack accurate and up-to-date data on 500 of the items, then quantify demand variation for the 4,500 items that you have data on and apply what you learn to your demand estimate for the remaining 500. Of course, in such a situation, you would definitely want to also begin gathering better data on those 500 items, moving forward. The bottom line here is to use what demand data you have and start collecting the data you lack.
Is Your Demand Data Bad?
You may find yourself in a scenario where you know that the data you have may not be reliable. Don’t fret, because you can still use it. You just have to be mindful of the potential inaccuracies and how that might affect your inventory and delivery. Remember to err on the side of caution. Overestimating the need for safety stock can be wasteful and is not a desirable outcome. But the risk of stocking out due to understating the need for safety stock is much worse.
How Bad is Your Demand Data?
If your demand data is truly inaccurate, it possesses zero analytical value. Bad data would be something that is unverifiable and highly suspect, and you would not be able to use it to make predictions and decisions about the operations. Use the following guidelines to help gauge whether your data can be used to quantify demand variation.
- Demand Data Accuracy => 80%: Use data for demand analysis.
- Demand Data Accuracy 50% to 79%: Perform analysis on the entire population of items and verify by analyzing either a smaller sample or shorter time period.
- Demand Data Accuracy < 50%: Calculations are not possible. Develop an educated best guess of demand variation for various sub-groups of items.
Calculating Demand Variation
Demand variation analysis helps us determine just how predictable (or unpredictable) demand is in order to better define target safety stock levels. In that effort, 2 key metrics that give us a solid grasp of demand are average demand and demand standard deviation.
- Average Demand: The average demand is the mean of all demand levels for an item during over a period of time. For example, this might be average weekly demand over a quarter or average daily demand over a month. For kanban solutions, you must always use daily demand.
Regardless of kanban, always remember that average demand, even when measuring average weekly demand, should only account for scheduled workdays not calendar days. Otherwise, the average will be inaccurate and unreliable, artificially lowering the average. To properly account for workdays only, simply exclude them in your count. Don’t enter “0” for non-workdays such as holidays and weekends.Keep in mind that in addition to average daily demand, there are many other forms of daily demand, relevant to inventory and supply analysis, including actual daily demand, annual daily demand maximum daily demand, and minimum daily demand.Use the AVERAGE function when calculating the standard deviation in Excel.
- Demand Standard Deviation: Standard deviation is a measure of the absolute distance that the typical data point within a defined data set tends to fall from the average. When the data points tend to be farther away from the mean, the standard deviation is higher as a result, indicating more volatility. Thus, demand standard deviation is a measure of demand volatility for an item or a population of parts.
Use the STDEV.S function to calculate the standard deviation of a sample in Excel.
In part 3 of of this series on measuring demand variation, we explore analyzing demand variation for kanban solutions.