Obtain a sample raw data file (in Excel) with 48 months of property and comp set

Obtain a sample raw data file (in Excel) with 48 months of property and comp set

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.