Copilot in Excel: How to build ARIMA forecasts with Python

ARIMA (short for AutoRegressive Integrated Moving Average) is a classic statistical model for time series forecasting. It works by combining three elements: autoregression (using past values to predict future ones), integration (removing trends to make data stationary), and moving averages (capturing patterns in forecast errors).

For analysts, it’s valuable because it turns historical data into actionable forecasts, helping answer questions like “What’s likely to happen next month?” or “How will sales look next quarter?”

Excel does have built-in forecasting models, such as exponential smoothing, but it does not include ARIMA. Implementing ARIMA in traditional Excel requires complex workarounds or external tools, making it impractical for most users.

With Python and Copilot in Excel, this changes. Instead of wrestling with formulas or switching to another application, you can describe your goal in plain language and let Copilot generate the Python code for you. Python handles the statistical heavy lifting while Copilot explains each step. This makes ARIMA forecasting far more accessible and transparent than it’s ever been for Excel users.

In this post, we’ll walk through an ARIMA forecast on the famous airline passengers dataset, which tracks monthly passenger totals from 1949 to 1960. Follow along with the exercise file below.

 

Explanation of ARIMA

First, we’ll have Copilot clearly explain the ARIMA model components and visualize the data with this prompt:

Explain the components of an ARIMA model and describe how each part captures different aspects of time series data. Then plot the data to visualize trends and seasonality.

Copilot gives us a clear, concise summary:

Checking the data’s suitability for ARIMA

It’s good to have a clear game plan in place if we decide to move forward with ARIMA. But how do we know if ARIMA is actually a good choice for our specific dataset? That’s exactly what we’ll ask Copilot next, with this prompt:

“Evaluate the suitability of this dataset for ARIMA forecasting by assessing its characteristics, and discuss whether alternative models should be considered based on the data’s properties.“

This explanation is clear and helpful, pointing out important details. Specifically, Copilot notes the strong autocorrelation, meaning past values significantly influence current observations. It also identifies the presence of seasonality and non-stationarity. Non-stationarity means the statistical properties, like mean and variance, change over time… something ARIMA explicitly addresses through differencing.

Copilot also rightly suggests that because of the clear seasonal pattern, we might want to consider a Seasonal ARIMA (SARIMA) or even an Exponential Smoothing model, both of which explicitly handle seasonality. This highlights the importance of carefully matching our forecasting model choice to the unique characteristics of our data.

Checking for stationarity and performing seasonal adjustments

At this point, Copilot has shown us that our dataset has strong seasonal patterns, which could make forecasting challenging if left unaddressed. With this next prompt, our goal is to break down the concept of seasonal differencing into clear, non-technical steps. This helps Excel users grasp exactly how adjusting for seasonality makes the dataset “stationary” for ARIMA to handle effectively.

“Explain what seasonal adjustments mean for this dataset and then apply a seasonal difference to the data step-by-step. Tell me how this helps ARIMA in a way a non-technical Excel user can follow.“

As Copilot explains, seasonal adjustments essentially mean removing regular, predictable patterns such as spikes in sales every holiday season from your data. It does this by subtracting each month’s value by the value from the same month the previous year. By doing this, the repeating seasonal pattern disappears, leaving behind clearer information about trends and unpredictable changes.

Creating the ARIMA forecast

Next, we’ll prompt Copilot to build the model. As we do this, it’s important to clearly see how closely our model’s predictions match actual historical data. We’ll use the following prompt:

“Fit a simple ARIMA model to the data. Visualize and explain the model summary in simple language. Focus on what Excel users need to know to see if the model is good.“

Copilot’s output is exactly what we want: clear, practical, and visual. The dashed orange line (predictions) closely tracks the blue line (actual data), which means our ARIMA model is accurately capturing the pattern in the historical data. This visual check is crucial because it gives Excel users a quick and intuitive way to judge the quality of the ARIMA forecast without needing to dive deeply into complex statistical metrics.

Visualizing the ARIMA forecast

Since this initial check looks promising, we might next explore using Copilot to evaluate forecast accuracy in greater depth.

