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

581581​)

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	581581	599599	642642	747747	674674	625625	729729	712712	754754	728728	671671	745745

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

Obtain the trend projection with regression forecast.

The forecast for week 13 is

​(Enter your response rounded to the nearest whole​ number.)

Specify the mean absolute deviation​ (MAD) and mean absolute percent error​ (MAPE). ​(Enter your responses rounded to two decimal​ places.)

MAD	MAPE

​(ii) Obtain the exponential smoothing forecast.

The forecast for week

13 is

​(Enter your response rounded to the nearest whole​ number.)

Specify the mean absolute deviation​ (MAD) and mean absolute percent error​ (MAPE). ​(Enter your responses rounded to two decimal​ places.)

MAD	MAPE

Based on​ MAD, the best method is

trend projection with regressiontrend projection with regression

.

Based on​ MAPE, the best method is

trend projection with regressiontrend projection with regression

.

Answer 1

Answer i)

Trend with regression:

n	At	Ft	et = At-Ft	|et|	|At-Ft|/At
Period	Sales	Forecast	Errors	Absolute Errors	Absolute % Error
1	581581	620941	-39360	39360	6.77%
2	599599	632516	-32917	32917	5.49%
3	642642	644091	-1449	1449	0.23%
4	747747	655666	92081	92081	12.31%
5	674674	667241	7433	7433	1.10%
6	625625	678816	-53191	53191	8.50%
7	729729	690391	39338	39338	5.39%
8	712712	701966	10746	10746	1.51%
9	754754	713541	41213	41213	5.46%
10	728728	725116	3612	3612	0.50%
11	671671	736691	-65020	65020	9.68%
12	745745	748266	-2521	2521	0.34%
13		759841
				MAD	MAPE
				32406.75	4.77%

Trend

In MS Excel,

Select the sales data

Click on Insert and then “Line”

On the line graph, do right click and select “Add Trendline”

Then from the menu, click on the “Display Equation on chart” checkbox.

y = 11575x + 609366

where,

                                           

Y = Ft

x = t

Using it,

Forecast for period 1:

y = 11575x + 609366 = 11575*1 + 609366

Forecast for period 13:

y = 11575x + 609366 = y = 11575*13 + 609366 = 759841

MAD (Mean Absolute Deviation) = Sum of all Absolute Errors / Total number of period = 388881/12 = 32406.75

MAPE (Mean Absolute Percentage Error) = (Sum of all Absolute % error / Total number of period) =57.27% / 12 = 4.77%

Answer ii)

Exponential Smoothing:

n	At	Ft	et = At-Ft	|et|	|At-Ft|/At
Period	Sales	Forecast	Errors	Absolute Errors	Absolute % Error
1	581581	581581	0	0	0.00%
2	599599	581581	18018	18018	3.01%
3	642642	588788	53854	53854	8.38%
4	747747	610330	137417	137417	18.38%
5	674674	665297	9377	9377	1.39%
6	625625	669048	-43423	43423	6.94%
7	729729	651679	78050	78050	10.70%
8	712712	682899	29813	29813	4.18%
9	754754	694824	59930	59930	7.94%
10	728728	718796	9932	9932	1.36%
11	671671	722769	-51098	51098	7.61%
12	745745	702330	43415	43415	5.82%
13		719696
				MAD	MAPE
				44527.32	6.31%

Exponential smoothing Forecast formula:

F (t+1)= Ft+α(At-Ft)

F (t+1) is Forecast for the current period

Actual Sales (At)

Forecast Sales (Ft)

Smoothing constant = α = 0.4

Forecast for period 3 = 581581+0.4*(599599-581581) = 588788

Forecast for period 13 = 702330+0.4*(745745-702330) = 719696

MAD (Mean Absolute Deviation) = Sum of all Absolute Errors / Total number of period = 534328/12 = 44527.32

MAPE (Mean Absolute Percentage Error) = (Sum of all Absolute % error / Total number of period) =75.70% / 12 = 6.31%