Hassan Shuvo
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.
📁 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.
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.
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.
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.
- Next, we will use Fill Handle to copy the formula in every row for every time period.
- 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.
🔨 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.
- Next, we will also calculate the sum of total actual demands. So, in cell D16, we type this formula and press Enter.
- Press Enter to see the value.
- Finally, to calculate Forecast Accuracy in BIAS method, we will divide the above two outputs. So, in cell D18, type this formula.
- Press Enter to see the value.
And there you go. We have our BIAS Forecast Accuracy Percentage.
📕 Read More: Calculate Percentage Change with Negative Numbers in Excel
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.
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.
- Next, we will use Fill Handle to copy the formula in every row for every time period.
- 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.
- Again, we will use Fill Handle to copy the formula in every row for every time period.
- Lastly, to get the MAPE, in cell D17, we use this formula.
=AVERAGE(F6:F15)
And there you go. We have our MAPE Forecast Accuracy Percentage.
📕 Read More: 6 Ways to Forecast Growth Rate in Excel
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.
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.
- Next, we will use Fill Handle to copy the formula in every row for every time period.
- Subsequently, we shall calculate the sum of Absolute errors. So in cell E16, we type this formula.
=SUM(E6:E15)
- Press Enter to see the value.
- Then, we will also calculate the sum of actual demands. So, in cell D16, we type this formula.
- Next, press Enter to see the value.
- Lastly, to get the MAD, in cell D17, we use this formula and press Enter.
And there you go. We have our MAD Forecast Accuracy Percentage.
📕 Read More: 3 Easy Ways to Calculate Accuracy Percentage in Excel
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.
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.
- Next, we will use Fill Handle to copy the formula in every row for every time period.
- Then we will find the square of every Absolute error in column F using this formula.
- Subsequently, press Enter to see the squared absolute error.
- Next, we will use Fill Handle to copy the formula in every row for every time period.
- Now that we have squared absolute error, we can calculate Mean Square Error using the AVERAGE So, in cell E18, we put this formula.
- Subsequently, press Enter to see the value.
- Finally, to calculate RMSE, in cell E19, we put the following formula.
- Lastly, press Enter to see the RMSE Forecast Accuracy Percentage.
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
- Calculate Percentage in Excel Based on Conditional Formatting
- 4 Easy Ways to Calculate Percentage in Excel Based on Cell Color
- Calculate Profit and Loss Percentage with Formula in Excel
- 5 Easy Methods to Add 10 Percent to a Number in Excel
- 3 Quick Ways to Add 20 Percent to a Price in Excel
