Excel guide · 7 min read
How to do an exponential smoothing forecast in Excel
=C2+C$1*($B2-C2) down from row 3. Pick alpha by comparing the mean absolute deviation (MAD) of a few values; on the sample, 0.1 wins with an average miss of 28.5 units.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Filters | 0.1 | 0.3 | 0.5 | Pumps | Pumps fcst | Pumps error |
| 2 | Jan | 520 | 520 | 520 | 520 | 300 | 300 | 0 |
| 3 | Feb | 470 | 520 | 520 | 520 | 310 | 300 | 10 |
| 4 | Mar | 545 | 515 | 505 | 495 | 325 | 303 | 22 |
| 5 | Apr | 480 | 518 | 517 | 520 | 330 | 309.6 | 20.4 |
| 6 | May | 515 | 514.2 | 505.9 | 500 | 350 | 315.72 | 34.28 |
| 7 | Jun | 455 | 514.28 | 508.63 | 507.5 | 360 | 326.004 | 33.996 |
| 8 | Jul | 530 | 508.352 | 492.541 | 481.25 | 375 | 336.2028 | 38.7972 |
| 9 | Aug | 490 | 510.5168 | 503.7787 | 505.625 | 390 | 347.84196 | 42.15804 |
| 10 | Sep | 540 | 508.46512 | 499.64509 | 497.8125 | 400 | 360.489372 | 39.510628 |
| 11 | Oct | 465 | 511.618608 | 511.751563 | 518.90625 | 420 | 372.3425604 | 47.6574396 |
| 12 | Nov | 510 | 506.9567472 | 497.7260941 | 491.953125 | 435 | 386.63979228 | 48.36020772 |
| 13 | Dec | 495 | 507.26107248 | 501.40826587 | 500.9765625 | 450 | 401.1478546 | 48.852145404 |
| 14 | Next Jan | 506.03496523 | 499.48578611 | 497.98828125 | 415.80349822 | |||
| 15 | MAD | 28.518419389 | 31.52330407 | 35.635653409 |
Download this sheet as an Excel practice file (.xlsx)
The formula: last forecast plus a share of the last error
The sample is a year of monthly demand for water filters (B2:B13). Demand bounces between 455 and 545 with no real trend, which is the kind of series simple exponential smoothing is built for. Columns C, D and E hold three forecasts of the same data, and the alpha for each one sits in the header cell above it: 0.1 in C1, 0.3 in D1, 0.5 in E1. Keeping alpha in a cell, not typed into the formula, means you can test a new value by changing one number.
C2 is the first forecast, =$B$2 (more on that choice below). From C3 down, each forecast is =C2+C$1*($B2-C2): last month's forecast, plus alpha times last month's error. The mixed reference C$1 keeps the row locked on the alpha while the column can move, so you can fill the formula right into D and E and each column picks up its own alpha. $B keeps every column pointing at the actuals.
Rewritten, the same formula is alpha x last actual + (1 - alpha) x last forecast. That's why it's called smoothing: each forecast is a weighted average of all past demand, with weights that shrink the further back you go. With alpha 0.3, last month counts 30%, the month before 21%, then 14.7%, and so on. Row 14 runs the formula one step past the data to give next January's forecast.
Result: spills 1 row × 3 columns
| 506 | 499.5 | 498 |
506, 499.5 and 498: next January's forecast with alpha 0.1, 0.3 and 0.5. The year's actual average is 501.25.
Pick alpha by MAD, not by eye
Alpha decides how fast the forecast reacts. A high alpha (0.5) moves the forecast half way to every new actual, so it chases noise: after the 545 in March, the 0.5 forecast jumps to 520 and then misses April's 480 by 40. A low alpha (0.1) barely moves, which is right for noise and wrong for a real change in demand.
Score each alpha by its mean absolute deviation: the average size of the miss, ignoring direction. Row 15 does it for each column with =SUMPRODUCT(ABS($B3:$B13-C3:C13))/ROWS(C3:C13). It starts at row 3 because the row 2 forecast is just the first actual, so its error of 0 would flatter every alpha. SUMPRODUCT handles the array in every Excel version; in Excel 365 =AVERAGE(ABS(B3:B13-C3:C13)) works too.
The results are 28.52 for 0.1, 31.52 for 0.3 and 35.64 for 0.5. Testing more values on a copy of the sheet gives 28.78 for 0.05, 29.86 for 0.2 and 33.44 for 0.4, so 0.1 is the best of the grid. On noisy, flat demand the best alpha is usually low, often 0.1 to 0.3. Don't push it toward 0 just because the score keeps improving: alpha near 0 never reacts at all, so a real change in demand would take months to show up. The XLOOKUP below returns the alpha with the lowest MAD; add a column for every alpha you want to test.
Result: 0.1
0.1: the alpha with the smallest average miss (28.52 units) on this year of data.
The first forecast: first actual or the average of the first 3
Exponential smoothing needs a starting forecast, and with a low alpha that start hangs around for a long time. The sample starts at the first actual (520), which happens to be one of the highest months. With alpha 0.1 the forecast is still 506 in December, above the year's average of 501.25, partly because it began high.
The other common start is the average of the first three actuals, =AVERAGE($B$2:$B$4), which is 511.67 here. Swapping it into C2:E2 on a copy of the sheet lowers the alpha 0.1 MAD from 28.52 to 28.05 and next January's forecast from 506 to 503.7. Alpha 0.3 and 0.5 change much less, because they forget the start faster.
Strictly, an average that includes February and March isn't something you could have known in January, so it flatters the first errors a little. For a fair score, start the MAD a few rows later (row 5 instead of row 3) when you use it. Either start is fine, as long as you use the same one when you compare alphas.
Result: 511.66666667
511.67: the average of January to March, an alternative first forecast that's less sensitive to one unusual month.
Why it lags a trend (and what the tracking signal shows)
Simple exponential smoothing assumes demand has a level and noise, nothing else. Columns F to H run the same method (alpha 0.3 from D1) on pumps, which grow every month from 300 to 450. The forecast is always chasing: every single error from February to December is positive, from +10 to +48.9, and the forecast for next January is 415.8, below December's actual of 450. A lower alpha lags even more; with 0.2 that January forecast would be 396.6.
The tracking signal catches this straight away: running sum of errors divided by MAD. Here it's 11 after 11 months, which is the highest value possible, because every error went the same way. Any value past about plus or minus 4 means the forecast is biased, and with exponential smoothing on a trending series, that's what you'll see.
Raising alpha shrinks the lag but doesn't remove it, and it makes the forecast jumpier. If demand has a trend, use a method that models one: double exponential smoothing (Holt's method) adds a second smoothed series for the trend with its own constant, or fit a straight line to the history. For strong seasonality, divide out a seasonal index first, smooth the result, then multiply back.
Result: 11
11: the tracking signal for the pumps forecast after 11 months, the maximum possible, so every miss was in the same direction.
Built-in options: the Analysis ToolPak and FORECAST.ETS
Excel has two built-in routes, and both are worth knowing about mainly so you don't get caught by them.
- The Analysis ToolPak's Exponential Smoothing tool (Data > Data Analysis, after you turn the add-in on in File > Options > Add-ins > Manage Excel Add-ins > Go) asks for a Damping factor, not alpha. The damping factor is 1 - alpha, so for alpha 0.3 type 0.7. Typing 0.3 by mistake gives you alpha 0.7, a forecast that chases every bump.
- FORECAST.ETS (Excel 2016 and later) uses a more advanced exponential smoothing model that also handles trend and seasonality, and picks its own smoothing constants. It's a reasonable choice when you want a quick forecast, but you can't see or set alpha, and the result can be hard to explain to someone who asks why. The Cellwise engine doesn't run FORECAST.ETS, so this page doesn't show results from it.
- Writing the formula yourself, as above, keeps every step visible: you can see each forecast, change alpha in one cell, score it with MAD and check bias with the tracking signal.
Common mistakes
Typing alpha into the formula
=C2+0.3*(B2-C2) has to be edited in every cell to test another value. Put alpha in a cell and reference it with C$1 or $J$1.
Scoring alpha with the seeded first row included
The first forecast equals the first actual, so its error is 0 for every alpha. Start the MAD range on row 3.
Entering alpha as the ToolPak's damping factor
The ToolPak wants 1 - alpha. For alpha 0.2, enter 0.8.
Using simple smoothing on trending demand
Every error lands on the same side and the tracking signal climbs. Use a trend method (Holt's) or a fitted line, and keep simple smoothing for flat, noisy demand.
FAQ
What is the exponential smoothing formula in Excel?
New forecast = old forecast + alpha x (actual - old forecast). With actuals in B, forecasts in C and alpha in J1: =C2+$J$1*(B2-C2), filled down from row 3, with C2 = the first actual.
What is a good alpha for exponential smoothing?
For noisy demand with no trend, usually 0.1 to 0.3. Don't guess: test several values side by side and keep the one with the lowest MAD on your own history.
What is the damping factor in Excel exponential smoothing?
It's what the Analysis ToolPak calls 1 - alpha. A damping factor of 0.7 means alpha 0.3: each forecast moves 30% of the way toward the last actual.
Is exponential smoothing better than a moving average?
It reacts more smoothly, because every past month counts with a shrinking weight instead of the last N months counting equally and older ones not at all. Both lag a trend. Compare them on your data with MAD, as in the moving average guide.
How do I check if my exponential smoothing forecast is accurate?
Use MAD or MAPE for the size of the misses and a tracking signal for bias. The forecast accuracy guide covers MAPE and weighted MAPE.
Functions used
SUMPRODUCTMultiply then addABSAbsolute valueROWSNumber of rows in a rangeAVERAGEMean of numbersXLOOKUPModern lookup in any directionMINSmallest numberROUNDRounds to digitsSUMAdds numbers
Related guides
- How to calculate a tracking signal in Excel
- How to calculate a moving average in Excel
- How to calculate forecast accuracy in Excel
- How to calculate a seasonal index in Excel
- How to calculate inventory record accuracy in Excel
- KPI formulas in Excel: how to calculate and score KPIs
- How to calculate cubic feet in Excel
Updated