Excel FORECAST.ETS: A Practical Demand Forecasting Guide

How to build better demand forecasts, avoid common Excel forecasting traps, and know when to move beyond exponential smoothing.

Demand forecasting is one of the most important activities in supply chain planning. A reliable forecast supports inventory availability, purchasing decisions, production scheduling, and working capital management. An unreliable forecast can create the opposite: excess inventory, stockouts, unnecessary purchasing, and unrealistic supply plans.

Many businesses still rely on simple moving averages, historical sales comparisons, or manually adjusted Excel forecasts. These methods are useful, but they often fail to capture recurring seasonal patterns and evolving demand trends.

Microsoft Excel offers a more sophisticated alternative: FORECAST.ETS.

Based on exponential triple smoothing, this function can model historical demand levels, trends, and seasonality without requiring dedicated statistical software.

However, using FORECAST.ETS successfully requires more than entering a formula. Understanding its limitations is particularly important for demand planners dealing with irregular orders, project-based demand, or products with limited sales history.

In this article, we will explore:

1. How FORECAST.ETS works
2. Its strengths and limitations
3. Six practical rules for reliable Excel forecasting
4. How to measure and improve forecast accuracy
5. When to use alternative forecasting models
6. How Excel compares with Python and enterprise planning platforms

1. What Is FORECAST.ETS?

FORECAST.ETS is a built-in Excel function that predicts future values using historical time-series data.

ETS stands for Error, Trend, and Seasonality.

Excel implements the AAA version of ETS, representing an additive error, additive trend, and additive seasonal structure.

A simplified representation is:

Forecast = Level + Trend + Seasonal Effect

The level captures the underlying demand baseline, the trend represents the direction of demand over time, and the seasonal component captures recurring fluctuations.

For example, consider a manufacturer selling industrial consumables:

• Average monthly demand is approximately 5,000 units.
• Demand is gradually increasing.
• Sales regularly peak during the final quarter of the year.

A simple moving average may smooth out the seasonal peak. A linear trend may capture growth but overlook seasonality.

FORECAST.ETS attempts to model these characteristics together.

Basic Excel syntax

=FORECAST.ETS(target_date, values, timeline,
              [seasonality], [data_completion],
              [aggregation])

The optional seasonality settings are especially useful:

• 1: Automatically detect seasonality (default).
• 0: Disable seasonality.
• 12: Specify a 12-month seasonal cycle for monthly data.
• 52: Specify a 52-week cycle for weekly data.

For a monthly dataset with historical values in B2:B37, dates in A2:A37, and the next month in A38:

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

This requests a forecast using a 12-month seasonal cycle.

Important: Specifying a seasonal cycle does not guarantee a reliable seasonal forecast. Excel needs sufficient history to estimate that pattern, and the pattern itself must be meaningful.

Microsoft recommends at least two complete seasonal cycles when manually specifying seasonality.

2. Advantages of FORECAST.ETS

Advantage 1: Accessible and easy to implement

Unlike statistical forecasting tools such as Python’s statsmodels, Excel does not require programming knowledge.

A demand planner can build a forecast directly from an existing sales report.

This makes FORECAST.ETS particularly suitable for:

• Small and medium-sized businesses
• Ad-hoc demand analysis
• Individual SKU forecasting
• Excel-based supply planning
• Forecasting prototypes before ERP or planning-system implementation

The function is available in supported desktop versions of Excel, including Microsoft 365 and Excel 2024, but not Excel for the web.

Advantage 2: Automatic seasonality detection

Many products exhibit recurring demand patterns.

Examples include construction materials affected by seasonal project activity, consumer products with holiday peaks, and industrial supplies with annual maintenance cycles.

Using:

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

Excel attempts to identify the seasonal pattern automatically.

This reduces the need to manually define the forecasting model.

However, automatic detection should always be validated against historical demand and business knowledge.

Advantage 3: Handles missing observations

Excel can accommodate up to 30% missing data points in the timeline.

The default data_completion=1 estimates missing observations using neighbouring values.

Alternatively:

=FORECAST.ETS(A38,$B$2:$B$37,$A$2:$A$37,1,0)

The 0 instructs Excel to treat missing observations as zeros.

This distinction matters significantly in supply chain forecasting.

Missing data does not necessarily mean zero demand.

A missing observation might reflect a reporting failure, while an actual zero could represent a period without customer orders.

Incorrectly treating these two situations as equivalent can distort forecasts.

Advantage 4: Built-in diagnostic functions

Excel provides companion functions for evaluating the forecast.

For example:

=FORECAST.ETS.SEASONALITY(B2:B37,A2:A37)

