0
$\begingroup$

Hi I'm trying to create a forecast in excel that uses dummy variables for the weeks to create seasonality as well as dummy variables for certain holidays. I created 52 weeks and then had them be 1 if the sales matched that week and 0 otherwise. I'm getting a really high p value for the weeks when I run the regression, should I still use the coefficients its giving me?

Also, it only lets me run 16 columns of X values at a time. Any ideas of how I can get around this? Below is what it looks like for me in excel, I put 1 for true and a 0 for false. I use Sales as the Y value in the regression and I tried to use all 52 weeks and the holidays as X values but it wouldn't let me. thanks

Week_ID Sales week 1 week 2 .... week 52 easter forth of July

Improve this question
New contributor
Alexa is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
$\endgroup$
5
  • $\begingroup$ That is a lot of dummy variables particularly if you have limited amounts of data. There are better methods like arima with seasonal data or looking at exponential smoothing models that model seasonality (among other options) if you want to predict primarily. If you want to do regression looking at regression with ARIMA error might be an alternative. $\endgroup$ – user54285 Apr 7 at 21:00
  • $\begingroup$ Thanks, do you know if that can be done in excel? If not can it be done in R? $\endgroup$ – Alexa Apr 8 at 1:38
  • $\begingroup$ I am not sure what 'it' is exactly. I would use R not excel. I am not very confident of excel for any but descriptive statistics. R will do any statistic you are likely wanting to do. What are you really trying to do, predict, or see the impact of one of the dummies? If you are predicting just use ARIMA or exponential smoothing which are in the forecasting and smooth modules. $\endgroup$ – user54285 Apr 8 at 1:47
  • $\begingroup$ I'm trying to do make a forecast for sales. Is it intuitive to use ARIMA? I was trying to do it in excel and found that when I used exponential smoothing, I had lags and lifts from the holidays. Is there a way to make ARIMA take holidays into account? Does ARIMA take the seasonality into account? Thanks $\endgroup$ – Alexa 2 days ago
  • $\begingroup$ I would always prefer R to excel. It is easy to use ARIMA in the forecast module. I use a few lines of code to do it (use auto.arima in that and then forecast with what results). I don't know a way to take holidays into account in any time series. You can use your expert judgement to modify the results if the results are consistent. ARIMA takes seasonality into account (formally this is SARIMA, but the forecast package does not care). Whether it will capture a holiday I am not sure. $\endgroup$ – user54285 2 days ago

Your Answer

Alexa is a new contributor. Be nice, and check out our Code of Conduct.

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

Browse other questions tagged or ask your own question.