Question

Use the case below to answer the following question(s) (Total marks = 100)

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 succesful, 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 make 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

Answer 1

i)

Time series plot

The time series plot is obtained in excel by first arranging the data values in a single column the follow the step,

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) 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 (30 marks)

ii)

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

From the time series plot we can observe that the  food and beverage sales for the restaurant follows a seasonal up-down pattern hence seasonal indexes make intuitive sense for the monthly sales data. From the plot there is a high seasonal sales month is January and low seasonal sale month is September.

iii)

The following assumptions need to be taking place for time series forecast,

The sales data should follow stationary pattern (No trend in the sales data such that mean and variance are constant over the year) and no large outliers.

Regression analysis

The regression analysis is done in excel for seasonal forecast for fourth year by following steps

Step 1: Write the data values in excel. The data values are 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,

iv)

Recommendations: The forecast system should be updated if trend (upward or downward) appears in the sales data over yearly data