Demand planners and purchasing managers must predict buying patterns, calculate lead times, and ensure they have enough cushion to carry them to the next shipment without giving up all their buying power to do so. Companies use a variety of calculations and formulas to determine how many items to keep on hand. Each of the available methods serves a purpose. In this article, we’re focused on weighted average forecasting.
What is weighted average forecasting?
If you want happy customers and cash in your account, you need to get familiar with weighted average forecasting.
Similar to a weighted grade in your college English class, the weighted average formula is placing more “weight” on certain months and then averaging the usage with those weights taken into consideration.
Weighted Average Forecasting is a method that determines how much inventory to keep on hand based on an item’s past performance and an assigned “weight” or emphasis. The formula works well for items that regularly sell, with sales in at least 8 of the prior 12 periods. For products with regular, recurring usage, demand will likely increase or decrease in the same months year after year, giving demand planners a good idea of what they’ll need on hand.
What information do I need to calculate the weighted average?
When using Weighted Average Forecasting, demand planners usually have about 24 months’ worth of data and can then assign “weight” to each month based on the item’s usage. There are several reasons why you would assign a higher or lower weight to a product:
-
- When marketing is running a promotion and may increase sales
- When a new competitor is coming in and may decrease sales
- Economic changes nationwide can cause usage rates to increase or decrease
We can’t talk about weighted average forecasting without also discussing exponential smoothing. When applying exponential smoothing, you’ll give higher weight to emphasize different points in history. This will help you determine which calculation gives you the best indicator of future performance.
Forecasting using weighted averages
Before we start, let’s find the straight 3-month average, which won’t take into account any weighted value. Let’s look at this example of an everyday product. Our average is (18+15+12) ÷ 3 = 15
| Period | Usage |
|---|---|
| Jan | 18 |
| Feb | 15 |
| Mar | 12 |
Now, let’s assign a weighted value to each month and find our weighted value.
For best practices, assign various weights and run the formula with those weights. Then compare that to the data you already have on hand. Compare the forecast numbers with your actuals and choose the forecast with the least amount of error.
Weighted value = weight x usage, where usage is how much was sold
Weighted average forecast = weighted value ÷ total weight
| Period | Weight | Usage | WV |
|---|---|---|---|
| Jan | 3.0 | 18 | 54 |
| Feb | 5.0 | 15 | 75 |
| Mar | 0.0 | 12 | 0 |
WV = 129, Weighted Average Forecast = 16.125
| Period | Weight | Usage | WV |
|---|---|---|---|
| Jan | 8.0 | 18 | 144 |
| Feb | 6.0 | 15 | 90 |
| Mar | 3.0 | 12 | 36 |
WV = 270, Weighted Average Forecast = 15.8
| Period | Weight | Usage | WV |
|---|---|---|---|
| Jan | 2.0 | 18 | 36 |
| Feb | 1.0 | 15 | 15 |
| Mar | 4.0 | 12 | 48 |
WV = 99, Weighted Average Forecast = 14.1
| Period | Weight | Usage | WV |
|---|---|---|---|
| Jan | 8.0 | 18 | 144 |
| Feb | 6.0 | 15 | 90 |
| Mar | 3.0 | 12 | 36 |
WV = 99, Weighted Average Forecast = 14.1
Now that you have a variety of forecasts, you need to calculate your percentage of error. A general rule of thumb is errors around 35% are great, and those around 25% and below are stellar. Don’t let a high percentage number like 60% get you down. It’s a normal part of doing business.
To find your rate of error, you need to make the following calculation:
Absolute value (ABS) of forecast – actual / lowest number of forecast and actual
In excel: = ABS (forecast-actual) ÷ MIN (forecast, actual)
If February is our test point, then our actual is 15.
The graphs above would be calculated as:
-
- Example 1: 16.125 – 15 / 15 = .0075 ( x100 = 7.5%)
- Example 2: 15.8 – 15 / 15 = .05 (x100 = 5.3%)
- Example 3: 14.1 – 15 / 15 = .06 (x100 = 6%)
Based on this, we’d use a forecast of 15.8 since it has the lowest error rate.
15.8 becomes the number that all reorder points are calculated from.
Weighted average for seasonal items
What happens when winter is coming, and you need to order snow shovels? How will you know how many is too many or ensure that any customers needing a snow shovel can get it from your store?
If you historically sell snow shovels in December, January, February, and March, then you would assign a greater weight to those months in your calculations. What happened in June, for instance, doesn’t matter.
Trust your numbers, but be ready to review and adjust as necessary.