This reveals the seasonality Excel identifies rather than leaving the planner to assume that a seasonal model has been applied.

These supporting functions make Excel more useful for investigating forecast behaviour.

3. Limitations and Common Pitfalls

Despite its accessibility, FORECAST.ETS is not suitable for every demand pattern.

Limitation 1: Additive seasonality

Excel uses an additive ETS structure.

Suppose monthly demand increases from 1,000 units to 10,000 units.

In some businesses, seasonal fluctuations increase proportionally with total sales. A 20% seasonal peak could represent an additional 200 units initially but 2,000 units after growth.

A multiplicative seasonal model can represent this proportional behaviour more naturally.

Excel’s FORECAST.ETS does not allow users to select a multiplicative ETS model.

For rapidly growing businesses with proportional seasonal variation, Python’s Holt-Winters implementation may offer greater flexibility.

Limitation 2: Negative demand forecasts

Demand cannot normally be negative, but an additive forecasting model can produce negative values.

Imagine a declining product:

If the downward trend continues mathematically, the forecast may fall below zero.

A result of -25 units has no meaningful interpretation as a purchasing requirement.

For operational reporting, a non-negative adjustment can be applied:

=MAX(0,FORECAST.ETS(A38,$B$2:$B$37,$A$2:$A$37,1))

This ensures the output cannot fall below zero.

However, flooring the forecast does not fix a poorly selected forecasting model. A consistently negative raw forecast is a warning that the model or underlying demand assumptions need to be reviewed.

Limitation 3: Short historical datasets

Annual seasonality is difficult to estimate using only one year of observations.

For weekly forecasting, a 52-week cycle ideally requires at least 104 weeks of historical data.

Even two years of history may be insufficient when sales are highly volatile or affected by exceptional events.

Forcing a seasonal setting into a short dataset does not create missing statistical evidence.

Limitation 4: Irregular and intermittent demand

This limitation is especially relevant in industrial B2B distribution.

Consider a specialized stainless steel fastener:

The series contains long periods of zero demand interrupted by large orders.

An ETS model may interpret these transactions as changes in level, trend, or seasonality, even though the underlying demand is driven by individual projects.

This can produce recurring forecasts for demand that is not genuinely recurring.

For these products, intermittent-demand forecasting or project-based planning may be more appropriate — we cover that in the Syntetos–Boylan demand classification template.

Limitation 5: No direct explanatory variables

FORECAST.ETS relies on the historical demand series.

It does not directly accept explanatory variables such as:

• Planned promotions
• Customer project schedules
• Product price changes
• Construction activity
• Weather conditions
• Known customer contract volumes

If demand is driven by these factors, statistical extrapolation alone may not provide a sufficient forecast.

4. Six Practical Rules for Reliable Forecasting in Excel

Rule 1: Lock the historical data range

This is one of the easiest mistakes to make when extending forecasts across future periods.

Suppose:

• B2:FW2 contains historical weekly period numbers.
• B3:FW3 contains actual demand.
• FX2 contains the next forecast period number.

Use:

=FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,52)

The dollar signs lock the historical ranges.

When copying the formula to the right, the future target period changes, but the actual historical observations remain unchanged.

Without absolute references, the input ranges may shift and eventually include forecast outputs rather than actual demand.

That creates a recursive forecasting problem: later predictions depend on earlier predictions being treated as observed history.

Best practice: Keep actual demand and forecast demand in clearly separated ranges.

Rule 2: Validate seasonal patterns before overriding Excel

For weekly data, annual seasonality is often represented by 52 periods.

However, a 52-week cycle should not be applied automatically to every SKU.

First, inspect the detected seasonality:

=FORECAST.ETS.SEASONALITY($B$3:$FW$3,$B$2:$FW$2)

Then compare:

=FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,1)

Against:

=FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,52)

And:

=FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,0)

These represent automatic seasonality, a specified annual seasonal cycle, and no seasonality.

The correct choice should be determined through historical validation rather than assumptions.

For weekly data, remember that ISO calendar years sometimes contain 53 weeks. A fixed 52-period seasonal model may require calendar alignment and special-event adjustments.

Rule 3: Prevent negative forecasts

A practical operational formula is:

=MAX(0,FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,1))

If a fallback is needed:

=MAX(0,
 IFERROR(
   FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,52),
   FORECAST.ETS(FX$2,$B$3:$FW$3,$B$2:$FW$2,0)
 ))

This tries the specified seasonal model first, then falls back to a non-seasonal model if the first calculation returns an error.

It is useful as an error-handling mechanism, but it should not be used to hide underlying data-quality problems.

The fallback also does not detect a statistically poor forecast when Excel successfully calculates a number.

