Question

Question 5. (Forecasting)                                                                                                                                            (17)

Actual sales of washing machines for the first six month in 20XX were as follows:

January      60            February     80          March   42

April           68            May             44          June     72

Use this information and determine in whole numbers:

1. The average actual monthly sales for that period                                                                       (2)         
2. Trends: Reduce this average by 6% per month for the next three months                        (3)
3. Simple Moving Average (SMA): make a forecast for July using a three months simple moving average.               (3)

4. Weighted Moving Average (WMA): make forecast for July, using a three months weighted moving average where the weights are 0.5; 0.3 and 0.2 respectively.                              (4)

5. Make a forecast for July, using the exponential smoothing technique, if the forecast for June was 74 and the smoothing constant is 0, 5.                                                                                  (5)                                 

Answer 1

Average monthly sales =AVERAGE(C3:C8)=61

By 6 % reduction :

Forecast for July =61*0.94=57

Forecast for Aug=57*0.94=54

Forecast for Sept =54*0.94=51

3-month moving average forecast for April=AVERAGE(C3:C5)=61

(Drag this cell formula down till July)

3-month moving average forecast for July=61

3-month weighted moving average forecast for April=0.5*C3+0.3*C4+0.2*C5=62

(Drag this cell formula down till July)

3-month weighted moving average forecast for July=62

Forecast for July using exponential method=G8+0.5*(C8-G8)=73

Calculations are as shown below: