SCM Awareness: The Forecasting Process 3
Continuing on our look into the Forecasting process now we will look at the data you will use.
Now that you know the method(s) you will use you have to start to Gather the data you need. We have covered some of this when discussing the types of forecasting methods (Qualitative or quantitative). You could argue that I should have covered this section before the long discussion on various methods. And initially I had it in that order. BUT really in order to gather the data you need to know what data you need to gather and that comes from first understanding the forecasting method or methods you are going to use.
In many ways the quantitative data on past sales etc are the easy part of the process. It is likely that this information is contained in your ERP system. But that said you must be able to extract it and be sure it is complete. I have seen a system where sales could be displayed on a report on screen but could not be extracted into excel (we ended up having to set up an SQL query that accessed the database itself. NOT something you should try without knowing how to do it). I also saw a system where sales were not all sales as some data was stripped out of it for some financial reporting reasons. So, we ended up having to use shipments. But usually if you sit down with your local IT team, they can get you the data you need in the format you need. Once you have that set up at the start it should be easy from then on.
The more time-consuming data gathering exercise will be the qualitative elements. When you are relying on personal opinions and informal data that means lots of conversations with different people. You need to learn who the key people are to get you the data you need, and you need to build up a relationship of trust with them. Really the forecast owner should spend a sizable amount of their week gathering that data and establishing relationships.
You also need to think about where and how you will store this data. You will want to keep historical data in a format that is easy to add to and easy to look back and review. A spreadsheet will probably suffice for historical sales etc. However, you will need to think about how you will store the other data such as personal opinion or market intelligence. It is important to store it so you can look back in the future and work out what was accurate and what was inaccurate. That information will be key to how you assess similar information in the future. There are many Customer Relationship Management (CRM) tools out there that are designed to do this. One of the best I have used in the past is Salesforce. However, these can be expensive.
Map the historical data and look for any trends, patterns etc.
Once you have gathered the data you need to display it in a way you can interpret it. If you have a forecasting system this will be an easy step. But if you are looking to create a new one then now is the time to set it up the way you want and the business needs. If you are setting it up in excel then you can put in filters, subtotals, pivot tables, graphs etc to help you analyse the data. One simple function that is often overlooked is conditional formatting. You can use that to highlight cells that fall outside certain criteria. If you have too much data to view in one-page look to set up report dashboards etc. Excel is a very powerful tool, and you can achieve a lot with it (but as a supply chain professional you probably already know that).
Once you have the data mapped in a way that you can start to interpret it then you can start to look for trends, patterns etc. There are always trends and patterns in the sales data. You just need to find them. Is it trending up or down? Do customers order certain parts at the same time. Do they order in set multiples? Do they always order on certain days or weeks? Do they never go below a certain number? The potential options are almost endless. But as you read through the data and grow more comfortable with it you will start to see the trends and patterns.
Determine the historical accuracy of past forecasts. Look for bias, accuracy etc.
As we discussed previously your forecast is wrong. That is fine in most cases. But you must know how inaccurate it is. Often this is displayed in units, percentage or financially. It really depends on the needs of the business.
In books on forecasting Units is often described as the least useful one on the basis that being out by 100,000 screws means something quite different to being inaccurate by 100,000 cars. Well I always find that academic opinion a bit strange because you will know what you are planning and what 100,000 means in terms of your business. Likewise many books say financial accuracy is the best measurement of forecast accuracy because you can tell the scale of the issue. For example, the 100,000 screws may only be worth $1000 but the 100,000 cars could be worth over a billion dollars. Well again it is all down to the nature of the business. Telling me the forecast was out by €20,000 this month is a nice headline number to know but tells me little in terms of fixing the forecast. I would still need to dig into the numbers to understand what the issue is.
Percentage error is possibly the most useful and is recommended in a lot of books. If your forecast is 99% accurate you are doing very well indeed. In fact, in most industries anything over 90% is considered particularly good. BUT again, it really depends on the needs of your business. In a tight margin business, a 10% inaccuracy of perishable products may not be acceptable but in another business, it might be very acceptable. There is no hard and fast rule as to what is best.
But my advice is that the forecast owner understand the causes of error and have agreed with management what an acceptable level of inaccuracy is. It is important that the acceptable level is agreed with senior management and they understand what it is and what are the limitations to getting above that level of accuracy. Then they will be comfortable with the risk. There are some guidelines and methods we can apply when measuring forecast accuracy.
Firstly, you must be aware of absolute value. This means you must convert any negative numbers in the error to be positive. Take for example take an item with a forecast of 5000 units a month. However, the actual sales vary between 4750 and 5550 units a month. In February the forecast was 250 units over the actual. In March it was 550 units less than the actual and in April it was 200 units more than the actual. This gives us a mix of positive and negative results. However, in this case we are interested in the error or Variance to the forecast. Therefore, the minus 550 becomes positive 550 in the absolute calculation. Sometimes you will see the calculation based on Actual minus Forecast and other times you will see it based on Forecast minus Actual. It really does not matter which you use once you convert it to the absolute value.
Your forecast error will be the absolute variance divided by the actual and expressed as a percentage. Using the same example of an item with a forecast of 5000 a month. The actual varies between 4750 and 5550 units. That gives us a forecast error of 5.3% in February, 9.9% in March and 4.2% in April.
Of course, you will probably want to describe and report this as how accurate you were rather than how wrong you were. Hence you will probably report it as Forecast Accuracy. Forecast accuracy is simply 100% minus the forecast error percentage. So in this case the forecast was 94.7% accurate in February, 90.1% accurate in March and 95.8% accurate in April. And doesn’t “The forecast was 90.1% accurate in March” sound a lot better than “The forecast was 9.9% wrong in March”. It is all politics and public relations in a way.
And now that you know the error you can start to root cause it and try to fix errors in the future forecasts. This can be a daunting task but start with the items or families or markets that are most inaccurate and start to investigate why this was. Often forecasts are inaccurate because you are missing some market information. Yes, there can be unexpected events, but few markets are that volatile. Most are predictable if you have the correct information. In my experience you will find that there are “low hanging fruit” issues which are problems you can solve relatively easily
Another metric to be aware of is Forecast Bias. This is the tendency to be over or under the forecast. Generally, you will find a forecast tends to be high or low. It is due to the natural human tendency to be pessimistic or optimistic. I find that salespeople always tend to be optimistic. I suppose it is a key trait of the role. Using the calculation for forecast bias will allow you to identify the tendency for your forecast to be high or low and you can adjust future forecasts accordingly. T
Another common measurement of Forecast error is the Mean Average Deviation or MAD. This is the sum of the average the absolute forecast errors. You must remember that this metric is an average of the Absolute error or Deviation. It only indicates that on average the forecast is incorrect by plus or minus X units. You don’t want to interpret it to increase the forecast by X each month as this may result in too high future forecasts. These are probably the most common error calculations you will find. As forecasting is at its heart statistics there are a number of more complex statistical methods for calculating the forecast and its error. Mean Squared Error (MSE), Standard Deviation, Mean Absolute Percentage Error (MAPE). If you have a system that calculates these for you then they are useful indicators of the accuracy of your forecast. But I’m going to assume that if you are using excel to develop your forecast then you are mainly interested in the “low hanging fruit” that gives you methods for estimating your forecast error and correcting it.