4 Simple Ways to Calculate Forecast Accuracy Percentage in Excel

If you are a businessman and you want to predict how much product you need to produce for the next season, you have to know the process of calculating forecast accuracy percentage.  Forecast accuracy helps businesses in numerous ways, as it gives vendors an idea about the demands in the market. Today in this article we shall discuss how anyone can calculate Forecast Accuracy Percentage in Excel using some of the established methods.

calculate Forecast accuracy percentage using different formula


📁  Download Excel File

Download the workbook to practice with us.


What is Demand Forecast and Forecast Accuracy Percentage?

Demand forecasting is the technique of estimating and predicting future consumer demand for a good or service using a probabilistic analysis of previous data. By estimating future sales and profits, demand forecasting assists the company in making more educated supply decisions. By anticipating future sales, demand forecasting enables organizations to optimize inventories. Demand managers may make well-informed decisions for the company about anything from stock management and warehousing requirements to executing flash sales and satisfying consumer expectations by reviewing past sales data. No business can exist if there isn’t any demand. Additionally, any firm will not be able to decide the amount of expense on marketing, producing, hiring, and other financial matters without a grasp of demand.

Calculating the forecast accuracy of your demand forecast is one technique to evaluate its reliability. The difference between the actual and anticipated demand is known as the Forecast Error/Forecast Accuracy. If you can figure out how much your prior demand estimates were off, you may take that into account for future ones and change your strategy accordingly.


Formula for Forecast Accuracy Percentage

Inventory planners can determine Forecast Accuracy/Error using a variety of methods, ranging in complexity from somewhat simple to highly sophisticated. However, in most cases, the forecast accuracy percentage can be calculated using one of four formulas.

    • BIAS Forecast Accuracy (Consistent Forecast Error)
    • Mean Absolute Percentage Error (MAPE) Forecast Error
    • Mean Absolute Error (MAE) Forecast Error
    • Root Mean Squared Error (RMSE) Forecast Error

For every method, you always have to calculate Error/Absolute Error which is the difference between Actual Demand and Demand Forecast.

Absolute error formula to calculate forecast accyracy percentage

From there, we will explain how each method will calculate Forecast Accuracy Percentage in this article.


Learn to Calculate Forecast Accuracy Percentage in Excel with These 4 Methods

We will discuss 4 different methods of calculating Forecast Accuracy/Error Percentage. Before every method, there will be a brief discussion as to how each formula works. For illustration purposes, we select a dataset of forecasted and actual demand for a specific product over various time periods. Apart from using the Forecast accuracy formula, we will also use some Excel functions like SUM, ABS, AVERAGE, and SQRT for our calculation procedure. Let’s get down to the main article.

Dataset to calculate Forecast accuracy percentage

Method 1

1. Using BIAS Forecast Accuracy

The disparity between forecast demand and actual demand is known as Forecast BIAS. Forecast BIAS is deemed good if the forecast overestimates demands. It is deemed unfavorable if the forecast understates demands. If you wish to assess BIAS as a percentage of accuracy, simply divide the total errors by the total demands; results greater than 100% indicate over-forecasting, while results less than 100% indicate under-forecasting.

BIAS Forecast Accuracy Percentage formula

Now, let’s use the formula to calculate Forecast Accuracy Percentage in Excel. We will use SUM and ABS functions for completing our objective.

⬇️⬇️ STEPS ⬇️⬇️

  • As the forecasted demand and actual demand have been detailed in the table, our first objective would be to calculate the error in our assumption(forecast). So in E6, we will type this formula and press Enter.
=D6-C6

Calculating Error

  • Next, we will use Fill Handle to copy the formula in every row for every time period.

Using Fill Handle to copy Error formula

  • Since, we have to use the sum of every error we made for the final calculation, in cell E16, we will write the following formula. Remember, we are calculating Absolute Error here meaning there will be no negative value in the result.
