Model Evaluation is a crucial aspect in the development of a system model. When the purpose of the model is prediction, a reasonable parameter to validate the model’s quality is the mean squared error of prediction. In this tutorial, you will learn Mean Squared Error (MSE) and how you can calculate the MSE using MS-Excel.
What Is Mean Squared Error?
The Mean Squared Error measures how close a regression line is to a set of data points. It is a risk function corresponding to the expected value of the squared error loss.
Mean square error is calculated by taking the average, specifically the mean, of errors squared from data as it relates to a function.
Fig: Regression Line
A larger MSE indicates that the data points are dispersed widely around its central moment (mean), whereas a smaller MSE suggests the opposite. A smaller MSE is preferred because it indicates that your data points are dispersed closely around its central moment (mean). It reflects the centralized distribution of your data values, the fact that it is not skewed, and, most importantly, it has fewer errors (errors measured by the dispersion of the data points from its mean).
Lesser the MSE => Smaller is the error => Better the estimator.
The Mean Squared Error is calculated as:
MSE = (1/n) * Σ(actual – forecast)2
- Σ – a symbol that means “sum”
- n – sample size
- actual – the actual data value
- forecast – the predicted data value
Calculate Mean Square Error Using Excel
Now, you will learn how you can calculate the MSE using Excel.
Suppose you have the sales data of a product of all the months.
Step 1: Enter the actual and forecasted data into two separate columns.
Step 2: Calculate the squared error of each data
The squared error is calculated by (actual – forecast)2
Step 3: Calculate the Mean Squared Error
MSE = (1/12) * (98) = 8.166
The MSE for this model is 8.17.
Get broad exposure to key technologies and skills used in data analytics and data science, including statistics with the Data Analytics Certification Program.
In statistics, the mean squared error (MSE) is a risk function that measures the square of errors. When performing regression, use MSE if you believe your target is normally distributed and you want large errors to be penalized more than small ones.
If you have any questions regarding this tutorial, do share them in the comment section. Our subject matter expert will respond to your queries. Happy learning!