Question

Gros Morne Distilleries is producing a new whiskey for launch on the Labour Day weekend. When the company has launched other products in the past, actual sales have been as follows:

Month	Actual Sales (bottles)
1	5500
2	6200
3	7150
4	8000
5	9300
6	10000

a) Using the 3-month Moving Average method, calculate sales forecasts for months 4, 5 & 6

b) Using the Exponential Smoothing method, calculate sales forecasts for months 4, 5 & 6; let α = 0.5 and a forecast of 5,000 for the first month

c) Using data from the first three months, determine the linear trend equation and calculate sales forecasts for months 4, 5 & 6.

d) Using the Trend Adjusted Exponential Smoothing model, calculate sales forecasts for months 4, 5 & 6; let α = 0.5 and β = 0.8 (start with initial estimates for month 1 of S1 = 5,200 and T1 = 800).

e) Calculate MSE for each of the forecasting techniques above, stating any necessary assumptions. Based on MSE, what is the preferred method?

f) Using the preferred method from e) above, what is the forecast for month 7?

Answer 1

Answer a: 3-Month Moving Averages:

(Rounded to 2 decimal places)

Let,

At-1 = Actual sales for the previous month

Ft-1 = Forecast sales for the previous month

α = Alpha = 0.5 (As given in the question)

Where At = Actual Sales for the month 't' and Ft = Forecast Sales for the month 't' =  (At-1 + At-2 + At-3) / 3

Calculations:

Answer b: Simple Exponential Smoothing Method:

(Rounded to 2 decimal places)

Where At = Actual Sales for the month 't' and

Ft = Simple exponential smoothing forecast for the current period = Ft-1 + α (At-1 - Ft-1)

Calculations:

Answer c: Linear Trend Equation Method:

We have the following data table:

Where, x = Independent Variable = A particular Month and y = Dependent Variable = Sales

Step 2: Find the Mean values for x and y:

Step 3: Find the three columns as mentioned below:

Where,

xy = x * y

x2 = x * x

y2 = y * y

Step 4: Find the value of byx:

Step 5: Find the linear trend equation:

Step 6: Now, find the sales forecast for months 4 to 6 as mentioned below:

(Rounded to 2 decimal places)

Answer d: Trend Adjusted Exponential Smoothing Method:

Step 1:

First, derive the formulas for the simple exponential smoothing forecast, trend value, and trend adjusted exponential smoothing forecast for the current period as mentioned below:

Let,

At-1 = Actual sales the previous month

Ft-1 = Forecast sales for the previous month

Tt-1 = Trend value for the previous month

FITt-1 = Trend adjusted exponential smoothing forecast for the previous month = Ft-1 + Tt-1

α = Alpha = 0.5 (As given in the question)

ß = Beta = 0.8 (As given in the question)

Then,

1) Ft = Simple exponential forecast the current period = Ft-1 + α (At-1 - Ft-1)

2) Tt = Trend value for the current period = Tt-1 + ß (Ft - Ft-1)

3) FITt = Trend adjusted exponential smoothing forecast for current period = Ft + Tt

Step 2:

By replacing the appropriate values in the formula, as derived in the previous step, we get the table showing the trend adjusted exponential smoothing forecast sales for the months through 2 to 6 as mentioned below:

(Rounded to 2 decimal places)

Calculations for forecast new accounts (Ft):

Calculations for Trend Value (Tt):

(Kindly raise an upvote for this answer, if you found it useful)