Question

A travel agent (TA) in Athens named “Thomas o Mageiras” has contracts with 135
hotels from all categories (stars) in the three major Greek summer destinations,
namely Crete, Ionian Islands and South Aegean Islands. In order to better support its
clients the TA yearly collects and updates data regarding the performance of these
hotels.
The dataset in the table that follows, provides various measures of
all 135 hotels, summarized in the following variables:
Variables Description
STARS Hotel class category
Total_Rooms Total number of rooms per hotel

Region_ID

1= Crete
2=Southern Aegean Islands
3=Ionian Islands

ARR_MAY Average Room Rate in May (in €)
ARR_AUG Average Room Rate in August (in €)
L_COST Labor cost per hotel (in €)
Note: The empty cells in the given Excel spreadsheet indicate missing values.

Question 1:
1.1 Construct the frequency distribution, the percentage distribution and the frequency
histogram of the variables ARR_MAY and ARR_AUG for ALL 135 hotels. Use
the same class width = 25 for both frequency distributions. Comment on the
characteristics of the distributions.
1.2 Construct separately, the frequency distributions and the percentage distributions
of the variable ARR_AUG for each of the three destinations. Use the same class
width = 35 for each of the three frequency distributions.
1.3 Construct separately, the frequency distributions and the percentage distributions
of the variable ARR_MAY for hotels with a) Number of rooms ≤ 50 considered
as small size hotels b) Number of rooms > 50 considered as large size hotels. Use
the same class width = 25 for both frequency distributions.

1.4 Plot three histograms of the frequency distributions of the variable ARR_AUG,
one for each of the three destinations, and comment on the main characteristics of
the three distributions.
1.5 Plot two histograms of frequency distributions of the variable ARR_MAY, one
for the small and one for the large size hotels, and comment on the main
characteristics of the two distributions.
1.6 What conclusions can you reach concerning the differences in distribution a) of
ARR_AUG among the three destinations and b) of ARR_MAY between the
small and the large size hotels?

