0:02 Hello friends, welcome to time series
0:05 tutorial. We will revise the entire
0:08 process of time series analysis that we
0:38 The data set has 500 data points with
0:40 serial number, years, quarters, and
0:43 sales. When we plot the sales data using
0:46 a line chart, we can clearly see a
0:48 long-term trend, seasonal variations,
0:51 and cyclical fluctuations. We will first
0:54 of all derive the trend line. Since the
0:56 graph presents a business cycle, a
0:59 polomial trend will fit it best. To find
1:01 a suitable order of the polomial
1:04 equation, we need help from Excel. Let's
1:08 ask Excel to fit a polomial trend line
1:10 on the series and align it the best we
1:13 could by changing order of the equation.
1:15 We can see that order three offers the
1:18 best fitting polomial line without
1:20 adding too much to the computation overhead.
2:00 To derive the coefficients of the trend
2:03 line with order three, we use linest
2:05 function. To use it, we select four
2:07 cells and in active cell, we type the
2:10 function with suitable parameters and we
2:13 press control shift enter keys. This
2:16 returns us the trend line equation. The
2:18 next step will be to estimate values
3:07 The equation returns three beta
3:10 coefficients and one intercept. The
3:12 highest order beta coefficient is
3:15 written first. We multiply the beta
3:17 coefficients with serial number the time
3:19 point with a power according to the
3:22 order. Finally we add the intercept 249.8519
3:25 249.8519
3:27 and we get estimate for the respective
3:29 time point. On plotting the estimated
3:32 value we can see the orange line
3:34 mimicking the red polomial trend we saw
3:37 before. Next step is to find seasonal
5:08 for seasonal indexes. First, we
5:10 calculate quarter-wise moving averages
5:14 for all years. Next, we find average of
5:16 every two averages to center them. Third
5:19 step is to take ratios of the actual
5:21 sales from the centered averages. And
5:24 finally using the ratios we calculate
5:26 quarter-wise indexes by taking
7:08 Once we have all seasonal indexes, we
7:10 apply respective index to our trend
7:13 value. This will apply the seasonality
7:15 effect to the trend estimate and will
7:18 bring it closer to the actual value. So
7:20 the final step is to multiply the trend
7:23 estimate value with respective seasonal
7:26 index. Let's do it and plot the final
8:50 Now the difference you see between the
8:54 gray line that depicts estimated values
8:56 and the blue line of actual values is
8:59 because of irregularities.
9:00 In real life, there are many factors
9:03 that affect the movement of a variable.
9:06 In time series analysis, we consider
9:09 only time as the impacting factor and so
9:11 there remains a difference between the
9:14 estimated and actual value. So this was
9:17 the revision of time series analysis.
9:19 Download the Excel file in the
