As analysts, we’re frequently tasked with explaining past data and predicting future outcomes. Building a forecast can be invaluable for this purpose. Excel offers several methods for creating forecasts, but Forecast Sheet stands out as the most powerful and user-friendly option, requiring minimal effort.
Let’s see it in action using housing starts data from the St. Louis branch of the Federal Reserve Bank.
Availability
Forecast Sheet is available in Excel for Microsoft 365 along with the last several static releases of Office. Get the full list here.
Assumptions of forecast sheet
Like with other uses of AI for Excel, it’s important to structure your data in a consistent format. In particular, Format Sheet requires consistent spacing between time periods. And although it can handle missing values and duplicates, better results are achieved if you resolve these issues in the data before feeding them into Forecast Sheet. Forecast Sheet will do its best to detect these issues before actually building the model.
Creating the forecast
To create the forecast, click anywhere in the data and go to Data > Forecast Sheet:
Customizing the forecast
Before inserting the forecast, you have a couple of customization options.
Customizing the forecast
Forecast Sheet is designed to work effortlessly, requiring minimal forecasting expertise or intervention. However, if you want to utilize additional options, just click on “Options.”
This will let you adjust the time horizon, tweak your seasonality, and more. To learn more about the customization options, check out the documentation.
Customizing the visualization
At the top of the menu, you can alsochoose between creating a line chart or a column chart:
When to use which? The default option, a line chart, is effective when data points follow a natural flow or have a continuous progression. It is ideal for revealing trends or fluctuations clearly. Conversely, a column chart is more suitable when you want to emphasize individual data points rather than the continuous progression of a trend.
Methods used in Forecast Sheet
The Forecast Sheet feature in Excel uses a version of the Exponential Triple Smoothing (ETS) algorithm. This method smooths out historical data by applying exponentially decreasing weights to past observations, giving more influence to recent data while still considering older points. It’s designed to detect and incorporate trends and seasonal patterns, making it a robust choice for time series forecasting when data exhibits recurring cycles or consistent growth patterns.
The algorithm automatically calculates seasonality and confidence intervals, which Excel then uses to generate a forecast table and chart, simplifying the process for users without requiring deep statistical knowledge.
This method works best at handling datasets with clear seasonal patterns, such as monthly sales or yearly weather data, and can manage up to 30% missing data through interpolation, enhancing its flexibility. However, its reliance on exponential smoothing means it assumes past patterns will continue, which can falter if sudden shifts or external factors disrupt the trend. It’s less effective for datasets with irregular or non-linear behavior, and its automated nature limits customization, potentially oversimplifying complex scenarios where manual parameter tuning might yield better results.
The Forecast Sheet is best suited for small to medium-sized businesses or individuals analyzing straightforward time series data, like retail sales, website traffic, or inventory levels, where seasonal trends are evident and historical consistency is a reasonable assumption. For volatile or sparse datasets, or those requiring advanced modeling beyond ETS, users might find it lacking, and more specialized tools or manual forecasting methods could be more appropriate. Its simplicity and integration into Excel make it a practical starting point for users needing quick, reliable insights without diving into advanced statistical methods.
Interpreting the results
Forecast Sheet table
When you have finished specifying the output of your forecast, click Create and you should see that your source data gets converted to a table along with some additional calculated columns:
Specifically, you will see a forecast column, along with estimated lower and upper confidence bounds. To learn more about the significance of confidence intervals, check out my book Advancing into Analytics: From Excel to Python and R.
Forecast Sheet chart
You should also see your forecast as the specified line or column chart, along with the confidence intervals.
In this case, the main forecast suggests that housing starts are expected to continue increasing over the next several years. However, the confidence interval serves as a reminder that the range of possibilities includes a more volatile set.
Is this really AI?
While Forecast Sheet is a remarkable Excel product, it’s arguably not exactly AI.
Forecast Sheet uses exponential smoothing, a popular time series forecasting approach, to develop a forecast. Exponential smoothing shares some AI-like traits with adaptability and learning from historical data for predictions, as well as predictive capabilities and real-world applications. Its fine-tuning potential aligns with incremental improvement in AI techniques.
However, it lacks adaptability, relying on fixed formulas, and lacks explicit learning mechanisms, cognitive abilities, generalization, and abstraction usually seen in AI algorithms. As a well-established statistical method predating AI, its core principles do not involve AI-specific methodologies.
That being said, Forecast Sheet’s ability to generate complex forecasts, provide compelling visualizations, and identify potential data issues before model building brings it close to an AI application.
Compare your forecast to mine below:
Do you build forecasts in Excel or another tool? If in Excel, do you prefer Forecast Sheet or something else? Let me know in the comments.
Leave a Reply