STARS	Total_Rooms	Region_ID	ARR_MAY	ARR_AUG	L_COST
1	44	2	16	35
1	18	2	16	20
1	21	3	16	40	30.000
1	21	3	16	40	20.000
1	31	2	18	35	10.450
2	32	3	19	45	6.500
1	46	3	19	23	43.549
4	38	1	22	51	59.200
2	73	2	22	41	70.000
4	65	1	25	63	83.000
3	122	2	25	33	568.536
1	16	2	25	70	379.498
1	13	3	25	45	15.000
1	16	3	25	31	27.084
2	29	1	26	44	30.000
2	42	2	28	40
2	17	1	29	53	6.121
1	23	1	30	35	50.237
2	30	2	30	55	40.000
2	15	2	30	45	4.296
1	11	2	30	50
1	22	2	30	35	1.520
3	39	3	30	68	50.000
3	32	3	30	100	40.000
2	49	3	30	73	41.000
1	13	3	30	50	9.500
1	21	3	30	40	10.000
1	8	1	31	49	7.888
2	25	3	32	55	61.766
1	57	1	35	90	11.720
1	20	1	35	45
1	18	1	35	40	112.181
2	26	1	35	40	3.575
2	37	2	35	95	270.000
2	14	3	35	70	5.500
3	98	2	38	75	150.000
3	44	2	38	84	75.704
3	75	1	40	60	37.735
3	32	1	40	60	3.500
2	23	1	40	50	6.500
1	11	1	40	55
1	15	1	40	55	3.500
3	23	1	40	55
3	10	2	40	70	10.000
1	22	2	40	100	20.000
1	12	2	40	65
4	112	3	40	56	363.825
3	72	3	40	75	177.833
1	21	1	42	54	5.700
5	378	3	44	128	2.429.367
2	68	1	45	55	199.000
3	75	2	45	70	220.000
3	62	2	45	90	50.302
3	25	2	45	95	118.049
2	15	1	47	50	19.670
4	221	2	47	102	623.117
3	27	1	48	55	20.906
4	117	2	48	91	360.000
3	62	3	50	90	252.390
3	30	3	50	80	45.000
2	41	3	50	90	166.903
2	14	3	50	80	4.000
3	66	1	51	65	230.000
2	48	1	52	60	284.569
2	39	1	53	104	107.447
3	102	3	53	91	173.481
4	62	2	55	75	249.205
2	21	2	55	100	12.000
2	53	3	55	80	48.200
4	10	1	57	97	30.000
4	70	1	59	128	437.684
1	25	1	59	128	156.316
3	69	1	60	70	256.658
3	47	1	60	120	255.020
3	170	2	60	104	538.848
2	18	2	60	100	10.000
5	270	3	60	90	1.934.820
5	240	2	61	132	1.312.601
4	241	1	64	109	793.009
4	121	1	64	132	494.566
3	54	1	65	90	200.000
4	172	1	68	148	1.383.854
5	57	2	68	140	300.000
4	227	3	69	123	1.538.000
4	322	1	70	159	1.608.013
4	27	1	70	100	130.000
2	10	2	70	100	12.500
4	56	3	70	100	96.000
3	78	3	70	120	377.182
2	24	3	70	120	116.056
2	20	3	70	120	96.713
5	133	1	71	136	801.469
4	96	3	73	134	210.000
3	93	1	76	130	626.000
4	200	2	77	178	796.026
2	35	1	80	110	64.702
1	25	2	80	120	36.277
1	25	2	80	120	36.277
3	16	2	80	100	14.300
4	216	3	80	124	1.339.903
3	74	3	80	95	111.000
5	265	1	81	174	1.393.550
5	280	3	81	138	903.000
5	127	1	85	114	1.072.000
3	33	3	85	120	238.000
4	353	2	87	152	1.511.457
5	172	1	90	195	1.151.600
4	10	1	90	105	15.950
5	219	3	93	162	1.675.995
5	313	1	94	173	2.214.985
3	18	1	94	104	722.069
4	97	3	94	120	441.737
2	43	3	94	120	195.821
5	412	1	95	160	2.165.000
4	276	2	95	160	2.050.000
3	11	3	95	120	3.000
5	166	3	98	183	900.000
3	33	2	99	218	271.724
5	139	2	100	130	495.000
5	50	2	100	180	517.729
2	25	2	100	150	60.000
5	181	3	100	187	1.143.850
5	119	3	100	150	600.000
4	9	3	100	180
5	174	3	102	211	2.500.000
5	124	3	103	160	1.103.939
5	330	2	112	240	434.237
4	324	2	112	211	1.800.000
4	161	3	112	213	1.370.968
5	306	2	113	235	2.074.000
5	261	3	119	211	3.000.000
5	204	1	131	225	2.460.634
4	34	2	133	218	96.619
4	27	2	180	250	51.000
4	12	2	215	265	45.000

Answer 1

Note : Running out of time - Able to complete on 3 questions

1.1 Construct the frequency distribution, the percentage distribution and the frequency histogram of the variables ARR_MAY and ARR_AUG for ALL 135 hotels. Use the same class width = 25 for both frequency distributions. Comment on the characteristics of the distributions.

Steps to generate histogram in excel.

Step 1 : Put the data in excel

Step 2 : Create a bin with class width of 25 as shown

Step 3 : Go to data -> data analysis -> histogram

Step 4 : Input the values as shown

Step 5 : The output will be generated as follows.

The frequency distribution and percentage table is shown below.

1.2 Construct separately, the frequency distributions and the percentage distributions of the variable ARR_AUG for each of the three destinations. Use the same class width = 35 for each of the three frequency distributions.

Filter the ARR_Aug data for each region into 3 columns as shown and create a bin of 35 class width.

Follow the steps shown mentioned above to make the histogram for each region

1.3 Construct separately, the frequency distributions and the percentage distributions of the variable ARR_MAY for hotels with a) Number of rooms ≤ 50 considered as small size hotels b) Number of rooms > 50 considered as large size hotels. Use the same class width = 25 for both frequency distributions.

Put the data for the total rooms and ARR May in an excel as shown.
Filter out the data for small room.( Total room less than or equal to 50)
Filter out the data for big hotel (Total room greater than 50)

Follow the steps shown mentioned above to make the histogram for each type of hotel