See all Microsoft Excel tips
A lot of forecasting professionals measure forecast error as (Forecast Actual)/Forecast, as shown in Fig. 456.
When I had to measure forecast error, I did not agree with this method. You have to understand that there are two kinds of problems in forecasting. If you forecast 400 units and the order does not show up, then the manufacturing plant has 400 sets of material on hand and nowhere to send them to. Inventory goes up. This is bad. On the other side, if you forecast no units and an order for 400 shows up, the plant has
to scramble and start buying material on the gray market. This means the product cost could double and your profits go away. This is also bad.
My formula for forecast accuracy treats both of these situations as equally bad. I take the absolute value of (Forecast–Actual) and divide by the larger of the forecasts or actuals.
My forecast accuracy calculation follows these steps.
1) First, calculate the absolute error on a product-by-product basis. Whether the forecast was high or low, the error is always a positive number. The ABS function returns the Absolute Value of a number, as shown in Fig. 457.
2) Then, calculate the divisor. This is what I call the “Size of the opportunity to screw up”. If you miss a 1000 unit sale, it is much worse than missing a 2 unit sale. As shown in Fig. 458, for column G, use the MAX function to find whichever is larger, forecast or actuals.
3) Finally, calculate the error percentage by dividing F2/G2, as shown in Fig. 459.
As shown in Fig. 460, the traditional forecast error calculation is in E. My forecast error calculation is in H. Sometimes they are the same. Overall, though, because my calculation takes into account the negative effect of an unforecasted order showing up, my error percentage will be higher (and, I feel, more meaningful).
Summary: This started out as a tutorial on using ABS and MAX functions, but turned into a sermon on the best way to calculate forecast accuracy. Note that I am currently the only guy I know who calculates accuracy this way. When I bounce it off the pros at forecasting conventions, they reject this method. So, if you are doing forecasting, feel free to use this method at your own risk.
Functions Discussed: =ABS(); =MAX()
See all Microsoft Excel tips