Excel Data Analysis Task

Posted on | by Linus

Obtain a sample raw data file (in Excel) with 48 months of property and comp set data (STR Number, Month Date, Subject Supply, Subject Demand, Subject Revenue, Comp Set Supply, Comp Set Demand, and Comp Set Revenue). Make a copy of the file from this link.
Insert 3 columns between columns E and F for Subject Occupancy (Subj Occ), Subject ADR (Subj ADR) and Subject RevPAR (Subj Rpr). Enter the KPI formulas and copy to all rows.
Use columns L, M, and N for Comp Set Occupancy (Comp Occ), Comp Set ADR (Comp ADR) and Comp Set RevPAR (Comp Rpr). Enter the KPI formulas and copy to all rows.
Insert a column after Subject Occupancy (between columns F and G) for Subject Occupancy Percent Change (Subj Occ %Chg). Insert a column after Subject ADR for Subject ADR Percent Change (Subj ADR %Chg) and insert a column after Subject RevPAR for Subject RevPAR Percent Change (Subj Rpr %Chg).
Enter the Percent Change formulas and copy to all rows. You will have to compare the “This Year” value to the “Last Year” value which appears 12 rows above. You will not be able to derive these metrics for the first year of data.
Insert columns after the Comp Set Occupancy, ADR, and RevPAR columns for those Percent Change numbers. Enter the formulas and copy to all rows.
Add columns at the end (U, V, and W) for Occupancy Index (Occ Indx), ADR Index (ADR Indx), and RevPAR Index (Rpr Indx). Enter the Index formulas and copy to all rows.
Insert a column after Occupancy Index (between columns U and V) for Occupancy Index Percent Change (Occ Indx %Chg). Insert a column after ADR Index for ADR Index Percent Change (ADR Indx %Chg) and insert a column after RevPAR Index for RevPAR Index Percent Change (Rpr Indx %Chg).
Enter the Index Percent Change formulas and copy to all rows. Use the same procedure as for the values. You will not be able to derive these metrics for the first year of data.
Optionally, you could use Excel to create graphs of the various metrics. Create a line graph of the three Index numbers (Occ, ADR, and RevPAR). Create a second line graph of the Index Percent Change numbers. What does this tell you about “your hotel” compared to the comp set?
Create a line graph for Occupancy (the actual value), one line for the Subject and another for the Comp Set. Create similar graphs for ADR and RevPAR. What does this tell you about the various KPIs?
Create similar graphs for the KPI Percent Changes of the Subject versus the Comp Set. You might be able to combine these on a single graph or you may find that is too busy. What does this tell you about the relative improvement of your hotel versus the comp set? You might find it interesting to graph the percent Changes for Supply, Demand, and Revenue.

Struggling with where to start this assignment? Follow this guide to tackle your assignment easily!

Step-by-Step Guide for the Excel Data Analysis Task

1. Obtain and Prepare the Data File:

  • Download the raw data file from the provided link. Ensure it contains the following columns:
    • STR Number
    • Month Date
    • Subject Supply
    • Subject Demand
    • Subject Revenue
    • Comp Set Supply
    • Comp Set Demand
    • Comp Set Revenue

2. Insert New Columns for KPIs:

  • Insert 3 columns between columns E and F for the following KPIs:
    • Subject Occupancy (Subj Occ): Occupancy is calculated as Subject Demand / Subject Supply.
    • Subject ADR (Subj ADR): ADR (Average Daily Rate) is calculated as Subject Revenue / Subject Demand.
    • Subject RevPAR (Subj Rpr): RevPAR (Revenue per Available Room) is calculated as Subject Revenue / Subject Supply.
  • Formulas for Subject KPIs:
    • In column F (Subject Occupancy): =E2/D2 (copy down for all rows)
    • In column G (Subject ADR): =F2/D2 (copy down for all rows)
    • In column H (Subject RevPAR): =G2/E2 (copy down for all rows)

3. Insert Columns for Comp Set KPIs:

  • Insert 3 columns after column N for the following KPIs:
    • Comp Set Occupancy (Comp Occ): Occupancy is calculated as Comp Set Demand / Comp Set Supply.
    • Comp Set ADR (Comp ADR): ADR (Average Daily Rate) is calculated as Comp Set Revenue / Comp Set Demand.
    • Comp Set RevPAR (Comp Rpr): RevPAR is calculated as Comp Set Revenue / Comp Set Supply.
  • Formulas for Comp Set KPIs:
    • In column L (Comp Set Occupancy): =K2/J2 (copy down for all rows)
    • In column M (Comp Set ADR): =L2/J2 (copy down for all rows)
    • In column N (Comp Set RevPAR): =M2/K2 (copy down for all rows)

