Predicting Time series data with the help of Excel (Understanding Purpose)

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.

1

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

2

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

3

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

solution

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s