Modelado y Pronóstico ARMA en Excel

Scene 1:

Welcome to a second ARMA modeling and forecast tutorial. In this video we'll demonstrate another example of how to use NumXL to construct an ARMA model and project a forecast. For our example we'll be using the following time series.

First let's examine the sample data summary statistics. The mean is significantly different from zero and there is no evidence of skew or of excess kurtosis. Nevertheless, the data failed the white noise test, so autocorrelation is evident.

Scene 2:

Next let's look at the ACF and PACF plots. Using the partial correlation or the PACF plot we guessed the order of the auto regressive component to be 1. Using the auto correlation or the ACF plot we guess the order of the moving average component to be either 1 or 2.

Scene 3:

Let's construct the two models and compare their goodness of fit.

First select an empty cell in your worksheet. Click on the ARMA icon in the toolbar. Select the input data range, which in our case is column B. The default model order is ARMA 1 1, so we'll leave it unchanged.

Next the output range will be by default the active cell, leave this unchanged as well. Click OK.

Scene 4:

The ARMA model has three parts, model parameters, goodness of fit, and residuals diagnosis. The parameters are set to quick none optimal values, so we need to calibrate the model before we can start using it. To calibrate select the cell on the top of the table and click on the calibrate icon.

Scene 5:

The Excel solver pops up, it's already initialized with the model's value, you don't need to do anything just click solve.

Scene 6:

The solver will try a different combination of parameters to maximize the LLF, it returns reporting success. Now click OK.

Scene 7:

The new optimal values are copied into the model parameters cell range. Now let's examine the residuals diagnosis table.

Scene 8:

The standardized residuals pass all tests and they look like a Gaussian noise. Next let's look at the other model possibility, ARMA 1 2. Again select an empty cell and invoke the ARMA wizard.

Scene 9:

Select the input cell range. Change the moving average order to two and click OK.

Scene 10:

The second model is displayed below the first one. Let's go ahead and calibrate the model. Select the cell at the top of the second model table and click on the calibration icon.

Scene 11:

Again the solver pops up with all its values initialized with the second model value. There's no need to change anything, so click solve.

Scene 12:

The solver optimizes the values for the second models parameters. Upon success a message box pops up with a report.

Scene 13:

After examining the standardized residuals of the second model, all tests passed as well, so which model should we use? The first one because although model 2 has a higher LLF, the AIC is also higher than model one. Model one has less parameters and this AIC is lower.

Now let's do a forecast using the first model. Select the header cell of the model and click on the forecast icon.

Scene 14:

The forecasting dialog box pops-up. For input range select the last few cells of the data set. For the forecast horizon select 12 steps. For the output range select an empty cell in your worksheet, this is where the forecast table will be written. Now click OK.

Scene 15:

The forecasts table includes the forecasts mean, error and the confidence interval.

Scene 16:

Now let's create a plot to display the forecast and its confidence interval.

Scene 17:

That is it for now, thank you for watching!