FORECAST.SPLINE

AXCEL.FORECAST.SPLINE function

Calculates or predicts a future value based on historical data by using a cubic smoothing spline algorithm. The cubic smoothing spline model is equivalent to an ARIMA(0,2,2) model but with a restricted parameter space which provides a smooth historical trend and a linear forecast function. The predicted value is a continuation of historical values. You can use this function to predict sales, revenue, inventory, or consumer trends.

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.

Syntax

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

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

data Required. The historical values, for which you want to forecast. If data has a header, data starts from the second row. Otherwise, the entire vector is considered for the input.

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 Required. start date of your data in “yyyy-mm-dd” format.

forecast Required. This is the number of periods which we would like to run the forecast.

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. Please note that the deployment name is case sensitive and should include alphabets, numbers, and non-repeating underline. You cannot use underline at the beginning or end of filename. For instance “abc-123” or “a-b-c-123” are allowed but “abc–123”, “abc-123-“, “-abc-123” or “abc-$123” are not allowed.Depends on your subscription, you can view and restrict access to the deployed model through Axcel web application. Please note for SPLINE, the forecast horizon in FORECAST.RUN function could not be longer than the horizon used in the training.

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:

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

In the example above, we have:

=AXCEL.FORECAST.SPLINE(A1#, 12, “2000-1-1”,6,,TRUE)

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

Please note that SPLINE function returns dates in Excel format. As a result you may see numbers instead of dates in data_index column. In this case, you need to change the type of column to “Date” from Excel Home menu.

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 --------- 
Forecast method: Cubic Smoothing Spline 
Model Information: 
$beta [1] 0.2985984 

$call splinef(y = y) 

Error measures: 
                     ME     RMSE     MAE       MPE     MAPE      MASE      ACF1 
Training set 0.06457252 39.95675 28.9321 0.2194395 10.06998 0.9032727 0.1308229 Forecasts: 
   Point Forecast      Lo 80     Hi 80      Lo 95     Hi 95 
Jan 2012 424.4109  373.20065  475.6211  346.09159  502.7301 
Feb 2012 433.2752  338.55110  527.9993  288.40720  578.1432 
Mar 2012 442.1395  292.57794  591.7011  213.40483  670.8742 
Apr 2012 451.0039  237.90147  664.1062  125.09188  776.9158 
May 2012 459.8682  175.78161  743.9547   25.39527  894.3411 
Jun 2012 468.7325  107.00479  830.4602  -84.48230 1021.9473 
Jul 2012 477.5968   32.13299  923.0607 -203.68132 1158.8750 
Aug 2012 486.4612  -48.40083 1021.3232 -331.53966 1304.4620 
Sep 2012 495.3255 -134.25270 1124.9037 -467.53126 1458.1822 
Oct 2012 504.1898 -225.13170 1233.5113 -611.21119 1619.5908

A template for this function is available here:

Sales SPLINE Example

Tags: