Question

Can you please answer this and show the formulas??

Consider the quarterly sales data for Worthington Health Club shown below:

			Quarter		Total
Year	1	2	3	4	Sales
1	4	1	3	7	15
2	6	5	2	18	31
3	13	1	7	18	39
4	14	11	6	18	49
5	20	11	13	30	74

1. Develop a four-period moving average model and compute MSE for your forecasts. Do not round intermediate calculations. Round your answers to two decimal places.

   Time Period	Observation	Forecast
   1	4
   2	1
   3	3
   4	7
   5	6
   6	5
   7	2
   8	18
   9	13
   10	1
   11	7
   12	18
   13	14
   14	11
   15	6
   16	18
   17	20
   18	11
   19	13
   20	30

   MSE (4-period):  

2. Find the best single exponential smoothing model by evaluating the MSE from 0.1 to 0.9, in increments of 0.1. Do not round intermediate calculations. Round your answers to two decimal places.

   Alpha		MSE
   0.1
   0.2
   0.3
   0.4
   0.5
   0.6
   0.7
   0.8
   0.9

   The model based on a smoothing constant of -Select-0.10.20.30.40.50.60.70.80.9Item 27  is the best.

   How does this model compare with the moving average model?

   The -Select-single exponential smoothing4-period moving averageItem 28  model has the lowest MSE.

Answer 1

a) 4-period moving average model and MSE

EXCEL FORMULAS:

b) Exponential smoothing

EXCEL FORMULAS:

In the above model, change the value of alpha from 0.1 to 0.9 in cell D1 to generate the various forecasts and their corresponding MSEs

Alpha	MSE
0.1	70.39
0.2	57.85
0.3	55.46
0.4	56.55
0.5	59.05
0.6	62.14
0.7	65.35
0.8	68.35
0.9	70.88

The lowest value of MSE is 55.46 for alpha 0.30. Therefore,

The model based on a smoothing constant of 0.30   is the best.

The 4-period moving average model has the lowest MSE.