Level 1 – Illustrating Travel Data for the Indiana Department of Tourism As a travel industry consultant, you have been working for the state of Indiana Department of Tourism. The department manager has asked you to present a report on where Indiana residents travel to and where Indiana visitors come from. The modes of transportation must also be presented. You will also include some information from a survey that your firm has recently completed. The travel survey is located in the workbook named Indiana.xlsx. Complete the following: 1. Open the workbook named Indiana.xlsx in the Chapter 3 folder, and then save the file as Indiana Travel.xlsx. 2. Create two bar charts to illustrate the differences in Trips per Traveler and Miles per Traveler of the average Indiana resident, the average U.S. resident, and the average Indiana visitor. (Hint: Create the charts for Indiana and Indiana Visitors first, and then add the data for United States using the Select Data Source dialog box. (Hint: Delete the “={1}” value in the Series values box before selecting the United States data.) 3. Create a bar chart to illustrate a state-by-state comparison of the number of residents from the rest of the nation who visit Indiana, and the number of Indiana residents who visit other states. Rank the information in this chart by the states with the most visitors to Indiana. 4. The Indiana Department of Tourism is very interested in learning more about how people travel in Indiana. They want to use this information to better target advertising to specific demographic groups and methods of travel. Use the appropriate chart or series of charts to illustrate how people travel; the purpose of their travel; and their income levels, age, and gender. This information should be presented for people who live within the state and those visiting the state with a comparison between the two groups. 5. Save and close the Indiana Travel.xlsx workbook.Gross, Debra; Akaiwa, Frank; Nordquist, Karleen. Succeeding in Business with Microsoft Excel 2013: A Problem-Solving Approach (New Perspectives) (Page 209). Cengage Textbook. Kindle Edition.
indiana_travel.xlsx

Unformatted Attachment Preview

Travel Characteristics for Indiana and the United States: 2006 and 2016
United States
Indiana
2006
People who live in area
Percent traveling households
Trips per household
Trips per traveling household
Trips per capita
Trips per traveler
Miles per traveler
Miles per trip
People who visit area
Trips per traveler
Miles per traveler
Miles per trip
2016
Percent
change
2006
2016
Percent
change
73.8
5
7
86.1
5.3
7.3
16.7%
6.0%
4.3%
81.7
4.6
6.5
94.3
6.5
8.2
15.4%
41.3%
26.2%
2.5
2.7
1722.4
478.9
4.6
4.9
3,441
807.7
84.0%
81.5%
99.8%
68.7%
2.8
4.5
3,218
804.5
4.7
5.7
4,649
975.2
67.9%
26.7%
44.5%
21.2%
4.3
2,226
591.2
5.8
3,300
675.7
34.9%
48.2%
14.3%
4.5
3,218
804.5
5.7
4,649
975.2
26.7%
44.5%
21.2%
Origin States of People Who Visit Indiana and Destination States of People Who Live in Indiana (In thousands): 2016
State
Illinois
Ohio
Kentucky
Michigan
Wisconsin
Missouri
Tennessee
Pennsylvania
Florida
California
Iowa
Georgia
Texas
New York
North Carolina
New Jersey
Minnesota
Virginia
Maryland
West Virginia
South Carolina
Kansas
Alabama
Oklahoma
Nebraska
Arizona
Massachusetts
Arkansas
Colorado
Mississippi
Connecticut
Washington
Louisiana
New Mexico
Oregon
Nevada
Delaware
District of Columbia
South Dakota
Utah
North Dakota
Maine
New Hampshire
Hawaii
Wyoming
Idaho
Montana
Vermont
Rhode Island
Visiting Indiana From Indiana
3,423
2,781
2,596
2,331
1,576
2,015
1,143
2,416
520
315
434
692
339
831
332
349
286
1176
281
202
213
147
212
171
198
247
164
106
160
139
117
41
116
117
109
128
102
48
97
120
90
186
87
49
86
230
77
65
74
24
51
130
47
83
45
118
45
178
43
40
34
40
26
57
24
58
22
20
16
28
14
209
13
8
13
179
13
21
13
41
12
10
11
15
11
11
10
27
8
27
6
28
5
10
5
4
3
15
Alaska
2
21
e in Indiana (In thousands): 2016
Travel Rates by Trip and Traveler Characteristics for Indiana: 2016
PEOPLE WHO LIVE IN AREA
Means of transportation
Personal use vehicle
Commercial air
Intercity bus
Train
Other
Person trips (in Number of travelers Trips per
thousands)
(in thousands)
traveler
23,715
5,732
4.5
20,508
2,614
28
32
533
23,714
5,364
1,455
29
24
357
7,229
3.2
2
1
1.4
1.6
9
4,523
9,519
6,136
3,538
1,149
3,430
3,015
1,785
4.3
3.1
2.2
2.2
Income
Less than $25,000
$25,000 to $49,999
$50,000 or greater
2,866
7,842
13,007
1006
2,248
2,508
2.1
3.8
5.7
Age
Less than 24
25 to 64
65 and over
5,413
16,350
1,953
1,792
3,484
494
3.3
5.1
4.3
Gender
Male
Female
13,445
10,270
2,950
2,795
4.9
4
PEOPLE WHO VISIT AREA
21,043
4,463
5.1
Means of transportation
Personal use vehicle
Commercial air
Intercity bus
Train
Other
18,812
1,734
14
12
470
3,708
567
6
5
176
5.6
3.4
2.3
2.3
3
Main purpose
Business
Visit friends or relatives
Leisure
Other
5,064
8,263
4,029
3,686
1000
1,840
1,278
1004
5.6
4.9
3.5
4
Income
Less than $25,000
$25,000 to $49,999
2,808
6,940
630
1,526
4.8
4.9
Main purpose
Business
Visit friends or relatives
Leisure
Other
$50,000 or greater
11,292
2,229
5.6
Age
Less than 24
25 to 64
65 and over
5,183
14,412
1,448
1,208
2,837
340
4.7
5.6
4.6
Gender
Male
Female
11,661
9,382
2,308
2,073
5.6
4.9
Miles per
Miles
Miles (in millions) traveler
per trip
16,869
3,066
748
11,335
5,120
16
29
370
16,870
2,220
3,695
592
1,239
1,091
8,837
581
2,059
615
920
729
4,904
3,355
5,780
5,783
1,951
3,069
1,771
2,016
1,149
779
638
990
580
1,843
6,032
8,993
1,925
2,818
3,768
676
808
726
3,682
11,382
1,805
2,159
3,432
3,839
716
731
971
9,076
7,792
3,232
2,929
710
797
12,540
2,941
625
8,641
3,374
10
20
485
2,449
6,260
1,529
3,468
2,896
482
2,045
732
1,663
1085
3,215
5,205
2,022
2,087
3,380
2,972
1,662
2,186
667
661
527
595
1,810
3,844
3,017
2,646
677
582
6,876
3,241
640
2,917
8,720
894
2,535
3,229
2,755
591
635
648
7,025
5,506
3,198
2,790
633
617

Purchase answer to see full
attachment