Question

2. Table below shows monthly beer sales at Gordon’s Liquor Store in 2017.

Month	Sales
Jan	900
Feb	725
Mar	1000
Apr	800
May	750
Jun	1200
Jul	1000
Aug	1100
Sep	1250
Oct	1050
Nov	1400
Dec	1600
Jan

a) Forecast beer sales needed for January 2018 using an exponential smoothing method

α = 0.2, α = 0.4 and α = 0.9

b) Compute an adjusted exponential smoothing for the data using:

α = 0.3 and β = 0.5, α = 0.2 and β = 0.6

c) Calculate MSE for Ft and Aft

Answer 1

a) Forecasted beer sales for Jan 2018 where = 0.2 (Please refer the below image )

The cell C3 is calculated by taking the average of all the values in column B [AVERAGE (B4:B15)]

The cell C4 is calculated by exponential smoothening formula [Alpha*B4+(1-Alpha) *C3], likewise it is calculated for C5 [Alpha*B5+(1-Alpha) *C4] and similarly it is calculated for all the following cells. (alpha = 0.2)

The value in cell C3 is transferred to D4, C4 to D5, C5 to D6 and likewise all the row of column forecast is populated and hence we find the forecasted beer sales value in January 2018 which is 1214 with = 0.2.

To find MSE (Mean squared error)

We find Error = Forecast value – Demand value (i.e. for E4 = D4-B4) and likewise we calculate for all the rows in error column.

Then we calculate Absolute Error (here we just use the modulus operator to remove the negative sign and just take the absolute value)

Finally we calculate MSE :

G4 = (sum of squares of value from E4 to E4 )/ 1

G5 = (sum of squares of value from E4 to E5 )/ 2

…..

…..

….

G15 = (sum of squares of value from E4 to E5 )/ 12

MSE = square root of G15 i.e. 245 with = 0.2 (Answer)

And similarly, we do it for = 0.4

Forecasted beer sale for January 2018 = 1369 and MSE = 223 with = 0.4

And similarly, we do it for = 0.9

Forecasted beer sale for January 2018 = 1577 and MSE = 226 with = 0.9

b)

Adjusted exponential smoothening , please refer image

We calculate the forecasted value in similar fashion explained above with = 0.3

Here, we find the trend Trend factor, Trend factor (T) for current period (t) is assumed 0.

T (t+1) [trend factor for period t+1] = Beta * [F(t+1)-F(t)] + (1- Beta) * T (t)

=($E$21*(D5-D4)) +((1-$E$21) *0) formula in the given excel image

T (t+2) [trend factor for period t+2] = Beta * [F(t+2)-F(t+1)] + (1- Beta) * T (t+1)

=($E$21*(D6-D5)) +((1-$E$21) *H4) formula in the given excel image

And likewise, for all rows

Where, F(t+1) = Forecasted value for period (t+1)

Finally, Adjusted forecast = F (t+1) + T (t+1) (forecasted value is added with trend value for the given period)

=D4+H4 for cell I4 formula in the given excel image and likewise we calculated all the adjusted forecast value with = 0.3 and Beta = 0.5. Answer (1347)

For = 0.2 and Beta = 0.6 please refer the image below

Answer = 1245 with = 0.2 and Beta = 0.6

C)

MSE for Ft calculated and explained above in part (a).

MSE for Aft

Procedure similar like explained in part (a) but the forecasted value is replaced by adjusted forecast value in the formula

We find Error = Adjusted Forecast value – Demand value (i.e. for E4 = I4-B4) and likewise we calculate for all the rows in error column.

Then we calculate Absolute Error (here we just use the modulus operator to remove the negative sign and just take the absolute value)

Finally we calculate MSE :

G4 = (sum of squares of value from E4 to E4 )/ 1

G5 = (sum of squares of value from E4 to E5 )/ 2

…..

…..

….

G15 = (sum of squares of value from E4 to E5 )/ 12

MSE = square root of G15 i.e. 191 with = 0.3 and Beta = 0.5 (refer 1st image in part (b)) Answer

MSE = square root of G15 i.e. 210 with = 0.2 and Beta = 0.6 (refer 2nd image in part (b)) Answer