How to Calculate Forecast Accuracy Percentage in Excel (4 Easy Methods)

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,

Demand Forecast = Average Sales X Seasonality X Growth

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.

How to Calculate Forecast Accuracy Percentage in Excel

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.

BIAS Forecast Accuracy = Total Error/ 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.

MAPE = Average of Error Percentage

How to Calculate Forecast Accuracy Percentage in Excel with MAPE

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.

MAE = Total Absolute Error/ Total Demand

How to Calculate Forecast Accuracy Percentage in Excel with MAE

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.

MSE = Average of Squared Error

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.

RMSE = Square Root of MSE/ Average of 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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo