Question

South Shore Construction builds permanent docks and seawalls along the southern shore of Long Island, New York. The following data show quarterly sales revenues (in $’000s) for the past 5 years.

Quarter	Year 1	Year 2	Year 3	Year 4	Year 5
1	20	37	75	92	176
2	100	136	155	202	282
3	175	245	326	384	445
4	13	26	48	82	181

Question 4

Now make adjustments for trend and seasonality.

1. Quantify the trend in the time series. What does the trend equation tell you?
2. Quantify the seasonality in the time series by calculating seasonality indexes. What do these indexes tell you?
3. Using the trend and the seasonality information from (a) and (b) make forecasts from Q1 Year 1 through Q4 Year 5.
4. Calculate the Mean Absolute Percent Error for this set of forecasts.
5. Plot the forecasts from (c) on the graph in Question 1. Your graph should now have the original data and 3 sets of forecasts plotted on it. Label the different plots appropriately.

Question 5

Using the method in Question 4, calculate forecasts for each of the 4 quarters of Year 6. These forecasts should be adjusted for both trend and seasonality.

Answer 1

a.

Quarter	Year 1	Year 2	Year 3	Year 4	Year 5	Total
1	20	37	75	92	176	400
2	100	136	155	202	282	875
3	175	245	326	384	445	1575
4	13	26	48	82	181	350
Total	308	444	604	760	1084	3200

Trendline equation is Y = a+bX

To quantify the trendline X, XY, X^2, b and a should be calculated.

X = time period

Y = Forecast value

a = Intercept at X =0

b = slope of the trend line.

Year (X)	Data (Y)	XY	X^2
1	308	308	1
2	444	888	4
3	604	1812	9
4	760	3040	16
5	1084	5420	25
15	3200	11468	55

X̅ = mean of X = 15/5 = 3

Y̅ = mean of Y = 3200/5 = 640

b = ( ∑ XY - n*X̅*Y̅ ) / (∑X^2 - n*X̅^2) = (11468-5*3*640) / (55-5*3^2) = 186.8

a = Y̅ - bX̅ = 640-3*186.8 = 79.6

Trend Equation is Y = 79.6+186.8X

The trend equation shows the forecasted value as a function of time (period) and provides the relation between the time period and the forecasted value. Using this equation, the forecasted value of any period can be calculated.

b. Seasonality Index, Si = Di / ∑D, Where Di is the sum of sales of a particular quarter of all available years.

From the first table, the Seasonality Index of all quarters can be calculated.

S1 = D1/∑D, = 400/3200 = 0.13

S2 = 875 /3200 = 0.27

S3 = 1575 / 3200 = 0.49

S4 = 350 / 3200 = 0.11

Comparing the indexes, Q3 is the peak season of the demands and Q4 has the lowest sales of all quarters. This is the pattern in all 5 years.

c. Forecast using the trend equation by replacing the value of X as 1,2..5. in the trend equation Y = 79.6+186.8X

Year (x)	Forecast
1	266.4
2	453.2
3	640
4	826.8
5	1013.6

Seasonal Forecast, SF = S*F

Replacing S with values of S1 to S4 for Q1 to Q4

S1F1 = 0.13 * 266.4 = 33.3

S2F1 = 0.27*266.4 = 72.8

S3F1 = 0.49*266.4 = 131.1

S4F1 = 0.11*266.4 = 29.1

SImilarly the values for all time periods

Quarter	Year 1	Year 2	Year 3	Year 4	Year 5
1	33.3	56.7	80	103.4	126.7
2	72.8	123.9	175	226.1	277.2
3	131.1	223.1	315	406.9	498.9
4	29.1	49.6	70	90.4	110.9

d. MAPE = (∑|Fi -Di|) / ∑Di

Calculating |Fi -Di|

example Year1 Q1 = 33.3-20 = 13.3

F-D	Year 1	Year 2	Year 3	Year 4	Year 5
1	13.3	19.7	5.0	11.4	49.3
2	27.2	12.1	20.0	24.1	4.8
3	43.9	21.9	11.0	22.9	53.9
4	16.1	23.6	22.0	8.4	70.1

Sum = 480.7

MAPE = 480.7 / 3200 = 0.15 or 15%