4. Insert Percent Change Columns for Subject KPIs:

  • Insert columns after Subject KPIs for Percent Change calculations:
    • Subject Occupancy Percent Change (Subj Occ %Chg): Compare the current month’s occupancy to the same month from the previous year.
    • Subject ADR Percent Change (Subj ADR %Chg): Compare the current month’s ADR to the same month from the previous year.
    • Subject RevPAR Percent Change (Subj Rpr %Chg): Compare the current month’s RevPAR to the same month from the previous year.
  • Formulas for Percent Change for Subject KPIs (starting from row 13):
    • For Subject Occupancy Percent Change: =(F13-F1)/F1*100 (copy down for all rows starting from row 13)
    • For Subject ADR Percent Change: =(G13-G1)/G1*100 (copy down for all rows starting from row 13)
    • For Subject RevPAR Percent Change: =(H13-H1)/H1*100 (copy down for all rows starting from row 13)

5. Insert Percent Change Columns for Comp Set KPIs:

  • Insert columns after the Comp Set KPIs for Percent Change calculations:
    • Comp Set Occupancy Percent Change (Comp Occ %Chg): Compare the current month’s occupancy to the same month from the previous year.
    • Comp Set ADR Percent Change (Comp ADR %Chg): Compare the current month’s ADR to the same month from the previous year.
    • Comp Set RevPAR Percent Change (Comp Rpr %Chg): Compare the current month’s RevPAR to the same month from the previous year.
  • Formulas for Percent Change for Comp Set KPIs (starting from row 13):
    • For Comp Set Occupancy Percent Change: =(L13-L1)/L1*100 (copy down for all rows starting from row 13)
    • For Comp Set ADR Percent Change: =(M13-M1)/M1*100 (copy down for all rows starting from row 13)
    • For Comp Set RevPAR Percent Change: =(N13-N1)/N1*100 (copy down for all rows starting from row 13)

6. Insert Index Columns:

  • Insert columns at the end of the data (U, V, and W) for the following Index calculations:
    • Occupancy Index (Occ Indx): Compare Subject Occupancy to Comp Set Occupancy.
    • ADR Index (ADR Indx): Compare Subject ADR to Comp Set ADR.
    • RevPAR Index (Rpr Indx): Compare Subject RevPAR to Comp Set RevPAR.
  • Formulas for Index Calculations:
    • Occupancy Index (U): =F2/L2 (copy down for all rows)
    • ADR Index (V): =G2/M2 (copy down for all rows)
    • RevPAR Index (W): =H2/N2 (copy down for all rows)

7. Insert Percent Change Columns for Indexes:

  • Insert columns for Percent Change of the Indexes after columns U, V, and W:
    • Occupancy Index Percent Change (Occ Indx %Chg): Compare the current month’s Occupancy Index to the same month from the previous year.
    • ADR Index Percent Change (ADR Indx %Chg): Compare the current month’s ADR Index to the same month from the previous year.
    • RevPAR Index Percent Change (Rpr Indx %Chg): Compare the current month’s RevPAR Index to the same month from the previous year.
  • Formulas for Percent Change for Indexes (starting from row 13):
    • For Occ Indx %Chg: =(U13-U1)/U1*100 (copy down for all rows starting from row 13)
    • For ADR Indx %Chg: =(V13-V1)/V1*100 (copy down for all rows starting from row 13)
    • For Rpr Indx %Chg: =(W13-W1)/W1*100 (copy down for all rows starting from row 13)

8. Create Graphs (Optional):

  • Graph 1: Line graph of Index Values
    • Create a line graph with the Occupancy Index, ADR Index, and RevPAR Index to visualize performance over time for the Subject hotel compared to the Comp Set.
  • Graph 2: Line graph of Index Percent Changes
    • Create a line graph of the Occupancy Index Percent Change, ADR Index Percent Change, and RevPAR Index Percent Change.
  • Graph 3: Line graph for Subject and Comp Set KPIs
    • Create separate line graphs for Occupancy, ADR, and RevPAR values for both the Subject and Comp Set.
  • Graph 4: Line graph for Percent Changes of the Subject vs. Comp Set
    • Plot Occupancy, ADR, and RevPAR Percent Changes for both the Subject and the Comp Set.
  • Graph 5: Supply, Demand, and Revenue Percent Change Graph
    • Optionally, create graphs comparing the Percent Change for Supply, Demand, and Revenue for both the Subject and Comp Set.

9. Analyze the Results:

  • Graph Insights:
    • The graphs will tell you whether your hotel is outperforming or underperforming the Comp Set in key KPIs such as Occupancy, ADR, and RevPAR.
    • Pay attention to the Percent Changes in KPIs to see whether your hotel is improving relative to the Comp Set.
    • Occupancy and Revenue indexes provide a quick way to see if your hotel is performing better or worse than the Comp Set.

With these steps, you can structure your Excel sheet to analyze property data over time effectively, comparing your hotel’s performance to the competitive set and visualizing the trends using graphs.

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount