Forecasting HW The following is the recent historical sales of Sony HDTV at a local BestBuy store. Month Jan Feb Mar Apr May 63 Actual HDTV sales 6 62 70 48 3 62 +70 +48 +63 I. (a) Use a 4-month moving average to forecast sales for June. __ (b) If June actually experienced a demand of 75)what is the forecast for July? 2. (a) Using weighted moving average method. ivith weights of 0.S one period ago, (b) If June actually experienced a demand of 75, what is the forecast for July? (a) Use exponential smoothing, first with a smoothing constant of 0.2 and then 0.3 two ago, and 0.2 three periods ago, to forecast sales for June. 3. with one of 0.9, to develop forecasts for months Feb. through June. Assuming the forecast for January had been 70. (b) Compute the two sets of MAD (a 0.2 and a 0.9) using data from Jan. through May. Which smoothing constant is a better choice by evaluating MAD? (c) Compute TS using data from Jan. through May, for the forecasting with a = 0.2. Does the store manager need to re-evaluate or adjust the forecasting model used? 4. Use simple linear regression to forecast sales for June. Selected Answers: 1. (a) F6- 60.75 2, (a) F6 = 59.9 3, (a) a-02: F6 = 63.85 a=0.9: F6 = 61.71 (b) a = 02: MAD is about 7.11; a-09: MAD is about 10.05; smoothing constant 0.2 is better because the error measurement values are smaller. (c) The TS value is out of acceptable range, indicating bias in the forecasting and an adjustment is needed. 4. F6=56.2
Expert Answer
1
(a) F6 = (62+70+48+63) / 4 = 60.75
(b) F7 = (70+48+63+75) / 4 = 64.00
2.
(a) F6 = (0.2*70 + 0.3*48 + 0.5*63) = 59.9
(b) F7 = (0.2*48 + 0.3*63 + 0.5*75) = 66.0
3.
(a)
Jan | Feb | Mar | Apr | May | Jun | |
Actual Sales (At) | 65 | 62 | 70 | 48 | 63 | |
Forecast [Ft = Ft-1 + 0.2(At-1 – Ft-1)] |
70 | 69 | 67.6 | 68.08 | 64.06 | 63.85 |
Jan | Feb | Mar | Apr | May | Jun | |
Actual Sales (At) | 65 | 62 | 70 | 48 | 63 | |
Forecast [Ft = Ft-1 + 0.9(At-1 – Ft-1)] |
70 | 65.5 | 62.35 | 69.24 | 50.12 | 61.71 |
(b)
Jan | Feb | Mar | Apr | May | MAD | |
Actual Sales (At) | 65 | 62 | 70 | 48 | 63 | |
Forecast [Ft = Ft-1 + 0.2(At-1 – Ft-1)] |
70 | 69 | 67.6 | 68.08 | 64.06 | |
Absolute Deviation |At – Ft| |
5 | 7 | 2.4 | 20.08 | 1.064 | 7.1088 |
Forecast [Ft = Ft-1 + 0.9(At-1 – Ft-1)] |
70 | 65.5 | 62.35 | 69.24 | 50.12 | |
Absolute Deviation |At – Ft| |
5 | 3.5 | 7.65 | 21.24 | 12.88 | 10.0523 |
Since MAD with alpha = 0.2 is smaller, chose alpha = 0.2
(c)
Jan | Feb | Mar | Apr | May | |
Actual Sales (At) | 65 | 62 | 70 | 48 | 63 |
Forecast [Ft = Ft-1 + 0.2(At-1 – Ft-1)] |
70 | 69 | 67.6 | 68.08 | 64.06 |
Absolute Deviation |At – Ft| |
5 | 7 | 2.4 | 20.08 | 1.064 |
Cumulative MAD | 5 | 6 | 4.8 | 8.62 | 7.109 |
Error (At – Ft) | -5 | -7 | 2.4 | -20.08 | -1.064 |
RSFE | -5 | -12 | -9.6 | -29.68 | -30.74 |
TS = RSFE / MAD | -1.00 | -2.00 | -2.00 | -3.44 | -4.32 |
Note that the TS values are getting outside the limit of [-2.0, 2.0], So, the forecast is biased
4.
Regression using Excel shortcut method
Jan | Feb | Mar | Apr | May | Jun | |
Period (t) | 1 | 2 | 3 | 4 | 5 | 6 |
Actual Sales (At) | 65 | 62 | 70 | 48 | 63 | |
Forecast (Ft) | 65.20 | 63.40 | 61.60 | 59.80 | 58.00 | 56.20 |