The Challenge of Prediction
Whether you manage a small retail store or coordinate enterprise supply chains, accurately predicting future demand is one of the most critical aspects of business. If you order too much inventory, you waste capital on storage; if you order too little, you lose sales to stockouts.
Traditionally, managers relied on basic moving averages or “gut feelings.” But real-world data is rarely a straight line. Sales fluctuate based on seasons—ice cream sells better in July, and heavy coats sell better in November.
Microsoft Excel includes a highly sophisticated, built-in machine learning algorithm designed specifically for this type of data: FORECAST.ETS.
ETS stands for Exponential Smoothing. Unlike a simple average, the ETS algorithm analyzes historical data, identifies the overarching upward or downward trend, and crucially, detects recurring seasonal patterns (seasonality) to project a highly accurate mathematical forecast.
Step 1: Structuring Your Data
The FORECAST.ETS function is incredibly strict about how your data is formatted. If your data is sloppy, the function will fail and return a #NUM! error.
You must have two distinct columns:
- A Timeline (X-axis): This must be a column of dates (e.g., Jan 1, Feb 1, Mar 1) or sequential numbers. Crucially, the step between each data point must be identical. You cannot have one row represent a day, the next row represent a week, and the next row represent a month. They must be evenly spaced.
- The Values (Y-axis): This is your historical data (e.g., total sales revenue, units sold, website traffic).
Let’s assume your Timeline (Dates) is in cells A2:A25 (representing 24 months of past data), and your Values (Sales) are in cells B2:B25.
Step 2: Setting up the Prediction Target
You want to predict the sales for the next month, which will be in cell A26.
In cell A26, type the target date you want to predict (e.g., “Jan-2024”).
Step 3: Writing the FORECAST.ETS Formula
Now, click on cell B26 (the blank cell next to your target date). This is where the prediction will appear.
The syntax for the function is:
=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
For most users, the last three arguments are optional, as Excel’s default automatic settings are excellent.
Type the following formula:
=FORECAST.ETS(A26, B2:B25, A2:A25)
Breaking down the formula:
- A26: The date in the future we want to predict.
- B2:B25: The historical sales numbers the algorithm will study.
- A2:A25: The historical dates corresponding to those sales numbers.
When you press Enter, Excel will instantly analyze the past 24 months, detect the seasonal peaks and valleys, calculate the overall growth trajectory, and output a predicted sales number for January 2024.
Step 4: Managing Seasonality
By default, Excel automatically attempts to detect the seasonal cycle. If your data is monthly, it usually detects a 12-month cycle. However, if your data is noisy, the algorithm might get confused.
If you explicitly know the cycle length, you can force Excel to use it by adding the 4th argument. For example, if you have daily data and you know sales spike every weekend, your cycle is 7 days. If you have monthly data and sales spike every winter, your cycle is 12.
To force a 12-month seasonality, adjust the formula like this:
=FORECAST.ETS(A26, B2:B25, A2:A25, 12)
Step 5: Visualizing the Forecast
While looking at a single predicted number is helpful, seeing the trend visually is much more powerful for presentations.
Instead of manually dragging the formula down, Excel has a dedicated wizard that builds a chart for you.
- Highlight your entire historical dataset (
A1:B25). - Go to the Data tab on the Ribbon.
- In the Forecast group, click Forecast Sheet.
A window will pop up showing a beautiful line chart. The blue line represents your historical data, and a thick orange line represents the FORECAST.ETS prediction extending into the future. Furthermore, Excel provides thinner orange lines above and below the prediction, representing the 95% Confidence Interval (the statistical margin of error).
Click Create, and Excel will automatically generate a brand new worksheet containing the mathematically perfect forecasting formulas and the professional presentation chart, saving you hours of manual calculation.