qant

profilemilesss
TimesSeriesPracticeinExcel.xlsx
Home>Mathematics homework help>Statistics homework help>qant

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
Sales Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131

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
Sales Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131

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

Actual 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131 Forecast #N/A 73 84.899999999999991 110.16999999999999 101.65099999999998 78.795299999999997 88.038589999999985 127.91157699999999 113.27347309999999 94.182041929999997 105.95461257899998 141.68638377369999 127.90591513210998 99.971774539632989 106.29153236188989 143.18745970856696

Time Point

Sales

.6 Dampening Factor

Actual 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131 Forecast #N/A 73 79.8 96.28 96.968000000000004 85.780799999999999 88.268480000000011 110.961088 109.37665280000002 100.02599168 104.415595008 125.44935700479999 124.06961420287999 109.641768521728 109.38506111303678 129.23103666782208

Time Point

Sales

.9 Dampening Factor

Actual 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131 Forecast #N/A 73 74.7 79.330000000000013 81.197000000000017 79.977300000000028 81.179570000000027 87.561613000000023 89.505451700000023 89.154906530000019 91.339415877000022 97.905474289300031 100.31492686037004 99.083434174333036 100.07509075689974 105.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.
Sales Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131

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
Values 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131 Forecast 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 131 102.94275968196206 126.30701097740565 168.69076920283931 138.17875042637019 112.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

Actual 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131 Forecast #N/A #N/A 94.666666666666671 103 96 86.333333333333329 102 114.66666666666667 112.66666666666667 101.33333333333333 118 130 122.33333333333333 106.33333333333333 118.66666666666667 133

Time Points

Sales

5 Quarter Moving Average

Actual 73 90 121 98 69 92 145 107 86 111 157 122 88 109 159 131 Forecast #N/A #N/A #N/A #N/A 90.2 94 105 102.2 99.8 108.2 121.2 116.6 112.8 117.4 127 121.8

Time Points

Sales

image1.png

image2.png

image3.png