Question

(a) The Marino Company in Ventura County, southern California sells swimming pool equipment. The sales for swimming pool motors in southern California over the last 12 months is given in Table Q4(a). Forecast the demand from March to December of the current year using the following forecasting models. Use excel for the tabulation.
(i) Three-month simple moving average;
(ii)Three-month weighted moving average. Use 0.7, 0.2, and 0.1 for the weights, with
0.7 for the most recent period, and 0.1 for the oldest period.   
(iii) Simple linear regression;
(iv) Determine the MAD, and SE for all the models, and choose which model is most appropriate.

Table Q4(a) Marino Company Sales of Swimming pool motors
Month					Quantity (units)
January					11,026
February					14,950
March					16,350
April					10,072
May					15,402
June					17,966
July					11,687
August					14,188
September					17,706
October					12,054
November					14,438
December					16,340

Answer 1

We can calculate the desired result as follows:.

a) In order to calculate the Simple moving average, we need to add the quantities for three consecutive months, and then divide the sum by 3.

For example, the three-month simple moving average for month of march comes out to be 14,102.67

= (11,026 + 14,950 + 16,350) / 3

= 14,102.67

We can calculate all the figures in excel as follows:

Formulas used in the excel sheet are

b) In order to calculate the weighted moving average, we need to take the weighted average for three month consecutively and the most recent month having weight 0.7, the preceding month having weight 0.2, and the oldest of the three months having weight 0.1.

For example, the three-month weighted moving average for month of march comes out to be 14,102.67

= [(11,026*0.1) + (14,950*0.2) + (16,350*0.7)]

= [(1,102.6 + 2,990 + 11,445)]

= 15,537.6

We can calculate all the figures in excel as follows:

Formulas used in the excel sheet are:

As there are multiple questions asked, I have solved the first 2. Kindly post the others seperately.

Hope it was helpful for you !!