1

I know this question is kind of complicated to understand at first. Here's the deal:

I've got organized, monthly sales data from various years, and when I graphed it I saw there's (obviously) a general behavior throughout the year (not taking into account the effect of external or internal factors which could affect the general behavior of the data). I would like to know if there is a method with which I could express the expected value of the data during that month, based on the historical data, for forecasting purposes.

It would be like some kind of regression, but in this case my data is not distributed parametrically, so Excel's trendlines and regular regressions can't help me. Maybe a non-parametric regression? I don't know any nor its logic behind, so if you know, please help me with it.

EDIT: Here I show you a graph of the time series per year, as well as a yearly weighted average of how the data behaves monthly (the weights were the % that each year represented vs the total of all years). I wouldn't want a parametric function that can't adjust properly to the data because each peak is a characteristic behavior of the time series during that month. Also, the autocorrelation function is below the Data graphs (from k = 1 to 83, as I have 7 years (= 84 months) of data. I think that much should be enough.

Data % ACF

C. Mayers
  • 13

2 Answers2

1

In addition to the other excellent answer, a few comments. Your plot of the autocorrelation function lacks something we are used to from R: lines indicating an upper/lower confidence limit around zero, see How is the confidence interval calculated for the ACF function?. For your data this limits are about $\pm 0.2$, and only the seasonal spikes can be seen outside. So you could try a pure seasonal model.

Otherwise, your plots is a bit difficult to understand. I understand you cannot show the data 'as is', so have chosen to show some percentages. But look at your first plot, with the six yearly series. For december (month 12), all the series show 100%, which looks strange. What happened?

kjetil b halvorsen
  • 77,844
  • 1
    Thank you for the observations and recommendations. As for the situation regarding my data and December, as I couldn't show the absolute values of the data and I wanted to keep the distribution as the same, I just expressed it as a percentage of December's sales (as I observed that December has been the month with the highest sales throughout all years). If you'd prefer it, I could express it as a % of the total of that year. – C. Mayers Oct 23 '18 at 15:19
  • Expressing it as percent total of that year would be better, it would'nt cause artifacts the same way – kjetil b halvorsen Oct 23 '18 at 16:26
  • I just updated the graphs. It shows the same distribution of data. Seasonality effect will be taken out after further analysis. I just have to learn the basics of R and go for it. – C. Mayers Oct 23 '18 at 19:07
0

The short answer is yes. I highly recommend Rob Hyndman and George Athanasopoulos textbook on Time Series Forecasting. It's open source. The page on simple forecasting methods is here: https://otexts.org/fpp2/simple-methods.html

It looks like you've got strong seasonality so you could start with a seasonal naive model if you don't want to get too complicated.

I don't believe you can do much time series modelling in excel, however. I suggest using R or Python. Most of the code required for time series forecasting in R is included in the recommended textbook.

kalidurge
  • 364
  • Alright, I am discussing with the ones in charge of installing software in PCs to see if I can download R. I've heard of it and I know it's better, but I don't know how to use it, so thank you for showing me the book and for your suggestions. – C. Mayers Oct 23 '18 at 15:22
  • The R download for windows can be found here: http://cran.us.r-project.org/

    The R Studio download can be found here: https://www.rstudio.com/products/rstudio/download/

     – kalidurge Oct 23 '18 at 15:50