THIS IS HOW YOUR FINAL MODEL SHOULD LOOK: TIM403: Revenue Management Homework 1: Computing KPIs Due by 6:00 PM Tuesday, Sep 14 Objective: In this exercise you will learn how to compute key performance indicators (KPIs) using Math Fundamentals Application Excrcisc raw data file. Please ensure that you save the file to the desktop or to a memory stick before beginning. ? M SuitOSN Core Core Core Cero w Camp Coro A24 Me Coro Oce 2 or ves ADA Yves nic kwa AB when the WE wig 123485 WWW.ALTXS X? 28 22 22341 e 24 122.3 THUS IS A 4. D21 i is 1 EEE 10. TRNA 19 1994 IN 13 123405 3000 3000 SUNS 92 21235 12.00 051014831 03 152.06 IN IL LOL? DO L125 10. LAI AV W SA 2012 LI 123466 200006 He 2 L2 22 10.2 200 177.60 190 2021 ULL > L24 LIS. I IHKO i5 2..99 12 www. w 19:19 WAT MY 15 1246.2 W LNGS 722 22 19030 XXX X X X 121 2002 1 MECHI TRAV? 3 IN 191 20% 2018 WE 1921 1184 12345 L U 12.08 65140 CT exco ca. LLIS 104 105 136 14 1 ETS TU 1 124 12366 XL 2.0 CLA 23 L2 22 17.0 23 XXX S7: 15 20 25 3002 2009-LES 2007 1 12 / REM: RE: KW 1.4 16 4 1 1 XXV. 1 28 12 wees www ws M4 ASM 12:41 111 115 14 13 15 WG01 TO 1719 1213 12464 20 W 3CX EX EO : 4728 mm - wc x L. 123 122 12 1 IN DEN SEX U THERE XXX TEE 112 4 874 1% 13455 MARTINS IR API 1 MEET Chart Title 24 123405 300200 3063 2012 14 WUM 1S WA 137 94 123466 12.7 2.0 2016 L L 21.0 1STE 123485 000 6 6 12 W TIM TE" " 11 10 32 123466 200006 sex REBER 12.367 123485 WWW LIS AT 32116 1 TINGS . 14 ER IN DEN FRI 120 X NT LES 1114 123465 30000093 21 ALLES 20 X 72756 02 OL.23 OL 17 18 03 2057 20 14 -2 112.8 LO 1. Obtain a sample raw data file (in Excel) with 60 months of subject and comp set data (STR Number, Month Dale, Subject Supply, Subject Demand, Subject Revenue, Comp Sel Supply, Comp Set Demand, and Comp Set Revenue). Make a copy of the file. 2. Insert 3 columns between columns E and F for Subject Occupancy (Subj Occ), Subject ADR (Subj ADR) and Subject RevPAR (Subj Bpc). Enter the headings above. Enter the KPI formulas and copy to all rows. 3. Use columns L, M, and N for Comp Set Occupancy (Comp Occ), Comp Set ADR (Comp ADR) and Comp Set RevPAR (Comp Rpt). Enter the headings above. Enter the KPI formulas and copy to all rows. 4. Insert a column after Subject Occupancy (between columns F and G) for Subject Occupancy Percent Change (Subj Oec %Chg.). Insert a column aller Subject ADR for Subject ADR Percent Change (Subj ADR%Chg) and insert a columnn after Subject RevPAR for Subject RevPAR Percent Change (Subi Rp%Che) 5. 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 mws above. You will not be able to derive these metrics for the first year of data. 6. Insert columns after the Comp Set Occupancy, ADR, and RevPAR columns for those Percent Change numbers. Enter the formulas and copy to all rows. 7. Add columns at the end (U, V, and W) for Occupancy Index (Occ Insix), ADR Index (ADR Indx). and RevPAR Index (Boc locx). Enter the Index formulas and copy to all rows. 8. 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 loda %Chg) and insert a column after RevPAR Index for RevPAR Index Percent Change (Rprlosis. %Cha). 9. 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 melrics for the first year of data. 10. 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? 11. 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? 12. Create similar graphs for the KPI Percent Changes of the Subject versus the Comp Sct. 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 or the actual values for Supply, Dernand, and Revenue. 2 THIS IS HOW YOUR FINAL MODEL SHOULD LOOK: TIM403: Revenue Management Homework 1: Computing KPIs Due by 6:00 PM Tuesday, Sep 14 Objective: In this exercise you will learn how to compute key performance indicators (KPIs) using Math Fundamentals Application Excrcisc raw data file. Please ensure that you save the file to the desktop or to a memory stick before beginning. ? M SuitOSN Core Core Core Cero w Camp Coro A24 Me Coro Oce 2 or ves ADA Yves nic kwa AB when the WE wig 123485 WWW.ALTXS X? 28 22 22341 e 24 122.3 THUS IS A 4. D21 i is 1 EEE 10. TRNA 19 1994 IN 13 123405 3000 3000 SUNS 92 21235 12.00 051014831 03 152.06 IN IL LOL? DO L125 10. LAI AV W SA 2012 LI 123466 200006 He 2 L2 22 10.2 200 177.60 190 2021 ULL > L24 LIS. I IHKO i5 2..99 12 www. w 19:19 WAT MY 15 1246.2 W LNGS 722 22 19030 XXX X X X 121 2002 1 MECHI TRAV? 3 IN 191 20% 2018 WE 1921 1184 12345 L U 12.08 65140 CT exco ca. LLIS 104 105 136 14 1 ETS TU 1 124 12366 XL 2.0 CLA 23 L2 22 17.0 23 XXX S7: 15 20 25 3002 2009-LES 2007 1 12 / REM: RE: KW 1.4 16 4 1 1 XXV. 1 28 12 wees www ws M4 ASM 12:41 111 115 14 13 15 WG01 TO 1719 1213 12464 20 W 3CX EX EO : 4728 mm - wc x L. 123 122 12 1 IN DEN SEX U THERE XXX TEE 112 4 874 1% 13455 MARTINS IR API 1 MEET Chart Title 24 123405 300200 3063 2012 14 WUM 1S WA 137 94 123466 12.7 2.0 2016 L L 21.0 1STE 123485 000 6 6 12 W TIM TE" " 11 10 32 123466 200006 sex REBER 12.367 123485 WWW LIS AT 32116 1 TINGS . 14 ER IN DEN FRI 120 X NT LES 1114 123465 30000093 21 ALLES 20 X 72756 02 OL.23 OL 17 18 03 2057 20 14 -2 112.8 LO 1. Obtain a sample raw data file (in Excel) with 60 months of subject and comp set data (STR Number, Month Dale, Subject Supply, Subject Demand, Subject Revenue, Comp Sel Supply, Comp Set Demand, and Comp Set Revenue). Make a copy of the file. 2. Insert 3 columns between columns E and F for Subject Occupancy (Subj Occ), Subject ADR (Subj ADR) and Subject RevPAR (Subj Bpc). Enter the headings above. Enter the KPI formulas and copy to all rows. 3. Use columns L, M, and N for Comp Set Occupancy (Comp Occ), Comp Set ADR (Comp ADR) and Comp Set RevPAR (Comp Rpt). Enter the headings above. Enter the KPI formulas and copy to all rows. 4. Insert a column after Subject Occupancy (between columns F and G) for Subject Occupancy Percent Change (Subj Oec %Chg.). Insert a column aller Subject ADR for Subject ADR Percent Change (Subj ADR%Chg) and insert a columnn after Subject RevPAR for Subject RevPAR Percent Change (Subi Rp%Che) 5. 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 mws above. You will not be able to derive these metrics for the first year of data. 6. Insert columns after the Comp Set Occupancy, ADR, and RevPAR columns for those Percent Change numbers. Enter the formulas and copy to all rows. 7. Add columns at the end (U, V, and W) for Occupancy Index (Occ Insix), ADR Index (ADR Indx). and RevPAR Index (Boc locx). Enter the Index formulas and copy to all rows. 8. 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 loda %Chg) and insert a column after RevPAR Index for RevPAR Index Percent Change (Rprlosis. %Cha). 9. 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 melrics for the first year of data. 10. 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? 11. 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? 12. Create similar graphs for the KPI Percent Changes of the Subject versus the Comp Sct. 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 or the actual values for Supply, Dernand, and Revenue. 2