1) Generate a line graph to viualize your time-series data.

Place t
Time
Index Quarter Sales
1 Q1 73 180

2 Q2 90 160

3 Q3 121 140

4 Q4 98 120

5 Q1 69 100

Sales
6 Q2 92 80

7 Q3 145 60

8 Q4 107 40

9 Q1 86 20

10 Q2 111 0
Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q
11 Q3 157 Quarters
12 Q4 122
13 Q1 88
14 Q2 109 2) How might you describe the TREND of the time-series? (upwar
15 Q3 159 Reading the graph from left to right, this time series look
16 Q4 131
time-series data. Place the time intervals on the horizontal axis

4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
arters

the time-series? (upward or downward)

ght, this time series looks as if the data is trending upward
 3) Use Excel to add a trendline to the time-series chart
Time
Index Quarter Sales
1 Q1 73 180

2 Q2 90 160

3 Q3 121 140

4 Q4 98 120

5 Q1 69 100

Sales
6 Q2 92 80

7 Q3 145 60

8 Q4 107 40

9 Q1 86 20

10 Q2 111 0
Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q
11 Q3 157 Quarters
12 Q4 122
13 Q1 88
14 Q2 109 4) Upon visual inspection, which trendline appears to be most re
15 Q3 159 For this chart, the Moving Averages, with period 2, trend
16 Q4 131
e-series chart

4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
arters

e appears to be most resprentative of the time-series data?

ges, with period 2, trend line appears to be most representative
 5) Use exponential smoothing (Data .. Data Analysis … Ex
Time
Index Quarter Sales Use dampening factors = .3, .6, and .9 to genera
1 Q1 73
2 Q2 90 see image below
3 Q3 121
4 Q4 98
5 Q1 69
6 Q2 92
7 Q3 145
8 Q4 107
9 Q1 86
10 Q2 111
11 Q3 157
12 Q4 122
13 Q1 88
14 Q2 109
15 Q3 159
16 Q4 131
ata .. Data Analysis … Exponential Smoothing) to smooth out the peaks and vallies in the plot to better see th

= .3, .6, and .9 to generate 3 charts

the plot to better see the trend
Time
Index Quarter Sales 0.3 0.6 0.9
1 Q1 73 #N/A #N/A #N/A
2 Q2 90 73 73 73 .3 dampening factor
3 Q3 121 84.9 79.8 74.7
4 Q4 98 110.17 96.28 79.33 160
5 Q1 69 101.651 96.968 81.197 120
Actu
6 Q2 92 78.7953 85.7808 79.9773

Sales
80 For
7 Q3 145 88.03859 88.26848 81.17957 40
8 Q4 107 127.9116 110.9611 87.56161 0
9 Q1 86 113.2735 109.3767 89.50545 1 3 5 7 9 11 13 15
10 Q2 111 94.18204 100.026 89.15491 Time Point
11 Q3 157 105.9546 104.4156 91.33942
12 Q4 122 141.6864 125.4494 97.90547
13 Q1 88 127.9059 124.0696 100.3149 6) Discuss what happens in the
14 Q2 109 99.97177 109.6418 99.08343
15 Q3 159 106.2915 109.3851 100.0751 ) Use of which dampening facto
16 Q4 131 143.1875 129.231 105.9676
pening factor .6 Dampening Factor .9 Damp
160 160
120 120
Actual Actual
Sales

Sales
Forecast 80 Forecast 80
40 40
0 0
9 11 1 3 5 7 9 11 13 15
13 15 1 3 5 7
Point Time Point Time P

uss what happens in the chart as the dampening factor increases?

which dampening factor has aided in your ability to see the time-series trend
 .9 Dampening Factor
160
120
Actual
Sales

