Seasonal adjustment for daily/weekly data

[lizzy28]

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

[udo_sas]

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

[lizzy28]

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

[udo_sas]

Hi Lizi -

Many many time series are we talking about?

Thanks,

Udo

[lizzy28]

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

Thanks,

Lizi

[udo_sas]

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

[lizzy28]

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

[udo_sas · Accepted solution]

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

[lizzy28]

You are so awesome, Udo!

I'll try it out and let you know.

Thanks a bunch!

Lizi

[lizzy28]

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

[udo_sas]

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

[lizzy28]

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