TimesSeriesPracticeinExcel.xlsx

samples and paper topics / By

Line Graph

1) Generate a line graph to viualize your time-series data. Place the time intervals on the horizontal axis
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 2) How might you describe the TREND of the time-series? (upward or downward)
15 Q3 159 Sample response: Reading the graph from left to right, this time series looks as if the data is trending upward
16 Q4 131

SalesQ1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q473901219869921451078611115712288109159131

Quarters

Sales

Trendline

3) Use Excel to add a trendline to the time-series chart. Select the trendline, change its color to one that is different from the graph, and thicken it
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 4) Upon visual inspection, which trendline appears to be most resprentative of the time-series data?
15 Q3 159 Sample Response: For this chart, the Moving Averages, with period 2, trend line appears to be most representative
16 Q4 131

SalesQ1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q473901219869921451078611115712288109159131

Quarters

Sales

Exponential Smoothing

5) Use exponential smoothing (Data .. Data Analysis … Exponential Smoothing) to smooth out the peaks and vallies in the plot to better see the trend
Time Index Quarter Sales Use dampening factors = .3, .6, and .9 to generate 3 charts
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

ExponentialSmoothing2

Time Index Quarter Sales 0.3 0.6 0.9
1 Q1 73 ERROR:#N/A ERROR:#N/A ERROR:#N/A
2 Q2 90 73 73 73
3 Q3 121 84.9 79.8 74.7
4 Q4 98 110.17 96.28 79.33
5 Q1 69 101.651 96.968 81.197
6 Q2 92 78.7953 85.7808 79.9773
7 Q3 145 88.03859 88.26848 81.17957
8 Q4 107 127.911577 110.961088 87.561613
9 Q1 86 113.2734731 109.3766528 89.5054517
10 Q2 111 94.18204193 100.02599168 89.15490653
11 Q3 157 105.954612579 104.415595008 91.339415877
12 Q4 122 141.6863837737 125.4493570048 97.9054742893
13 Q1 88 127.9059151321 124.0696142029 100.3149268604 6) What happens in the chart as the dampening factor increases?
14 Q2 109 99.9717745396 109.6417685217 99.0834341743
15 Q3 159 106.2915323619 109.385061113 100.0750907569 7) Use of which dampening factor has best aided in your ability to see the time-series trend? Explain
16 Q4 131 143.1874597086 129.2310366678 105.9675816812

.3 dampening factor

Actual73901219869921451078611115712288109159131Forecast#N/A7384.899999999999991110.16999999999999101.6509999999999878.79529999999999788.038589999999985127.91157699999999113.2734730999999994.182041929999997105.95461257899998141.68638377369999127.9059151321099899.971774539632989106.29153236188989143.18745970856696

Time Point

Sales

.6 Dampening Factor

Actual73901219869921451078611115712288109159131Forecast#N/A7379.896.2896.96800000000000485.78079999999999988.268480000000011110.961088109.37665280000002100.02599168104.415595008125.44935700479999124.06961420287999109.641768521728109.38506111303678129.23103666782208

Time Point

Sales

.9 Dampening Factor

Actual73901219869921451078611115712288109159131Forecast#N/A7374.779.33000000000001381.19700000000001779.97730000000002881.17957000000002787.56161300000002389.50545170000002389.15490653000001991.33941587700002297.905474289300031100.3149268603700499.083434174333036100.07509075689974105.96758168120978

Time Point

Sales

Seasonality

