Question

A convenience store recently started to carry a new brand of soft drink. Management is interested in estimating future sales volume to determine whether it should continue to carry the new brand or replace it with another brand. The following table provides the number of cans sold per week. Use both the trend projection with regression and the exponential smoothing​ (let alpha α=0.4) with an initial forecast for week 1 of 569​) methods to forecast demand for week 13. Compare these methods by using the mean absolute deviation and mean absolute percent error performance criteria. Does your analysis suggest that sales are trending and if​ so, by how​ much?

Period   1   2   3   4   5   6   7   8   9   10   11   12
Sales   569   615   645   742   640   606   732   718   713   690   678   738
Observation   1   2   3   4   5   6   7   8   9   10   11   12

(i) Obtain the trend projection with regression forecast.

The forecast for week 13 is

                                                                                                                    

Week 13 corresponds to t=13. Obtain the forecast for the next week by substituting t=13 into the regression equation.

​(ii) Now obtain the exponential smoothing forecast.

The exponential smoothing method is a weighted moving average method that calculates the average of a time series by giving recent demands more weight than earlier demands. The equation for the forecast​ is:

Ft+1=α(Demand this period)+(1−α)(Forecast calculated last period)=αDt+(1−α)Ft.

Answer 1

(i) Linear Regression:

Let X be the number of Period (Week) and Y be the sales.

The Linear Regression equation shall be : Y = MX + C

Sum of X = 1+ 2 + 3+ 4 + 5+ 6 + 7 + 8 + 9 + 10 + 11 + 12 = 78

Sum of Y = 569 + 615 + 645 + 742 + 640 + 606 + 732 + 718 + 713 + 690 + 678 + 738 = 8086

Mean Value of X = 78 / 12 = 6.5

Mean Value of Y = 8086 / 12 = 673.83

X	Y	X -Mean X	(X - Mean X)^2	Y - Mean Y	(X - Mean X)(Y - Mean Y)
1	569	-5.5	30.25	-104.83	576.58
2	615	-4.5	20.25	-58.83	264.75
3	645	-3.5	12.25	-28.83	100.92
4	742	-2.5	6.25	68.17	-170.42
5	640	-1.5	2.25	-33.83	50.75
6	606	-0.5	0.25	-67.83	33.92
7	732	0.5	0.25	58.17	29.08
8	718	1.5	2.25	44.17	66.25
9	713	2.5	6.25	39.17	97.92
10	690	3.5	12.25	16.17	56.58
11	678	4.5	20.25	4.17	18.75
12	738	5.5	30.25	64.17	352.92

Sum of (X - Mean X)^ 2) = 30.25 + 20.25 + 12.25 + 6.25 + 2.25 + 0.25 + 0.25 + 2.25 + 6.25 + 12.25 + 20.25 + 30.25 = 143

Sum of ((X - Mean X)(Y - Mean Y)) = 576.58 + 264.75 + 100.92 - 170.42 + 50.75 + 33.92 + 29.08 + 66.25 + 97.92 + 56.58 + 18.75 + 353.92 = 1478

M = Sum of ((X - Mean X)(Y - Mean Y)) / Sum of (X - Mean X)^ 2) = 1478 / 143 = 10.335

C = Mean Y - ( M x Mean X) = 673.83 - ( 10.335 x 6.5 ) = 673.83 - 67.1775 = 606.6525

Therefore, the linear regression equation is as follows:

Y = 10.335 X + 606.6525

So, the forecast for Week 13:

Y = ( 10.335 x 13 ) + 606.6525

= 134.355 + 606.6525

= 741.0075 = 741 (Rounding off)

Exponential smoothing forecast:

α = 0.4

Forecast for Week 1 = 569

Forecast for Week (n +1 ) = α (Sales of Week n) + (1 -α)( Forecast of Week n)

So, the forecast shall be as per below table:

Week	Sales	Forecast
1	569	569.000
2	615	569.000
3	645	587.400
4	742	610.440
5	640	663.064
6	606	653.838
7	732	634.703
8	718	673.622
9	713	691.373
10	690	700.024
11	678	696.014
12	738	688.809
13		708.485

Forecast for Week 13 = 708.485 = 708 (Rounding off)

Mean Absolute Deviation(MAD) and Mean Absolute Percent Error(MAPE):

Linear Regression:

Period	Sales	Forecast	Error (Sales - Forecast)	Absolute value of Error	Percent of Absolute Error(Absolute Error / Sales)
1	569	616.988	-47.988	47.988	0.084
2	615	627.323	-12.323	12.323	0.020
3	645	637.658	7.342	7.342	0.011
4	742	647.993	94.007	94.007	0.127
5	640	658.328	-18.328	18.328	0.029
6	606	668.663	-62.663	62.663	0.103
7	732	678.998	53.002	53.002	0.072
8	718	689.333	28.668	28.668	0.040
9	713	699.668	13.333	13.333	0.019
10	690	710.003	-20.003	20.003	0.029
11	678	720.338	-42.338	42.338	0.062
12	738	730.673	7.327	7.327	0.010

Mean Absolute Deviation = Mean value of Absolute Error = Sum of Absolute Error / Number of weeks

= 407.32 / 12 =  33.94

Mean Absolute Percent Error = Sum of Percent of Absolute Error / Number of Weeks = 0.607 / 12

= 0.051 = 5.1 %

Exponential smoothing:

Week	Sales	Forecast	Error (Sales - Forecast)	Absolute value of Error	Percent of Absolute Error(Absolute Error / Sales)
1	569	569.000	0.000	0.000	0.000
2	615	569.000	46.000	46.000	0.075
3	645	587.400	57.600	57.600	0.089
4	742	610.440	131.560	131.560	0.177
5	640	663.064	-23.064	23.064	0.036
6	606	653.838	-47.838	47.838	0.079
7	732	634.703	97.297	97.297	0.133
8	718	673.622	44.378	44.378	0.062
9	713	691.373	21.627	21.627	0.030
10	690	700.024	-10.024	10.024	0.015
11	678	696.014	-18.014	18.014	0.027
12	738	688.809	49.191	49.191	0.067

Mean Absolute Deviation = Sum of Absolute Error/ Number of weeks = 546.594 / 12

= 45.55

Mean Absolute Percent Error = Sum of Percent Absolute Error / Number of weeks = 0.789 / 12

= 0.065 = 6.5 %

Linear Regression provides a better trend of the forecasts, as the Mean Absolute Deviation and Mean absolute Percent Error is lower in the case of the Linear Regression method.