How can you calculate the mean absolute deviation (MAD) for a demand forecast?

1 What is MAD?

MAD is the average of the absolute values of the differences between the forecasted and the actual demand. It tells you how much your forecast is off, on average, regardless of the direction of the error. The lower the MAD, the more accurate your forecast is. For example, if your forecasted demand for a product is 100 units, and the actual demand is 90 units, the absolute deviation is 10 units. If you have multiple periods of forecast and actual demand, you can calculate the MAD by adding up all the absolute deviations and dividing by the number of periods.

2 How to calculate MAD in Excel?

One of the easiest ways to calculate MAD in Excel is to use the AVERAGE and ABS functions. Suppose you have your forecasted and actual demand data in columns A and B, respectively, for 12 periods. You can enter the following formula in any cell to get the MAD: =AVERAGE(ABS(B1:B12-A1:A12)). This formula will subtract the forecasted demand from the actual demand for each period, take the absolute value of the difference, and then average the results.

3 Why use MAD for demand forecasting?

MAD is a simple and intuitive metric that can help you evaluate the performance of your demand forecasting method. By comparing the MAD values of different methods, you can choose the one that minimizes the error and fits your data best. For example, you can use MAD to compare a simple moving average method with an exponential smoothing method, and see which one produces a lower MAD. MAD can also help you set safety stock levels, as it reflects the variability of the demand. The higher the MAD, the more safety stock you need to avoid stockouts.

4 What are the limitations of MAD?

While MAD is a useful metric, it also has some limitations that you should be aware of. First, MAD does not indicate the direction or the magnitude of the error. It only tells you how much your forecast is off, on average, but not whether you are overestimating or underestimating the demand, or by how much. Second, MAD does not account for the scale or the seasonality of the demand. It treats all errors equally, regardless of how large or small they are relative to the mean or the seasonal pattern of the demand. Third, MAD can be influenced by outliers or extreme values that may distort the average.

5 How to improve MAD?

In order to improve your MAD, it is important to improve your demand forecasting method. Depending on the data and business context, there are several ways to do this. Analyzing the data to identify any patterns, trends, cycles, or seasonality that impacts demand is one step. Additionally, choosing a demand forecasting method that matches the data characteristics and forecasting horizon is necessary. Historical data should be used to test and validate the method, then compare the forecasted and actual demand while calculating MAD and other error metrics like MSE or MAPE. Adjustments should be made based on feedback and results, such as changing parameters, weighting factors, or smoothing constants. Monitoring performance regularly and updating the method as needed is also key - this could include using more recent data or incorporating new information into the forecast.