5

I am building a Box-Jenkins model in Excel using solver. The model is AR(2). The data that I have contains trend and seasonality both.

I know how to remove seasonality using seasonal indexes and add it back to the forecast. But, how do I handle trend? If I remove trend from the data, how should I add it back to the forecast?

Also, is the excel solver best way to find the AR parameters?

Stephan Kolassa
  • 95,027
  • 13
  • 197
  • 357
Abhishek
  • 95
  • 2
  • 4

4 Answers4

4

If you are at all familiar with R (if you're building time series models, you should be), check out the forecast package. It's designed to choose parameters for Arima as well as exponential smoothing models, and uses a solid methodology to do so. It will probably get you a lot farther than what you are building in excel, especially because it will also allow you to explore exponential smoothing models. The two functions you are interested in are 'auto.arima' and 'ets.'

/Edit: auto.arima can also be used to fit ARMAX models, which (if properly specified) can solve many of the problems identified by IrishStat.

Zach
  • 22,308
  • 18
  • 114
  • 158
  • 1
    Any automation that ignores the possibilities of Level Shifts, Local Time Trends, Seasonal Pules and/or One-Time Pulses should be reviewed very carefully as these kinds of structures in the error process "blind-side" identification strategies. – IrishStat May 03 '11 at 19:03
  • @IrishStat: Abhishek asked "is the excel solver best way to find the AR parameters?" I think using R in this case to find the AR parameters would be better than using excel. – Zach May 03 '11 at 20:17
  • You are right R would be the best of two bad choices. – IrishStat May 03 '11 at 20:21
  • Thanks @Zach and @IrishStat . I will be using R to get more reliable results. – Abhishek May 04 '11 at 05:16
3

The time series are usually decomposed into 3 parts, trend, seasonality and irregular. (The link gives 4 parts, but cyclical and seasonality are usually lumped together). Strictly speaking ARIMA type of models are only used for irregular part and by their design these model do not incorporate any trend (I am assuming that trend is some function which varies in time). So if you simply want to estimate AR(2) model no software will estimate the trend for you, since if it did, it would not be fitting AR(2) model.

To forecast the trend you will first need to create some sort of model and test it, and only after you are confident that your model truly estimates the trend, then you can use it to forecast the trend. Without such model any forecasting is impossible. Sadly the majority of time series textbooks do not stress this when talking about forecasting.

mpiktas
  • 33,140
  • 5
  • 82
  • 138
  • @:mpiktas full fledged ARIMA software can include differencing factors ,a trend coefficient and an arbitrary ARMA structure . Full fledged Transfer Function software can include ( in lieu of the differencing operator and the trend coefficient ) one or more TIME VARIABLES ( E.G. 0,0,0,0,0,1,2,3,4... ) reflecting specific points in time where a time trend has been identified. Basic time series texts,as you suggested, do not cover this extension. – IrishStat May 04 '11 at 08:40
  • @IrishStat, good remark, but my main point is still valid, if you want to forecast trend, you need to go outside classical ARIMA framework. I think the features you mention fall into definition of ARIMAX, but the X part remains the obstacle, you need to have an external model. – mpiktas May 04 '11 at 08:57
  • 1
    I agree you need to find a specification for the X partof the ARMAX Model.Classical ARIMA modelling died in about 1979 when work was done to empirically identify via Intervention Detection Pulses,Level Shifts and Local Time Trends (see I.Chang or G.C. Tiao or Ruey Tsay's literature on this). Their work and in some rare cases my developments of their work have lead to much more usable ARIMA structures incorporating Deterministic Structure such as multiple time trends and/or multiple Level Shifts.Level Shifts are simply first differences of a time trend as Pulses are to Level Shifts. – IrishStat May 04 '11 at 09:26
1

Your approach suggests initially adjusting in a deterministic manner the impact of seasonality. This approach may or may not be applicable as the impact of seasonality may be auto-projective in form. The best way to answer this question is to evaluate alternative final models for adequacy in terms of separating the observed observations to signal and noise. There are a number of possible pitfalls awaiting you . One of them " does the series have one or more trends AND/OR one or more level shifts " ? Another possible issue "does the series have a constant set of monthly indicators or have some months had a statistically significant change in the their effects ? In terms of a seasonal ARIMA model this question translates to " have the model parameters changed over time " ?. My experience with Excel Solver has not been very positive.

IrishStat
  • 27,906
  • 5
  • 29
  • 55
  • I am removing the seasonality using time series decomposition by calculating the seasonla indexes for each of the 12 months. If you mean that seasonality may change over time, an adaptive model whose parameters are recalculated when new data is available should work. Currently I am using time series decomposition to identify randomness and working on data without seasonality and randomness. I add the seasonality back to the forecasts but don't know how to deal with adding randomness to the forecast. May be R will deal with this. – Abhishek May 04 '11 at 05:24
  • @Abhishak.Unfortunately your seasonal indices assume a specific weighting structure i.e.the # of periods to use to compute the "weights" and that there are no Pulses or Level Shifts or Time Trends. In terms of seasonality changing over time you are not quite correct. Adapting to change requires a model form. For example if you had 6 years of data and thee was no June effect for the the first 3 years . The correct seasonal factor would reflect the June impact for the last three years. There is no adaptivity here in the sense you are using the word. Your approach has too many presumptive steps. – IrishStat May 04 '11 at 08:50
0

As mentioned, Use R, not excel.

My understanding of this process you are asking for.

Say you have a data set with a linear trend. Let's assume that trend to be Y = 3t+1, also assume you have 15 data points.

Use that model and find the residuals from that. Fit your time series model to these residuals. To forecast, use the 'predict' function in R. Get the next point. Let's assume the model tells you the next error will be -2.

(If you wanted to predict the next point, this would be the 16th data point.) Take Y= 3*16+1 = 49,,, and now add in the -2 from the time series prediction. Your forecast is now :49+(-2) = 47.

Adam
  • 813
  • 10
  • 16
  • this approach appears to be piecemeal rather than simultaneous. Rather than " doing this and then that" simply estimate a model that has both the regression component and the arima component. This model is called a TRansfer Function or an ARMAX Model. – IrishStat Nov 10 '11 at 19:41
  • 2
    Adam and @IrishStat, I have removed your exchange of comments because they do not advance the solution to the original question. If I may paraphrase (and read into) Adam's comment, now deleted, it seemed to suggest that although a good response to this question might indeed discuss "transfer functions" and "ARMAX models," it would need to *explain* what these things are, *why* they might be appropriate here, and *how* to apply them. Without such explanations, many readers might be impressed but not enlightened. Let's work to *improve,* rather than attack, each other's replies. – whuber Nov 14 '11 at 14:46