Question

The CSUSM Restaurant just finished its fourth year of operation. Through the great efforts of its manager and staff, this restaurant has become one of the most popular and fastest-growing restaurants in the SD County.

The manager has recently decided to improve the capacity planning process of the restaurant. To do so, they need to come up with an effective forecasting procedure to predict the monthly sales of foods for up to a year in advance (12 months). Data file RestaurantCSUSM shows the value of food sales ($100s) for the first four years of operation.

You, as the new forecasting analyst, have been asked to propose a new forecasting procedure with a summary of your method, forecasts, and recommendations. Include the followings:

A time series plot. Comment on the underlying pattern in the time series. What forecasting technique(s) do you recommend based on your visualization?

Do you consider moving average as an effective method for forecasting the food sales of this restaurant? Why?

Using dummy variables for seasonality effects, forecast sales for January through December of the fifth year.

Add a trend effect to your forecasting model in (3) and forecast sales for January through December of the fifth year again with the new model.

Which model is expected to give more accurate forecasts, (3) or (4)? Calculate MSE for both techniques.

Assume that restaurant’s sales in January of the fifth year turn out to be $165,000. What was your forecast (and what is the forecasting error)? How do you explain this error to the manager?

Month Sales 1 1331 2 1293 3 1276 4 979 5 1012 6 770 7 798 8 836 9 605 10 715 11 836 12 1133 13 1447 14 1309 15 1359 16 1062 17 1062 18 820 19 864 20 886 21 671 22 715 23 919 24 1265 25 1551 26 1403 27 1458 28 1128 29 1155 30 880 31 913 32 957 33 693 34 814 35 952 36 1293 37 1651 38 1513 39 1558 40 1228 41 1255 42 980 43 1013 44 1015 45 798 46 919 47 1052 48 1394

Answer 1

Time Series Plot. There is cyclical behaviour in the data and centred moving average would be an effective method for forecasting.

Yes, moving average is an effective method for forecasting as there appears to be a cyclical behaviour in the data as shown in the time series plot.

For ease of understanding I have named the given months from Jan15-Dec18. We will be calculating the seasonality of each month (ie Jan – Dec) and use it to predict Sales for next 12 months (Jan19-Dec19)

Steps:

1. First we calculate 4 months moving Average.
2. Then we calculate Centred Average of these 4 months moving Average.
3. Then % Average ie Sales/Centred Average
4. Then we table the % Average for each month (1-12) for the four years (2015-2018) and calculate mean for each month. (As Shown in Table 2)
5. 1200%/The total of these 12 months’ mean will be the x Adjustment Factor
6. Seasonality Index for each month will be X Adj Factor * Month’s Mean ie Seasonality Index for Jan = 115%*1.01 = 1.17
7. Then we calculate ∑X, ∑Y, ∑XY, ∑X² and using these values we calculate the regression equation.
8. Regression Equation will be: Y = 998.06 + 3.09*X
9. Using this equation and Seasonality Index, we calculate value for Predicted Sales for next 12 months. Eg: Jan19 Sales = (998.06 + 3.09*49)*1.17

Table 1

Table 2:

Centred moving average is expected to give better results than linear regression as it accounts the seasonality factor of the data.

From our model , Predicted sales for Jan = 133914.

Error = 165,000 – 133,914 = 31086