Question

For the data given below, forecast period 17 using following methods and suggest the best forecasting model

1. Simple Moving Average (Three Period)

2. Weighted Average (50% on t-1, 30% on t-2, 20% on t-3)

3. Exponential Smoothing (α=.1), F1= y intercept from regression)

4. Exponential Smoothing (α=.3), F1= y intercept from regression)

5. Exponential Smoothing with trend (α=.3) and (δ=.3), F1= y intercept from regression)**

5. Exponential Smoothing with trend (α=.1) and (δ=.3), F1= y intercept from regression)**

6. Regression analysis

7. Plot a single graph representing actual sales and all of the above forecastined sales.

8. Calculate MAD, MSE and MAPE. Suggest which of the above method is most accurate.

**When initial trend forecast (T1) is not given, use slope of the regression line as initial trend.

Year	Quarter	Period	Sales
		0
1	I	1	98
	II	2	106
	III	3	109
	IV	4	133
2	I	5	130
	II	6	116
	III	7	133
	IV	8	116
3	I	9	138
	II	10	130
	III	11	147
	IV	12	141
4	I	13	144
	II	14	142
	III	15	165
	IV	16	173
5	I	17

Answer 1

Question: For the data given below, forecast period 17 using following methods and suggest the best forecasting model

Answer:

Note: I have include the calculations with excel formulas for your reference.

Let us first calculate Linear regression Equation:

To find the linear regression equation we need to find Slope and Intercept, which is given by the formulas:

Slope (b) = ((N * ∑xy) - (∑x * ∑y)) / ((N * ∑(x^2)) - ((∑x)^2)) = 3.82

Intercept (a) = (∑(x^2) - (Slope (b) * ∑xy)) / N = 100.08

Therefore the Linear Regression Equatio is:

Y-hat = 100.08 + 3.82 (x)

1. Simple Moving Average (Three Period)

Calculation:

Excel Formulas:

2. Weighted Average (50% on t-1, 30% on t-2, 20% on t-3)

Calculations:

Excel Formulas:

3. Exponential Smoothing (α=.1), F1= y intercept from regression)

Calculations:

Excel Formulas:

4. Exponential Smoothing (α=.3), F1= y intercept from regression)

Calculations:

Excel Formulas:

5. Exponential Smoothing with trend (α=.3) and (δ=.3), F1= y intercept from regression)**

Calculations:

Excel Formulas:

5. Exponential Smoothing with trend (α=.1) and (δ=.3), F1= y intercept from regression)**

Calculations:

Excel Formulas:

6. Regression analysis

Calculations:

Excel Formulas:

7. Plot a single graph representing actual sales and all of the above forecastined sales.

Conclusion: Linear Regression Forecast Model gives better forecast in terms of MAD, MSE and MAPE since the values are lowest when compared to other forecasting techniques and is closest to the actual values. Hence, Linear Regression Forecast Model provides best forecast for this particular time period.