BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lizzy28
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
udo_sas
SAS Employee

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

11 REPLIES 11
udo_sas
SAS Employee

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
Quartz | Level 8

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
SAS Employee

Hi Lizi -

Many many time series are we talking about?

Thanks,

Udo

lizzy28
Quartz | Level 8

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

Thanks,

Lizi

udo_sas
SAS Employee

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
Quartz | Level 8

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
SAS Employee

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
Quartz | Level 8

You are so awesome, Udo!

I'll try it out and let you know.

Thanks a bunch!

Lizi

lizzy28
Quartz | Level 8

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
SAS Employee

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

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