Practical First Steps #1 – The Accuracy vs. Volatility Scatterplot

Last week I was a guest of Gaurav Verma on the SAS Applying Business Analytics Web Series, and presented “What Management Must Know About Forecasting.” One of the most important things you can bring to management’s attention is the benefit of making your demand forecastable.

In forecasting we tend to treat demand patterns as given, as if there were nothing we could do about them. This “passive” attitude makes our performance contingent on the forecastability of the demand patterns. If the patterns are easy to forecast then we should do fine, but if they are not, we risk failing to meet our forecast accuracy objectives.

In my July 1 posting on The BFD, I used this scatterplot to illustrate the usual relationship between demand volatility (along the horizontal axis), and forecast accuracy (the vertical axis). I argued that while imperfect, the volatility of a demand pattern (as measured by its coefficient of variation) is a good indicator of how accurately we can expect to forecast that demand. As a practical first step to improving your forecasting performance, it is worthwhile to construct this scatterplot with your own data…and here’s how:

1) Determine at what level you want to do the analysis. This is typically the level at which you focus your forecasting and planning efforts. For example, a manufacturer might forecast the number of units demanded for each Item, or at each Item / Distribution Center combination. A retailer might forecast unit sales by Item / Store. An insurance company might forecast claims by Region or Postal Code. (In the above scatterplot, this manufacturer sold roughly 500 Items through 10 DCs, and forecasted at the Item / DC level. There are, therefore, roughly 5000 (= 500 x 10) points in the plot.) Suppose for this example that we are doing the analysis by Item.

2) You will need to gather the last year of data for each Item, in whatever time bucket you use (typically week or month). (I prefer using weekly data if it is available, but many organizations plan in monthly buckets, so we’ll use monthly for this example.) For each item you will need to capture the monthly Sales, and whatever the Forecast was for that month. So you will create a dataset with four variables . The four columns on the left show what this dataset might look like with the 12 monthly observations for Item XXX.

3) You next compute the volatility of sales over the past year. Volatility is measured by the coefficient of variation (CV) of the monthly sales, which is defined as the standard deviation divided by the mean. For item XXX over the past year, it had mean monthly sales of 102.5 units, and a standard deviation of 26.8. Therefore, CV = 26.8 / 102.5 = 26.2%.

4) You finally have to compute Forecast Accuracy for each Item. This requires two new computations shown in the two rightmost columns. The Absolute Error in forecasted sales each month (i.e. |Forecast – Sales|) and the Maximum of Forecast or Sales in each month. Forecast Accuracy is computed as 1 – (Sum of Absolute Errors) / (Sum of Maximums) = 1 – (150/1320) = 88.6%.

5) Create the scatterplot by plotting the Forecast Accuracy and Volatility coordinates for each Item. The Forecast Accuracy metric will always be from 0 to 100%, so the vertical axis can be scaled 0 – 100. The Volatility, however, can become indefinitely large. For clarity and usefulness of the presentation, you may want to ignore really extreme values of CV (unless there are a lot of them), and only run the horizontal axis from 0 to 150% or 200%, and footnote how many Items are not being shown. The point is to illustrate the overall volatility of your sales patterns, and to show the relationship with your ability to forecast.

As long as you have the past year of sales data, along with the last year of forecasts, it is very easy to create this scatterplot. It is a simple way to visualize the extent of volatility issues with your sales, and the likely impact on your forecasting performance.