In this video, I will develop a regression equation
for this seasonal sales data with trend and use it to make forecasts for Year 4.
We first define dummy or indicator variables for the quarters.
We define Q1 = 1 if Quarter 1 and 0 otherwise
Q2 = 1 if Quarter 2 and 0 otherwise Q3 = 1 if Quarter 3 and 0 otherwise
And thus, Q4 will be the baseline (that is, the reference quarter
that others are compared against) or what we refer to as the omitted category.
That is, if Quarter 4, then Q1, Q2, and Q3 will all be zero.
Note that any of the four quarters could be chosen to be the baseline category.
I’m going to begin by creating a column for the dependent variable
and call it Sales. And then drag Year 2 and Year
3 data into that column beneath Year 1 data. I’m going to replicate the quarter numbers
by copying 1 2 3 4 from Year 1 and pasting for Years 2 and 3.
Next, I’m going to create a column title Q1 for Quarter 1 here
and drag to the right for quarters 2 and 3. Note that we don’t need to create Q4
since we’ve decided to choose Quarter 4 as baseline.
Now for Quarter 1, I will enter 1 for Q1 and zeros for Q2 and Q3.
For Quarter 2, 0 for Q1, 1 for Q2, and 0 for Q3.
For Quarter 3, 0, 0 and 1 for Q3. And it will be all zeros for Quarter 4.
I’m going to copy that and paste for Years 2 and 3.
Since we have trend, we need to set up a Period or t variable to account for
increasing or decreasing sales over time. I’m going to type 1 and 2 here, highlight
and drag the fill handle all the way down for the rest of the periods.
To generate the regression output in Excel, we go to Data > Data Analysis,
select Regression, and click OK.
For Y Range, I will select the Sales data For X range, I will select from Q1 up to Period
I will check Labels because I selected the titles And Click OK.
And here is the output. To create the regression equation,
we only need this bottom left corner of the output containing the coefficients.
The regression equation is therefore as follows:
Here again is the regression equation we just obtained from Excel.
And this table here shows us the period number for each year.
Since, for Year 1 to Year 3, t = 1 to 12. Then, for Year 4, t will be from 13 to 16.
So, for Quarter 1 of Year 4, Q1 will be 1; Q2 and Q3 will be 0, and t will be 13.
Thus we can forecast sales for Quarter 1 of Year 4 by substituting those values into the
regression equation as shown here. For Quarter 2 of Year 4,
Q1 is 0, Q2 is 1, Q3 is 0, t is 14, and the sales forecast is calculated as seen here.
We do the same for Quarter 3 of Year 4 Q3 is 1, t is 15 and the sales forecast
is calculated as shown here. Now for Quarter 4,
Q1, Q2, Q3 are all zero and t is 16. Plugging those into the equation,
we can obtain the sales forecast for that period in the same manner.
And that’s it for this video. Thanks for watching.