Gain experience in sales forecasting by learning to apply simple, weighted, and exponential moving averages in Excel, smoothing data fluctuations effectively. Enhance your analytics skills by understanding how recent data can be emphasized for more accurate sales predictions.
Key Insights
- A simple moving average is calculated by averaging car sales over a three-day period, smoothing short-term fluctuations and making trends easier to visualize compared to overall averages, resulting in a slightly improved average (45 instead of 44).
- The weighted moving average assigns importance to recent sales data, allocating specific percentages—60% to yesterday, 30% to two days ago, and 10% to three days prior—yielding a higher average (46) than the simple moving average.
- An exponential moving average puts greater weight on recent data points, controlled through a dropdown menu that adjusts the smoothing constant (2 divided by n+1), with smaller values producing smoother curves and larger values more closely tracking the original sales data.
This lesson is a preview from our Data Analytics Certificate Online (includes software) and Data Analytics Certification Online (includes software & exams). Enroll in a course for detailed lessons, live instructor support, and project-based training.
Moving averages. We're going to create a forecast that smooths out the data by averaging recent data points together. We have a chart that displays the average.
And this is the average of cars that are sold. But we want to create a three-day simple moving average of the number of cars sold, where the average only relies on the last three days. So I'm going to go over to the simple moving average column.
And instead of having the average just be calculated based off all the values, I'm just going to create an average that's based on the last three values. So I'll type equal average, and I'll select the last three values. I'm not going to lock anything, because I want this to move down as I autofill this.
I'll press ENTER. And so the first type of moving average we're looking at is the simple moving average. Now I'm going to go here, and I'm going to autofill this down.
And now we can see our simple moving average as the yellow line. It's not as dramatic as the average based on all the data. So here, this is only based on the last three days.
When we say it smooths out the data, we have a lot of hills for the blue line. But the yellow line is a lot smoother. It's more like a horizontal line that's kind of tilted.
So this would be ideal for someone who doesn't want their sales to be based on an average of all the cars sold, but only the last three days. Because maybe their improvement in those last three days is going to override the average overall. Now if I go here, I'll just get the average, go to the Formulas tab, I'll press ENTER.
And my average here overall is 44. What is my average here? Let's see what the difference is. Press ENTER.
It's 45. It's a little better, not that much. But graphically, it looks better.
Now let's take a look at what a weighted moving average is. A weighted moving average calculates the number of cars sold based on yesterday being the most important day, the day before being less important, and the day before that being the least important. And how we determine importance is by multiplying those last three days by percentages.
So yesterday will count for 60% of my weighted moving average. The day before will count for 30%. And the day before that will count for 10%.
And this is the calculation that we have here. If I click on a chart, we'll see which lines are selected, which data is selected. Cars Sold and Simple Moving Average are included in the chart.
Now I would like to bring in the weighted moving average. So I'm going to go to the corner. And when I see a double arrow, I'm going to drag this over.
And now I have the line that's included, which is gray. That line is included. And it represents the weighted moving average.
So you're going to get different values. Let me just do the same old average function here and see if we see much of a difference. Yes.
So the weighted moving average, I'm getting a higher amount. Again, it's slight. But if you have a lot of values, it could potentially make a big difference.
So regular average is 44. Simple moving average is 45. And weighted moving average is 46.
Our final moving average is the exponential moving average. And so this depends on the value that we have here. That is a data validation dropdown that refers to all the values we have here on the right.
So this gives higher weight to recent data and exponentially less to the earliest piece of data. All right. We're going to use the data point here, which represents the data point minus the previous EMA, Exponential Moving Average.
Now, if I click this, orange is representing the Exponential Moving Average. I can determine the influence of the previous EMA by choosing another value from the dropdown. And you can see how that changes the line.
A higher value will closely follow the original data. And if I choose a lower amount, it starts to smooth things out. So I'll go and choose 0.4. And then I'll choose 0.2. And so there you go.
I'll go back to 0.5. So where n is the number of periods, in this case, 3. And this symbol, which is like an infinity symbol, is the smoothing constant. It's 2 divided by n plus 1. So a smaller value means that it's a smoother curve. A larger value means it's a less smooth curve.
There is an exercise that you can work on here that uses both the simple moving average and the weighted moving average. And so you can do this based on what we just covered in this last section. This concludes the section on moving averages.
Thank you for watching.
