Question

i. Find the forecast for week 13.

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 a = 0.4 with an initial forecast for week 1 of 592) 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? 5 6 9 Period Sales 1 592 2 610 3 644 4 730 7 734 8 703 10 708 11 658 12 750 667 624 770

ii. Now obtain the exponential smoothing forecast for the weeks.

Answer 1

Below are the calculations with each method

& Cut Σ AutoSum , Calibri A - 11 - AF 'Wrap Text T General WE Y E Copy Fill Paste BI U - 1 $ - %, 6.0 ,00 .00 →.0 Insert Delete Format Merge & Center Format Painter Clear Conditional Format as Cell Formatting Table Styles Styles Sort & Find & Filter - Select Editing Clipboard Font Alignment Number Cells E25 X fic A F G H M N 1 2 3 4 K L ii. Trend projection with regression. Y = a + bx Y is Forecast, X is year, a is intercept and bis slope a= 613.50 INTERCEPT(B7:B18,A7:A18) 10.62 SLOPE(B7:B18,A7:A18) Forecast Error Mean absolute deviation=Sum of Demand-Forecastſ/n MAPE = Sum of (Error %)/n Mean absolute deviation=Sum of Demand-Forecast|/n MAPE = Sum of (Error %)/n 5 b= 6 Period Error % Error % 7 1 8 2 25 ABS(B8-08) ABS(B9-09) E8/B8 E9/B9 ABS(B8-18) ABS(B9-19) K8/B8 K9/B9 9 635 $J$4+$J$5*A8 645 $J$4+$J$5* A9 656 3 B C D E i. Exponential smoothing F(t+1) = alpha*A(t) + (1-alpha)*F(t) Alpha= 0.4 Forecast (y)= 592 Error = Actual demand- Forecast Demand Forecast Error 592 610 592 18 644 599 45 730 617 113 667 662 5 624 664 40 734 648 86 703 682 21 770 691 79 708 722 14 658 717 59 750 693 57 716 48.7 1 74 4 10 11 12 5 667 6 13 7 2.95% 6.96% 15.46% 0.71% 6.44% 11.70% 2.92% 10.30% 2.03% 8.91% 7.58% 14 4.05% 0.21% 10.14% 0.06% 8.52% 6.29% 0.65% 7.92% 1.65% 10.98% 1.22% 0 53 46 5 61 12 72 9 677 688 698 709 720 730 741 752 8 9 10 11 15 16 17 18 12 19 13 AVERAGE(E8:E18) AVERAGE(68:618) 32.6 AVERAGE(K8:K18) AVERAGE(M8:M18) 6.91% MAPE 4.70% MAPE MAD MAD 20 21 22 23 24 25 26 27

Please give thumbs up/ likes if you find this answer helpful. Thank you!