Moving Average (Excel Tutorial)
Here's a step-by-step Excel tutorial for calculating Simple Moving Average (SMA), Weighted Moving Average (WMA), and Exponential Moving Average (EMA)
1️⃣ Create a table with month and sales data in units
| Month | Sales (Units) |
|---|---|
| Jan | 100 |
| Feb | 120 |
| Mar | 110 |
| Apr | 130 |
| May | 140 |
2️⃣ Calculate Simple Moving Average (SMA)
For a 3-month SMA, use this formula in Cell C4 (assuming data starts from A2:B7):
=AVERAGE(B2:B4)
Drag the formula down for the remaining rows. This calculates the average of the last 3 months (B2 to B4).
3️⃣Calculate Weighted Moving Average (WMA)
Let’s assign weights 1, 2, and 3 (more weight on recent months).
In Cell D4, use this formula:
=(B2*1 + B3*2 + B4*3) / (1+2+3)
Drag the formula down. More weightage is given to recent months before averaging
4️⃣Calculate Exponential Moving Average (EMA)
The smoothing factor controls how much weight recent data gets compared to past data. It determines how quickly EMA reacts to new information. Choose a smoothing factor (α). A common value is 0.5.
When to Use a High or Low α?
| α Value | Effect | Best For |
|---|---|---|
| High (0.7 - 0.9) | Reacts quickly to changes | Fast-changing demand (e.g., fashion, tech trends) |
| Low (0.1 - 0.3) | Smoother, less responsive | Stable demand (e.g., groceries, household items) |
Example with Different α Values
Let’s say yesterday’s EMA was 100, and today’s sales are 120.
- If α = 0.2
- If α = 0.8
- First EMA (same as SMA for the first calculation).
In E4, use
=AVERAGE(B2:B4)
Next EMA values (recursive formula).
In E5, use this formula:
=0.5 * B5 + (1-0.5) * E4
Drag the formula down. EMA gives more weight to recent data while keeping past trends
Final Thoughts
- SMA = Good for stable demand
- WMA = Better for Trends
- EMA = Best for quick demand shifts