INFO 564 Homework Assignment 5
This work must be done completely in EXCEL. Answer each question on a separate tab. Label
each tab appropriately. You can copy and paste the data given into an Excel worksheet.
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
1
2
3
4
Year 1
20
100
175
13
Year 2
37
136
245
26
Year 3
75
155
326
48
Year 4
92
202
384
82
Year 5
176
282
445
181
Question 1
Plot this data with quarters from years 1-5 on the horizontal axis. What components do you
see in this time series?
Question 2
Ignore any trend or seasonality in the data.
a. Suppose the company uses moving averages to make forecasts. Make forecasts all the
way through Q4 Year 5. Assume the company uses (i) 3-quarterly moving averages and
(ii) 4-quarterly moving averages.
b. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent
Deviation. Which is more accurate – 3 quarterly moving average or 4 quarterly moving
average?
c. On a line chart plot the time series along with the forecasts from the method you select
in (b).
Question 3
Ignore any trend or seasonality in the data.
a. Suppose the company uses weighted moving averages to make forecasts. What are the
forecasts starting with Q4 Year 1 all the way through Q4 Year 5? Assume the company
uses (i) 3-quarterly moving averages with weights 0.6, 0.3, and 0.1 and (ii) 4-quarterly
moving averages with weights 0.4, 0.3, 0.2, and 0.1. In both cases the most weight is
given to the most recent quarter and the least to the oldest quarter in the moving
average.
b. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent
Deviation. Which is more accurate – 3 quarterly weighted moving average or 4
quarterly weighted moving average?
c. On a line chart plot the time series along with the forecasts from the method you select
in (b).
Question 4
Again ignore any trend or seasonality in the data.
a. Suppose the company uses exponential smoothing to make forecasts. What are the
forecasts for periods Q2 Year 1 through Q4 Year 5 assuming (i) alpha = 0.3 and (ii) alpha
= 0.7? In both cases assume that the forecast for Q1 Year 1 was 25 units.
b. Compare the two sets of forecasts from (a) on the basis of Mean Absolute Percent
Deviation. Which is more accurate – alpha of 0.3 or alpha of 0.7?
c. On a line chart plot the time series along with the forecasts from the method you select
in (b)
Question 5
Now make adjustments for trend and seasonality.
a. Quantify the trend in the time series. What does the trend equation tell you?
b. Quantify the seasonality in the time series by calculating seasonality indexes. What do
these indexes tell you?
c. Using the trend and the seasonality information from (a) and (b) make forecasts from
Q1 Year 1 through Q4 Year 5.
d. Calculate the Mean Absolute Percent Deviation for the forecasts in (c).
e. On a line chart plot the time series along with the forecasts from (c).
Question 6
Using the most accurate method of all of the above,
a. Make forecasts for the four quarters of Year 6.
b. Plot these forecasts on the same line chart as the time series.
c. Summarize in a few lines your findings from your answers to Q1 through Q6b.
Quarterly Sales Revenues for South Shore Construction
Year
Quarter Sales (000's)
Year 1
1
20
2
100
3
175
4
13
Year 2
1
37
2
136
3
245
4
26
Year 3
1
75
2
155
3
326
4
48
Year 4
1
92
2
202
3
384
4
82
Year 5
1
176
2
282
3
445
4
181
Question 1:
The time series displays an
upward trend component.
Theres a seasonality because
theres a cycle that occurs
within each year. For the
first two quarters sales are
highly increasing and once it
reaches the third quarter
sales start declining to the
forth quarter, this cycle
repeats itself year after year.
Time Series plot of Sales Re
450
400
350
300
250
200
150
100
50
0
1
2
3
Year 1
4
1
2
3
Year 2
4
eries plot of Sales Revenue for Shore Construction
Sales (000's)
4
Year 2
1
2
3
Year 3
4
1
2
3
Year 4
4
1
2
3
Year 5
4
Quarterly Sales Revenues for South Shore Construction
3 Quarterly Moving Average Errors
Year
Quarter Sales (000's) MA 3
MA 4
Mean error
MAE
Year 1
1
20
2
100
3
175
4
13 98.33333
-85.3333333 85.3333333
Year 2
1
37
96
77
-59
59
2
136
75
81.25
61
61
3
245
62
90.25
183
183
4
26 139.3333
107.75 -113.333333 113.333333
Year 3
1
75 135.6667
111 -60.6666667 60.6666667
2
155 115.3333
120.5 39.6666667 39.6666667
3
326 85.33333
125.25 240.666667 240.666667
4
48 185.3333
145.5 -137.333333 137.333333
Year 4
1
92 176.3333
151 -84.3333333 84.3333333
2
202 155.3333
155.25 46.6666667 46.6666667
3
384
114
167
270
270
4
82
226
181.5
-144
144
Year 5
1
176 222.6667
190 -46.6666667 46.6666667
2
282
214
211
68
68
3
445
180
231
265
265
4
181
301
246.25
-120
120
19.0196078 119.098039
Moving Average Errors
MAPE
4 Quarterly Moving Average Errors
Mean error
MAE
MAPE
Time Series plot of Sales Revenue
15%
63%
223%
134%
23%
124%
391%
135%
35%
109%
433%
142%
57%
377%
415%
168%
151%
1607%
450
-40
40
54.75
54.75
154.75
154.75
-81.75
81.75
-36
36
34.5
34.5
200.75
200.75
-97.5
97.5
-59
59
46.75
46.75
217
217
-99.5
99.5
-14
14
71
71
214
214
-65.25
65.25
31.28125 92.90625
62.71%
222.95%
133.88%
22.94%
123.63%
390.76%
135.46%
34.95%
109.09%
432.86%
142.22%
56.94%
377.14%
414.71%
167.92%
150.83%
186.19%
400
350
300
250
200
150
100
50
0
1
2
3
Year 1
4
1
2
3
Year 2
4
1
s plot of Sales Revenue for Shore Construction
Sales (000's)
1
2
3
Year 3
4
1
2
3
Year 4
4
1
2
3
Year 5
4
INFO 564
Operations & Supply Chain Management
Module 5a: Measuring Forecast Accuracy
Copyright 2017 Montclair State University
Forecast Accuracy
• Measured retrospectively based on past forecasts and
their errors
• Error = Actual – Forecast
• Also referred to as deviation
• Common measures are functions of past errors
• Mean Error (also called bias)
• Mean Absolute Error (MAE)
• Mean Absolute Percent Error (MAPE)
Mean Error
• Suppose we made forecasts for 5 past
periods and wish to measure their
accuracy.
• Error = Actual - Forecast
• Mean error is the average of the errors
in the 5 periods.
• Tells us that on average we are underforecasting by 1.2 units.
• Caveat: Small mean error does not
necessarily mean accurate forecasts
• Large negative errors in some periods could
cancel out large positive errors in others
Period
1
2
3
4
5
Actual
22
29
29
26
26
Forecast
25
26
26
28
21
Mean Error =
Error
-3
3
3
-2
5
1.2
Doesn’t seem to be of same
magnitude as the errors.
Mean Absolute Error
• Very popular measure
• Absolute Error ignores the sign
associated with error.
• Mean Absolute Error averages
the absolute errors.
• More reliable measure of
forecast errors.
• Forecasts are typically off by 3.2
units
• But is 3.2 big or small? MAE
does not tell us
Period
1
2
3
4
5
Actual
22
29
29
26
26
Forecast
25
26
26
28
21
Error
-3
3
3
-2
5
Absolute
Error
3
3
3
2
5
Mean Absolute Error =
Is of same magnitude
as the errors.
3.2
Mean Absolute Percent Error
• Absolute Percent Error = Absolute
Error ÷ Actual
• Mean Absolute Percent Error =
average of all the Absolute
Percent Errors.
• On average, forecasts are off by
about 12.2% of actual.
• Provides estimate of the relative
size of forecast error
• Another popular measure of
forecast accuracy
Period
1
2
3
4
5
Actual
22
29
29
26
26
Forecast
25
26
26
28
21
Error
-3
3
3
-2
5
Absolute
Absolute
Error
Percent Error
3
13.6%
3
10.3%
3
10.3%
2
7.7%
5
19.2%
Mean Absolute Percent Error =
12.2%
Forecasts off typically by
about 12.2% of actual values.
In Conclusion…
• Three very common measures of forecast accuracy
• Mean Error (also called bias)
• Mean Absolute Error (MAE)
• Mean Absolute Percent Error (MAPE)
• Found in all forecasting software
• Other measures available for specialized situations
• Can be used to compare different forecasting methods
• All based on past performance
• No guarantee of future performance of forecasts
INFO 564
Operations & Supply Chain Management
Module 5b: Patterns in Time-Series Data
Closing Price of Stock
Closing Price $
What are Time Series?
80
60
40
20
0
• Data collected over time
2
3
4
5
6
Week
7
8
9
10
Daily High Temperature
Temperature (F)
Monthly energy bills
Yearly college enrollment
Daily closing value of the DJIA
Hourly temperatures in a given
zip-code
• Quarterly earnings of a
company
90
88
86
84
82
80
78
76
74
1
2
3
4
5
6
7
8
Laptop Sales
3500
Sales (Units)
•
•
•
•
1
3000
2500
2000
1500
9 10 11 12
Patterns in Time Series: Randomness
• No pattern
• Seemingly random small ups
and downs in the time series
• Too many small causes that
contribute
• Difficult to forecast
• Impact reduced by averaging
1200
1100
# of Calls
• Movement not too big
compared with general level of
the series
Number of Calls to Help Center
1000
900
800
700
600
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
Day
Patterns in Time Series: Trend
• The spikes are random ups
and downs
• Useful for forecasting
• If we can assume trend will
continue
Temperature (F)
• Upward: cloud services, Alexa,
electric cars, battery life
• Downward: compact discs, stickshifts, cash
Daily High Temperature
90
88
86
84
82
80
78
76
74
1
2
3
4
5
6
7
8
9
10
11
12
10
11
12
Day
Bank Balance ($)
6000
5000
Dollars
• Trend: sustained upward or
downward movement
4000
3000
2000
1000
0
1
2
3
4
5
6
7
End of Week
8
9
Patterns in Time Series: Seasonality
Laptop Sales
• Often products and services
exhibit seasonal demand
• Randomness is present.
2500
2000
1500
1000
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
• Patterns can repeat annually,
monthly, weekly, even daily.
• If pattern can be expected to
continue, can use in forecasting.
• Pattern is not perfectly repeated.
3000
# of Laptops
• Some periods are consistently high,
some consistently low
• Christmas trees, school supplies,
vacation travel, business travel,
construction, etc.
3500
Patterns in Time Series: Cycles
•Cycles are like seasonality, but they repeat over much longer periods
•Correspond with business cycles, economic cycles
•Relevant in medium-term (3-5 years) and long-term (5 or more
years) forecasting
•Require lots of past data to recognize these patterns
Year-1
Year-2
Year-3
Year-4
Year-5
Year-6
Year-7
Year-8
Year-9
Using the Patterns for Forecasting
• Time-series can exhibit one or more of these patterns.
• Recognizing patterns – trends, seasonality, cyclicality
– allows us to use them for forecasting
• We have to be able to quantify them.
• Assumption: these patterns will hold in the future.
• Cyclicality is hard to recognize and quantity
• Only occasionally used in time-series forecasting
INFO 564
Operations & Supply Chain Management
Module 5c: Forecasting with Moving Averages
Example
Month
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
Demand
988
978
1059
1013
1092
948
1002
952
958
1029
978
917
944
955
998
1017
• Past demand for a product is given in the time-series on the left.
• A graph of the series is shown below:
Demand
1200
1100
1000
900
800
700
600
500
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr
• The series shows no trend or seasonality
• Only random ups and downs.
• What is our forecast for the next period, May?
Approach – Moving Averages
• Forecast for May based on a moving average of recent data
• Example:
• 3-month moving average: Average of demand in February, March,
and April
• = (955+998+1017)/3 = 990 units
• 6-month moving average: Average of demand from November
through April
• = (978+917+…+1017)/6 = 968 units
• Moving averages assume only recent periods are relevant.
• Older periods may be ignored safely
• Reasonable assumption in real life
Moving Averages: Impact of Period
• Graph shows demand, 3-month, and
6-month moving average forecasts
• 3-month moving average forecasts more
responsive to actual demand
• 6-month moving average forecasts less
responsive to demand
• Which is better?
• Longer periods dampen random fluctuations
(good) but also dampen trends (bad)
• Shorter periods respond to random
fluctuations (bad) and to trends (good)
Demand & Forecasts
1050
1000
950
900
850
800
750
700
Jan
Feb
Mar
Apr
Demand
• We want forecasts to ignore random
fluctuations but highlight trends
May
Jun
3-Mth MA
Jul
Aug
6-Mth MA
Sep
Oct
How to pick a period for moving averages?
• Experience, knowledge, instinct
OR…
• Use past data to experiment
• 3-month moving average forecasts
have a MAD of 37
• 6-month moving average forecasts
have a MAD of 36
• 6-month moving averages seem
slightly superior
• May be the choice going forward
Month
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
Demand
1002
952
958
1029
978
917
944
955
998
1017
3-Mth MA Abs Dev
1017
15
1014
62
967
9
971
58
980
1
988
72
975
31
947
9
939
59
966
52
37
MAD
6-Mth MA Abs Dev
1013
11
1015
64
1011
53
994
35
997
18
978
61
973
28
963
8
964
34
970
47
36
MAD
Weighted Moving Averages
• Simple moving averages assume equal importance (weight) of
each period used to compute the moving average.
• We could give different weights to different periods
• In a 3-month weighted moving average, 60% to the most recent
period, 30% to the one before, and 10% to the oldest period.
May forecast = 0.6*1017+0.3*998+0.1*955 = 1005 units
• Weights are subjective
• Most recent period is considered the most important and gets most
weight
• Oldest period is least important and gets the least weight
• Add up to 1
Summary
• Moving averages are appropriate when the time-series shows
no trend or seasonality
• Subjective considerations
• Averaging period
• Weighting if any
• Moving averages are reactive
• When there is trend moving averages will always lag behind
• Easy to understand
• Easy to implement on a spreadsheet
INFO 564
Operations & Supply Chain Management
Module 5d: Forecasting with Exponential Smoothing
Exponential Smoothing
• A weighted-average forecasting method
• Forecasts are a series of adjustments to previous forecasts
• New Forecast = Old Forecast + Adjustment
• Adjustment depends on forecast error
• All past periods are used in calculating the new forecast
• Unlike moving averages
• Given declining weights; most recent period the most.
• A subjective parameter, denoted α, is used to perform the
weighting
• α is between 0 and 1
Basic Idea
• Ft+1 = Forecast for period t+1
(upcoming period)
• Ft= Forecast for period t
(period that just ended)
• At= Actual demand for period t
Now
Ft
Period t+1
Period t
Ft+1
At
Adjustment
Ft+1= Ft + α(At - Ft)
Thus Ft+1 is Ft plus a portion of
the forecast error.
May also be written as:
Ft+1 = αAt + (1-α)Ft
Easier for calculation
Example
Suppose α = 0.4
Forecast for
April=30
• FMay = FApr + α(AApr-FApr)
= 30+0.4*(25-30) = 28
Actual for
April=25
• FJun = FMay + α(AMay-FMay)
Forecast for
May=28
= 28+0.4*(29-28) = 28.4
Actual for
May=29
= 28.4+0.4*(32-28.4) = 29.84
Forecast for
June=28.4
Actual for
June=32
Forecast for
July=29.84
• FJul = FJun + α(AJun-FJun)
• And so on…
Effect of α
• Alternate form Ft+1 = αAt + (1-α)Ft
• Can interpret Ft+1 as a weighted average of At and Ft
• α is the weight given to At, 1-α the weight given to Ft
• Large values of α give more weight to actual demand At
• Forecasts become more responsive to actual demand
• Small values of α give less weight to At
• Forecasts less responsive to actual demand
Effect of α
Forecasts
Month
Exponential Smoothing Forecasts
Demand Alpha=0.2 Alpha=0.8
988
1000
1000
1150
Feb
978
998
990
1100
Mar
1059
994
980
1050
Apr
1013
1007
1043
May
1092
1008
1019
Jun
948
1025
1077
Jul
1002
1009
974
Aug
952
1008
996
Sep
958
997
960
Oct
1029
989
959
Nov
978
997
1015
Dec
917
993
986
Jan
944
978
931
Feb
955
971
942
Mar
998
968
953
Apr
1017
974
989
* Forecasts for this January are
assumed numbers
Units Demand
Jan*
1000
950
900
850
800
750
700
Jan
Feb
Mar
Apr
May
Jun
Demand
Jul
Aug
Sep
Alpha = 0.2
Oct
Nov
Dec
Jan
Feb
Mar
Alpha=0.8
Forecasts with the smaller value of α are much steadier than with the larger
value of α
Apr
Picking a value of α
• Judgment, experience, intuition
• Using value of α that works well on past
data
• Table on right, forecasts for past periods
using α=0.2 and α=0.8.
• α=0.2 provides more accurate forecasts
(smaller MAD)
• Going forward, α=0.2 may be a better value
than α=0.8.
• Can also experiment with other values to
obtain best value.
• No guarantee that this value will work
well in the future
Month
Demand
Alpha=0.2
Abs.Dev Alpha=0.8
Abs.Dev
Jan
988
1000
12.4
1000
12.4
Feb
978
998
20.0
990
12.5
Mar
1059
994
65.6
980
79.1
Apr
1013
1007
6.1
1043
30.6
May
1092
1008
83.8
1019
72.8
Jun
Jul
948
1002
1025
1009
77.0
7.3
1077
974
129.4
28.4
Aug
952
1008
56.3
996
44.8
Sep
958
997
38.4
960
2.3
Oct
1029
989
40.0
959
70.2
Nov
978
997
18.4
1015
36.3
Dec
917
993
76.3
986
68.8
Jan
944
978
33.7
931
13.5
Feb
955
971
15.8
942
13.8
Mar
998
968
30.1
953
45.6
Apr
1017
974
43.5
989
28.5
39.0
43.1
MAD
MAD
Advantages of Exponential Smoothing
• More accurate than more sophisticated methods
• Easy to use and understand
• Easy to adjust importance given to actual demand through α
• Nested mechanism means that all past periods are used in making a
forecast
• FNov depends on AOct and FOct. FOct depends on ASep and FSep. FSep depends on
AAug and FAug, and so on.
• Thus FNov depends on AOct, ASep, AAug, and so on.
• No period is ignored
• More importance is given to more recent data
• Included in all popular forecasting packages
INFO 564
Operations & Supply Chain Management
Module 5e: Trend in Time Series
Example – # of Passengers
• Weekly number of passengers
carried by a bus service
reveals an upward trend.
• How to quantify this trend?
• Trend in this instance seems
linear
• A straight line with random
departures from it
#
Week Pass
1
305
2
302
3
380
4
372
5
452
6
404
7
424
8
408
9
533
10
522
11
510
12
588
13
604
14
581
15
585
16
617
# of Passengers
700
600
500
400
300
200
100
0
1
2
3
4
5
6
7
8
9
Week
10
11
12
13
14
15
16
Example – # of Passengers
• Weekly number of passengers
carried by a bus service
reveals an upward trend.
• How to quantify this trend?
• Trend in this instance seems
linear
• A straight line with random
departures from it
#
Week Pass
1
305
2
302
3
380
4
372
5
452
6
404
7
424
8
408
9
533
10
522
11
510
12
588
13
604
14
581
15
585
16
617
# of Passengers
700
600
500
400
300
200
100
0
1
2
3
4
5
6
7
8
9
Week
10
11
12
13
14
15
16
Quantifying Trend
• A line is described by a slope and
an intercept
• Y = bX + a
• b: slope, a: intercept
• The slope b measures the rate at
which the line climbs or falls – trend
• How to find slope and intercept?
• Line of best fit
• Formula for b and a
• Software
• Spreadsheet
# of Passengers
700
600
500
400
300
200
100
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Week
• The line of best fit above is
# of Passengers = b(Week) + a
• b captures the trend: the rate at which the
number of passengers is increasing each
week.
16
Trendline with EXCEL
Put mouse cursor on plot
and Left Click.
Trendline with EXCEL
Right Click and select Add
Trendline…
Trendline with EXCEL
Check
these.
Trend Line
• y ...
Purchase answer to see full
attachment