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

MonthSales (Units)
Jan100
Feb120
Mar110
Apr130
May140

2️⃣ Calculate Simple Moving Average (SMA)

Simple Moving Average

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)

Weighted Moving Average

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)

Exponential Moving Average

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 α?

α ValueEffectBest For
High (0.7 - 0.9)Reacts quickly to changesFast-changing demand (e.g., fashion, tech trends)
Low (0.1 - 0.3)Smoother, less responsiveStable 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
Smaller value- Smoother, slower reaction
  • If α = 0.8
Higher value- faster reaction to change
  • 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

Subscribe to Visual Dragon

Don’t miss out on the latest issues. Sign up now to get access to the library of members-only issues.
jamie@example.com
Subscribe