Sales Forecasting Methods Solutions

1. National Scan, Inc., sells radio frequency inventory tags. Monthly sales for a seven –month period were as follows: Month Feb. Mar. Apr. May. Jun. Jul. Aug. Sales (000 units) 19 18 15 20 18 22 20 a. Plot the monthly data b. Forecast September sales volume using each of the following: (1) A linear trend equation (2) A five-month moving average (3) Exponential smoothing with a smoothing constant equal to 0.2, assuming a March forecast of 19(000). (4) The naive approach (5) A weighted average using 0.6 for August, 0.3 for July, and 0.1 for June. a. 25 20 15 Series1 10 5 0 Feb. Mar. Apr. May. Jun. Jul. Aug. b. 1) x 1 y 19 xy 19 2 3 18 15 36 45 4 20 80 5 6 18 22 90 132 7 20 140 From Table 3.1 with n = 7, b a 7 ( 542 )  28 (132 ) 7 (140 )  28 ( 28 ) 132  . 50 ( 28 ) 7  . 50  16 . 86 132 542 For Sept., x= 8, and Y8 = 16.86 + .50(8) = 20.86 (000) 2) MA 5  15 20 18 22  20 5  19 x = 28, x2 = 140 3) Month April May Forecast = F(old) 18.8 = 19 18.04 = 18.8 + .20[Actual – F(old) ] + .20[ 18 – 19 ] + .20[ 15 – 18.8 ] June July 18.43 18.34 = 18.04 = 18.43 + .20[ 20 + .20[ 18 – 18.04 ] – 18.43 ] August 19.07 = 18.34 + .20[ 22 – 18.34 ] = 19.07 + .20[ 20 – 19.07 ] 4) September 19.26 20 5) .6 (20) + .3(22) + .1(18) = 20.4 2. Two different forecasting techniques (F1 and F2) were used to forecast demand for cases of bottled water. Actual demand and the two sets of forecasts are as follows: Period 1 2 3 4 5 6 7 8 Demand 68 75 70 74 69 72 80 78 Predicted Demand F1 66 68 72 71 72 70 71 74 F2 66 68 70 72 74 76 78 80 a. Compute MAD for each set of forecasts. Given your results, which forecast appears to be more accurate? Explain. b. Compute the MSE for each set of forecasts. Given your results, which forecast appears to be more accurate? c. Compute MAPE for each set of forecasts. Given your results, which forecast appears to be more accurate? Period 1 2 3 4 5 6 7 8 Demand F1 68 75 70 74 69 72 80 78 e |e| 66 68 72 71 72 70 71 74 e^2 -2 -7 2 -3 3 -2 -9 -4 2 7 2 3 3 2 9 4 32 F2 4 49 4 9 9 4 81 16 176 e 66 68 70 72 74 76 78 80 |e| -2 -7 0 -2 5 4 -2 2 a. MAD F1: 32/8 = 4.0 MAD F2: 24/8 = 3.0 F2 appears to be more accurate. b. MSE F1: 176/8 = 22 MSE F2: 106/8 = 13.25 F2 appears to be more accurate. c. MAPE F1 = .4269/8 = .0534=5.34% MAPE F2 = .3284/8 = .0411=4.11% F2 appears to be more accurate. 3. The month of November is over, and by now we know that the actual demand for item number #1984 during this period was 35 units. Examine the data shown below. You can download it in the form of a comma separated values file using the link provided below. Month t At St Gt Ft+1 October 9 36 36.82 1.09 37.91 November 10 Assume that Alpha=0.25 and Beta = 0.10. Use a level and trend exponential smoothing approach to forecast the demand for December (t=11). Month t October November Answer: 38.2 At 9 10 St Gt Ft+1 36 36.82 1.09 37.91 35 37.18 1.017 38.2 Alpha Beta 0.25 0.1 e^2 2 7 0 2 5 4 2 2 24 4 49 0 4 25 16 4 4 106 4. Building upon the bagel forecasting problem, suppose it is now the end of Tuesday, and we have seen that the actual sales for Tuesday (t=106) was 113 bagels. In the data below you are given the updated seasonality factors, which have already been normalized. t 101 102 103 104 105 106 Day Tue. Wed. Thu. Fri. Mon. Tue. At St 76 113 Gt 121 124.4 0.3 0.5 ct Ft, t+1 Alpha 0.749 Beta 1.249 Gamma 0.999 1.498 60.7 0.505 93.7 0.1 0.08 0.05 Using the same smoothing factors and the Holt-Winter approach, what is your forecast demand for Wednesday (t=107)? Answer: 160.13