Mean Squared Error (MSE) or Mean Square Deviation (MSD) is used to determine the measure of the average of the square of error for any model. In this tutorial, we are going to see how you can calculate the Mean Squared Error or Mean Squared Deviation In Excel by using just simple 3 steps.
Table of Contents
- 1. Enter Actual and Forecasted Values separately
- 2. Find the squared error
- 3. Determine Mean Squared Error
- Why We don’t Recommend Mean Squared Error
As we have already finished SMAPE, where we learned, that the smaller value we get the better model will be. The same goes for MSE.
Without further delay, let’s head to our main topic. Here we break down all these processes in just 3 steps. All you need is just follow these 3 points step-by-step.
1. Enter Actual and Forecasted Values separately
For now, I am just using the data 6 days a week. Then I assumed Actual Values and Forecasted Values.
2. Find the squared error
In order to find the squared Error consider this formula: (actual – forecast)2. We are going to use this formula in order to find the Squared Error of each row.
I used Column D which indicates the Squared Error and Column E which represents the formula.
3. Determine Mean Squared Error
All you need is now to find the average of the Squared Error, it will become the MSE.
The MSE of this particular data model turns out to be approx 19.66.
Why We don’t Recommend Mean Squared Error
Now a day people prefer Mean Absolute Percentage Error (MAPE) over MSE because it is easy for a user and a developer to understand the model.
MSE is scaled-dependent, so it is very difficult for us to use it in business fields. People understand percentages better than squared errors.
In some rare cases, when the percentage makes no sense then we can use MSE indeed.