A better implementation includes a separate warning flag identifying which model was used.

Rule 4: Use a consistent numeric timeline

Many ERP reports export weekly periods in text format:

2026-W01
2026-W02
2026-W03

These labels should not be passed directly as the numeric timeline.

Instead, create a helper row:

1, 2, 3, 4, 5 ... 156, 157, 158

The first 156 observations can represent historical weeks, while the subsequent values represent future forecast periods.

This produces a consistent numerical sequence suitable for FORECAST.ETS.

Alternatively, use genuine Excel date values with a consistent interval.

Rule 5: Separate missing observations from genuine zero demand

This is especially important for industrial distributors.

Consider two different scenarios.

Scenario A: Missing sales data

The ERP system was unavailable for one week, and sales were not captured correctly.

This represents missing information requiring investigation or an appropriate data-completion method.

Scenario B: Actual zero demand

A customer did not order a particular industrial component during that week.

This is a genuine observed zero and should generally remain zero.

Replacing genuine zero demand with interpolated positive values may create artificial recurring demand and inflated inventory requirements.

Before forecasting, review whether zero sales indicate:

• Genuine lack of orders
• Stockouts or lost sales
• Delayed invoicing
• Data extraction errors
• Product discontinuation

These situations have very different forecasting implications.

Rule 6: Evaluate the forecast before using it for purchasing

A forecasting formula is not automatically a reliable planning model.

A demand planner should compare ETS against simpler alternatives using historical backtesting.

For example:

1. Use the first 24 months of a 36-month dataset to generate forecasts.
2. Predict the following three months.
3. Compare those forecasts with actual demand.
4. Repeat the exercise using multiple historical cutoff dates.
5. Compare errors against moving average, seasonal naïve, and other candidate models.

This process is called rolling-origin backtesting.

It is especially valuable because the most sophisticated model is not necessarily the most accurate.

A simple seasonal naïve forecast can outperform ETS when demand follows a stable annual pattern.

5. Measure Forecast Accuracy, Not Just Forecast Output

Forecasting models should be evaluated using measurable performance indicators.

Weighted Absolute Percentage Error (WAPE)

WAPE is useful for measuring aggregate forecast accuracy across multiple SKUs or periods.

WAPE = Sum of Absolute Forecast Errors / Sum of Actual Demand × 100%

Suppose actual demand totals 10,000 units and the sum of absolute forecast errors is 1,500 units.

WAPE is 15%.

Lower WAPE generally indicates better forecasting performance, although the measure becomes unstable when aggregate actual demand is very small or zero.

For a fuller comparison of metrics — including why MAPE misleads on slow-moving items — see A Practical Guide to Forecast Error Metrics.

Forecast Bias

Forecast bias indicates whether demand is systematically overestimated or underestimated.

Using the convention:

Bias = Sum(Forecast − Actual) / Sum(Actual) × 100%

A positive result indicates overforecasting.

A negative result indicates underforecasting.

Always document the sign convention because some organizations calculate bias in the opposite direction.

Forecast Value Added (FVA)

Forecast Value Added measures whether an activity or model actually improves accuracy compared with a benchmark.

For example:

These are hypothetical results.

In this example, ETS improves on the simple benchmarks, but the planner’s manual adjustments make performance worse.

The lesson is important:

Forecast accuracy should improve because of a decision, not simply because more effort was invested in forecasting.

The same principle applies when introducing machine learning or enterprise forecasting software.

A more advanced system should demonstrate measurable improvement in forecast accuracy, bias, or business outcomes.

6. Do Not Confuse Forecast Confidence Intervals with Safety Stock

Excel provides:

=FORECAST.ETS.CONFINT(A38,$B$2:$B$37,$A$2:$A$37,0.95,12)

This calculates the radius of the model’s 95% forecast uncertainty interval at the specified future period.

If the point forecast is 500 units and the returned interval radius is 120 units:

• Forecast: 500 units
• Lower interval: 380 units
• Upper interval: 620 units

These values provide an indication of forecast uncertainty under the model’s assumptions.

However, the upper confidence interval is not a safety stock recommendation.

Safety stock depends on additional operational factors, including:

• Target service level
• Demand variability during replenishment lead time
• Lead-time variability
• Forecast errors
• Inventory review frequency

For a simplified continuous-review system with independent, normally distributed period demand and constant lead time:

Safety Stock = Z × σ × √L

Where:

• Z represents the chosen service-level factor.
• σ represents standard deviation of demand per period.
• L represents lead time measured in the same periods.

For forecast-driven inventory systems, it may be more appropriate to estimate the uncertainty of total demand across the replenishment protection period.

