Blog · Forecast and pipeline · Finance and FP&A teams
Build Holt's level-and-trend forecast in Excel from monthly sales. Follow the formulas, initialization and held-out accuracy comparison against two simpler baselines.
Double exponential smoothing in Excel uses a level and a trend to forecast a nonseasonal business series. Holt's linear method updates both after each actual result, then extends the latest trend into future periods. Build those equations in cells and judge them on later observations that were kept out of the calculation.
Start with one row per completed month from a sales export. Keep the business scope, currency and revenue definition consistent. Aggregate transaction rows before smoothing, and distinguish a missing month from a month with confirmed zero sales.
This example uses monthly sales for a US business, in USD. There is no seasonal component. If the business has a recurring holiday peak, an unadjusted level-and-trend calculation is an incomplete description of the pattern. The exponential smoothing glossary puts the methods in context.
Holt's method is one specific interpretation of double exponential smoothing. State its equations so someone opening the workbook knows exactly what was calculated. Forecasting: Principles and Practice describes Holt's linear trend method as separate level and trend updates with forecasts that extend the latest trend.
Level today = alpha × actual today + (1 - alpha) × (previous level + previous trend)
Trend today = beta × (level today - previous level) + (1 - beta) × previous trend
Forecast h periods ahead = latest level + h × latest trend
Alpha controls how much the new actual affects level. Beta controls how much the latest level change affects trend. Both parameters lie between zero and one; a larger weight gives the newest information more influence in its equation.
Use A for month, B for actual sales, C for level, D for trend and E for the estimate made from the preceding row. Put alpha of 0.50 in H2 and beta of 0.25 in H3. These are transparent illustration inputs, not fitted recommendations.
The first observation is January in row 2. Initialize level to January sales and trend to February sales minus January sales:
C2 =B2
D2 =B3-B2
E3 =C2+D2
C3 =$H$2*B3+(1-$H$2)*(C2+D2)
D3 =$H$3*(C3-C2)+(1-$H$3)*D2
Fill C3:E3 down through June in row 7. Initialization uses the first two training observations, so the February estimate is not an independent accuracy test. Do not include it in a claimed out-of-sample score.
| Month in 2026 | Actual sales (USD) | Updated level (USD) | Updated monthly trend (USD) | Estimate from prior row (USD) |
|---|---|---|---|---|
| January | 100,000 | 100,000.00 | 10,000.00 | — |
| February | 110,000 | 110,000.00 | 10,000.00 | 110,000.00 |
| March | 108,000 | 114,000.00 | 8,500.00 | 120,000.00 |
| April | 120,000 | 121,250.00 | 8,187.50 | 122,500.00 |
| May | 125,000 | 127,218.75 | 7,632.81 | 129,437.50 |
| June | 131,000 | 132,925.78 | 7,151.37 | 134,851.56 |
Keep full precision in the cells; the table displays dollars to two decimals. For March, level is 0.50 × 108,000 + 0.50 × 120,000 = $114,000. Trend is 0.25 × (114,000 - 110,000) + 0.75 × 10,000 = $8,500. The next estimate is therefore 114,000 + 8,500 = $122,500.
This row calculation is a useful implementation check: the estimate in each row must equal the previous row's level plus trend. Accidentally using the current level makes the estimate depend on the actual it is supposed to predict.
June is the forecast origin. Keep July and August actuals out of the level and trend updates. Place those months in A10:A11, their actual sales in B10:B11 and horizons of 1 and 2 in C10:C11.
D10 =$C$7+C10*$D$7
E10 =ABS(B10-D10)
Fill both down one row. Both forecasts must use the June cells with absolute references. Otherwise, the second forecast may quietly become a different experiment.
| Held-out month | Horizon | Actual sales (USD) | Holt forecast (USD) | Absolute error (USD) |
|---|---|---|---|---|
| July 2026 | 1 | 139,000 | 140,077.15 | 1,077.15 |
| August 2026 | 2 | 143,000 | 147,228.52 | 4,228.52 |
The unrounded June level is 132925.78125 and trend is 7151.3671875. Using those values gives the displayed forecasts. Calculate mean absolute error, or MAE, with =AVERAGE(E10:E11): $2,652.83, rounded only after averaging unrounded errors. Microsoft defines AVERAGE as the arithmetic mean.
Use the same June origin and the same two held-out actuals for every method. A last-value baseline forecasts June sales for both months. A drift baseline extends the average monthly change between January and June: (131,000 - 100,000) / 5 = $6,200 per month.
Last-value forecast =$B$7
Drift forecast =$B$7+C10*($B$7-$B$2)/(ROWS($B$2:$B$7)-1)
| Method | July forecast (USD) | August forecast (USD) | July absolute error (USD) | August absolute error (USD) | MAE (USD) |
|---|---|---|---|---|---|
| Holt, stated parameters | 140,077.15 | 147,228.52 | 1,077.15 | 4,228.52 | 2,652.83 |
| Last value | 131,000.00 | 131,000.00 | 8,000.00 | 12,000.00 | 10,000.00 |
| Drift | 137,200.00 | 143,400.00 | 1,800.00 | 400.00 | 1,100.00 |
Drift has the lowest MAE in this small example. That is a reason to retain the simpler method for further testing. Two observations do not establish a durable winner; repeat the comparison across multiple historical origins and the horizons finance actually uses.
The forecast accuracy chapter distinguishes training fit from genuine forecasts and defines MAE from absolute errors. Keep those two jobs separate. A moving average in Excel offers another baseline, while budget versus forecast explains how a forecast belongs alongside an approved plan.
Covirage is not affiliated with Microsoft.
Bring a monthly sales workbook or Google Sheet with actuals, forecast versions, horizons and forecast origins. Covirage maps the columns, and its tools compute and check the supplied forecast comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See forecast analysis with Covirage for the supporting workflow.
Holt's linear method estimates a level and trend without a seasonal component. Holt-Winters adds seasonality. The names are sometimes used loosely, so specify the equations and whether a seasonal pattern is included before comparing workbook outputs.
Choose them using training data and a stated accuracy objective. Keep later test observations separate. The illustrative values here make the arithmetic reproducible; they are not recommended defaults or parameters selected from the held-out results.
It updates as new observations arrive, but the historical trend does not anticipate an unobserved contract loss, price reset or capacity closure. Keep approved business assumptions beside the statistical forecast and compare both with subsequent actual results.