Question

Question 2

Poppy's Seafood Restaurant just completed its 3rd year of operation in southern Florida.

Poppy wants to be better able to forecast sales by month for up to one year in advance.

The second worksheet in the Excel workbook shows sales ($000) for the first 3 years

Perform an analysis of the sales data for Poppy's Seafood Restaurant. Include the following:

(1) A time series plot. Comment on the underlying pattern in the time series.

(2) Develop a regression model to capture trend and seasonality patterns. Comment on the seasonality patterns you find in your model? Do they make intuitive sense?

(3) Using your "best" model, forecast sales for January through December of the fourth year

(4) Assume January sales for the fourth year actually turn out to be $290,000. What was your forecast error? Is this considered large or small? Why?

Month	Sales ($000)
Jan-15	242
Feb-15	235
Mar-15	232
Apr-15	178
May-15	184
Jun-15	140
Jul-15	145
Aug-15	152
Sep-15	110
Oct-15	130
Nov-15	152
Dec-15	206
Jan-16	263
Feb-16	238
Mar-16	247
Apr-16	193
May-16	193
Jun-16	149
Jul-16	157
Aug-16	161
Sep-16	122
Oct-16	130
Nov-16	167
Dec-16	230
Jan-17	282
Feb-17	255
Mar-17	265
Apr-17	205
May-17	210
Jun-17	160
Jul-17	166
Aug-17	174
Sep-17	126
Oct-17	148
Nov-17	173
Dec-17	235

Answer 1

(1)

The nature of the time series plot suggests that there is a clear sign of seasonality though the trend is very feeble.

(2)

We approach a multiple regression model to capture both trend and seasonality.

The nature of the data shows that the seasonality was observable n a month on month basis. Therefore, each of the months is taken as a season i.e. 12 seasons in a month.

(3)

2018 forecast

Month	Period (t)	Seasonal indices	De-seasonalized Trend forecast	Adjusted forecast
Jan	37	1.4	204.4	285.7
Feb	38	1.3	205.3	265.5
Mar	39	1.3	206.3	272.5
Apr	40	1.0	207.2	211.9
May	41	1.0	208.1	217.0
Jun	42	0.8	209.0	166.7
Jul	43	0.8	209.9	174.5
Aug	44	0.9	210.8	182.4
Sep	45	0.6	211.7	134.7
Oct	46	0.7	212.6	154.1
Nov	47	0.9	213.5	186.7
Dec	48	1.2	214.4	255.7

(4)

Month	Period (t)	Seasonal indices	De-seasonalized Trend forecast	Adjusted forecast (Ft)	Actual (At)	Error (At - Ft)	|At - Ft|	Error^2
Jan	37	1.4	204.4	285.7	290.0	4.3	4.3	18.24

The error seems small with respect to the absolute value of the variable.

The % error is only 4.3 / 290 = 1.5%