8) Do you notice any SEASONAL effects? (predictable fluctuations (systematic) that occur during the same month (or quarters, etc ..)?
Time Index Quarter Sales Explain. I so, use the line drawing tool to indicate this on your chart
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 Sample response: There appears to be a seasonal effect present in the graph. The graph fluctuates in a predictable pattern
from quarter 1 to quarter 4 or yearly. Sales start low in quarter 1 and increases to a peak in Quarter 3
then decreases in Quarter 4 to near quarter 2 sales levels but not quite as low as sales posted for quarter 1.
That is, it is expected that quarter 1 sales will be the lowest for the year and quarter 3 sales will be highest.

SalesQ1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q4Q1Q2Q3Q473901219869921451078611115712288109159131

Quarters

Sales

Forecast Sheet

9) Use Excel to Generate a Forecast sheet (Data … Forecast Menu … Forecast Sheet…Options) to predict values for the next 5 time intervals
Use the Time Period colum for the Timeline Range window (see image below)
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

Enter the last Excel row number in your dataset

Add 5 to your Forecast Start value

Uncheck this box

Time period column

Measurement Data

Forecast Sheet 2

Timeline Values Forecast
1 73 10) List the next 5 values forecast
2 90
3 121
4 98
5 69
6 92
7 145
8 107
9 86
10 111
11 157
12 122
13 88
14 109
15 159
16 131 131
17 102.942759682
18 126.3070109774
19 168.6907692028
20 138.1787504264 Format this table to APA expectations
21 112.411840624 Timeline Forecast
17 102.942759682
18 126.3070109774
19 168.6907692028
20 138.1787504264
21 112.411840624

Values73901219869921451078611115712288109159131Forecast123456789101112131415161718192021131102.94275968196206126.30701097740565168.69076920283931138.17875042637019112.41184062403286

Moving Average

Time Index Quarter Sales if there is no apparent trend, then smoothing with moving averages could be a next step to help identify the long term trend
1 Q1 73 used to reduce the random fluctuation
2 Q2 90 Simple moving average (SMA) is an arithmetic average of values at and near a particular time period – each observation is weighted equally
3 Q3 121 compute means for a sequence of L observed values
4 Q4 98 assumes observations which are nearby in time are also likely to be close in value
5 Q1 69
6 Q2 92
7 Q3 145 11) Use Data … Data Analysis … Moving Average ) to generate a 3 and 5 time period moving average chart
8 Q4 107 (See image below)
9 Q1 86
10 Q2 111
11 Q3 157
12 Q4 122
13 Q1 88
14 Q2 109
15 Q3 159
16 Q4 131

Data Measured

Moving average period (3 or 5)

Check to generate chart

Moving Average2

Time Index Quarter Sales
1 Q1 73 ERROR:#N/A ERROR:#N/A
2 Q2 90 ERROR:#N/A ERROR:#N/A
3 Q3 121 94.6666666667 ERROR:#N/A
4 Q4 98 103 ERROR:#N/A
5 Q1 69 96 90.2
6 Q2 92 86.3333333333 94
7 Q3 145 102 105
8 Q4 107 114.6666666667 102.2
9 Q1 86 112.6666666667 99.8
10 Q2 111 101.3333333333 108.2
11 Q3 157 118 121.2
12 Q4 122 130 116.6
13 Q1 88 122.3333333333 112.8 12) Using visual inspection, discuss the differences in relation with the actual graph and the 3 and 5 period Moving Averages graphs
14 Q2 109 106.3333333333 117.4
15 Q3 159 118.6666666667 127
16 Q4 131 133 121.8

3 Quarter Moving Average

Actual73901219869921451078611115712288109159131Forecast#N/A#N/A94.6666666666666711039686.333333333333329102114.66666666666667112.66666666666667101.33333333333333118130122.33333333333333106.33333333333333118.66666666666667133

Time Points

Sales

5 Quarter Moving Average

Actual73901219869921451078611115712288109159131Forecast#N/A#N/A#N/A#N/A90.294105102.299.8108.2121.2116.6112.8117.4127121.8

Time Points

Sales

image1.png

image2.png

image3.png

Our customer support team is here to answer your questions. Ask us anything!
WeCreativez WhatsApp Support
Support Executive
Jacob
Available
WeCreativez WhatsApp Support
Support Supervisor
Brian
Available
Hi, how can I help?