Question

A certain restaurant located in a resort community is owned and operated by Karen Payne. The restaurant just completed its third year of operation. During this time, Karen sought to establish a reputation for the restaurant as a high quality dining establishment that specialises in fresh seafood. The efforts made by Karen and her staff proved successful, and her restaurant is currently one of the best and fastest-growing restaurant in their neighbourhood.
Karen concluded that, to plan better for the growth of the restaurant in the future, she needs to develop a system that will enable her to forecast food and beverage sales by month for up to one year in advance. Karen compiled the following data on total food and beverages sales for the three years of operation:

Food and beverage sales for the restaurant (R1000s)

month	First year	Second Year	Third year
January	242	263	282
February	235	238	255
March	232	247	265
April	178	193	205
May	184	193	210
June	140	149	160
July	145	157	166
August	152	161	174
September	110	122	126
October	130	130	148
November	152	167	173
December	206	230	235

Perform an analysis of the sales data for the restaurant. Prepare a report for Karen that summarises your findings, forecasts, and recommendations. Include the following:
(i) A graph of the time series.
(ii) An analysis of the seasonality of the data. Indicate the seasonal indexes for each month, and comment on the high seasonal and low seasonal sales months. Do the seasonal indexes make intuitive sense? Discuss.
(iii) Stating any assumptions you may make, provide forecast sales for January through December of the fourth year.
(iv) Recommendations as to when the system that you developed should be updated to account for new sales data that will occur. (10 marks

Answer 1

i)

The time series plot is obtained in excel by following these steps,

Step 1: Arrange the data values in a single column. The screenshot is shown below,

Step 2: Select all the data values > INSERT > Recommended Charts > X Y (Scatter) > Scatter with Smooth Lines and Markers > OK. The screenshot of the chart is shown below,

ii)

From the time series plot we can observe that,

High sales month: January

Low sales month: September

From the plot we can see that there is up and down seasonal pattern in the sales data and the monthly sales pattern shows a seasonal pattern each year hence we can say that seasonal indexes make intuitive sense

Let the seasonal indexes for each months are defined as,

Indexes	Description
s1	1 if month January selected otherwise 0
s2	1 if month February selected otherwise 0
s3	1 if month March selected otherwise 0
s4	1 if month April selected otherwise 0
s5	1 if month May selected otherwise 0
s6	1 if month June selected otherwise 0
s7	1 if month July selected otherwise 0
s8	1 if month August selected otherwise 0
s9	1 if month September selected otherwise 0
s10	1 if month October selected otherwise 0
s11	1 if month November selected otherwise 0

iii)

Assumption:

To perform the seasonal analysis we have taken the assumption that the time series sales data are stationary (no trend) such that series is normally distributed and and the mean and variance are constant over time.

Regression analysis

To forecast sales for fourth year, the regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Sales' column, Input X Range: 'S1, S2,.....S11' column then OK. The screenshot is shown below,

The result is obtained. The screenshots are shown below,

The regression equation is.

The seasonal forecast for year 4 is obtained by putting the value of independent variables in above regression equation,

month	s1	s2	s3	s4	s5	s6	s7	s8	s9	s10	s11	Forecast
January	1	0	0	0	0	0	0	0	0	0	0	262.3333
February	0	1	0	0	0	0	0	0	0	0	0	242.6667
March	0	0	1	0	0	0	0	0	0	0	0	248
April	0	0	0	1	0	0	0	0	0	0	0	192
May	0	0	0	0	1	0	0	0	0	0	0	195.6667
June	0	0	0	0	0	1	0	0	0	0	0	149.6667
July	0	0	0	0	0	0	1	0	0	0	0	156
August	0	0	0	0	0	0	0	1	0	0	0	162.3333
September	0	0	0	0	0	0	0	0	1	0	0	119.3333
October	0	0	0	0	0	0	0	0	0	1	0	136
November	0	0	0	0	0	0	0	0	0	0	1	164
December	0	0	0	0	0	0	0	0	0	0	0	223.6667

iv)

Since over system doesn't taken account for trend in the sales data, the forecast model need to be updated if the trend (upward or downward) occurs with time.