Builds 22 - Hotels Revenue Management

[FPastor]

@FPastor

Hello All,

WELCOME TO CHALLENGE 22

“Hotel Revenue Management”

We are excited to introduce Challenge 22 now!

Challenge 21 “EDNA YouTube Channel Data Analysis” was succeeded by 62 entries, 240+ analysis pages, 550+ DAX measures, and 350+ Navigation buttons, bookmarks, and Tooltips. Thanks for everyone’s engagement and dedication.

EDNA data challenge LinkedIn Group:

LinkedIn: Log In or Sign Up

Support Resources:

Challenge Forum:

About the Challenges category

Everything you need to know about Power BI challenges

Previous challenge highlights:

Challenges

Categories

Experts & Previous Winners – We, the challenges team, reserve the right to include any participant in this group if we, the challenges team, believe he/she qualifies as an advanced report developer.

Overall, Winner - all entrants are eligible, excluding Experts & Previous winners.

First Time Participant winner - open to any Enterprise DNA member who is taking part in the challenge for the first time.

Winning Non-member - open to entrants not currently eDNA members.

Out of the Box Thinker - for those digging deep into their analysis.

Creative Head - for those bringing a bit of extravagance into their report and analysis.

Prizes

There are some excellent prizes on offer from free subscriptions to Enterprise DNA on demand, for all category winners, and more – much more!!

[FPastor]

The Brief

After rolling out an excellent EDNA You tube Analysis, we are announcing Challenge 22.

“Hotel Revenue Management”

About the datasets

2 Zip files, Modelled (PBI File provided) and Unmodelled (Raw data) datasets.

One Excel Workbook – Hotel Revenue Management (100K+ Records)
PBI file – Data model provided.
Data Dictionary
Extended Calendar Version V2.01 - August 2022
Brief in PDF format

Important Read!!

Our project start date is 19/08/2022 and your final reports must be submitted by the 19/09/2022 at 11:59pm GMT.

Report Developer Role

It is your job as an analyst to prepare a deep-dive analysis report about the Hotel Revenue Management dataset.

Special Client Requirements:

1. Client does not want an Intro Page but suggest vertical/horizontal navigation bars or pop-up menu.

2. Not scrollable or fancy shape reports. Preferred size 16:9.

3. Client likes KPIs.

4. Client is very much interested in Time analysis exploration (Seasonality, Festive periods, weekday vs weekend etc)

5. Client is interested in Agent’s performance, overview.

6. Client is interested in any other insights providing further information on Customers type (family with children, single or couples visitors) etc.

Reports are not limited by the number of pages or techniques. Feel free to use all the techniques at your disposal – tooltips, drill throughs, page navigations, etc.

That is all for the brief!

Submission of entries

To be considered within the competition, entries are due no later than 11:59pm GMT Monday, September 19, 2022.

Join our challenge group at

Sign Up | LinkedIn

How to submit:

Email the completed PBIX file to

powerbichallenge@enterprisedna.co

federico.pastor@enterprisedna.co

Take an image of your report and post it to the Enterprise Challenges Group

Sign Up | LinkedIn

Take the image plus URL and post them on LinkedIn tagging Enterprise DNA to say, “I accepted the Enterprise DNA challenge.”

Hashtag: #EDNADataChallenge & #EDNAHotelRevenueManagement

We always encourage all participants to do a brief description of how they approach the project and share their experience of participating in the challenge and sharing it on the forum/social media.

If you need any help with publishing, please reach out to one of the team managers for assistance (post in the forum/LinkedIn group) or email to federico.pastor@enterprisedna.co

Federico Pastor

[mspanic]

Hi,

Great to have modelled Dataset with model in pbix - so we can play with the data and have fun immediately. Excellent.

Thanks.

[mspanic]

Little bit of strange that self-catering (no meal) is more expensive then full board - FB

or I missed something?

Also I see reservation with Check-out status
without any guests (0 adults, 0 children, 0 babies)

Sample view:

127 records / reservations (38 with 0 nights, others with stay nights different from 0 ).

[FPastor]

ATTENTION ALL PARTICIPANTS

The PBI File contained in the Modelled Dataset zip file contained an error. This has been fixed and it is fully functional now.

Please see PBI File here below for your use, please update your files.

Sorry for the error, please if in doubt do not hesitate to contact me or post in this forum.

Federico Pastor

Hotel Revenue_Challenge 22_Modelled.pbix

[Abee]

Hi Federico,
Thanks for setting out challenge 22.
Just wondering how the lead time is worked out?
Thanks,
Anupama.

[FPastor]

@Abee,

