How can I obtain a function that describes the expected value of data based on monthly data throughout different years?

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.

• You have a time series. Can you show us a plot of (prt of) the series, or/and a link to the data (or a mockup of it)? Oct 19, 2018 at 18:38
• Sure, I just added the plot (I couldn't show the data, just the behavior, because of the company's policy). Hope you or anyone else are able to help. Oct 22, 2018 at 14:02
• Can you also please add a plot of the autocorrelation function? Oct 22, 2018 at 15:06
• Just added, as I'm using MS Excel to my stats analysis (I know it's not the most efficient, but is the only one available), I had to do the ACF manually. Oct 22, 2018 at 18:18

2 Answers

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.

• 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. Oct 23, 2018 at 15:22
• The R download for windows can be found here: cran.us.r-project.org The R Studio download can be found here: rstudio.com/products/rstudio/download Oct 23, 2018 at 15:50

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?

• 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. Oct 23, 2018 at 15:19
• Expressing it as percent total of that year would be better, it would'nt cause artifacts the same way Oct 23, 2018 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. Oct 23, 2018 at 19:07