=ABS(SUM(E6:E15))

using ABS and SUM function to calculate Forecast Accuracy Percentage

🔨 Formula Breakdown

👉 Firstly, the SUM function calculates the sum of errors in cell E6:E15.

👉 Then, the ABS function turns the sum into absolute value.

  • Press Enter to see the value.

total Error

  • Next, we will also calculate the sum of total actual demands. So, in cell D16, we type this formula and press Enter.
=SUM(D6:D15)

Using SUM function to calculate total A.D

  • Press Enter to see the value.

Total A.D

  • Finally, to calculate Forecast Accuracy in BIAS method, we will divide the above two outputs. So, in cell D18, type this formula.
=(E16/D16)*100%

BIAS formula

  • Press Enter to see the value.

BIAS result

And there you go. We have our BIAS Forecast Accuracy Percentage.

📕 Read More: Calculate Percentage Change with Negative Numbers in Excel

Method 2

2. Applying Mean Absolute Percentage Error (MAPE)

Next, we shall discuss the Mean Absolute Percentage Error (MAPE) which is a measure that defines the efficiency of a forecasting system. It depicts the average of the absolute percentage errors of each record in a dataset to assess how accurate the anticipated quantities were in contrast to the actual numbers. The use of dataset values other than zero is necessary for MAPE to be effective when evaluating huge data sets.

The average or mean of forecasts’ absolute percentage errors is known as the mean absolute percentage error (MAPE). Error is defined as the difference between the actual demand and the forecasted demand. To calculate MAPE, percentages of the errors are added despite the sign. Because it shows the mistake in percentages, this measurement is simple to comprehend. When using absolute percentage errors, the problem of offsetting positive and negative mistakes is eliminated. As a result, MAPE is a metric that is frequently employed in forecasting and has management appeal. Forecast accuracy increases with decreasing MAPE.

MAPE Forecast Accuracy Percentage formula

Now, let’s use the formula to calculate the MAPE Forecast Accuracy Percentage in Excel. We will use ABS and AVERAGE functions for completing our objective.

⬇️⬇️ STEPS ⬇️⬇️

  • As the forecasted demand and actual demand have been detailed in the table, our first objective would be to calculate the Absolute error in our assumption(forecast). So in cell E6, we will type this formula and press Enter.
=ABS(D6-C6)

using ABS to calculate absolute error

  • Next, we will use Fill Handle to copy the formula in every row for every time period.

Using Fill Handle to copy A.E formula

  • Then we will calculate the Absolute error percentage for each period using this formula. So, in cell F6, we put this formula and press Enter.
=(E6/D6)*100%

calculating absolute error percentage

  • Again, we will use Fill Handle to copy the formula in every row for every time period.

Using Fill handle to copy Absolute error percentage

  • Lastly, to get the MAPE, in cell D17, we use this formula.

=AVERAGE(F6:F15)

using AVERAGE function to calculate MAPE Forecast Accuracy Percentage

And there you go. We have our MAPE Forecast Accuracy Percentage.

MAPE result

📕 Read More: 6 Ways to Forecast Growth Rate in Excel

Method 3

3. Using Mean Absolute Deviation (MAD)

The simplest way to assess prediction accuracy is to use Mean Absolute Deviation/Error (MAD/MAE). The mean of the absolute errors, or MAE, is just what its name implies. The difference between the forecasted demand and the actual demand, expressed as an absolute number, is the absolute error. The MAE reveals the average size of the forecast error that we may anticipate.

MAD Forecast Accuracy Percentage formula

Now, let’s use the formula to calculate the MAD Forecast Accuracy Percentage in Excel. We will employ the ABS and SUM functions in this section.

⬇️⬇️ STEPS ⬇️⬇️

  • As the forecasted demand and actual demand have been detailed in the table our first objective would be to calculate the Absolute error in our assumption(forecast). So in cell E6, we will type this formula and press Enter.
