Sanjida Ahmed
Calculating the Forecast Accuracy Percentage is a very familiar task to do not only for the people who work with statistics and data analysis but also for the people who work with data science and machine learning. In this article, we will show you 4 of the easiest and most efficient methods of how to calculate Forecast Accuracy Percentage in Excel.
Download Workbook
You can download the free practice Excel workbook from here.
Introduction to Forecast Accuracy
Forecast Accuracy is the deviation between the forecasted demand and the actual demand. It is also called Forecast Error. If the errors from the previous demand forecasts are calculated correctly, it allows you to modify your future business planning, such as increasing your service rate, reducing the stock-outs, reducing the cost of the supply chain etc. to make it more successful.
Calculating forecast accuracy is very important in business, so you must have a consistent and reliable method to estimate the forecast easily.
In this article, we will show you how you can calculate forecast accuracy percentage in the 4 most reliable ways. But before showing you those calculation methods, first, you need to know what the actual demand in forecasting accuracy is.
Introduction to Demand Forecast to Calculate Forecast Accuracy Percentage
Demand Forecasting or Sale Forecasting is a very broad topic. The goal of this article is to show you how you can calculate Forecast Accuracy Percentage in Excel. So, here we will just provide you with a brief of the demand forecasting.
On the other hand, Demand Forecast is something that is not very common in every organization. Or even if your company has any, you may not be aware of that. If your company has ERP or related software, then most probably you have a forecast.
The formula to calculate the Demand Forecast is,
By implementing this formula, you can easily find out the demand forecast of your organization.
4 Methods on How to Calculate Forecast Accuracy Percentage in Excel
Now that you know about the Actual Forecast and the Demand Forecast, you can get started on the calculation of the Forecast Accuracy Percentage in Excel.
The steps to calculate the Forecast Accuracy for product by product in Excel are given below.
Steps:
- At first, simply subtract the forecast from the demand to calculate the forecast error for each product.
- After that, use the ABS() formula in Excel to calculate the absolute value of the error.
- Finally, simply divide the absolute value of the error by the demand and multiply it by 100 to calculate the percentage of the error at the product level.
All these calculations’ steps are shown below for a 2-month sales horizon.
You can utilize the SUM() function to calculate the Total of all the attributes in calculating the forecasting accuracy percentage in Excel.
Well, as you already know, these errors are at the item level. We now need to know how to get an overall indicator based on these measurements.
Following this section, you will learn the 4 most simple and commonly used mathematical formulas to calculate forecast accuracy percentages in Excel.
1. BIAS Forecast Accuracy/ Consistent Forecast Error to Calculate Forecast Accuracy Percentage
Prediction BIAS is the analytical deviation between the actual values and the estimated values.
To calculate the forecast accuracy simply divide the Total Error by the Total Demand.
To check whether the prediction for all the products is overestimated (BIAS > 0) or underestimated (BIAS < 0), you can utilize this method.
Read More: How to Calculate Profit Percentage in Excel (3 Methods)
2. Mean Absolute Percentage Error (MAPE) to Calculate Forecast Accuracy in Excel
Another simple and effective way to calculate forecast error is to calculate the Mean Absolute Percentage Error (MAPE) of the forecast. MAPE is defined as the average of the Error percentages.
As MAPE is a calculation of errors, a high percentage means bad, and a low percentage means good.
We don’t recommend this method as there is no weighting on quantities or on values. Periods of high demand can easily be underestimated if you fully rely on this indicator to measure your forecasts.
Read More: Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)
3. Mean Absolute Error (MAE)/ Mean Absolute Deviation (MAD)/ Weighted Absolute Percentage Error (WAPE)
Mean Absolute Error (MAE) or Mean Absolute Deviation (MAD) or Weighted Absolute Percentage Error (WAPE) is the average of weighted absolute errors. Absolute value means even when the difference between the forecasted demand and the actual demand is a negative value, it becomes positive.
To calculate the Mean Absolute Error (MAE) of the forecast just divide the Total Absolute Error by the Total Demand.
This method is weighted by quantity or value, making it highly recommended in demand planning.
However, there is one drawback to this method. As the demand error is not proportional, this method works best when working on one product. If it is used on products with different volumes, the outcome will be crooked with the product of heavier volumes.
Read More: How to Calculate Weight Loss Percentage in Excel (5 Methods)
4. Root Mean Squared Error (RMSE) to Calculate Forecast Accuracy in Excel
Root Mean Squared Error (RMSE) is calculated from the square root of Mean Squared Error (MSE) or Mean Squared Deviation (MSD).
We need to add the Squared Error (Error^2) for each product for this indicator. Then we can calculate the Mean Squared Error. The Mean Squared Error (MSE) is the average squared error for each product.
Now that we have the value of MSE, we can now measure the RMSE for our forecast.
To calculate the RMSE, just divide the square root of MSE by the Average of the Demand.
The RMSE indicator is comparatively more complex to implement and extract results. But this formula strongly penalizes large forecast errors.
This is also a highly recommended method because this method is able to ignore computation errors and produce results accurately.
Read More: How to Calculate Win-Loss Percentage in Excel (with Easy Steps)
Conclusion
To conclude, this article showed you 4 easy and useful methods how to calculate Forecast Accuracy Percentage in Excel. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.
Related Articles
