Question

Year	Quarter	Sales (in Billions)
2009	1	2.62
2009	2	2.33
2009	3	2.4
2009	4	2.42
2010	1	2.72
2010	2	2.53
2010	3	2.61
2010	4	2.84
2011	1	2.95
2011	2	2.79
2011	3	2.93
2011	4	3.03
2012	1	3.44
2012	2	3.2
2012	3	3.3
2012	4	3.36
2013	1	3.79
2013	2	3.56
2013	3	3.74
2013	4	3.8
2014	1	4.24
2014	2	3.87
2014	3	4.15
2014	4	4.18
2015	1	4.8
2015	2	4.56
2015	3	4.88
2015	4	4.91
2016	1	5.37
2016	2	4.99
2016	3	5.24
2016	4	5.71
2017	1	5.73
2017	2	5.29
2017	3	5.66
2017	4	5.7
2018	1	6.07
2018	2	6.03
2018	3	6.31

Find a regression forecast.

• Pair each value of the current quarter’s demand with the previous quarter’s demand.

• Find the values β0 and β1 for the equation yt = β0 + β1yt−1.

• Use the equation to find the forecast for each quarter.

• Calculate the MAD and MSE.

Answer 1

Step 1
Put the data in excel as shown below.

Step 2
We need to create y and x for the regression.
We start with value for second quarter of 2009,as the first value of y.
In x, the first value will be the value of first quarter of 2009.
In this manner we create the data, as shown in the screenshot below.

Step 3
From the data analysis tab, select regression and update the value as shown below.

Step 4
From the regression output we use the variable coefficient (highlighted in yellow) to formulate the regression equation as given below.

Step 5

Using the above equation we calculate the predicted value as shown below. The first predicted values is calculated as an example

$y = \beta_0 + \beta_1 Y_{(t-1)}\\\\ y = 0.078694 + 1.004611 Y_{(t-1)}\\ y = 0.078694 + 1.004611 \times 2.62 } = 2.71\\$

Step 6.

We find the residuals by using the formula
residuals = y - predicted

Step 7.

We find the absolute value and square of the residual by using the ABS() and predicted^2 in excel.

Step 8 We calculate the MAD and MSE as given below.

MAD = Average(Absolute error)
MSE = Average (Square of the error).