Question

Year	Qtr	t	revenue ($M)
2011	1	1	5.889
	2	2	6.141
	3	3	8.272
	4	4	9.302
2012	1	5	6.436
	2	6	6.932
	3	7	8.987
	4	8	10.602
2013	1	9	7.517
	2	10	7.731
	3	11	9.883
	4	12	12.098
2014	1	13	8.487
	2	14	8.685
	3	15	11.559
	4	16	15.221
2015	1	17	11.132
	2	18	11.203
	3	19	13.83
	4	20	16.979
2016	1	21	12.312
	2	22	13.452
	3	23	17.659
	4	24	21.655
2017	1	25	17.197
	2	26	19.05
	3	27	22.499
	4	28	25.629

Perform a linear time series regression (“Trend Analysis” in Minitab or “Trendline” in Excel) of the historical data. For the parts below, generate a regression output report produced in either Excel or Minitab and submit it with your completed test.

a.   Attach a copy of your “Trend Analysis” or “Trendline” graph in a space created BELOW.

b.   State (here) the equation of the fitted regression line.

c.   On the basis of this regression analysis, calculate and state numerical values of the sales revenue forecasts for all four quarters of 2018.

d.   Calculate and state (here) the RMSE of this simple linear regression. [Hint: Different from forecasting, for regression RMSE = √SSE/√(n-2).]

Answer 1

(a) The output is:

(b) The trend equation is:

y = 4.02 + 0.58*t

(c)

	Demand (y)	Period(x)
Forecast	20.71	29
	21.29	30
	21.86	31
	22.44	32

(d) RMSE = √5.643796 = 2.376