Time Series Forecasting with ARIMA

Powered by Axcel: “No Coding” Data Science Studio in Excel

ARIMA model is a popular and widely used statistical method for time series forecasting. In this article, we show how you can instantly run the pre-built ARIMA model in Excel. For this purpose, you need to have access to Axcel cloud service through its Add-in.

Axcel is a cloud service for running advanced AI and ML solutions inside Excel. Axcel turns spreadsheets into a fully automated and no-coding Data Science Studio with data sharing, visualization, and execution of prebuilt, fine-tuned, and industry-specific ML and AI solutions.

The ARIMA function in Axcel, calculates or predicts future values based on historical data by using Autoregressive Integrated Moving Average (ARIMA) algorithm. The predicted value is a continuation of historical values. You can use this function to predict sales, revenue, inventory, or consumer trends. You can easily forecast, visualize, and even deploy the model at Axcel.

This function requires the data to be organized as a series of data observed at each point of time with a constant step between the different points such as monthly, weekly, or daily.


AXCEL.FORECAST.ARIMA(data, frequency, start, forecast, [deployment], [plot])

The AXCEL.FORECAST.ARIMA function syntax has the following arguments:

data Required. The historical values, for which you want to forecast. You may add external predictors as additional columns. In this case, your external predictor should extend until the end of forecast horizon.

frequency Required. Frequency of your data. 365 for daily, 52 for weekly, 12 for monthly, and 4 for quarterly data. If you do not know the frequency, you can set it as 0. In this case, Axcel automatically identifies the frequency of your data.

start start date of your data in “yyyy-m-d” format.

deployment Optional. You can define a deployment name to deploy your model. After deployment, you can use the deployed function in AXCEL.FORECAST.RUN function.

plot Optional. default is FALSE. When it is TRUE, Axcel produces the plot inside the sidebar. You can expand the plot and show it in your browser. Please note that no plot is produced when deployment is requested. Here is an example of the plot:

In this article, we show you how in a few minutes you can forecast the number of passengers of classic Box & Jenkins airline data (1949 to 1960) with Axcel’s ARIMA forecast function.

when you type =AXCEL.FORECAST.ARIMA in an Excel cell, the IntelliSense guides you through required and optional (shown in [] brackets) inputs:

In the example above, we have:

=AXCEL.FORECAST.ARIMA(A1:A145, 12, “2005-03-01”,12,,TRUE)

This means that our data is located at cell A1 through A145, it is monthly, i.e. frequency is 12, it starts from March 1, 2005 (2005-03-01), we would like to forecast for 12 months, we skip the deployment and request for plots. After running this command we have:

As shown, fitted values and forecasts are reported in the workbook. The first column is the date index, the second column is the actual values used for training of the model, and the next column is the forecast. Axcel also reports the forecast with 80% and 95% confidence to help the process of decision making.

In the console section in the sidebar, model performance, structure, and statistical indicators are reported for validation and transparency in the process. Here is an example:

-------- TIME SERIES Summary ---------
Series: y 

         ar1     ar2      ma1
      0.5960  0.2143  -0.9819
s.e.  0.0888  0.0880   0.0292

sigma^2 estimated as 132.3:  log likelihood=-504.92
AIC=1017.85   AICc=1018.17   BIC=1029.35

Training set error measures:
                   ME     RMSE     MAE       MPE     MAPE     MASE         ACF1
Training set 1.342299 10.84619 7.86754 0.4206976 2.800458 0.245628 -0.001248475

A template for this function is available here:

Sales ARIMA Example