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.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

For ease of understanding I have named the given months from Jan15-Dec18. We will be calculating the seasonalisity 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