In this video, we'll demonstrate how to construct seasonal adjusted time series, build an regARIMA model and project a forecast in Excel with the help of NumXL and the U.S. Census Bureau program "X-12-ARIMA"
Hello and welcome to the NumXL seasonal adjustment tutorial. In this video we'll demonstrate how to construct a seasonal adjusted time series, build a regARIMA model and project a forecast in Excel with the help of NumXL and the US Census Bureau program called X12 ARIMA.
For this tutorial we'll use a sample of the monthly International Airlines passengers data. Let's start!
First select an empty cell in your worksheet then in the numbered cell toolbar locate the X12 icon and click on it.
The X12 dialog box pops up. For input data, select the cell range and column C In the date start field, select the date of the earliest observation. Under prior adjustment let's change the transformation to none then uncheck the calendar adjustment for the intercept/constant, the Easter holiday effect and the trading day effect. Click on apply to generate the native script for the X12 program. Once done the open X12 SBC file button will be enabled. Click on this button to examine the generated script.
The notepad is launched with a loaded generated script. Note that this file was generated by NumXL using the options that we have selected in the dialog. This syntax and structure of the file is compliant with those of X12A program specifications.
Now let's examine the X11 filter options. The type is mult or multiplicative. The X11 options are set to defaults as specified by the X12A program. Close this file and let's go back to our dialog.
Let's explicitly run the program, but note that this step is not really needed because NumXL runs the program as soon as an output values requested by worksheet functions. For now click on Run X12A button.
Upon program completion a dialog box pops up. Click OK.
Now the output files are created and different dialog buttons are enabled. Let's open the error file to see if the program encountered any issues.
A warning of a trading day effect is present. Remember that we unchecked the prior adjustment for trading days.
Now open the output file of the X12A program.
This is a long and detailed text file. If you're not familiar with this format you can easily close it and use the NumXL functions.
There is an enormous amount of information here. The output is best explained in the X12 ARIMA documentation found on the US Census Bureau website.
Close the file. Click OK to exit the X12 wizard.
Now we have a unique identifier in the D1 cell. The ID starts with X12A underscore for easy differentiation. The part on the right side can change when we change the selected options, input data, or location of the cell in your worksheet. We are ready to retrieve the seasonal adjusted trend time series. In D5 type X12ACOMP in the formula bar then click on the FX button on the left.
The function argument dialog pops up.
In the first field let's reference the cell D1, then lock it for row and column movements.
In step, type the offset from the beginning of the sample data, for this tutorial we have a separate column for the index aka column A. Select the A5 cell and also lock this for column movement.
The component designates which output series we are targeting. For the final seasonal adjusted time series, type 1.
The corresponding value is now in D5. Copy the formula to the rows below it. Now we can plot the seasonal adjusted time series. Copy the cells formula in D5 to the neighboring cell on the right. Then select it and click on the FX button on the left of the formula bar.
Change the component from seasonal adjusted time series to seasonal factors. In order to do that let's launch the help file to get the full list of the components.
The function reference page is loaded. Now we see that the seasonal factor component type is four. Close the help file.
Type four under component then click OK.
Copy the formula to the rows below it. Now let's go back and change the options in the X12 dialog. Select the D1 cell where the unique identifier is shown and click on the X12 ARIMA icon in the NumXL toolbar.
The dialog box pops up preset with our latest selection. Let's now change the transformation to logarithmic. Then add all the regression variables. Hit apply to update the X12 script, now The unique identifier of the model is changed and the old files are deleted.
Let's examine the new model specification file. Note that under transform, the function log is present. Close the file.
Now let's run the program. Examine the error file for any warnings.
We have a warning of seasonal and trading day effect. Close the file, hit OK to exit the wizard.
The cell value is updated to reflect the new models options. The models output values in our worksheet are also automatically updated.
Move the graph to the right.
Now let's copy the model to a different cell.
First select the D1 cell. Then copy the cell value and formula to the clipboard. Select an empty cell on the right then paste the value in formula by pressing ctrl V. Note that the copied models identifier is not equal to the one in the D1 cell. This is to designate that those are two separate models.
Now copy the formulas in D5 and D6 into the empty cells on the right. The copied cell references the model in D1. To change that, update the first argument to reference the cell in G1. Copy the formula in G5 to H5 and update the component number to four. Now copy the formulas and G and H columns to all the rows below.
By now we have a second seasonal adjusted time series. Let's plot it along with the earlier trend. To demonstrate how the two models are independent we'll change the setting in the second model. Select G1 and click on the X12 icon in the tool bar.
Change the X11 filter mode to additive. Change the filter option to 3x9, click apply to generate a new script. Next examine the specification file.
The X11 section shows the new mode and option.
Run the program and examine the error log.
The X12A reported a problem. The time series is not long enough to war an adjustment for leap year. Let's remove the calendar adjustment, close the file.
Uncheck all the regression variables then click apply again to generate a new script. Now run the program. Examine the error log file.
And it looks like the program ran successfully with one warning about training days.
Examine the output file, quickly glance at different tests and analysis and close it.
Click OK and close the X12 dialog.
The two seasonally adjusted trends are not exactly on top of each other.
We are now ready to display the forecast of the regARIMA model. In the L27 cell type X12 A4 function then click on the FX button.
In the function arguments dialog, reference the model cell in G1. Lock it for row and column movements.
Under step, select the offset from the end of the sample data, also lock for column movement. In the return type, choose one for mean forecast, click OK.
Copy the formula to the right, select the copied cell and click on the FX button.
Change the return type to the upper limit or three.
Again copy the formula and change the return type to lower limit or two. Select the cells and copy the formulas to the rows below.
Now let's plot the confidence interval and the forecast mean.
That is it for now, thank you for watching!