Statistical Forecasting in Excel: FORECAST.ETS and Beyond

Excel statistical forecasting is a practical way for finance and planning teams to produce a defensible baseline forecast without leaving the spreadsheet. This guide is part of business analytics for finance and operations, follows Python vs. Excel for analysis and analytics automation, and leads to ML vs. simple models and predictive analytics for business. Excel includes functions that fit a trend, estimate seasonality, project forward and give a confidence range: FORECAST.LINEAR for straight-line trends, FORECAST.ETS for exponential smoothing with seasonality, and the Forecast Sheet command that builds a chart and table in a few clicks. They are not a substitute for a dedicated forecasting system or for judgment about events the history cannot know. But for a few dozen series, or as a baseline and sanity check, they are quick, transparent and good enough to be useful. This guide shows how to use these tools, explains what is happening underneath with worked examples of exponential smoothing and seasonal indices with checked arithmetic, describes how to test accuracy on a holdout and lists the setup rules and pitfalls. Function behavior can vary between versions of Excel, so check the documentation for yours. Baselines like these feed budgeting and forecasting.

The main tools

FORECAST.LINEAR(x, known_y's, known_x's) fits a straight line through the history and returns the predicted value for a given x, such as a future date or period number. It suits a steady trend with no seasonality. The related functions TREND (returns values along the fitted line) and SLOPE and INTERCEPT (return the line's parameters) give the same information in other forms. GROWTH and LOGEST fit exponential curves for compounding growth.

FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) uses an exponential triple smoothing method (the AAA version of ETS) that estimates level, trend and seasonality from the history, giving more weight to recent data. It returns the forecast for the target date. Companion functions include FORECAST.ETS.CONFINT (a confidence interval around the forecast), FORECAST.ETS.SEASONALITY (the length of the seasonal pattern Excel detected) and FORECAST.ETS.STAT (statistics about the fit).

Forecast Sheet (Data tab, Forecast group) is a wizard that takes a time series, builds a new sheet with the historical and forecast values, upper and lower confidence bounds and a chart. It uses the ETS function underneath, and it lets you set the end date, confidence level, seasonality and how to fill missing points.

Data Analysis ToolPak adds regression, moving average and exponential smoothing tools, if you enable the add-in.

Setting up the data

1. A timeline with regular intervals. Dates must be evenly spaced: every month, week or day. FORECAST.ETS needs a consistent step, and will tolerate some missing points (it can interpolate), but irregular spacing leads to errors.
2. A clean numeric history. Remove or adjust one-off events that you do not want the method to repeat, such as a stockout month or a large one-time order. Document each adjustment.
3. Enough history. For seasonal patterns, at least two full cycles is the minimum, and three or more is better. With less, the seasonal estimate is unreliable.
4. The right level of aggregation. Monthly totals are more stable than daily noise. Choose the level that matches your decision.
5. Sorted dates. The timeline should be in order.

A worked example: simple exponential smoothing

To understand what ETS does, run the simplest version by hand. Exponential smoothing keeps a running level, updated each period as a blend of the new observation and the previous level: level = alpha × new value + (1 - alpha) × previous level. With alpha of 0.3 and a history of 100, 104, 98, 110 and 107 (hypothetical):

• Start: level 1 = 100
• After 104: 0.3 × 104 + 0.7 × 100 = 31.2 + 70 = 101.2
• After 98: 0.3 × 98 + 0.7 × 101.2 = 29.4 + 70.84 = 100.24
• After 110: 0.3 × 110 + 0.7 × 100.24 = 33 + 70.168 = 103.168
• After 107: 0.3 × 107 + 0.7 × 103.168 = 32.1 + 72.2176 = 104.3176

The forecast for the next period is the latest level, 104.3. A larger alpha reacts faster to new data and a smaller alpha smooths more. With alpha of 0.7, the same sequence ends at a level of about 106.5, because the latest observations carry more weight. Excel's FORECAST.ETS chooses its parameters automatically by fitting the history, and adds trend and seasonal terms. The hand calculation shows why the method adapts to change and why it lags a sudden shift.

A worked example: seasonal indices

Seasonality can be understood with a quarterly series. Suppose two years of quarterly sales are: year 1: 80, 100, 120, 100; year 2: 88, 110, 132, 110 (hypothetical). The average for year 1 is 100 and for year 2 is 110, so the grand mean is 105. The average for each quarter across the two years is Q1: (80 + 88) / 2 = 84; Q2: (100 + 110) / 2 = 105; Q3: (120 + 132) / 2 = 126; Q4: (100 + 110) / 2 = 105. The seasonal index is each quarter's average divided by the grand mean: Q1 84 / 105 = 0.80; Q2 105 / 105 = 1.00; Q3 126 / 105 = 1.20; Q4 105 / 105 = 1.00. The indices average to 1.0. Growth is 10 percent a year (from 100 to 110). If year 3 averages 120, the quarterly forecasts are 120 × the index: 96, 120, 144 and 120. Check: 120 × 0.80 = 96; 120 × 1.20 = 144. FORECAST.ETS does something similar in a more refined way, estimating level, trend and seasonal factors together and updating them with weights. The lesson is that seasonal forecasting combines an underlying level and trend with a repeating pattern, and that you can check what the tool does with a simple calculation.

Using the functions

For monthly data with dates in A2:A37 and values in B2:B37, a forecast for a date in cell D2 is:

=FORECAST.ETS(D2, $B$2:$B$37, $A$2:$A$37, 12, 1, 1)

where 12 is the seasonality (months), 1 fills missing points by interpolation and 1 aggregates duplicates by averaging. Setting seasonality to 1 turns it off, and leaving it blank or at 1 versus the automatic setting changes the result, so check the documentation in your version. For a 95 percent confidence interval around the same forecast, use =FORECAST.ETS.CONFINT(D2, $B$2:$B$37, $A$2:$A$37, 0.95, 12, 1, 1). The forecast plus and minus the interval gives the range. To check the seasonal length that Excel detects, use =FORECAST.ETS.SEASONALITY($B$2:$B$37, $A$2:$A$37).

For a quick forecast with the wizard: select the dates and values, choose Forecast Sheet, pick the end date, set the confidence level, and either leave seasonality on automatic or set it manually when you know the pattern. Review the chart. If the forecast looks implausible, such as a steep trend continuing forever, adjust.

Test the accuracy on a holdout

Never trust a forecast tool on faith. Reserve the last six periods of history, fit on the earlier data and compare. Suppose the actual values for the six held-out months were 210, 225, 198, 240, 232 and 250, totaling 1,355, and the forecasts were 205, 220, 210, 232, 236 and 244, totaling 1,347 (hypothetical).

• Absolute errors: 5, 5, 12, 8, 4, 6, totaling 40
• WAPE: 40 / 1,355 = 2.95 percent
• Bias: (1,347 - 1,355) / 1,355 = -0.59 percent, a slight under-forecast

Check: 210 + 225 + 198 + 240 + 232 + 250 = 1,355; 205 + 220 + 210 + 232 + 236 + 244 = 1,347; 5 + 5 + 12 + 8 + 4 + 6 = 40. Compare with simple alternatives, such as last year's same month, or last year's value times a growth factor. If the ETS forecast does not beat the simple alternative, use the simple one. See operations KPIs for more on forecast error measures.

Adjusting for what the history cannot know

A statistical baseline extends the past. Planned price changes, new customers, product launches, promotions and supply constraints need judgment. Layer adjustments on top of the baseline in separate rows, so that they are visible: baseline, plus promotion uplift, plus new account, minus discontinued item. Record the reason for each. Over time, measure whether the adjustments improved accuracy.

Limitations

1. One series at a time. The functions do not learn across items or use outside drivers such as price.
2. Automatic choices can surprise. Seasonality detection can be wrong on short or noisy data. Check it.
3. Intermittent demand. Items with many zero periods are poorly handled. Use other methods.
4. Scale. For hundreds of items, building and maintaining workbooks becomes laborious.
5. Structural breaks. After a major change such as a lost customer, the history before the break misleads.
6. Over-confidence in intervals. Confidence bands assume the pattern continues.

When these limits bite, consider specialized forecasting software or scripting. See machine learning for forecasting.

Common mistakes

1. Irregular timelines.
2. Too little history for seasonality.
3. Not testing on a holdout.
4. Forecasting at the wrong level of detail.
5. Ignoring outliers and one-time events.
6. Forgetting to document adjustments.
7. Presenting a single number with no range.

FAQs

What is FORECAST.ETS in Excel?

A function that forecasts a future value using exponential triple smoothing, which estimates level, trend and seasonality from historical data.

What is the difference between FORECAST.LINEAR and FORECAST.ETS?

FORECAST.LINEAR fits a straight line and ignores seasonality. FORECAST.ETS adapts to recent data and can capture seasonal patterns.

How much history do I need?

For seasonal data, at least two full cycles, and preferably three or more.

Can Excel produce a forecast range?

Yes. FORECAST.ETS.CONFINT and the Forecast Sheet give confidence intervals.

Is Excel forecasting accurate enough for business use?

Often, as a baseline, if tested on a holdout and compared with simple alternatives. For many series or outside drivers, consider specialized tools.