This tutorial revises the process of time series analysis, focusing on decomposing sales data into trend, seasonal, and cyclical components to forecast future values.
Mind Map
Click to expand
Click to explore the full interactive mind map • Zoom, pan, and navigate
Hello friends, welcome to time series
tutorial. We will revise the entire
process of time series analysis that we
The data set has 500 data points with
serial number, years, quarters, and
sales. When we plot the sales data using
a line chart, we can clearly see a
long-term trend, seasonal variations,
and cyclical fluctuations. We will first
of all derive the trend line. Since the
graph presents a business cycle, a
polomial trend will fit it best. To find
a suitable order of the polomial
equation, we need help from Excel. Let's
ask Excel to fit a polomial trend line
on the series and align it the best we
could by changing order of the equation.
We can see that order three offers the
best fitting polomial line without
adding too much to the computation overhead.
To derive the coefficients of the trend
line with order three, we use linest
function. To use it, we select four
cells and in active cell, we type the
function with suitable parameters and we
press control shift enter keys. This
returns us the trend line equation. The
next step will be to estimate values
The equation returns three beta
coefficients and one intercept. The
highest order beta coefficient is
written first. We multiply the beta
coefficients with serial number the time
point with a power according to the
order. Finally we add the intercept 249.8519
249.8519
and we get estimate for the respective
time point. On plotting the estimated
value we can see the orange line
mimicking the red polomial trend we saw
before. Next step is to find seasonal
for seasonal indexes. First, we
calculate quarter-wise moving averages
for all years. Next, we find average of
every two averages to center them. Third
step is to take ratios of the actual
sales from the centered averages. And
finally using the ratios we calculate
quarter-wise indexes by taking
Once we have all seasonal indexes, we
apply respective index to our trend
value. This will apply the seasonality
effect to the trend estimate and will
bring it closer to the actual value. So
the final step is to multiply the trend
estimate value with respective seasonal
index. Let's do it and plot the final
Now the difference you see between the
gray line that depicts estimated values
and the blue line of actual values is
because of irregularities.
In real life, there are many factors
that affect the movement of a variable.
In time series analysis, we consider
only time as the impacting factor and so
there remains a difference between the
estimated and actual value. So this was
the revision of time series analysis.
Download the Excel file in the
Click on any text or timestamp to jump to that moment in the video
Share:
Most transcripts ready in under 5 seconds
One-Click Copy125+ LanguagesSearch ContentJump to Timestamps
Paste YouTube URL
Enter any YouTube video link to get the full transcript
Transcript Extraction Form
Most transcripts ready in under 5 seconds
Get Our Chrome Extension
Get transcripts instantly without leaving YouTube. Install our Chrome extension for one-click access to any video's transcript directly on the watch page.
