# 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)? – kjetil b halvorsen Oct 19 '18 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. – C. Mayers Oct 22 '18 at 14:02
• Can you also please add a plot of the autocorrelation function? – kjetil b halvorsen Oct 22 '18 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. – C. Mayers Oct 22 '18 at 18:18

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. – C. Mayers Oct 23 '18 at 15:22
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.