This is not necessarily the same as multiplying a single-period forecast error by the square root of lead time.

A practical approach is to use ETS for baseline forecasting, then independently calculate inventory requirements using the appropriate lead-time demand distribution and service-level policy.

Forecasting and inventory optimization are connected activities, but they are not interchangeable. If your safety stock numbers have been quietly drifting, this piece on why your safety stock is lying to you is the natural next read.

7. Alternative Forecasting Methods

Different demand patterns require different forecasting approaches.

A. Native Excel alternatives

1. FORECAST.LINEAR

Useful for estimating a linear trend when strong seasonality is absent.

=FORECAST.LINEAR(A38,$B$2:$B$37,$A$2:$A$37)

Suitable for relatively stable trend-driven demand, although extrapolated trends should be treated cautiously.

2. Simple Moving Average

For the most recent six observations:

=AVERAGE(B32:B37)

Moving averages are easy to interpret and can be effective for stable, high-frequency demand.

However, they generally lag behind sudden changes in demand.

3. Weighted Moving Average

A weighted moving average assigns greater importance to more recent observations.

=SUMPRODUCT(B32:B37,{1;2;3;4;5;6})/21

This example assumes the observations run vertically and assigns progressively higher weights to more recent periods.

The weighting structure should be validated rather than selected arbitrarily.

4. Excel Forecast Sheet

Available through:

Data → Forecast Sheet

This feature generates forecast tables, visualizations, and uncertainty intervals using Excel’s ETS forecasting functionality.

It is useful for quick analysis and communicating results to nontechnical stakeholders.

B. Python statistical and machine-learning methods

Python provides substantially more flexibility for forecasting at scale.

1. Holt-Winters Exponential Smoothing

The statsmodels library supports additive and multiplicative seasonal specifications.

It is suitable when historical demand exhibits identifiable level, trend, and seasonal components.

2. SARIMAX

Seasonal AutoRegressive Integrated Moving Average with Exogenous Variables.

SARIMAX extends time-series modelling by allowing explanatory factors, such as price changes, promotional events, or economic indicators.

It is particularly useful when business drivers can be quantified.

3. Croston and SBA

Croston’s method was developed for intermittent demand, where many periods contain zero demand.

The Syntetos-Boylan Approximation (SBA) addresses bias associated with the original Croston approach.

These methods are useful candidates for slow-moving spare parts, industrial components, and certain project-driven products.

However, Croston-style methods primarily estimate average demand rates and are not necessarily suitable for accurately predicting the timing of individual large orders.

4. XGBoost and LightGBM

Gradient-boosted machine-learning models can combine historical demand with additional features such as:

• Lagged sales
• Rolling averages
• Customer characteristics
• Promotional activity
• Product categories
• Calendar events
• Project pipeline indicators

These approaches can be effective when forecasting thousands of related products, particularly when useful cross-product information is available.

They also require careful validation to prevent future information from leaking into the training data.

5. Prophet and Time-Series Foundation Models

Prophet is an additive forecasting framework designed to model trends, seasonal effects, and holidays. It is useful for certain business time series but is not a pretrained foundation model.

Amazon Chronos is a family of pretrained time-series models capable of zero-shot forecasting.

More recent Chronos variants also support multivariate and covariate-informed forecasting.

These technologies are promising, but they should not automatically replace conventional statistical methods.

Performance depends on the dataset, forecast horizon, available explanatory variables, and operational requirements.

For highly intermittent demand, classical specialized methods may still provide a better baseline.

8. Enterprise S&OP and Demand Planning Platforms

As planning complexity increases, companies often move beyond Excel.

The primary advantage of an enterprise planning platform is not simply access to more sophisticated algorithms.

It is the ability to connect forecasting with inventory, supply constraints, financial planning, and cross-functional decision-making.

SAP Integrated Business Planning (SAP IBP)

SAP IBP supports integrated demand, inventory, supply, and sales and operations planning processes.

Its key advantage is connecting planning decisions across business functions and SAP-oriented enterprise environments.

Blue Yonder

Blue Yonder provides supply chain planning capabilities including demand forecasting, inventory optimization, and supply planning.

It is relevant to organizations managing large product portfolios and complex distribution networks.

Kinaxis

Kinaxis focuses on integrated supply chain orchestration, scenario analysis, and concurrent planning.

It is particularly useful where changes in demand, supply, inventory, and capacity need to be evaluated together.

o9 Solutions

o9 provides an integrated planning platform supporting demand, supply, commercial, and financial decision-making.

Its enterprise knowledge-graph architecture helps connect planning data and business relationships.

