Microsoft Excel: Measure the Accuracy of a Sales Forecast

Problem: You handle forecasting for a company. You collect forecasts from the sales reps and attempt to turn this into a production plan for the manufacturing plant.

When creating a sales forecast in Excel, a lot of forecasting professionals measure forecast error as (Forecast Actual)/Forecast, as shown in Fig. 456. (Click any image for a larger view.)

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 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()

 

If you're looking for more Excel tips, be sure to check out the following collections at Bright Hub.

91 Tips for Calculating with Excel – This collection of 91 Excel calculating tips is taken from the book Learn Excel from Mr. Excel, written by Bill Jelen. It includes strategies for all types of Excel calculations and even includes tutorials on how to create dice games in Excel.

Excel Formatting Tips from Mr. Excel – Want to learn tricks to help better organize and analyze your data? Take a look at this set of 72 tips that focuses on how to get the most out of Excel's high-powered formatting capabilities.