W24-Forecasting-template-1

Monthly sales for a product were as follows: Year Demand Weights 1 7 0.1 2 9 0.3 3 5 0.6 4 9 7.0 6.4 5 13 7.7 7.8 6 8 9.0 11.0 7 12 10.0 9.6 8 13 11.0 10.9 9 9 11.0 12.2 10 11 11.3 10.5 11 7 11.0 10.6 9.0 8.4 Three-year moving average Weighted three-year moving average

625 12.50 400 8.33 225 5.00 400 7.41 400 8.70 2050 41.94 410 8.39 Squared Error Absolute Percent Error ( E t ) 2 Sum of squared errors Sum of percent errors Mean Squared Error Mean Absolute Percent Error |𝑬_𝒕 |/𝑨_𝒕 ×𝟏𝟎𝟎

Forecasts Simple Moving Average Exponential smoothing Month Price Per Chip 2 month 3 month 0.1 0.3 0.5 January $1.80 1.80 1.80 1.80 February $1.67 1.80 1.80 1.80 March $1.70 1.74 1.79 1.76 1.74 April $1.85 1.69 1.72 1.78 1.74 1.72 May $1.90 1.78 1.74 1.79 1.77 1.78 June $1.87 1.88 1.82 1.80 1.81 1.84 July $1.80 1.89 1.87 1.80 1.83 1.86 August $1.83 1.84 1.86 1.80 1.82 1.83 September $1.70 1.82 1.83 1.81 1.82 1.83 October $1.65 1.77 1.78 1.80 1.79 1.76 November $1.70 1.68 1.73 1.78 1.75 1.71 December $1.75 1.68 1.68 1.77 1.73 1.70 1.73 1.70 1.77 1.74 1.73 or alternatively 𝐹_𝑡=𝐹_(𝑡−1)+𝛼(𝐴_(𝑡−1)−𝐹_(𝑡 −1) ) 𝐹_𝑡=𝛼𝐴_(𝑡−1)+(1−𝛼)𝐹_(𝑡−1)

al forecast for May was 105 units; α=0.2. cember.

6 7 8 9 10 (At)

This is also referred to as trend projection. Month (t) 1 12 2 17 3 20 4 19 5 24 6 21 7 31 8 28 9 36 10 Forecast for month 10: 35.8661 or alternatively, we can use the TREND function to find the forecast for m 35.861 43.511 Forecast for month 13 using trend pr An alternative method to dealing with linear trends is to use regression based forecasti Simple linear regression can be applied to forecasting using time as the independent va Trend Projection: A time-series forecasting method that fits a trend line to a series of and then projects the line into the future for forecasts. Approach 1: Use trendlines Actual Demand (A t ) F 10 = F 10 = F 13 = 0 1 2 3 4 5 6 0 5 10 15 20 25 30 35 40 Actual Demand (

y=2.55x+10.3661 month 10 directly: rojection ting . ariable . f historical data points 6 7 8 9 10 (At)

SUMMARY OUTPUT Regression Statistics Multiple R 0.936461713 R Square 0.876960539 Adjusted R Square 0.859383474 Standard Error 2.79639791 Observations 9 ANOVA df SS MS F Significance F Regression 1 390.15 390.15 49.89231706 0.0001999971 Residual 7 54.7388888889 7.81984127 Total 8 444.888888889 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept 10.36111111 2.03153598245 5.100136646 0.001399293 5.557291859 15.16493036 X Variable 1 2.55 0.3610134178 7.063449374 0.000199997 1.6963389171 3.403661083

Lower 95.0% Upper 95.0% 5.557291859 15.164930363 1.6963389171 3.4036610829

Month (t) 1 12 2 17 3 20 4 19 5 24 6 21 7 31 8 28 9 36 10 ? Intercept 10.361 =INTERCEPT(B4:B12,A4:A12) Slope 2.55 =SLOPE(B4:B12,A4:A12) 0.9364617 =CORREL(B4:B12,A4:A12) We can use the TREND function to compute forecasts directly: 18.0111111 =TREND(B4:B12,A4:A12,A6) Approach 3: Use the Regression tool under Data Analysis Approach 2: Using Excel's INTERCEPT , SLOPE , TREND functions Actual Demand (A t ) Forecast (F t ) Correlation coefficient F 3 = 0 1 2 3 4 5 6 7 8 9 0 5 10 15 20 25 30 35 40 Chart Title

10