Question

11-14 Highland Automotive wishes to forecast the number

of new cars that will be sold next week. The following

table summarizes the number of new cars sold

during each of the past 12 weeks: (PLEASE ANSWER BY USING EXCEL)

week	number sold
1	22
2	26
3	23
4	27
5	21
6	25
7	28
8	26
9	29
10	29
11	27
12	31

(a) Provide a forecast by using a 3-week weighted

moving average technique with weights 5, 3, and

1 (5 = most recent).

(b) Forecast sales by using an exponential smoothing

model with a = 0.45.

(c) Highland would like to forecast sales by using

linear trend analysis. What is the linear equation

that best fits the data?

(d) Which of the methods analyzed here would you

use? Explain your answer.

Answer 1

a)

A B E Week actuals wt moving avg 0.111111111 1 0.555555556 0.333333333 22 Wt Wt-1 Wt-2 2 1 2 26 3 23 Error МАРЕ 4 3 Sq Err 23.88888889-19.88888889 27 395.5679012 5 4.972222222 25.55555556 20.55555556 21 4.111111111 422.5308642 23.22222222-17.22222222 25 2.87037037 7 296.6049383 28 23.88888889 -16.88888889 285.2345679 2.412698413 26 26.22222222-18.22222222 332.0493827 2.277777778 29 26.55555556 -17.55555556 9 1.950617284 10 308.1975309 27.88888889| -17.88888889 10 29 1.788888889 11 320.0123457 11 27 -17.66666667 312.1111111 1.606060606 12 28.66666667 27.88888889| -15.88888889 1.324074074 12 31 13 252.4567901 14 SSE 2924.765432 18.0270335 15 RMSE 16 МАРЕ 23.31382075 |LOCO LO LO

Formula under each cell

A E F G 3/9 -1/9 wt moving avg -5/9 1 Week actuals 2 1 22 Wt-2 Wt Wt-1 26 3 2 23 MAPE 4 3 Error Sq Err =ABS(E5) / A5 27 -SE$1*B4+$F$1*B3+$G$1*B2 A5-D5 ESE$1*B5+SF$1*B4+$G$1*B3 |=$E$1* B6+$F$ 1*B5+$G$1*B4 A7-D7 -$E$1 B7+$F$1 B6+$G$1*B5 A8-D8 |=SE$1* B8+$F$ 1*B7+$G$1*B6 -SE$1*B9+$F$1*B8+$G$1*B7 -SE$1*B10+$F$1*B9+$G$1*B8 =A11-D11 5 4 -E5*E5 21 -ABS(E6) /A6 6 5 =A6 - D6 -E6*E6 25 =ABS(E7)/A7 7 6 -E7*E7 28 E8 E8 ABS(E8)/A8 8 7 26 -E9*E9 ABS(E9)/A9 9 8 =A9 - D9 29 =ABS(E10)/A10 10 9 =A10-D10 =E10*E10 29 E11 *E11 11 10 ABS(E11)/A11 27 +$ -E12*E12 *B=A12-D12 12 31 -SE$1*B12+$F$1*B11+$G$1*B1=A13-D13 =ABS(E13)/A13 13 12 =E13*E13 14 =SUM(F5:F13) -SQRT(SUM(F5: F13)/(ROW(F13)+1-ROW (F5)) =SUM (G5:G13) SSE 15 RMSE 16 MAPE

b)

A D G J M 24 |alpha 25 0.45 0.45 0.45 0.45 26 Week Sales SqError Error МАРЕ 22 27 26 2 28 22 -4 16 0.153846154 3 23 29 22 -1 1 0.043478261 27 4 30 23.8 -3.2 10.24 0.118518519 5 21 5.9536 0.116190476 31 23.44 2.44 25 32 25.042 0.042 0.001764 0.00168 28 7 33 23.2231 -4.7769 22.81877361 0.170603571 26 34 24.022705 -1.9773 3.909695517 0.076049808 29 35 25.81248775 -3.18751 10.16023434 0.109914216 29 10 36 9.62942658 0.107004543 25.89686826 -3.10313 11 27 37 27.29327754 0.29328 0.086011718 0.010862131 12 31 38 28.06130265 -2.9387 8.635942118 0.094796689 39 SSE 88.43544789 40 RMSE 2.714704525 41 МАРЕ 0.091176761

Formula of the above excel calc

