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αequals=0.40.4

with an initial forecast for week 1 of

574574​)

methods to forecast demand for week

1313.

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	574	635	643	732	650	610	729	706	677	716	651	735

​(i) Obtain the trend projection with regression forecast.

Answer 1

Solving for regression coefficients

• Regression: y = a + bx, where a is the intercept and b is the slope of the regression line
• Slope b is given by  

• Intercept, a, is given by

Calculating the intermediate values as shown in the table

	Period (x)	Sales (y)	xy	x square
	1	574	574	1
	2	635	1270	4
	3	643	1929	9
	4	732	2928	16
	5	650	3250	25
	6	610	3660	36
	7	729	5103	49
	8	706	5648	64
	9	677	6093	81
	10	716	7160	100
	11	651	7161	121
	12	735	8820	144
Sum	78	8058	53596	650

So, from the above table, we have calculated

  

Substituting these values to get b and a

So, Y = 616.09+8.524x

Value at x =13,

Y = 616.09+ (8.524*13) = 726.9

To calculate the MAD and MAPE , we need to calculate the error terms

Period (x)	Sales (y) Dt	Forecast	Error (et)	Abs Error |et|	Abs % error |et/Dt|
1	574	624.62	-50.62	50.62	8.82%
2	635	633.14	1.86	1.86	0.29%
3	643	641.66	1.34	1.34	0.21%
4	732	650.19	81.81	81.81	11.18%
5	650	658.71	-8.71	8.71	1.34%
6	610	667.24	-57.24	57.24	9.38%
7	729	675.76	53.24	53.24	7.30%
8	706	684.29	21.71	21.71	3.08%
9	677	692.81	-15.81	15.81	2.34%
10	716	701.34	14.66	14.66	2.05%
11	651	709.86	-58.86	58.86	9.04%
12	735	718.38	16.62	16.62	2.26%
Sum			0.00	382.48	57.28%

In this table, the error is (Dt- Forecasted value)

So MAD =31.87 and MAPE = 4.77%

Now calculating for exponential smoothening

For t=1, we take Ft = Dt and then calculate for other values as shown in table

Period (x)	Sales (y) Dt	Forecast
1	574	574.00
2	635	574.00
3	643	598.40
4	732	616.24
5	650	662.54
6	610	657.53
7	729	638.52
8	706	674.71
9	677	687.23
10	716	683.14
11	651	696.28
12	735	678.17
13		700.90

The forecast for 13th week is 700.9

Now calculating the error terms

		alpha	0.4	0.6
Period (x)	Sales (y) Dt	Forecast	Error (et)	Abs Error |et|	Abs % error |et/Dt|
1	574	574.00	0.00	0.00	0.00%
2	635	574.00	61.00	61.00	9.61%
3	643	598.40	44.60	44.60	6.94%
4	732	616.24	115.76	115.76	15.81%
5	650	662.54	-12.54	12.54	1.93%
6	610	657.53	-47.53	47.53	7.79%
7	729	638.52	90.48	90.48	12.41%
8	706	674.71	31.29	31.29	4.43%
9	677	687.23	-10.23	10.23	1.51%
10	716	683.14	32.86	32.86	4.59%
11	651	696.28	-45.28	45.28	6.96%
12	735	678.17	56.83	56.83	7.73%
Sum			317.25	548.41	79.71%

So MAD =45.7 and MAPE = 6.64%

So, the summary of both the method is

Regression method		Exponential Smoothening
13th week	726.9		13th week	700.9
MAD	31.87		MAD	45.70
MAPE	4.77%		MAPE	6.64%

1)

Trend projection with regression forecast

Steps

1. Click on the two columns of data and insert the scatter plot
2. After the scatter plot, click on any point
3. We see the menu bar and click on trendline-> linear
4. we get this chart