Recall earlier, to make our dataset suitable for ARIMA, we performed seasonal differencing. While differencing helped us make the data stable enough to accurately forecast, it also transformed the data away from its original units, making our predictions harder to interpret directly. That’s why it’s essential we now reverse this step, restoring the forecasts back to their original scale, such as actual monthly passenger counts. This way, Excel users can directly compare forecasts to historical data.

We’ll use the following prompt to have Copilot clearly handle this step and complete the forecast:

“Use my fitted ARIMA model to forecast the next 12 months of the original (non-differenced) dataset. After forecasting, reverse the seasonal differencing to return the forecasted values to their original scale. Plot these forecasted values alongside the historical data (on its original scale), clearly labeling actual vs. forecasted values. Explain the forecast in a way that Excel users can easily understand.“

Copilot’s visual clearly shows the forecast in the familiar context of our original historical passenger counts, making it easy for Excel users to see how the future predictions naturally follow the historical pattern. By reversing the seasonal differencing step, users can confidently interpret forecasts in practical terms, quickly gauging expected trends and seasonal behaviors going forward.

Comparing ARIMA to Holt-Winters visually

We’ve successfully created forecasts with our ARIMA model. But to make sure we’re using the best model possible, let’s compare ARIMA to another popular forecasting method—Exponential Smoothing (specifically the Holt-Winters approach)—which is particularly good at handling clear trends and seasonal patterns like we have in this dataset.

Here’s the prompt we’ll give Copilot to do that:

“Now fit an exponential smoothing (Holt-Winters) model with a 12-month seasonal period. Plot its forecast on the same chart as the ARIMA forecast and the actual data so they’re easy to compare. Briefly explain in plain language which model appears to fit better and why that might be the case for this dataset.“

If we look closely at the visualization, both forecast lines (ARIMA and Holt-Winters) appear very similar and closely match historical patterns. While Copilot’s explanation gently nudges us toward Holt-Winters—primarily because Holt-Winters explicitly handles trend and seasonality without requiring differencing—it’s tough to confidently pick the “best” model by eye alone.

Comparing ARIMA to Holt-Winters through accuracy metrics

Since these forecasts look pretty close, we can’t make a definitive choice based solely on visual comparisons. To choose confidently, we should dive into summary metrics (such as mean absolute error or mean squared error) next. These numerical measures will clearly quantify which model provides the most accurate predictions.

“Evaluate my forecasts by calculating key accuracy metrics like RMSE (Root Mean Squared Error), MAE (Mean Absolute Error), and MAPE (Mean Absolute Percentage Error). Explain each metric simply, focusing on what it means for assessing forecast quality. Then summarize whether my models provide reliable forecasts based on these results.“

Copilot’s summary metrics give us a precise, easy-to-follow comparison. RMSE, MAE, and MAPE all indicate forecast accuracy—the smaller, the better. While visually both models seemed similar, these numbers clarify that ARIMA actually provides slightly more precise forecasts in this case.

This step highlights to Excel users that accuracy metrics can uncover subtle differences between forecasting methods, helping them confidently select the best model for their data.

Conclusion

Working through ARIMA forecasting step by step shows how each component of the model contributes something valuable. Autoregression lets past values guide our predictions, integration helps manage trends and keep forecasts realistic, and moving averages handle unexpected fluctuations smoothly. ARIMA combines these elements effectively, making it versatile for various datasets with clear historical patterns.

Yet as we’ve seen, exploring alternative methods such as Holt-Winters exponential smoothing provides additional context. Holt-Winters explicitly manages trends and seasonality, often making it an excellent choice for data with strong repeating patterns. Evaluating these models side by side underscores the value of testing multiple forecasting techniques. Accuracy metrics like RMSE, MAE, and MAPE further highlight subtle but meaningful differences between models, guiding more confident model selection.

The next logical steps in your forecasting journey include experimenting with more sophisticated variations. Tools like Prophet or even machine learning methods such as gradient boosting or neural networks might also be appropriate when the data becomes particularly complex or when traditional methods fall short. Many of these advanced approaches remain easily accessible directly in Excel through Python and Copilot integration.

By expanding your toolkit to include methods like ARIMA and Holt-Winters, you gain deeper insight into the strengths and limitations of each forecasting technique, enabling you to make better-informed decisions tailored specifically to your data challenges.

The post Copilot in Excel: How to build ARIMA forecasts with Python first appeared on Stringfest Analytics.