D G M A 26 Week Sales Error SqError МАРЕ 22 27 1 =D28- $B28 -ABS(G28)/$B28 28 2 26 =B27 -G28^2 D29-$B29 -ABS(G29)/SB29 23 =SD$25*B27+(1-$D$25) * D28 -G29^2 29 3 =$D$25*B28+(1-$D$25)* D29 G30 2 =ABS(G30)/$B30 D30-$B30 30 4 27 31 5 21 =SD$25*B29+(1-$D$25)*D30 -D31- $B31 -G31^2 =ABS(G31)/$B31 -$D$25*B30+(1-$D$25)*D31 -D32- $B32 -ABS(G32)/$B32 =ABS(G33)/$B33 25 32 6 G32 2 =$D$25*B31+(1-$D$25) * D32 33 7 28 D33-$B33 G33^2 -SD$25*B32+ (1-$D$25)* D33 =$D$25*B33+ (1-$D$25)* D34 26 =ABS(G34)/$B34 34 8 D34-$B34 -G34^2 =ABS(G35)/$B35 35 9 29 D35-$B35 G35 2 =D36-$B36 36 10 29 =SD$25*B34+(1-$D$25)* D35 -ABS(G36)/$B36 -G36^2 - $D$ 25 *B35+(1-$D$25)*D36 37 11 27 D37-$B37 ABS(G37)/$B37 G37 2 =SD$25* B36+(1-$D$25)* D37 -G38 2 31 D38-$B38 =ABS(G38)/$B38 38 12 39 SSE -SUM (J28: J38) 40 =SQRT(J39/12) =SUM(M28:M38)/(ROW(L38)+1-ROW(L28)) RMSE 41 МАРЕ 42

D G M A 26 Week Sales Error SqError МАРЕ 22 27 1 =D28- $B28 -ABS(G28)/$B28 28 2 26 =B27 -G28^2 D29-$B29 -ABS(G29)/SB29 23 =SD$25*B27+(1-$D$25) * D28 -G29^2 29 3 =$D$25*B28+(1-$D$25)* D29 G30 2 =ABS(G30)/$B30 D30-$B30 30 4 27 31 5 21 =SD$25*B29+(1-$D$25)*D30 -D31- $B31 -G31^2 =ABS(G31)/$B31 -$D$25*B30+(1-$D$25)*D31 -D32- $B32 -ABS(G32)/$B32 =ABS(G33)/$B33 25 32 6 G32 2 =$D$25*B31+(1-$D$25) * D32 33 7 28 D33-$B33 G33^2 -SD$25*B32+ (1-$D$25)* D33 =$D$25*B33+ (1-$D$25)* D34 26 =ABS(G34)/$B34 34 8 D34-$B34 -G34^2 =ABS(G35)/$B35 35 9 29 D35-$B35 G35 2 =D36-$B36 36 10 29 =SD$25*B34+(1-$D$25)* D35 -ABS(G36)/$B36 -G36^2 - $D$ 25 *B35+(1-$D$25)*D36 37 11 27 D37-$B37 ABS(G37)/$B37 G37 2 =SD$25* B36+(1-$D$25)* D37 -G38 2 31 D38-$B38 =ABS(G38)/$B38 38 12 39 SSE -SUM (J28: J38) 40 =SQRT(J39/12) =SUM(M28:M38)/(ROW(L38)+1-ROW(L28)) RMSE 41 МАРЕ 42

c) Performed the regression analysis below for the linear trend in excel's data analysis tool pack

A C D F F G H 1 SUMMARY OUTPUT 2 3 Regression Statistics Multiple 0.745499 R Square 6 Adjusted 0.511346 4 5 0.555769 7 Standard 2.104164 8 Observati 12 10 ANOVA df E Significance F 11 SS MS 12 Regressio 1 55.39161 55.39161 12.51079 0.00538307 13 Residual 10 44.27506 4.427506 14 Total 11 99.66667 15 Coefficientsandard Err P-value Lower 95% Upper 95%ower 95.09 Upper 95.0% 16 t Stat 17 Intercept 22.12121 1.295023 17.08171 9.97E-09 19.23572023 25.0067 19.23572 25.00670402 18 X Variable 0.622378 0.175959 1.014438767 3.53706 0.005383 0.230316478 1,014439 0.230316 19 20 21 22 T CO co o

d)If we compare a b and c the least sum of squares of error is in the case of exponential smoothening.

Hence we will use exponential smoothening for forecasting

Hope the above answer has helped you in understanding the problem. Please upvote the ans if it has really helped you. Good Luck!!