4
$\begingroup$

I have to forecast data that exhibits dual seasonality. For example, the first day of the week can show seasonality and also the first week of the month can show seasonality. I am planning to use Taylor's adaptation of Holt-Winters exponential smoothing. This takes care of dual seasonality.

But according to the formula I am required to initialize two time periods $m_1$ and $m_2$ where $m_1$ is the time period of the 7-day weekly season, and $m_2$ is the time period of the monthly season.

The challenge here is that I cannot decide what $m_2$ will be? I cannot enter 31 as there are some months which have 30 or 28 days (29 in case of leap year).

Please suggest any method that I can implement for the problem above.

UPDATE: I have a restriction of using MS EXCEL only. I cannot use R.

|cite|improve this question
$\endgroup$
  • $\begingroup$ I edited the post quite a bit (the edit will appear after it is peer reviewed), please take a look whether I did not distort the meaning. $\endgroup$ – Richard Hardy Apr 2 '15 at 9:29
  • $\begingroup$ I highly advise learning R, and really putting it at the top of your to do list. You can learn the basics in less than a full day and it will open up a powerful set of tools to you that will be closed to you if you only know how to use excel (not because they are impossible to do in excel, but because you have to write your own excel code from scratch to do them - meaning you must understand the intricacies behind the algorithms and be proficient at coding up complex algorithms) $\endgroup$ – WetlabStudent Apr 3 '15 at 2:22
  • $\begingroup$ I am actually well versed with R and well aware of its capabilities. But, I have a contraint of using Excel. $\endgroup$ – Aman Mathur Apr 3 '15 at 7:20
  • $\begingroup$ Can anyone please guide me on how to to it in excel? $\endgroup$ – Aman Mathur Apr 4 '15 at 19:14
6
$\begingroup$

To the best of my knowledge you cannot use exponential smoothing for daily forecasting that involves irregular seasonal effects or causal variables like holidays. The paper you cite is requires well defined seasonal cycle example 24 hours a day X 7 days a week = 168 hours a week, typically you see these type of seasonality in weather forecasting, electricity demand forecasting. Real world business data, has messy seasonal factors such as day of the month etc., holiday effects, sometimes moving holiday effects (Example: Easter) In these instance you have to use dummy coding to capture these effects. It is key that you not only capture the effect on the holiday but also the effect surrounding that holiday - this is called lead and lag effects. Example: People don't shop on New Years day. All the shopping starts at least 2 weeks prior to new years day and ramps down after New Years day. The weeks surrounding the Holiday will be different from a typical week say you observe in atypical week February. An illustrative chart showing the Holiday effect around New Years day on a retail outlet (hypothetical) is shown below. If you don't capture this behavior you are bound to have poor predictions.Exponential smmothing/ETS and the likes a cannot handle regressors.

enter image description here

I would recommend using the following two methods and see if it works:

  • Naive Model: Use multiple linear regression and dummy code and the lead and lag effects. For seasonality: dummy code Day of the week, day of the month and month of the year. For forecasting trend: Use linear, log and/or quadratic trend
  • Arima Model: Do the same as multiple regression in terms of dummy coding for holidays. Arima will handle automatically for trend, seasonality.
    • Arima with Transfer function models - is more complex, and can handle lag/lead effects parsimoniously there by reduces the curse of dimensionality. Can handle any type of seasonality. You could dummy code the day of the month and month of year in transfer function. In both Arima and Transfer function you can automatically detect and treat outliers. I know SAS/SPSS/Autobox has the capablity to do this. Autobox does the model building automatically, while SAS and SPSS you need to build your own model which is not too difficult if you have some background in ARIMA via transfer function which you can easily gain by reading Pankratz. You should also consider detecting/treating outliers. Arima can handle outliers.
|cite|improve this answer
$\endgroup$
  • 3
    $\begingroup$ +1. You can also start with a simple ARIMAX model (using auto.arima() in the forecast package for R and feeding calendar events as dummies into the xreg parameter). This does not use transfer functions, but it may be easier to understand at the beginning. $\endgroup$ – Stephan Kolassa Apr 2 '15 at 17:46
  • $\begingroup$ Thank you for your answers. But unfortunately I am restricted to using MS Excel. $\endgroup$ – Aman Mathur Apr 2 '15 at 19:02
2
$\begingroup$

There is really no way to address your problem in Taylor's (2003) context. The best you could do is use a lag of 30, which is "roughly" equal to 365.25/12 (at least it's closer than either 29 or 31), and hope that small changes between month lengths average out.

However, I'd recommend that you look at De Livera, Hyndman & Snyder (2011), who propose modeling complex seasonality through Fourier series in their TBATS model (the "T" stands for "trigonometric"). This works with non-integer lags, e.g. 365.25/12, and it requires setting far fewer initial states than Taylor (2003). They implement their method in the tbats() function in the forecast package in R.

This recent question, as well as this other recent question on daily forecasting may be helpful.

|cite|improve this answer
$\endgroup$
  • $\begingroup$ +1, for references. I have never had any success in TBATS in real world business forecasting, would love to hear your perspective on its use in business forecasting. $\endgroup$ – forecaster Apr 2 '15 at 15:39
  • 2
    $\begingroup$ @forecaster: thanks. I personally have never used TBATS. My background is in retail forecasting, and as you write in your answer - +1 - causals are more important there, so we use regression-based approaches to cover weekly & yearly seasonality, promotions/prices, and calendar events. I find the JASA TBATS paper convincing, at least for electricity forecasting, and to be honest, a lot of my trust comes from knowing Rob Hyndman. He knows what he's doing. $\endgroup$ – Stephan Kolassa Apr 2 '15 at 17:44
  • $\begingroup$ Thanks a lot for your answer. But unfortunately I am restricted to excel. TBATS implementation will be tough in Excel I guess. $\endgroup$ – Aman Mathur Apr 2 '15 at 18:50
0
$\begingroup$

The key is to do two separate forecasts and allocate them to capture both seasonalities.

  1. Forecast by day to capture daily seasonality
  2. Add them up to get monthly totals
  3. Divide each forecast by the monthly total to get the daily % of monthly total
  4. Do a separate monthly forecast to capture monthly seasonality
  5. Multiply the daily % by the newly forecasted monthly total. This will allocate this new monthly total between the daily forecasts - essentially giving you both seasonal effects.

I have used this method several times and have almost always increased forecast accuracy.

Example (I am using weeks for simplicity but in practice weeks do not allocate well to months):

enter image description here

Feb Forecast total = 19,219

enter image description here

PS: I know week numbers do not align with traditional calendar. I used these in a previous example and was too lazy to fix them.

|cite|improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.