This is the proffesional definition “the number of days between the time a guest books their room and the time they are scheduled to arrive at the hotel”. Nonetheless, I have notice some discrepancies so please be aware.

I hope this helps.

Federico

[Abee]

Thanks Federico - I thought it was and was trying to get the days in between to add up to confirm it to myself. Thanks for letting me know about the discrepancies.
Anupama.

[jafernandezpuga]

Hi @Abee, @FPastor
I think that the Lead Time should be taken regardless of the Reservation Status Date since of the 141,585 total rows, only for 5089 the Lead Time coincides with the difference in days between the Arrival Date and the Reservation Status Date.

Can it be that the date of the reservation status changes when the reservation status changes and they are not giving us the data of the reservation date, which would have to be calculated by subtracting the arrival date minus the waiting days ?

Regards

[FPastor]

@jafernandezpuga & @Abee

Agree with Jose, discrepancies are obvious.

Thanks

Federico

[andeei]

Hi,

Not sure what to make of the is_cancelled vs. reservation_status fields.

For is_cancelled = 0 all rows have the “Check-Out” status which makes perfect sense, but for is_cancelled = 1 there are also a few on “Check-Out”.

Dataset error? Or is this a part of the case?

[FPastor]

@andeei

When encountering this kind of irregularities, my suggestion is to make your own assumptions and record your findings in the report. Excellent opportunity to tell the employeer, client, boss that there are recording problems with the data. There is no perfect dataset in the real world!!

I hope this helps

Federico

[ChallengeTeam]

Hi everyone, this is an entry submitted by Monika spyclyzn via email.

Here’s how Monika spyclyzn described it:

I’ve accepted EDNA Challenge no 22.

Had a great time exploring and testing out a few new tricks I’ve learned recently thanks to the incredible EnterpriseDNA

You can view the full report here
Monika

Please feel free to give Monika your feedback on her report.

[ChallengeTeam]

Hi everyone, this is an entry submitted by Fatma Ramadan via email.

Here’s how Monika Fatma Ramadan described it:

"I had accepted eDNA challenge 22, very interesting dataset to explore. Please see my report and I hope you like it."​

You can view the full report here
Fatma Ramadan

Please feel free to give Fatma Ramadan your feedback on her report.

[ChallengeTeam]

Hi everyone, this is an entry submitted by Fred Bevan via email.

Here’s how Fred Bevan described it:

To help with the time analysis, I generated a separate table containing a separate row for each date of each reservation. This made it much easier to answer questions.

You can view the full report here
Fred Bevan

Please feel free to give Fred Bevan your feedback on her report.

[jafernandezpuga]

Hi @FredBevan,
I have also followed the approach of generating a record for the day of stay and basing the time calculations on the date of the stay instead of taking only the arrival date of the reservation, since, as you say, it facilitates temporal analysis, although increases the size of the dataset.
I don’t know what the rest of the participants think.

https://www.biti.es/transformar-columnas-desde-hasta-en-filas/

Regards,

[jafernandezpuga]

Hi @FPastor,
I wanted to consult some doubts that I have about the calculation of income and how to apply discounts by market segment and the subject of meal.

To calculate room revenue I am multiplying the AVG Daily Rate by the Total Number of Nights.
Are market segment discounts applied to these room revenues to arrive at net revenue?

To calculate the income for the meal, are the number of adults + number of children multiplied by the daily rate according to the accommodation regimen and by the number of nights? Are discounts applied to this calculation?

Regards,

[FPastor]

@jafernandezpuga

There is not direction on this, so make your own assumptions. I will take AVG - Discount as fixed subtotal and add any other service to this subtotal to reach a total. Prices are per Room and not number of guests.

Again, my last time in a Hotel was pre-covid

I hope this helps,

Federico
PS. Now that you’re Enterprise DNA Certifified. You only have 1 question per challenge

[ChallengeTeam]

Hi everyone, this is an entry submitted by Veronica Aide via email.

Here’s how veronica Aide described it:

I’m very happy because with this Dataset I have learned new things and I continue on the journey of learning Data Analysis, thanks again for all the support

You can view the full report here
Veronica Aide

Please feel free to give Veronica Aides your feedback on her report.

[ChallengeTeam]

Hi everyone, this is an entry submitted by Katy Chiang via email.

Here’s how Katy Chiang described it:

Data can be filtered by calendar year, quarter, month or week. In this project, I learned how to insert a new table using DAX. However, due to the space limitation, I had to use the country abbreviations instead

You can view the full report here
Katy Chiang

Please feel free to give Katy Chiang your feedback on her report.