How to Analyse Variations in Forecast vs. Actuals to Improve Forecasts.
The job of demand planners is not over by generating forecast numbers. In order to assist the planning process team and correct the forecast it is best to have exception reports in place.
It is important to find the anomalies in the data and create an exception reports, else it is a bunch of numbers which all of us struggle to make sense. An ideal planning process starts with placing the actual data of last month next to the forecast of last month (with lag). This acts as a mirror to the entire team who were involved in this process.
Now that we have forecast vs actual in place, what would a planner do with it?
Let us discuss a few options.
ABC Analysis: The process starts with categorizing the data based on ABC items. It is important to do that as the planning team should focus on important A items and make use of the time more effectively. A company dealing with thousands of products will find it challenging to focus on all items within the short span of planning window. The main aim here is to identify high deviation and focus only on the important items.
For a greater understanding of the theoretical aspects of ABC analysis, read on from here.
Bucket Analysis: Bucketing the error into various types helps us deep dive into the causes. Some of the suggestions for these buckets could be as follows:
1. Forecasted but not sold
2. Not forecasted but sold
3. Under forecasted
4. Over forecasted
5. Not forecasted and not sold
This categorizations should happen at the level at which planning happens and forecast accuracy is measured. Along with this you may also add monthly trend and Year on Year data for better perspective of the numbers.
Now that we have the categorization and placed the items in several buckets, it is easier to focus, identify problems and find solutions. You will know what questions to ask and to whom, based on the bucket in which the item falls. For eg, a high deviation A item falling in first category of forecasted but not sold, you would want to ask the sales team about why an A item was not sold.
In case the item falls in second category, not forecasted but sold, you might want to ask inventory manager or distributor as to why this item which was not forecasted was there in store. Also since this item was sold you would want to check if there is demand for that product during this time of the month which you were not expecting and hence not forecasted.
Similarly for other categories it is easier to pin point the problem and find the reason behind it.
Growth Analysis: You would want to use Month on Month growth and Year on Year growth to check the sanity of forecast. But a problem might occur if you use current month MOM and this years YOY as you would still not be able to judge the number based on these growth parameters alone.
Take an example, let’s say an item has MOM of 55% you can easily jump to the conclusion that this is very high growth, but this could be because the product is seasonal and has it’s peak in this month. So you would also check YOY and the growth is let’s say 65%, here you might assume that since YOY is so high the forecast is not correct. But truly you still haven’t seen the complete picture and to accurately conclude you should check last years YOY and MOM for the same period. If the product had similar growth trend and values last year as well, then you can ignore these high values forecast and focus on those items which have high difference in these values over years. If there is very high difference in these over years then either the trend is changing or you had missed something important. These are truly the exceptions that need to be worked and correct the forecast further. When these are consolidated in the form of exception reports, keeping this in mind will contribute significantly to the planning process.
In the above picture you can see that the first item has low YOY and MOM % of -50% and -49% respectively compared to the last year values. Thus we have to highlight only those exception where the trend is drastically different.
These are a few exception reports that will help you in making your initial forecast more robust and improve the effectiveness of any S&OP process.
The job of demand planners is not over by generating forecast numbers. In order to assist the planning process team and correct the forecast it is best to have exception reports in place.
It is important to find the anomalies in the data and create an exception reports, else it is a bunch of numbers which all of us struggle to make sense. An ideal planning process starts with placing the actual data of last month next to the forecast of last month (with lag). This acts as a mirror to the entire team who were involved in this process.
Now that we have forecast vs actual in place, what would a planner do with it?
Let us discuss a few options.
ABC Analysis: The process starts with categorizing the data based on ABC items. It is important to do that as the planning team should focus on important A items and make use of the time more effectively. A company dealing with thousands of products will find it challenging to focus on all items within the short span of planning window. The main aim here is to identify high deviation and focus only on the important items.
For a greater understanding of the theoretical aspects of ABC analysis, read on from here.
Bucket Analysis: Bucketing the error into various types helps us deep dive into the causes. Some of the suggestions for these buckets could be as follows:
1. Forecasted but not sold
2. Not forecasted but sold
3. Under forecasted
4. Over forecasted
5. Not forecasted and not sold
This categorizations should happen at the level at which planning happens and forecast accuracy is measured. Along with this you may also add monthly trend and Year on Year data for better perspective of the numbers.
Now that we have the categorization and placed the items in several buckets, it is easier to focus, identify problems and find solutions. You will know what questions to ask and to whom, based on the bucket in which the item falls. For eg, a high deviation A item falling in first category of forecasted but not sold, you would want to ask the sales team about why an A item was not sold.
In case the item falls in second category, not forecasted but sold, you might want to ask inventory manager or distributor as to why this item which was not forecasted was there in store. Also since this item was sold you would want to check if there is demand for that product during this time of the month which you were not expecting and hence not forecasted.
Similarly for other categories it is easier to pin point the problem and find the reason behind it.
Growth Analysis: You would want to use Month on Month growth and Year on Year growth to check the sanity of forecast. But a problem might occur if you use current month MOM and this years YOY as you would still not be able to judge the number based on these growth parameters alone.
Take an example, let’s say an item has MOM of 55% you can easily jump to the conclusion that this is very high growth, but this could be because the product is seasonal and has it’s peak in this month. So you would also check YOY and the growth is let’s say 65%, here you might assume that since YOY is so high the forecast is not correct. But truly you still haven’t seen the complete picture and to accurately conclude you should check last years YOY and MOM for the same period. If the product had similar growth trend and values last year as well, then you can ignore these high values forecast and focus on those items which have high difference in these values over years. If there is very high difference in these over years then either the trend is changing or you had missed something important. These are truly the exceptions that need to be worked and correct the forecast further. When these are consolidated in the form of exception reports, keeping this in mind will contribute significantly to the planning process.
In the above picture you can see that the first item has low YOY and MOM % of -50% and -49% respectively compared to the last year values. Thus we have to highlight only those exception where the trend is drastically different.
These are a few exception reports that will help you in making your initial forecast more robust and improve the effectiveness of any S&OP process.
