FutureHotel Data Analytics Project With Microsoft Excel

TABLE OF CONTENTS

• Project Overview
• Data Dictionary
• Tools
• Data Cleaning and Preparation
• Exploratory Data Analysis
• Data Analysis
• Findings
• Limitation

Project Overview

FutureTale Hotel speaks dynamic modernity. The Chinese Restaurant, Japanese Gourmet Restaurant, Lobby Lounge & Bars, and Grand Ballroom, as well as the guest rooms and suites, meet the most exacting comfort and service standards. FutureTale Hotel has noticed inconsistencies in its returns from 2017 to 2018. Being a modern relaxation center with a booking platform, A significant number of hotel reservations are called off due to cancellations or no-shows. The typical reasons for cancellations include change of plans, scheduling conflicts, etc. This is often made easier by the option to do so free of charge or preferably at a low cost which is beneficial to hotel guests, but it is a less desirable and possibly revenue-diminishing factor for hotels to deal with.

Data Dictionary

1. Booking_ID: unique identifier of each booking
2. no_of_adults: Number of adults
3. no_of_children: Number of Children
4. no_of_weekend_nights: Number of weekend nights (Saturday or Sunday) the guest stayed or booked to stay at the hotel
5. no_of_week_nights: Number of weeknights (Monday to Friday) the guest stayed or booked to stay at the hotel
6. type_of_meal_plan: Type of meal plan booked by the customer:
7. required_car_parking_space: Does the customer require a car parking space? (0 - No, 1- Yes)
8. room_type_reserved: Type of room reserved by the customer. The values are ciphered (encoded) by INN Hotels.
9. lead_time: Number of days between the date of booking and the arrival date
10. arrival_year: Year of arrival date
11. arrival_month: Month of arrival date
12. arrival_date: Date of the month
13. market_segment_type: Market segment designation.
14. repeated_guest: Is the customer a repeated guest? (0 - No, 1- Yes)
15. no_of_previous_cancellations: Number of previous bookings that were canceled by the customer prior to the current booking
16. no_of_previous_bookings_not_canceled: Number of previous bookings not canceled by the customer prior to the current booking
17. avg_price_per_room: Average price per day of the reservation; prices of the rooms are dynamic. (in euros)
18. no_of_special_requests: Total number of special requests made by the customer (e.g. high floor, view from the room, etc)
19. booking_status: Flag indicating if the booking was canceled or not.

Tools

Microsoft Excel download here

Data Cleaning and Preparation

The file provided is an Excel Sheet. The following tasks were performed: -

1. Opening the Excel File using Microsoft 365
2. Handling all missing data/values
3. Data cleaning and formatting

Exploratory Data Analysis

• Total booking for 2017 and 2018
• Provide insight on all booking arrangements by visitors for 2017 and 2018
• Show the percentage cancellation trend concerning market segment, meal plans, and room types

Data Analysis

• Used the pivot table and chart functions to analyze my data I also used data, insert and countif and sumif functions to analyze my data

Findings

• From 2017 to 2018, this outlook reveals that there is an additional 22% increase in booking cancelation with an attendant reduction in redeemed bookings of clients at the Hotel
• Across Market segment, The ONLINE method has the highest booking Year on Year
• Across Meal plans, MEAL PLAN 1 has the highest booking Year on Year
• Across Room Types, ROOM TYPE 1 has the highest booking Year on Year

Limitation

• Data was arbitrary so assumptions were made