This guide will discuss how to calculate SMAPE in Excel.
Since Excel contain several built-in functions and tools, it allows us to perform difficult statistical calculations easily. For instance, we can easily perform different methods of forecasting accuracy in Excel.
So forecasting accuracy is often used in statistics to determine the closeness of a quantity or data set to the actual value of that specific quantity or data set. Furthermore, the actual value is also called the true value.
Additionally, forecasting accuracy is often used by business professionals to keep track of their sales records to determine how to maintain the demand and supply of their products. So forecasting accuracy helps us make better and more accurate forecasting or predictions.
Since there are various methods to calculate forecasting accuracy, we will be focusing on one specific method: the symmetric mean absolute percentage error, more commonly known as SMAPE. So SMAPE is used to measure the accuracy based on relative errors.
To calculate SMAPE in Excel, we can utilize the different built-in functions to make the process faster and easier. In this case, we will be using the ABS function, the SUM function, and the COUNT function.
Let’s take a sample scenario wherein we must calculate the SMAPE in Excel.
Suppose you have a data set containing the actual number of sales and forecasted number of sales during 8 consecutive months for your company. And you are tasked to perform the symmetric mean absolute percentage error or SMAPE to determine the predictive accuracy of the data set.
To make your work easier, you opted to calculate the SMAPE in Excel with the help of the ABS function, SUM function, and COUNT function.
Before we move on to a real example of calculating SMAPE in Excel, let’s first understand how to write the COUNT function in Excel.
The Anatomy of the COUNT Function
The syntax or the way we write the COUNT function is as follows:
=COUNT(value2, [value2],...)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- COUNT() is our
COUNTfunction. And this is used to count the number of cells in the selected or inputted range that contain numbers. - value1 is the only required argument. So this refers to 1 to 255 arguments that have or refer to various types of data. However, the function only counts the cells containing numbers.
- value2 is an optional argument. And this serves as an additional 1 to 255 arguments containing different types of data. But, the function only counts the cells with numbers.
Great! Now we can move on and dive into a real example of calculating SMAPE in Excel.
A Real Example of Calculating SMAPE in Excel
Let’s say we have a data set containing the actual number of sales and the forecasted number of sales for 8 consecutive sales periods or months. So our initial data set would look like this:
We want to calculate the SMAPE of our data set. So SMAPE, or symmetric mean absolute percentage error, is a forecasting tool used to check our forecasting methods’ accuracy.
Additionally, it is used to measure the accuracy based on relative errors similar to MAPE or weighted MAPE. And it is represented by a percentage. Moreover, we may use the symmetric mean absolute percentage error or the SMAPE over MAPE.
Since SMAPE has both the lower and the upper bounds, it fixes the shortcoming of MAPE, which favors models or data sets that are under-forecast over models or data sets that have over-forecast values.
In this case, symmetric means an equation that compares both predictions that are over and under the actual results. Then, the mean refers to a reference we use in the formula to calculate the average difference instead of a single measured difference.
And absolute is a calculation that only results in positive numbers due to the absolute value symbol. Then, the percentage refers to the output of the difference between the forecasted prediction we made and the true or actual outcome for the data set or even as part of the number 100.
Lastly, error refers to the difference between predicted and actual outcomes.
To calculate SMAPE, we will be utilizing the formula = 1/n x Σ(|Forecast - Actual| / ((|Actual + Forecast|) / 2) x 100 wherein n refers to the sample size, Σ is a symbol to represent the sum. Furthermore, |x| refers to the symbols denoting absolute values.
Then, forecast refers to the prediction or forecasted number of sales in our data set. And actual refers to the real outcome or the actual number of sales in our data set. Lastly, 100 converts the result of the rest of the equation into a percentage.
To apply this formula in Excel, we will first utilize the ABS function to calculate the SMAPE differences of each month. Then, we can calculate the SMAPE by dividing the sum of the SMAPE difference by the count or number of SMAPE differences.
Moreover, the smaller the value or result of our SMAPE, the better the predictive accuracy of our data set. And this can help us adjust our calculations to make better or more accurate forecasts or predictions for our sales.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can discuss the steps of how to calculate SMAPE in Excel.
How to Calculate SMAPE in Excel
In this section, we will explain the step-by-step process of how to calculate the SMAPE in Excel. Furthermore, each step contains detailed instructions and pictures to help you follow the process easier.
To apply this method in your work, simply follow the steps below.
1. Firstly, we will create a new column to input the results for our SMAPE differences. To calculate the SMAPE difference, we will use the formula together with the ABS function. So we will type in the formula “=ABS(D2-C2)/((ABS(C2)+ABS(D2))/2)”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag the Fill Handle tool down to copy the formula and apply it to the rest of the cells.
3. Thirdly, we will finally calculate the SMAPE of our data set. To do this, we will input the formula “=SUM(E2:E9)/COUNT(E2:E9)”. Then, we will press the Enter key to return the result.
4. Then, we can change the format of our SMAPE to a percentage. To do this, we can simply right-click on the cell containing our result and select Number Format in the dropdown menu.
5. Next, we will go to the Percentage category. From there, we will click the dropdown arrow beside Decimal Point and select 2. Lastly, we will click OK to apply the changes.
6. And tada! We have successfully calculated the SMAPE in Excel.
And that’s pretty much it! We have successfully explained the step-by-step process of how to calculate SMAPE in Excel. Now you can easily follow this guide and apply it to your work whenever you need to calculate the SMAPE of your data set.
Are you interested in learning more about what Excel can do? You can now use the COUNT function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
