Scenario
You have recently been employed in a large hotel chain operating in the US. Your company own and manage a number of hotels over different classes (Economy, mid-scale, up-scale and upper upscale). Your hotel chain will be chosen during class time.
is Upscale Courtyard. (I need to analyze the data of Courtyard in VIRGINIA!!! No need to consider Miami and Tampa.)
The board has asked you to conduct statistical research into the market. The company is experiencing a decline in Revenue across the board and needs to close some hotels.
In order for them to make an informed, well researched decision, you have obtained data from hotels in 3 of their key markets.
Instructions:
In all the questions you need to keep in mind your report will be read by management, so should be well organised, neat and easy to read.
All relevant files can be found in the folder “Project files” in week 8 of eLearning. There are Excel files on the 3 areas of the US that your company is considering. The Data is mostly in raw format. You need to find ways to simplify the Excel file and organise the data. Use separate Excel sheets for each section.
Things to includes in the analysis:
- Rates
- 2017 2018 2019
Q1&2 by Thursday this week
- Investigate the complete market structure in all three main areas –CENSUS DATABASE FILE (Individual)
- What does the market look like in each area. (pie charts)
- In each class, which hotel group (exclude Independents) has the biggest market share.
- Where does your company fit it? More in the middle? upper? lower?
- Who is your biggest competitor?
- You may decide which chart types will make this data easiest to understand .(Make sure to use charts other than pie for all 3 Qs). At least 3 different charts is needed to obtain the maximum mark. Bar, histogram, pie,(skew graph kurtosis)
- Write an analysis and explain your findings using textboxes in the Excel file. [10]
- Forecasting – (Individual)
Using the Monthly Excel file, do forecasts of the occupancies for all the areas your area. (Line charts)
Combine the occupancy forecast with any other KPI forecast that you think will give the best indication of which area to consider for closure. (look at KPIs other than occupation and create forecast sheets for them as well)
Which area is the least favourable and why?
Include forecast sheets for each KPI you include. [15]
YOU MAY NOT make use of Excel’s automatic forecasting tool.
- Revenue and costs (Individual)
Analyse Revenue and Cost data for 2019 and 2020 for your area by using the “Host …. Full service” Excel file. Discuss with your team what you think is important and show your findings using charts to compare all 3 regions. Type your findings in the Excel sheet using text boxes.[10] (Whats gone better and whats gone worst measure from statistics)
- Measures of Central tendency and spread (Individual)
Use the file Census database and construct frequency tables to show the distribution of room numbers across the classes. (Histograms)
Construct histograms of each class and indicate where your hotel is situated in your respective class.
Compile Descriptive statistics (variants, SD, range, mean, mode, median) on each class. Show all values in a readable format.
Write a brief analysis on your findings using a textbox. [10]
- Boxplot – rate distribution (Individual)
Create a boxplot for each room rate in your specific class. (Closed hotels not to be included due to a minimum bound of 0)
Create boxplots using Average room rates (Exclude all 0 room hotels, and all 0 values per room type). (Done using excel)
Analyse the market using a textbox in the Excel sheet as well as your position in the market.
- Seasonality (Individual)
Use the file Monthly data for your area for this question.
Construct a Centered Moving average data sheet in which you determine the adjusted quaterly seasonality factors for the area based on Demand. (seasonality and line graphs)
Construct an Excel chart that will adequately display the actual demand vs centered moving average demand.
Comment on your findings using a textbox in the Excel sheet. [15]
- Correlation and regression analysis – (Individual)
Use the file Monthly data. Establish if there is a change in correlation between occupancy and ADR over the past year for your area compared to the following time periods: (monitor the change in correlation)
2017
2018
2019
- a) Find the Correlation Coefficients for all time periods (PEARSON)
- b) Use charts to show the relationship between the two variables. (scatter plot)
- c) Explain the relationship (if any) between the two variables for 2021
- d) Use trend analysis and forecast ADR rates for 2021 at
- 45% occupancy
- 75 % occupancy
- 90 % occupancy
Use your findings to analyse the current correlation in Occupancy and ADR rates for 2021 in relation to 2017.
- Hotel data analytics analytics -(Individual and Group)
Analyse the KPIs for your individual areas using the Monthly data Excel file.
(RevPar, etc. show using graphs)
Compare your area to those of the other group members. Which area looks best/worst. Your excel sheet will be evaluated by considering relevant functions, graphs and any other statistical tool you deem useful. [15]
- Decicion tree (Individual)
To be added during Thursday’s lesson.
- Report / Recommendation (Group)
You need to create a MS Word document Saved as yourNameReport. Once your are satisfied that your project is finisedfinished, save a PDF version of the file to upload the eLearning. Keep the Word copy as a backup.
Compile a Report to management where you outline all your findings and make a recommendation for the area in which one or more hotels should be closed. Bring in all aspects and justify all your answers using graphs and statistics. Draw on all the areas covered before to substantiate your decision.
Your document should include:
- a header with your names and student numbers,
- page numbers at the bottom of each page and
- a table of contents
- a main title page including your hotel logo or any relevant photos.
Marks will be allocated for neatness, style and readability.
Your report should contain the following as a minimum:
- a) Market analysis
- b) Area forecasts
- c) Revenue and cost analysis
- d) Correlation analysis
- e) KPI analysis
- f) Decision tree analysis
- g) Recommendation
- h) (Make sure the all the charts are labelled correctly)
All of the above should be answered in the form of a report that will systematically make a final recommendation to the company by means of justifications on all your decisions. Keep in mind this will be read by senior management so should be very easy to understand and as visual as possible. [50] (Start by discussing all the stats and then finally explain your decision based on the supplied data)