Chat with us, powered by LiveChat TimesSeriesPracticeinExcel.xlsx - STUDENT SOLUTION USA

TimesSeriesPracticeinExcel.xlsx

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

Get Your Paper Before the Deadline. Our Services are 100% private and Confidential

Useful Links That Will Help You Around

Link to new order   https://studentsolutionsusa.com/orders/stud/new

link to login page   https://studentsolutionsusa.com/orders/login

New user registration link   https://studentsolutionsusa.com/orders/register

Forgot Password https://studentsolutionsusa.com/orders/forgot/password

 

FREQUENTLY ASKED QUESTIONS

Question: How does this work?

Answer: Good Question. We are a group of a freelance board of students and professional essay writers. At our website, you may get help with any type of academic assignments: essay, coursework, term paper, business plan, case study, article review, research paper, presentation, and speech. Top writers can help with complex assignments such as dissertations, thesis papers, etc. All of them are professionals possessing excellent knowledge in their field of expertise, perfect writing skills, quality, and speed. When you place an order on our website, we assign it to the best writer.  Once the writer finishes the work, the paper is submitted to our quality assurance desk who go through it and ensure it is unique and plagiarism free and that the instructions were followed to the detail. After this step we upload the paper in your account, we also send a copy to the email that you used to register the account with. we can guarantee you that the paper will be 100% plagiarism free. Besides, our services are 100% private and confidential

Question: How do I place an Order after getting to the order page

Answer: There are three major steps in the ordering process

Step 1 ....................................................paper details In this step, you will fill in the instructions of your paper; you can upload any materials that you feel will make your assignment a success. Besides, you can also email us at [email protected] Remember to specify the correct academic level. Please note that sources mean the number of references.

Step 2...................................................... Price calculation Kindly specify the number of pages, type of spacing and the correct deadline. This step will give you the estimated cost minus discount -- you may add the extra features if you wish.

Step 3 ....................................................discount and payment Use the discount code HAPPY2018 to enjoy up to 30% discount of your total cost After this step, proceed to safe payment; you can checkout using your card or PayPal Please note we will send the complete paper to the email you will provide while registering. A copy will also be uploaded to your account

 

Question: How will I know when my paper is complete? or How will I get the complete Paper?

Answer: Once we are done with the paper, we will be uploaded to your account. A copy will also be sent to the email you registered with. We can guarantee you the following:- 1. Our service is private and confidential; we don't spam or share your contacts with anyone  2. The final paper will be plagiarism free. We will send a Turnitin Report to the email you registered with 3. At our company, willing to do free unlimited revisions until you are satisfied with your paper

Question:- Am a new client, How can I get the guarantee that the paper will be completed and sent to me before my deadline?

Answer: Thank you for expressing your concerns. We would love to have you as our loyal customer. We are certain if we do good work, you will come back for me. Besides, you will give us referrals to your friends and family. For that reason, we can’t fail to deliver your paper within your specified time frame. We will ensure we submit the paper on time so that you can have enough time to go through it, if you have problems with the paper delivered, you can request a free revision. You are entitled to as many revisions as you would wish until you get a paper that satisfies you

 

Useful Links That Will Help You Around

Link to new order   https://studentsolutionsusa.com/orders/stud/new

link to login page   https://studentsolutionsusa.com/orders/login

New user registration link   https://studentsolutionsusa.com/orders/register

Forgot Password https://studentsolutionsusa.com/orders/forgot/password

 

error: Content is protected !!