Forecasting using SAS Forecast Server, SAS/ETS, and more

Seasonal adjustment for daily/weekly data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 93
Accepted Solution

Seasonal adjustment for daily/weekly data

Hi all,

I have daily sales data which display strong weekly seasonality as well as monthly seasonality. It means that there was spike at the end of each week and greater spike at the end of each month. To adjust the aggregated weekly data for their seasonality, can I use X12 procedure as monthly data?

Thank you as always!

Lizi


Accepted Solutions
Solution
‎08-20-2015 12:18 PM
SAS Employee
Posts: 416

Re: Seasonal adjustment for daily/weekly data

Hello -

If it is only one series you are dealing with you don't need to worry about HPFTEMPRECON, as it is designed to handle large hierarchical projects.

One approach might be to do the following:

  1. First model the daily forecast - which will most likely not capture the end of end of month spikes
  2. Look at the residuals of the daily forecast - and based on the residuals create a new data set which only includes your end of week period - for example you may say the end of month peak always occurs 5 days before and 3 days after the true end date of a month (for example August 31st). Set all other periods to 0.
  3. Create a custom interval which captures your end of month behavior (se: http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_intervals_sect...)
  4. Model the end of month residuals using your custom interval using a seasonal model - the custom interval will take care of the "off-season" periods.
  5. Finally adjust your daily forecast with the end of month forecast - for example simple add the uplift to your daily forecast
  6. This white paper illustrates a similar approach: http://www.sas.com/offices/latinamerica/chile/emailmkt/forecast/pdf/Forecasttimecompression.pdf

Thanks,

Udo

View solution in original post


All Replies
SAS Employee
Posts: 416

Re: Seasonal adjustment for daily/weekly data

Hello -

If you have access to SAS Forecast Server you may consider the following approach:

  1. Role up the data to weekly frequency and use a seasonal model to capture end of month spikes (low-frequency forecast)
  2. Model daily data to capture week seasonal behavior (such as high demand on weekends) (high-frequency forecast)
  3. Use the HPFTEMPRECON procedure to "align" 1) and 2). HPFTEMPRECON reconciles the high-frequency forecasts to the low-frequency forecasts in such a way that the accumulation of the reconciled high-frequency forecasts is equal to the low-frequency forecasts.

You may find this thread useful, too: https://communities.sas.com/message/151039

Thanks,

Udo

Frequent Contributor
Posts: 93

Re: Seasonal adjustment for daily/weekly data

Thanks, Udo!

Unfortunately I don't have SAS Forecast Server. I have looked into the HPFTEMPRECON procedure, and it looks that it only works in the Forecast Server. Would there be other way that works in the regular environment?

Thanks again!

Lizi

SAS Employee
Posts: 416

Re: Seasonal adjustment for daily/weekly data

Hi Lizi -

Many many time series are we talking about?

Thanks,

Udo

Frequent Contributor
Posts: 93

Re: Seasonal adjustment for daily/weekly data

Udo, there are only two-year daily sales data, which have decent weekly and monthly seasonality.

Thanks,

Lizi

SAS Employee
Posts: 416

Re: Seasonal adjustment for daily/weekly data

Lizi -

I should have been clearer in my question: I was wondering how many products (or sales) you are trying to forecast. Are we talking about 1 product, 10s, 100s, 1000s, more?

In general having only 2 years of daily data will be a challenge - as you observed each day only twice at max. So depending on your patterns coming up with accurate models might be difficult.

Thanks,

Udo

Frequent Contributor
Posts: 93

Re: Seasonal adjustment for daily/weekly data

Thanks, Udo.

Right now I am only looking at one product. I thought about the short time period I am looking at as well. But unfortunately the project is to find how marketing campaign helps predict retail sales, and we only have two-year marketing campaign data. I have to just use less data points.

I tried vector autoregression model with unadjusted data and hope to run the model again after the data are seasonally adjusted.

Thank you!

Lizi

Solution
‎08-20-2015 12:18 PM
SAS Employee
Posts: 416

Re: Seasonal adjustment for daily/weekly data

Hello -

If it is only one series you are dealing with you don't need to worry about HPFTEMPRECON, as it is designed to handle large hierarchical projects.

One approach might be to do the following:

  1. First model the daily forecast - which will most likely not capture the end of end of month spikes
  2. Look at the residuals of the daily forecast - and based on the residuals create a new data set which only includes your end of week period - for example you may say the end of month peak always occurs 5 days before and 3 days after the true end date of a month (for example August 31st). Set all other periods to 0.
  3. Create a custom interval which captures your end of month behavior (se: http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_intervals_sect...)
  4. Model the end of month residuals using your custom interval using a seasonal model - the custom interval will take care of the "off-season" periods.
  5. Finally adjust your daily forecast with the end of month forecast - for example simple add the uplift to your daily forecast
  6. This white paper illustrates a similar approach: http://www.sas.com/offices/latinamerica/chile/emailmkt/forecast/pdf/Forecasttimecompression.pdf

Thanks,

Udo

Frequent Contributor
Posts: 93

Re: Seasonal adjustment for daily/weekly data

You are so awesome, Udo!

I'll try it out and let you know.

Thanks a bunch!

Lizi

Frequent Contributor
Posts: 93

Re: Seasonal adjustment for daily/weekly data

Hi Udo,

One more question for the adjustment:

In the second step "2. Look at the residuals of the daily forecast...", create a new data set that only includes the end of week period. Then your example talked about the end of month peak. Also, the following steps, 4 and 5, they are about adjusting the forecast with end of month forecast. Should the example and the following steps be about adjusting the forecast with end of week forecast, rather than end of month forecast?

I thought that the process would be to adjust with end of week forecast and then adjust with end of month forecast.

Sorry for endless question.

Thanks a lot!

Lizi

SAS Employee
Posts: 416

Re: Seasonal adjustment for daily/weekly data

Hi Lizi -

Of course, I should have been more careful in my description - sorry for that.

Please read my response in combination with my earlier response:

  • Model daily data to capture week seasonal behavior (such as high demand on weekends) (high-frequency forecast)
  • Role up the data to weekly frequency and use a seasonal model to capture end of month spikes (low-frequency forecast)

Thanks,

Udo

Frequent Contributor
Posts: 93

Re: Seasonal adjustment for daily/weekly data

Hi Udo,

Continuing from my previous ask, I was wondering whether I am supposed to use seasonally adjusted data series, i.e. forecast of sales with residual forecasts of spikes, in a vector autoregression model (VAR) or any regression analysis.

The purpose of doing seasonal adjustment is to get better prediction in a VAR. Please advice.

Thanks again!

Lizi

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 2426 views
  • 3 likes
  • 2 in conversation