Question

PLEASE USE EXCEL/SHOW STEPS ON EXCEL

For the following data set below: 1) USE THE Weighted Moving Averages in forecasting this data. 2) Write a brief (no more than 12 page) explanation of why you chose the method you did - observations about the data and characteristics of the chosen methodology. 3) Generate a forecast for 2002. Show the spreadsheet(s) you used to build the model. Annotate briefly to explain your methodology.

TABLE 1		SALES FIGURES
Month	1993	1994	1995	1996	1997	1998	1999	2000	2001
January	139.7	165.1	177.8	228.6	266.7	431.8	381	431.8	495.3
February	114.3	177.8	203.2	254	317.5	457.2	406.4	444.5	533.4
March	101.6	177.8	228.6	266.7	368.3	457.2	431.8	495.3	635
April	152.4	203.2	279.4	342.9	431.8	482.6	457.2	533.4	673.1
May	215.9	241.3	317.5	355.6	457.2	533.4	495.3	558.8	749.3
June	228.6	279.4	330.2	406.4	571.5	622.3	584.2	647.7	812.8
July	215.9	292.1	368.3	444.5	546.1	660.4	609.6	673.1	800.1
August	190.5	317.5	355.6	431.8	482.6	520.7	558.8	660.4	736.6
September	177.8	203.2	241.3	330.2	431.8	508	508	609.6	685.8
October	139.7	177.8	215.9	330.2	406.4	482.6	495.3	584.2	635
November	139.7	165.1	215.9	304.8	393.7	457.2	444.5	520.7	622.3
December	152.4	177.8	203.2	292.1	406.4	431.8	419.1	482.6	622.3

Answer 1

For the above question, we will use Weighted Moving average method to present the forecast for 2002. Since, the question is silent on weights to be assigned to various years and the approach, we will define it as follows:

1. The weights will be assigned to the years as follows:

	1993	1994	1995	1996	1997	1998	1999	2000	2001
Weights	5	5	5	5	10	10	15	20	25

2. This is with a view to give maximum weightage (25%) to recent most year (2001) and decreases backward. The understanding is that the data which is more than 5 years old will impact the forecast equally irrespective of the duration / gap.

This is because the sales numbers have shown a jump of around 30% between 1996 and 1997. Hence, 1997 onwards the data seems more relevant. Thereafter, the growth looks more consistent.

3. The 2002 forecast will be closely affected by 2001 numbers and so it is right to analyse the numbers by giving highest weightage to 2001.

This answers Part 1 and 2 of the question.

Below is the sales forecast for 2002:

Month	2002 Sales forecast
January	372.745
February	398.145
March	443.865
April	483.87
May	528.955
June	601.98
July	612.775
August	565.15
September	511.175
October	481.965
November	452.755
December	440.055

We see that the forecast is a conservative one and more closer to the 1999 numbers.

The formula SUMPRODUCT in excel can be used to quickly calculate the Weighted moving average for each month. Please note that the weights for all months are same in a year.