Excel Sales Forecast Accuracy Formula and Calculation
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 actual.
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()