From the course: Excel Data Analysis for Supply Chain: Forecasting

Using Excel to define seasonality - Microsoft Excel Tutorial

From the course: Excel Data Analysis for Supply Chain: Forecasting

Using Excel to define seasonality

“

- [Speaker] When winter approaches, we know it's time to get the sweaters and coats out, and when summer returns, so do the shorts and t-shirts. The seasons are generally predictable and as we'll soon see, seasonality is not only something we see with weather, we also see it with time-series data. Seasonality in our data presents itself as fluctuations that occur at regular and, if not, predictable intervals, and it happens over and over again. We, of course, see it with weather and in business, we see seasonality in sales, whether it's increased sales around holidays like Christmas or airline miles traveled during the summer months, perhaps even energy consumption during the coldest winter months. As you can see, this is why understanding seasonality can be so helpful for supply chain managers or anyone else in the world of business. Once you understand seasonality, you have the ability to make more informed-decisions about your resources, whether they are people, inventory, machines, or capital. Okay, let's go to Excel and explore seasonality. So let's begin with something that isn't seasonal and what you see here is the average temperature in Central Park. And you'll notice that we have data from 1919 till about 2024, and we can definitely see that this is not something that is seasonal. Yes, there are ups and downs, but there is no regular cycle, no repeating pattern that is very easy to see. And you may say, well, it looks like there is some up and down, looks like there is an upward trend. But once you start to see what seasonality really looks like, you'll see a much more noticeable trend. So let's go ahead and take a look at something that is seasonal, and here we can find weather on a monthly basis. So again, this is data we've seen before. I want to go ahead and create the chart for the periods. And you'll notice, we have the different months, January through December for a number of different years. I've included the level in there, the mean for each month for the total period. And when we go ahead and we put this in a chart, so remember, this is 15 years worth of data, and if we count the peaks, you'll notice there's one, two, three, four, five, six, seven, eight, nine, 10, 11, 12, 13, 14, 15, 15 peaks. I bet you if you count the valleys, you'd find something very, very similar because what we can see here is that this is repeating over and over again. Every single year. It gets warm in the summer, every single year, it gets cold in the winter. So this is definitely seasonal and that's what we would expect with weather. Now let's go ahead and look at and see whether we see these, the seasonality in other areas. So here, we have revenues for an online store. And again, we see this on a monthly, oh no, actually we have this on a quarterly basis. And so this company is reporting four different times per year. And you'll notice the mean is 88.3 because once we start going down here, you'll notice that the numbers go up significantly. So we started in 2015 and we have data that goes to 2024. And so when we put this up on our chart, what do we have here? Well, this is a company that sells things online. And as you might expect, in the fourth quarter of every year, it looks like sales go up, whether it's in 2015 or 2016 and 2017 and beyond. And once again, if we want to see is there some seasonality, we can do something as simple as counting those peaks. So we see one here, two, three, four, five, six, seven, eight, nine, 10. And I believe we have data in this area for for 10 years. So that would be 40 quarters. So once again, it's sort of easy to see the seasonality. We can also see an upward trend in general sales over time, and we see our level there as well. So for this online store, they are experiencing seasonality. How about for cars? Again, we can see some quarterly data. We want to see, is there a busy time of the year in which electric vehicles are sold? And when I put this up on my chart, do I notice anything on any particular year? First thing is it's a little bit tough to tell in those first few years just because things look rather flat based on the perspective that we're given. But beyond that, we don't see any real recognizable pattern. Now, if there is some seasonality and we just can't see it, well, we'll take a look at that later in the course. But for right now, I don't see any very noticeable seasonality in this particular case. It doesn't look like people are buying cars at any specific time of year, more than they'd buy them at other times. There are other things that may be influencing when car sales go up or down. Let's take a look at smartphones. So again, we have quarterly data for a number of years. I think it's 10 years. So that means we have 40 different quarters. We have our revenue, and then we have our mean. And just as we've seen before, if we count those peaks, we have one, two, three, four, five, six, seven, eight. It looks like we're roughly have those 10 peaks. Now the thing here is you might say, "Well, why would cell phones or something like a cell phone, why would that have a seasonal component?" There's a number of different things that we can think about that might be causing that. One is the holidays are, are there more people getting new phones as gifts? The other thing is, is it possible that new phones are released by this company at a certain time of year? And is that something that's influencing larger sales during that particular quarter? So again, we can explore that in much more detail as we get into those chapters. But for right now, it's nice to see via our charts that we do have some possibility of some real seasonality in the area of smartphones.

Contents