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.
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