Please show all formulas and list all steps if possible

Note: Tab 1, 2, and 3 are 3 different worksheets but belongs to the same file

Indiana Spread sheet

Tab 1

Travel Characteristics for Indiana and the United States: 2006 and 2016
	Indiana			United States
	2006	2016	Percent change	2006	2016	Percent change
People who live in area
Percent traveling households	73.8	86.1	16.7%	81.7	94.3	15.4%
Trips per household	5	5.3	6.0%	4.6	6.5	41.3%
Trips per traveling household	7	7.3	4.3%	6.5	8.2	26.2%
Trips per capita	2.5	4.6	84.0%	2.8	4.7	67.9%
Trips per traveler	2.7	4.9	81.5%	4.5	5.7	26.7%
Miles per traveler	1722.4	3,441	99.8%	3,218	4,649	44.5%
Miles per trip	478.9	807.7	68.7%	804.5	975.2	21.2%
People who visit area
Trips per traveler	4.3	5.8	34.9%	4.5	5.7	26.7%
Miles per traveler	2,226	3,300	48.2%	3,218	4,649	44.5%
Miles per trip	591.2	675.7	14.3%	804.5	975.2	21.2%

Tab 2

Origin States of People Who Visit Indiana and Destination States of People Who Live in Indiana (In thousands): 2016
State	Visiting Indiana	From Indiana
Illinois	3,423	2,781
Ohio	2,596	2,331
Kentucky	1,576	2,015
Michigan	1,143	2,416
Wisconsin	520	315
Missouri	434	692
Tennessee	339	831
Pennsylvania	332	349
Florida	286	1176
California	281	202
Iowa	213	147
Georgia	212	171
Texas	198	247
New York	164	106
North Carolina	160	139
New Jersey	117	41
Minnesota	116	117
Virginia	109	128
Maryland	102	48
West Virginia	97	120
South Carolina	90	186
Kansas	87	49
Alabama	86	230
Oklahoma	77	65
Nebraska	74	24
Arizona	51	130
Massachusetts	47	83
Arkansas	45	118
Colorado	45	178
Mississippi	43	40
Connecticut	34	40
Washington	26	57
Louisiana	24	58
New Mexico	22	20
Oregon	16	28
Nevada	14	209
Delaware	13	8
District of Columbia	13	179
South Dakota	13	21
Utah	13	41
North Dakota	12	10
Maine	11	15
New Hampshire	11	11
Hawaii	10	27
Wyoming	8	27
Idaho	6	28
Montana	5	10
Vermont	5	4
Rhode Island	3	15
Alaska	2	21

Tab 3

Travel Rates by Trip and Traveler Characteristics for Indiana: 2016
	Person trips (in thousands)	Number of travelers (in thousands)	Trips per traveler	Miles (in millions)	Miles per traveler	Miles per trip
PEOPLE WHO LIVE IN AREA	23,715	5,732	4.5	16,869	3,066	748
Means of transportation
Personal use vehicle	20,508	5,364	3.2	11,335	2,220	581
Commercial air	2,614	1,455	2	5,120	3,695	2,059
Intercity bus	28	29	1	16	592	615
Train	32	24	1.4	29	1,239	920
Other	533	357	1.6	370	1,091	729
	23,714	7,229	9	16,870	8,837	4,904
Main purpose
Business	4,523	1,149	4.3	3,355	3,069	779
Visit friends or relatives	9,519	3,430	3.1	5,780	1,771	638
Leisure	6,136	3,015	2.2	5,783	2,016	990
Other	3,538	1,785	2.2	1,951	1,149	580
Income
Less than $25,000	2,866	1006	2.1	1,843	1,925	676
$25,000 to $49,999	7,842	2,248	3.8	6,032	2,818	808
$50,000 or greater	13,007	2,508	5.7	8,993	3,768	726
Age
Less than 24	5,413	1,792	3.3	3,682	2,159	716
25 to 64	16,350	3,484	5.1	11,382	3,432	731
65 and over	1,953	494	4.3	1,805	3,839	971
Gender
Male	13,445	2,950	4.9	9,076	3,232	710
Female	10,270	2,795	4	7,792	2,929	797
PEOPLE WHO VISIT AREA	21,043	4,463	5.1	12,540	2,941	625
Means of transportation
Personal use vehicle	18,812	3,708	5.6	8,641	2,449	482
Commercial air	1,734	567	3.4	3,374	6,260	2,045
Intercity bus	14	6	2.3	10	1,529	732
Train	12	5	2.3	20	3,468	1,663
Other	470	176	3	485	2,896	1085
Main purpose
Business	5,064	1000	5.6	3,215	3,380	667
Visit friends or relatives	8,263	1,840	4.9	5,205	2,972	661
Leisure	4,029	1,278	3.5	2,022	1,662	527
Other	3,686	1004	4	2,087	2,186	595
Income
Less than $25,000	2,808	630	4.8	1,810	3,017	677
$25,000 to $49,999	6,940	1,526	4.9	3,844	2,646	582
$50,000 or greater	11,292	2,229	5.6	6,876	3,241	640
Age
Less than 24	5,183	1,208	4.7	2,917	2,535	591
25 to 64	14,412	2,837	5.6	8,720	3,229	635
65 and over	1,448	340	4.6	894	2,755	648
Gender
Male	11,661	2,308	5.6	7,025	3,198	633
Female	9,382	2,073	4.9	5,506	2,790	617

Case Problem 3 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 recently completed. The travel survey is located in the workbook named Indiana.xlsx presented. You will also include some information from a survey that your firm has Complete the following 1. Open the workbook named Indiana.xlsx in the Chapter 3 folder, and then save the Marketing 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 thc -(1)" value in the Scrics 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 advertis- ing 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. 209