=ABS(D6-C6)

calculating Absolute errors

  • Next, we will use Fill Handle to copy the formula in every row for every time period.

Using Fill Handle to copy formula

  • Subsequently, we shall calculate the sum of Absolute errors. So in cell E16, we type this formula.

=SUM(E6:E15)

Using SUM function to calculate total absolute error

  • Press Enter to see the value.

Total absolute error

  • Then, we will also calculate the sum of actual demands. So, in cell D16, we type this formula.
=SUM(D6:D15)

Using SUM function to calculate total actual demand

  • Next, press Enter to see the value.

total actual demand

  • Lastly, to get the MAD, in cell D17, we use this formula and press Enter.
=(E16/D16)*100%

MAD formula

And there you go. We have our MAD Forecast Accuracy Percentage.

MAD result

📕 Read More: 3 Easy Ways to Calculate Accuracy Percentage in Excel

Method 4

4. Using Root Mean Squared Error (RMSE)

One of the most important forecast error metrics is the RMS or Root Mean Squared Error. However, compared to the considerably more prevalent MAD and MAPE forecast error computations, it is a distant third due to the intricacy of its calculation and the difficulty of its explanation. RMSE is nonintuitive because of nonproportionality. You will discover how to calculate RMSE, which is comparable to but more comprehensive than other typical prediction error assessments, as well as its limits for use in enhancing forecast accuracy.

The mean squared error between forecasted and actual demand is known as the root mean squared error, or RMSE.

RMSE Forecast Accuracy Percentage formula

Now, let’s use the formula to calculate the RMSE Forecast Accuracy Percentage in Excel. We will use ABS, SUM and AVERAGE functions for completing our objective.

⬇️⬇️ STEPS ⬇️⬇️

  • As the forecasted demand and actual demand have been detailed in the table, our first objective would be to calculate the Absolute error in our assumption(forecast). So in cell E6, we will type this formula and press Enter.
=ABS(D6-C6)

Using ABS function to calculate Absolute error

  • Next, we will use Fill Handle to copy the formula in every row for every time period.

Using Fill Handle

  • Then we will find the square of every Absolute error in column F using this formula.
=E6*E6

calculating squared absolute error

  • Subsequently, press Enter to see the squared absolute error.

Squared absolute error to calculate forecast accuracy percentage

  • Next, we will use Fill Handle to copy the formula in every row for every time period.

Using Fill Handle to copy Squared Error Formula

  • Now that we have squared absolute error, we can calculate Mean Square Error using the AVERAGE So, in cell E18, we put this formula.
=AVERAGE(F6:F15)

using AVERAGE function to calculate MSE to calculate forecast accuracy percentage

  • Subsequently, press Enter to see the value.

MSE result

  • Finally, to calculate RMSE, in cell E19, we put the following formula.
=SQRT(E18)/AVERAGE(D6:D15)

using SQRT and AVERAGE functions to calculate RMSE Forecast Accuracy Percentage

  • Lastly, press Enter to see the RMSE Forecast Accuracy Percentage.

RMSE result

And there you go. We have our RMSE Forecast Accuracy Percentage.

📕 Read More: Calculate Percentage in Excel Using Absolute Cell Reference


📝 Takeaways from This Article

🖊️ First, we explained the definition of Demand forecast and the forecast accuracy percentage.  We also included the formula required to calculate Forecast accuracy percentage.

🖊️ Next, we showed how to find Forecast accuracy using the BIAS formula in Excel.

🖊️ Then, we calculated the same using Mean Absolute Percentage Error.

🖊️ We also narrated Mean Absolute Deviation/Error in the same context.

🖊️ Finally, we described Root Mean Squared Error as the concluding method.


Conclusion

We have come to the conclusion of our article. I hope you will be able to use these functions and formulas to calculate Forecast Accuracy Percentage in Excel. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.


Related Articles

(Visited 73 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo