Welcome to Highline BI348, class video number 53.
And if you want to download this Excel workbook,
Bi 348 Chapter 05 and follow along,
click on the link below the video.
OK, we're in Chapter 5.
And Chapter 5 is all about time series data and forecasting.
Now, we're going to start off by looking at different data sets.
And each one of these data sets has some time variable,
like month month month, day-- you
could have year-- and some number.
And what we want to do in this video
is plot each one of these data sets with a line chart
and see what the pattern is.
Now, any time you have some time variable and a number,
we can use the line chart to plot the time series.
Now, there's going to be a difference, if we actually have
a date, like January, February, March, and then some number,
if we highlight both of these and use the Excel line chart,
it will put this on the horizontal axis and this
on the vertical axis.
However, if we have month or year,
the charting engine won't interpret it correctly.
And so we'll have to do something different when we
have a time variable like this.
Hey, but let's just start out with this first one.
month and units boomerangs sold.
We want to plot this and see what kind of pattern it is.
Once we see the pattern of our time series,
it can help us choose the best forecasting method.
Now, I'm going to go up to Insert, over to Charts.
And there is our line.
I'm going to pick with markers.
And sure enough, it put the date,
which happens to be month and our value.
Now these dates right here are actually official serial number
dates underneath with custom number formatting.
Click on the chart and go to the Plus.
I want a Axis Titles.
And right here I see that solid line.
So I'm a type an Equal sign.
That shoots me up to the Formula bar.
And then I'm going to say units boomerang sold and Enter.
Horizontal axis, I click.
Equal sign, click on that and Enter.
Click on the Chart Title, Equal sign,
and I'm going to click in A1.
Now, this pattern here is called horizontal or constant or
stationary.
And what that means is that there's
as if there was a line plotting the mean value from all
of these.
And it's as if there's a random fluctuation
above and below the mean line.
Now, actually, in a more advanced forecasting class,
you would have other methods besides visual
to test whether it's a stationary,
constant, or horizontal method.
But here, we're just going to look and see
what type of pattern we seen.
Now, to kind of understand what it
means by a mean and a random fluctuation,
let's go ahead and calculate the mean plotted
against each one of the months.
So I'm going say Equal average.
Highlight these numbers.
F4 to lock it.
Control Enter.
And copy it down.
Now, I want to edit this chart.
Right click Select data.
That's the real power of charting.
Or select the chart and go up to Design and select Data.
I want to click Add.
And I want to add a new series.
The series name will be mean.
Down for the values.
I'm going to highlight and delete that.
And then highlight and the mean and click OK.
Click OK.
I want to click on the line, Control 1
to open up the Task pane.
I'm going to come over to the Fill bucket, Markers,
Marker options and say None.
Back over to Line.
I want to come down here and change the width of something
like 1.
I don't want it to be too big.
And there is an example of horizontal, constant,
or stationary.
It's as if there is a mean and there's a random fluctuation
above and below.
Later in the next video, we'll see
if we have a pattern like this, we
can use either the naive method, the average method, moving
average, or exponential smoothing method
to forecast from these values.
Now, I'm going to drag this down here.
We're going to look at our next potential pattern that we see.
We're going to highlight, Insert, over to Line.
I'm going to select Markers.
Try to I get over here.
Equal sign.
Click D and Enter.
Green arrow, Axis titles.
I see that it's selected.
Equal sign, I click on Units.
Enter.
Equal sign.
Click on Month, Enter.
And so here what we see is it definitely
looks like there is a horizontal or consonant pattern.
But all of a sudden somewhere in March,
it jumps and then it continues on
in a similar horizontal pattern.
And then sure enough in March what happened
is a new contract was signed with Rangs of Japan.
And so it jumped.
But then it continues with this horizontal pattern.
Now, if this sort of thing occurs,
there are some forecasting methods,
like a moving average or exponential smoothing,
that will pick this up more quickly
than if we were to do a method like average all past values.
And we will see an example of that later.
So that's two patterns we've seen for time series data.
Let's highlight this next series of data.
And we have months, 1 to 24, and sales.
And Insert.
Over to Line.
And I'm going to click the line with markers.
And you can clearly see that the charting engine did not
interpret the month correctly.
We're going to right click.
The power in charting comes from Select data.
We see that there is a month and sales.
Month is the one we do not want plotted.
We actually want that over here on our horizontal axis.
So I'm going to click Remove full month.
And sure enough, you could see that it's gone.
Now, even though, 1, 2, 3, 4, all the way to 24 will show up
correctly here, at least in my class,
don't hand in any test, any homework if it's not linked.
You always want to link it.
Even if it happens to be that the charting engine plotted it
correctly, you still want to link it.
I'm going to click OK.
Click OK.
And so now we have this one linked as a series.
That's the actual numbers.
This horizontal axis is linked to this month here.
Now, I don't need this legend.
Delete.
I click on the Green Plus, Axis Titles.
Equal sign, and I'm going say sales.
Equal sign month.
Equal sign and I'm going to click on G2 and Enter.
So the question is here-- now looks
like there's a random kind of fluctuations--
but is there some sort of trend, either downward or upward?
Now last chapter, we talked about linear regression.
And guess what?
You can use linear regression in forecasting also.
So this will be a trend pattern.
And the forecasting method we will use is linear regression.
Now, let's go and look at another example here.
Here, we have days of the week and YouTube views.
Now I'm going to highlight days of the week and YouTube views.
Because that's text, the line chart
will interpret it correctly.
Insert.
Over to Line.
And there's our markers.
Green Plus, Axis Titles.
Equal sign YouTube views.
Equal sign day of the week.
Equal sign, and this is called in the textbook,
the term is seasonal pattern, also known as periodic pattern.
If you look through this, there seems
to be a pattern in accordance with our horizontal axis,
the time variable.
Now notice that it looks like but if you point to these,
there's Saturday looks like to be the lowest.
Saturday is the lowest.
So Saturday is always the lowest.
And it always looks like Tuesday, Wednesday, Monday,
Tuesday, Wednesday, so mid-week always seems to be the highest.
But the Saturday value is always the lowest.
This is called a periodic pattern.
And we can make forecasts, not using moving average
or exponential smoothing, but we'll
use the linear regression.
And we'll treat the day of the week--
or the other great common example in business
is quarterly sales-- we'll treat whatever the time variable
it is here that's displaying a periodic or seasonal pattern.
We'll treat it as a categorical variable.
And we'll break it apart and then build
our multiple linear regression model.
We actually have an example last chapter
of doing that, not with time, but with a different
categorical variable.
Now let's do another example of seasonal or periodic pattern.
There's text here.
And there's a number there.
So Insert, Line with markers, it'll interpret it correctly.
Then go to the Green Plus, say Axis Titles.
Equal sign revenue in thousands, Enter.
Equal sign quarter Enter.
Equal sign, there's our chart title up there.
So the fourth quarters sales always seem to be the biggest.
You can see there's quarter 4, quarter 4.
And this is a very typical pattern for many businesses.
You can think of a ski manufacturer,
their sales are going to be during winter, whereas someone
who makes swimsuit or summer ware is their peak
will be in the summer.
Now those are periodic or seasonal patterns.
Let's see what happens if there is a trend and seasonal.
So I'm actually going to click in a single cell.
I've been highlighting these every time.
But Charting Engine usually is pretty smart.
I'm going to click in a single cell.
Insert, over to Line and Markers.
Drag this over here.
Green Plus Axis.
Equal sign YouTube views, Enter.
Equal sign day, Enter.
Equal sign trend and seasonal.
I'm actually going to see if I can stretch this way over here.
And look at that.
So this is that same YouTube view data.
And if you go through here it is just without exception
Saturday is always the lowest.
So we definitely have our periodic pattern, or seasonal.
But it also looks like there's a slow trend over time upwards.
And so our forecasting method here can be linear regression.
And we'll combine a slope and x value for the time trend.
And then the slope and x value for the seasonal effect,
which for us will be Monday, Tuesday, and Wednesday.
And actually, this is not the example that we will use.
We will use a quarterly example that
demonstrates the same pattern, that the quarters are clearly
a seasonal pattern, but there's an upward trend over time.
All right, so in this video we looked at the different types
of time series pattern.
We can have a trend and a seasonal pattern.
We can have a seasonal or periodic pattern like quarters.
We could have a seasonal or periodic pattern over days.
We could actually have trend and seasonal together.
We could have just a trend.
We could have a horizontal, constant, or stationary pattern
when there's a sudden jump.
And then we can have just straight horizontal, constant,
or stationary pattern.
All right, next video, we'll talk about basic methods
for forecasting.
All right, we'll see you next video.