Learn how to develop seasonal factors over several years of quarterly data, and apply them to a forecasting model to create a baseline in Microsoft Excel.
- [Instructor] I'm in the 02_04_Begin Exercise File. Just so we can visualize our data I'm going to go ahead and create another line chart of the data that we already have. So I'm going to go to Insert, Line. And I can see that there's some zig-zags in here and these zig-zags may or may not follow a pattern, I'm not really sure yet. So we can tease out a seasonal effect and I'll show you how to do that here. I'm going to go and get rid of this graph for now, it's not really useful to us anymore. And I'm going to scroll over to columns J and K.
You can see that I've split up my Sales by Quarter of every year, but in order for me to develop a seasonal effect I need to develop some factors. So I've split up my data by quarters and I'll develop some factors for each quarter. In column O I'll make a list of my quarters really quickly, and that's simply 1, 2, 3, and 4. And in column P I'll make a header for Factors and those are something that we're going to have to calculate. So, for example, if I want to calculate the factor for quarter 1 what I'll have to do is figure out my ratio of Q 1 sales to total sales.
So I'll take the average of my Q 1 sales and divide by my average of overall sales. So in cell P2 let me go ahead and write a formula that'll do that. I'm going to use a function called AVERAGEIF, so I'll open it with =AVERAGEIF, and then I'm going to open my parenthesis. My range is going to be J2 through J11, those are all the quarters. And these are fixed reference quarters and I'm going to indicate that by pressing cmd + t on my Mac and you'll see the dollar signs that popped up.
I believe you can do this on Windows using F4. My criteria is quarter 1, so I'll press comma, and I'll just click on O2, because that's quarter 1. I'll press comma again. And my average range is all the sales from K2 through K11. And again, these are fixed references, so I'm going to press Command + T to indicate that. And I'll close out my parentheses and hit Enter and you'll see that I get a really big number. That's not really a factor, is it? It's just missing something.
And what's it's missing is a denominator. The denominator is the average of all sales. So let me go back to my formula and to this formula I'm going to add something. So I'll add a / to indicate division and then an AVERAGE function for the fixed range of all our sales. So I'll have to open up AVERAGE, open parenthesis, and it's going to be all our Sales, K2 through K11, and it's a fixed range, so I'll press Command + T.
I'll close out the parenthesis, hit Enter. There you go. It looks like I have a factor of .845. So let's think about what this means. Because our factor is less than one that means our average Q 1 sales are generally a bit lower than our average yearly sales. Let me double-click the bottom right of P2 to auto-fill that formula and we can do this because we've put our salary range and our quarter range as fixed references.
Now that we have our factors we can deseason our sales figures. So in column L I'll create another label called Deseason. This is obviously not a real term, I'm kind of making a joke about it, but I just want to get rid of that effect of seasonality in our sales figures. Maybe quarter 1 is selling lower, because people have already done all their Christmas shopping and have nothing to buy in quarter 1. Maybe quarter 2 blows up, because we have spring sports. Maybe quarter 4 blows up, because we've got Christmas shopping going on.
So I want to get rid of that seasonality, so I'll deseason these figures here. We'll use the VLOOKUP reference function in Excel for this. For this many data points I can just do it by hand, but it's good practice to use VLOOKUP with fixed ranges in case you have a much larger data set. So in order to get rid of the seasonality influence I'll basically divide the sales figures by the factor. So in L2 I'll open up the equation with an = sign, and then click on K2, which is our sales figure, and then I'm going to divide that by the VLOOKUP function.
Our lookup value is the appropriate quarter, in this case 1, so I'll use J2 as my reference for quarter 1. Then I'll press comma. Our table array is our table of quarters and factors, so I'll highlight that and use it as a fixed reference. And then I'll press comma. Our column index number is 2, since we're using the factors in the second column of our table. And I'll press comma. And our range lookup is false, because we want an exact match.
And so that's already closed and I'll press Enter to get my deseasoned value. I want to format this to look like currency with no decimals, so I'll click on the cell and press cmd + 1, and go into Currency, and I'm going to put 0 as my Decimal place, press OK. And then I'm going to auto-fill this formula all the way down. So look at what happened here. The lower numbers from Q 1, like 4 million, 12 million, and 18 million, all of those went up, they increased, and the numbers from Q 4 went down.
So, for example, 13 went to 11, 20 went to 16, and that makes sense, because you're actually adjusting for the seasonal effect, you're getting rid of that seasonal effect, so now Christmas time is no longer affecting your revenue. So let's use these new values to create a forecast that'll give us a linear equation. We'll need to create a couple of new columns to accomplish this. So first, in column I I'm going to create a Time label. It's basically going to give me an x-axis with a time series.
So in column I I'll give a label Time and I'm going to bold that, so it's easier to see. I'll fill this in from 1 through 10 in consecutive order. Then in column M I'm going to create a label as well. And this label is going to be called Forecast. We'll use the built-in FORECAST function in Excel to calculate the values in this column. You can use this FORECAST function to predict future sales, inventory requirements, and consumer trends.
So let's do it. In cell M2 I'll type an = sign to start out my formula, and then FORECAST, and then I'll open the parentheses there. And my X value is Time 1, so that's I2, and then I'll press comma. And my known Ys are the deseason values, L2 through L11, so I will highlight those. And I'm going to fix those, fixed references, Command + T, comma. And my known Xs are the Time series from I2 through I11, so I'm going to highlight those and get those fixed as well.
And I'll close out my parens, hit Enter, and get my forecast. Then I'll auto-fill by double-clicking the right hand bottom of that cell. While I'm at it let me go ahead and format this for Currency with no decimals. Now for this forecast to extrapolate to quarters in the future we need to create a scatter plot and a regression trendline. So let's highlight the data from column I, just the numbers, and column M, again, just the numbers, go over to Insert, Scatter plot.
And there I have it, I have a really nice looking scatter plot here. And some of your numbers may have just like pound signs and stuff, but if you click on them they'll return to their original number. I'm not sure why that happens, but it does sometimes. So I'm going to right-click on any given point and go to Add Trendline and it's going to be a Linear Trendline with an equation on the chart. So here's my equation. So now what you can do is you can really plug in any time point for X and solve for Y.
That value for Y is going to be the expected revenue for that particular time point. So, for example, if you want it to forecast for quarter 3 that would be time point 11, so you'd literally plug in the number 11 and solve for Y. So let's think about this. Is it valuable for you to deseasonalize data? Well, maybe, maybe not, but if you don't know the source of the pattern it's probably best to factor in seasonality. And the reason I say that is because seasonality holds true for revenue streams in the US, like people definitely spend more money in Q 4 than they do in Q 1 for example.
And just because there's a seasonal pattern now doesn't mean that there's going to be one in the future, but if you know that you're having higher sales in Q 4 because of Christmas season it may serve you well just to keep this little test in your back pocket for any analysis that you have to do for future forecasting.
- Explain the four different types of financial statements.
- Distinguish between the types of moving averages.
- Determine a seasonal adjusted trend.
- Break down pro-forma financial statements.
- Identify cash flows, and what increased liabilities and decreased earnings generally indicate.
- Tell what a regression is.
- Outline the naive approach.