80 Forecast
40
0
1 3 5 7 9 11 13 15
Time Point
 8) Do you notice any SEASONAL effects? (predictable fluctuation
Time
Index Quarter Sales
180
1 Q1 73
2 Q2 90 160

3 Q3 121 140

4 Q4 98 120
5 Q1 69
100
6 Q2 92

Sales
7 Q3 145 80

8 Q4 107 60
9 Q1 86 40
10 Q2 111
20
11 Q3 157
12 Q4 122 0
Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2
13 Q1 88 Quarters
14 Q2 109
15 Q3 159
16 Q4 131 There appears to be a seasonal effect present in the grap
from quarter 1 to quarter 4 or yearly. Sales start
then decreases in Quarter 4 to near quarter 2 sa
That is, it is expected that quarter 1 sales will be
(predictable fluctuations (systematic) that occur during the same month (or quarters, etc ..)

Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
ers

ffect present in the graph. The graph fluctuates in a predictable pattern

r 4 or yearly. Sales start low in quarter 1 and increases to a peak in Quarter 3
er 4 to near quarter 2 sales levels but not quite as low as sales posted for quarter 1.
at quarter 1 sales will be the lowest for the year and quarter 3 sales will be highest.
 9) Use Excel to Generate a Forecast sheet (Data … Forecast Menu … Forec
Use the Time Period colum for the Timeline Range window (see i
Time
Index Quarter Sales
1 Q1 73
2 Q2 90
3 Q3 121
4 Q4 98
5 Q1 69
6 Q2 92
7 Q3 145
8 Q4 107
9 Q1 86
10 Q2 111
11 Q3 157
12 Q4 122
13 Q1 88
14 Q2 109
15 Q3 159
16 Q4 131

Add 5 to your Forecast Start value

Enter the last Excel row number in your

dataset

Uncheck this box

 Forecast Menu … Forecast Sheet…Options) to predict valuse for the next 5 time intervals
ne Range window (see image below)
Time period column

Measurement Data
Timeline Values Forecast
1 73 10) List the next 5 values forecast
2 90
3 121
180
4 98
5 69 160
6 92 140
7 145
120
8 107
9 86 100
10 111
80
11 157
12 122 60
13 88 40
14 109
20
15 159
16 131 131 0
17 102.94276 1 2 3 4 5 6 7 8 9 10 11 12 13 14
18 126.30701 Values Forecast
19 168.69077
20 138.17875
21 112.41184 Timeline Forecast
17 102.9428
18 126.307
19 168.6908
20 138.1788
21 112.4118
9 10 11 12 13 14 15 16 17 18 19 20 21

Values Forecast
Time
Index Quarter Sales if there is no apparent trend, then smoothing with movin
1 Q1 73 used to reduce the random fluctuation
2 Q2 90 Simple moving average (SMA) is an arit
3 Q3 121 compute means for a sequence
4 Q4 98 assumes observations which ar
5 Q1 69
6 Q2 92
7 Q3 145 11) Use Data … Data Analysis … Moving Average ) to gen
8 Q4 107 (See image below)
9 Q1 86
10 Q2 111
11 Q3 157 Data Measured
12 Q4 122
13 Q1 88
14 Q2 109
Moving average period (3 or 5)
15 Q3 159
16 Q4 131

Check to generate chart

n smoothing with moving averages could be a next step to help identify the long term trend
om fluctuation
average (SMA) is an arithmetic average of values at and near a particular time period - each observation is w
e means for a sequence of L observed values
s observations which are nearby in time are also likely to be close in value

Moving Average ) to generate a 3 and 5 time period moving average chart

- each observation is weighted equally
Time
Index Quarter Sales
1 Q1 73
3 Quarter Moving Average
#N/A #N/A
2 Q2 90 180
#N/A #N/A
160
3 Q3 121 94.66667 #N/A 140
4 Q4 98 103 #N/A 120
100
5 Q1 69 96 90.2

Sales
80
6 Q2 92 86.33333 94 60
7 Q3 145 102 105 40
20
8 Q4 107 114.6667 102.2
0
9 Q1 86 112.6667 99.8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
10 Q2 111 101.3333 108.2 Time Points
11 Q3 157 118 121.2
12 Q4 122 130 116.6
13 Q1 88 122.3333 112.8 12) Using visual inspection, discuss the
14 Q2 109 106.3333 117.4
15 Q3 159 118.6667 127
16 Q4 131 133 121.8
 r Moving Average 5 Quarter Moving Average
180
160
140
120
Actual 100 Actual

Sales
Forecast 80 Forecast
60
40
20
0
8 9 10 11 12 13 14 15 16 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
me Points Time Points

inspection, discuss the differences between the 3 and 5 period Moving Averages charts
age

Actual
Forecast

5 16