Table of Contents
SMAPE (Symmetric Mean Absolute Percentage Error) is a commonly used mathematical formula for measuring the accuracy of a forecast or prediction. It takes into account both the magnitude and direction of the error, making it a more balanced measure compared to other error metrics.
To calculate SMAPE in Excel, you can follow these steps:
1. First, gather the actual values and forecasted values in two separate columns in your Excel sheet.
2. In a third column, calculate the absolute difference between the actual and forecasted values using the formula: =ABS(actual-forecast)
3. In a fourth column, calculate the sum of the actual and forecasted values using the formula: =(actual+forecast)
4. In a fifth column, calculate the SMAPE using the formula: =SUM(absolute difference/sum of actual and forecasted values)*100
5. The resulting value is the SMAPE for your forecast.
For example, let’s say you have the following data in your Excel sheet:
Actual Values: 100, 120, 150, 200
Forecasted Values: 90, 110, 140, 180
Using the above steps, the SMAPE would be calculated as follows:
Absolute difference: 10, 10, 10, 20
Sum of actual and forecasted values: 190, 230, 290, 380
SMAPE: 5.26%, 4.35%, 3.45%, 5.26%
In conclusion, SMAPE can be easily calculated in Excel using basic mathematical formulas, and it provides a comprehensive measure of the accuracy of a forecast.
Calculate SMAPE in Excel (With Examples)
The symmetric mean absolute percentage error (SMAPE) is used to measure the predictive accuracy of models. It is calculated as:
SMAPE = (1/n) * Σ(|forecast – actual| / ((|actual| + |forecast|)/2) * 100
where:
- Σ – a symbol that means “sum”
- n – sample size
- actual – the actual data value
- forecast – the forecasted data value
The smaller the value for SMAPE, the better the predictive accuracy of a given model.
The following step-by-step example explains how to calculate SMAPE in Excel.
Step 1: Enter the Data
First, we’ll enter some fake data for the actual sales and the forecasted sales during 12 consecutive sales periods for some company:
Step 2: Calculate the SMAPE Differences
Next, we’ll calculate the SMAPE difference for each sales period using the following formula:
Step 3: Calculate SMAPE
Lastly, we’ll use the following formula to calculate SMAPE:
The SMAPE for this particular model turns out to be 9.89%.