EDNAChallenge22 Hotel Revenue Analysis in Power BI:

It is a Hotel Revenue Analysis of EDNA Challenge 22.

Overview:

• I was provided one excel file with different year data and workbooks of 2018, 2019 and 2020. But, I clubbed them up and made a new workbook named "Full Dataset" which includes all the rows of the data.

• The sheet contains several columns like Hotel Type, is_canceled, lead_time, arrival_date_year, arrival_date_month, stays in weeks and weekend nights, avg daily rate, adults, babies,children, etc.

Original Excel Page:

Demands of the client:

After loading dataset in Power BI, I applied the following applications:

Steps taken In Power BI Application:

Theme and Color Palette:

• First of all, I chose a color palette for the dashboard which included black, green, brown, orange and blue.
• Theme chosen was dark.

Dataset Applications of DAX in Power BI:

I applied some DAX measures inside the dataset like DISTINCTCOUNT, SUM, CALCULATE measures to get the desired mathematical values in either percentages or whole numbers.

Visualization and parameters used:

• From visualization point of view, I thought here to go with 3 paged dashboard with horizontal navigation panes containing 3 pages which are of "Agent Profile", "Guests Profile", "KPI Indicators".

• In KPI indicators, I used 4 criteria here which are "Cancellations", "Lead Time", "Repeated Guests" and "Stays in Weeks and Weekends w.r.t to Reservation Date". It overall gave a nice picture in terms of various aspects like age categories, country, etc.

• I used 4 filters in the report which were "Reservation Year", "Hotel Type", "Distribution Type" and "Customer Type" and used them as side pane.

• Also used combination of selection, bookmark and buttons for the navigation purpose of the dashboard.

• Pixel Size used was 16:9 which is (720*1280)px .

Key Insights and Recommendations from the Report:

• August has most number of check-outs over the period of 3 years.
• July has most number of cancelled reservations over the period of 3 years.
• Agent 9 contributed most to the revenue and his/her pay could be increased if the management would like to consider on it.
• Total Adults has been the highest among all guests who stayed at hotel.
• Portugal is the country where most number of people stayed at hotel.
• Transient Customer type is among the highest customer type.
• Most number of cancelled reservations are from adults and least from babies.
• Average Lead time is highest for the month of July.

Conclusion:

Overall, it was nice dataset to work around and loved to visualize and come up with insights.

Dashboard