Using past base data

On this page:

1. How to create a forecast from the past base data
2. How to compare it with the sales forecast
3. Investigating the differences

 

How to create a forecast from the past base data

There are many ways to use the past data to create the RM forecast. The following methods give almost all the benefits we need at a low cost of implementation. They are easy to understand and fix. Other methods will be discussed in tier 3.

1. Simple Average

Method: Do a simple average on the past data demand over X amount of periods. Then the average becomes the forecast demand. The X (number of periods) is very important. It varies from one organization to another. It should be long enough to reduce “noise” such as one-time sales deals or a week’s holiday, but it needs to be “close to the market”. That means that if the demand is rapidly changing, don’t use a big X of past data since it might no longer be relevant. The X should be close enough to the market, but long enough to reduce the noise. 4-6 periods work in most cases.

Pros:

1. Very easy to do
2. Reduces the noise of one-time events. For example, if we take 4 time periods of which 3 have a demand of 100 and the last one of 160, the change will not be 60%, but only 15%. So the planner will know that there is a change in the market, but will not order according to the one-time event.

Cons:

1. The second advantage is also the disadvantage. If the market has really changed and this is not a one-time change, the planner will “miss the market”. It will take X periods of time to fully follow the changing market. The simple average is good in reducing the noise, but it also reduces the capability to follow the market.

2. Weighted average

Method: Look for the average consumption per period by giving each period a different weight. The result will give more “weight” to the latest periods. It is done by multiplying each demand value by its corresponding weight.

In a simple average if we look at 4 periods of time, each period has the same weight of 25% on average. The weighted average means that we give each period a different weight in percentage. The last period might get 35%, the one before it 30%, and so on, as seen in the example.

Pros:

1. A real change in consumption is noticeable more quickly than simple average since the last periods get more weight. In the example above the simple average would suggest an average of 107.5 while the weighted average shows 110 which is more likely to be the real value.
2. It is still easy to implement.

Cons:

1. Noise in the last period amplifies since it gets more weight.

3. Seasonal factors

Method: Some products have seasonal changes, such as ice cream or holiday gifts. You can use past data to understand at what periods it happens and correct your projection. How to implement:

1. Define which RM has seasonal changes.
2. Try to understand what causes the change.
3. See if it is a fix calendar event or it happens around some event.
4. Define the base behavior.
5. Divide each period consumption by the base consumption.
6. Decide what is the barrier beyond which you will apply the factor. Look at the example below. If the real average is 100, then the second and third periods are just statistical fluctuations and we should not take them as seasonal behavior. But it looks like the fifth period is seasonal since we see a major increase (25% from base) and then another decrease. Make sure it happens in the last few years as well. So in the example the 25% will be the barrier. We then look for periods of time that are above 25% and assume that future demand for this periods will be multiplied by the factor we found.
   
7. Apply the factor when calculating next periods forecast.
8. When there is a decline in the factor (as in the example: going from 130 to 125 and then to 120), then a correction factor should also apply to the  averages, either simple or weighted.

Let’s analyze the example above. We can see that in periods 5,6, and 7 the seasonal factor is above the barrier (25%).  That means that if the current average consumption per month is 1,000 units per period, then the average for period 4 will be 1,000, for period 5 1,250 units, for period 6 1,300, and so on, as in the following table:

Seasonal factor example

Summery of seasonal factor: When we predict the future demand based on past data, we decide which is the best method to use: average, weighted average, or another method. Then we multiply the result by the seasonal factor. The seasonal factor is calculated as statistical data from past periods. We find the base, define the barrier, and find the seasonal factor for each period.

Creating the past data forecast

We usually want to compare the past data forecast and the sales forecast (after it’s been converted to RM). So if the sales forecast is updated every month for the next 4 months, we will do the same with the past data forecast.

For the next example we will base the past data forecast on the last 4 months’ demand (again, this is not the actual consumption, it is the real sales orders converted to RM using the actual BOM).

And here is the same example with weighted average (watch how the average value changes) :

 

Again the same example corrected by the seasonal factor:

 

Comparing past data forecast with sales forecast

Quick rules for easy comparison:

1. Compare only 2 values at a time.
2. Make sure that you compare apples to apples, i.e. the same RM at the same time period.
3. Most of the time the 2 numbers will not be the same. You need to define what is “The same”. For example, if the sales forecast value is 101 parts , and the past data forecast value is 99 , are they “The same”? A computer will not define it as the same.  So we need to define what is “the same”.
4. We need to automate the process, so will only deal with the exceptions.

Lets start…

Firstly let’s define what is “the same”. We need to set a tolerance around the sales forecast that is acceptable for planning purposes.   We can have a 2-sided tolerance such as:This means that if the sales forecast is 200 and the past base forecast value is ranging from 180 to 220, we will consider them “the same”.

Or we can use a one-sided tolerance usually on the up direction like:The one-sided tolerance is used when it might be acceptable to have a little more inventory, but not acceptable when having less.

How to define the tolerance?

What to consider when picking the tolerance for each RM ID (you can pick one for all or give different tolerance to different RM groups):

1. As a rule of thumb more than 30% is not OK.
2. You have to consider the cost of having too much or to little of the relevant RM ID. For example, If you don’t have minimum inventory planned or you have huge lead time, consider a one-sided tolerance so you can support at least the sales forecast value. If this is not an expensive product and you have enough minimum inventory planned, then you can increase the tolerance. Usually if you divide the RM ID to ABC groups (where A is the more expensive and C is the less), you should  allow more tolerance for group C and less for group A.
3. The tolerance should be changed according to the planner experience with both methods of forecast. If the 2 methods usually are the same, decrease the tolerance. If you have a lot of noise, increase the tolerance.
4. See how many “investigations” you can handle each cycle. If there are too many, you must increase the tolerance.

Investigate

What are we looking for?

1. If this is a real change in the market, we want to identify the final product or the final product group that generated the change. So we can contact the salesperson who made the forecast to confirm it. Salespeople understand final products, not raw materials, so we need to focus them on the right product(s).
2. The change can come from one product replacing another product that has different BOM. That is a natural change and the planner should be aware of it.
3. REMEMBER, if there was a change in BOM, it will have no effect since we already neutralized it.

How to investigate?

1. Go deeper: After identifying the suspicious RM ID we need to investigate. We must find the product/product group that generated the change (between the sales forecast and the past data forecast). We need to sort the product generated records (watch the next table) by quantity difference, not by percentage. Let’s demonstrate it in an example. In the following table we can see the RM record generated from the relevant FG. That is the output of the MRP process after exploding the FG to RM using BOM.  In column 3 we can see the sales forecast RM quantity and in column 4 we can see the past data quantity. The difference is in column 3. In column 4 we can see the difference of every record by percentage and in the last column (pink) we can see what effect the record has on the total change. The pink is calculated as the difference between forecasts (column 3) divided by one of the forecast totals. In this case I divided it by column 1. As you can see, FG W (last line) has a difference of +67%, but only contributes 4% to the change. On the other hand, FG X (first line) has a change of only -20%, but a much larger contribution of 13%.

So after sorting the table for each RM that we investigate by the contribution column (pink) we need to look at both sides of the table – the up size and the down size. If we find something unusual, we can go back to the salesperson and see why there is a big change in the FG. In the example above we will ask why FG X has a 20% up change. But we will need to convert it back to FG to talk to the salesperson. Remember, salespeople understand FG, not RM. So we will contact the salesperson in charge of the FG X and ask why he put a 20% increase in his FG forecast. If he gives a credible answer, we can order the RM by the sales forecast figure. If he gives an incredible answer, we can order by the past data forecast.