In our day to day activity we have so much of data , and all the data is always related with time. Data that we record with time we call as time series data. Data that we do not record with time , we do not consider as time series . However the data recorded was at a particular time , it is different that we did record the time.

We often face a situation where we need to present some immediate results. With not much time to think i’m gona show you some simple methods which can be implemented in excel on a small data set and further also be extended to R.

So what is the case Study. Let us assume we have hired by a client who wants to predict his sales of 2013 for 4 quarters. The Client has historical data of quarters 1-4 from the year 2009 onward.

Here is the data

Year | Quarter | Sales |

2009 | 1 | 4800 |

2 | 4100 | |

3 | 6000 | |

4 | 6500 | |

2010 | 1 | 5800 |

2 | 5200 | |

3 | 6800 | |

4 | 7400 | |

2011 | 1 | 6000 |

2 | 5600 | |

3 | 7500 | |

4 | 7800 | |

2012 | 1 | 6300 |

2 | 5900 | |

3 | 8000 | |

4 | 8400 | |

2013 | 1 | |

2 | ||

3 | ||

4 |

Its but obvious that the first step before determining what model to use to predict or before thinking of feature engineering is visualisation.

From Here it is clear that our data has upward additive seasonality.

HMMMMMMMMMMMM……………..

So what should we do lets try Multi linear regression

here y=Sales

x=time,quarter

The Result of regression here seems marvellous

Lets try interpreting this our R Square value is 0.96 . It ideally should be 1. R Square is a check of how good our model fit the data. The PValue of all is also less than 0.05 means all our variables are important.

You may have noticed that i have broken the quarters in 3 separate columns . I have used them as dummies. For more information refer to my attached excel file.

y=-1165916+Q1*525+Q3*1875+Q4*2325+Year*5825

So using this equating we can predict future values. So give me year and quarter, and we can successfully predict sales.

So our final predicted graph would look like

To understand how i solved it please refer to my excel file, it has comments so that we all can understand easily.

So this is how we can predict sales using linear regression based on historical time series data.