Enterprise platforms differ significantly in forecasting algorithms, optimization modules, integrations, licensing, and implementation scope.

Their value depends on configuration, data quality, planning-process maturity, and organizational adoption.

9. Forecasting Model Comparison

The most appropriate option depends on more than forecasting accuracy.

Organizations should consider the volume of data, product complexity, available skills, system architecture, and need for cross-functional collaboration.

10. Special Consideration: Industrial B2B and Project-Driven Demand

Demand forecasting in industrial distribution can be fundamentally different from forecasting in consumer goods.

A business supplying industrial fasteners, maintenance components, or engineered products may carry thousands of SKUs with highly uneven demand.

Some products sell every week.

Others are purchased only when a particular customer project requires them.

Applying ETS across the entire product portfolio can create misleading results.

Example: Project-driven fastener demand

Consider a specialized M20 stainless steel bolt.

A customer places a one-off order for 20,000 pieces to support a construction project.

Historical sales then show a large demand spike.

An ETS model may partially incorporate that spike into the forecast, even if no future project has been confirmed.

Purchasing against this forecast could create excess inventory.

The planning process should first distinguish repeatable consumption from project-specific requirements.

A more practical segmentation strategy

No single forecasting algorithm is suitable for every SKU.

A useful demand planning process begins with demand classification, followed by appropriate model selection.

ABC analysis can then help prioritize forecasting effort and inventory risk.

For example, high-value intermittent items may require close customer engagement and inventory-policy review rather than increasingly complicated time-series models.

Statistical forecasting versus business intelligence

For project-driven demand, a combined approach is often more effective:

Total Demand Plan = Baseline Recurring Demand + Risk-Adjusted Project Demand

Baseline demand can be estimated using statistical models.

Project demand can be built from confirmed orders, customer schedules, tenders, and qualified sales opportunities.

Where probabilities are used, they should be based on calibrated historical conversion rates rather than arbitrary sales confidence.

Care must also be taken to avoid double-counting project demand already reflected in historical baseline sales.

This approach connects quantitative forecasting with real business activity.

11. A Practical Forecasting Improvement Roadmap

For a business currently relying on Excel, improving forecasting does not necessarily require immediate investment in an enterprise planning platform.

A structured improvement process can deliver substantial value.

Stage 1: Establish a reliable demand dataset

Standardize dates, remove duplicate transactions, distinguish stockouts from genuine zero demand, and identify unusual project orders.

Stage 2: Segment the SKU portfolio

Use ABC classification together with demand variability and intermittency measures.

Separate smooth, erratic, intermittent, lumpy, and project-driven demand where appropriate.

Stage 3: Establish benchmark forecasts

Start with simple methods such as naïve, seasonal naïve, and moving average.

Add FORECAST.ETS where trend and seasonality justify its use.

Stage 4: Validate performance

Use rolling-origin backtesting, WAPE, bias, and Forecast Value Added.

Measure accuracy at the appropriate SKU, customer, and aggregate planning levels.

Stage 5: Automate model selection

As the product portfolio grows, use Python or supported planning-system functionality to compare suitable models automatically.

Stage 6: Integrate forecasting with supply planning

Translate the demand plan into inventory requirements while accounting for lead times, minimum order quantities, service targets, stock availability, and supplier constraints.

Stage 7: Introduce structured S&OP

Build a repeatable process for reviewing demand assumptions, supply risks, financial implications, and management decisions.

The objective is not to generate the most statistically sophisticated forecast.

It is to improve operational decisions.

Final Thoughts

FORECAST.ETS is an accessible and valuable forecasting tool for demand planners working in Excel.

It can capture trend and seasonal patterns without requiring advanced statistical programming, and its supporting functions provide useful diagnostics.

However, it should not be treated as a universal forecasting solution.

Its additive structure, limited support for explanatory variables, dependence on historical patterns, and weaknesses with intermittent or project-driven demand must be understood.

For industrial B2B environments in particular, the most effective approach is often to combine statistical forecasting with SKU segmentation, customer intelligence, and disciplined inventory planning.

The best forecasting model is not necessarily the most sophisticated one. It is the model that consistently produces useful, measurable improvements in planning decisions.

Excel ETS can be an excellent starting point. The next step is building a forecasting process capable of selecting, evaluating, and improving the right model for each demand pattern.

Further Reading

• Microsoft — FORECAST.ETS function
• Microsoft — Create a Forecast in Excel
• Microsoft — FORECAST.ETS.STAT function
• Microsoft — FORECAST.ETS.CONFINT function
• Statsmodels — Exponential Smoothing
• Amazon Science — Chronos: Learning the Language of Time Series
• Amazon Chronos — Official GitHub Project