Case Study on Revenue Management

profileFaridak3
M9116-REVENUEprojecttemplate-2021.21.xlsx
Home>Business & Finance homework help>Accounting homework help>Case Study on Revenue Management

1. RGI

This is a template to manually calculate the RGI in a city where we have hotels. Calculate all fields based on the date given. Explain the results including the postitioning strategy of "MY HOTEL 1" & "MY HOTEL 2".
Month OCTOBER
Year 2020
ANALYSIS:
HOTELS av. Rooms av. Month FMS room occ Occ % MS MPI ARR Total Room Revenue ARI RGI
hotel 1 120 68.92% 72.67
hotel2 146 62.99% 50.18
hotel 3 148 77.65% 54.16
hotel 4 86 78.10% 40.40
hotel 5 176 39.15% 66.28
MY HOTEL 1 245 59.17% 64.06
hootel 6 124 85.50% 55.00
hotel 7 133 74.09% 64.96
hotel 8 179 41.62% 84.38
hotel 9 117 84.05% 56.60
hotel 10 93 72.40% 55.76
MY HOTEL 2 162 70.37% 50.46
Total
FMS hotel 1 hotel2 hotel 3 hotel 4 hotel 5 MY HOTEL 1 hootel 6 hotel 7 hotel 8 hotel 9 hotel 10 MY HOTEL 2 MS hotel 1 hotel2 hotel 3 hotel 4 hotel 5 MY HOTEL 1 hootel 6 hotel 7 hotel 8 hotel 9 hotel 10 MY HOTEL 2

2. PROJECTION EXERCISE

The hotel to be analysed is a 4* Urban hotel. It counts with infrastructure to attrack groups and events, clients going to the airport and a mix of leisure and business travellers.
Hotel room inventory 149
Days of the month 31
We are about to start the new month of March 2020. The situation of the hotel in shown in "Proyection data".
Answer the questions that appear in the box below, including the correspondent formulas in the cells.
What was the pick up variation in terms of RN and ADR (RN & ADR) during the current month in 2019, from the beginning to the end of the month?And What was the % of growth?
RN ADR
% %
What is the % of occupancy and RevPar that the hotel did obtain last year in the given month?
% OCC REVPAR
To achieve the existing revenue budget objective given, what ADR should we achieve in the pending rooms to be sold for the rest of the month?
Comparing last year results and the current year budget, what is the % of revenue growth stablished in the budget when we compare both data?
Do a forecast proposal in the grey cells in the "Proyection data" sheet. You can use formulas where you feel convenient, and show % occupancy, accumulated ADR and resulting rooms revenue.
% OCC ADR ROOMS REV.
What is the % of completion of the given revenue budget that is expected, according to your forecast? Would you reach, exceed or fail to achieve the objective?
Explain in brief the reasons why the budget will be met or not. Do you think that you should have required more information than the one given for your forecast, explain why.
Taking into accoun the current OTB situation and the rest of the information provided in the document, Do you think that the strategy of sales could have been improved? Why?
If your answer is yes, explain the main aspects that should have been taken into account to apply such a change of strategy and its main repercussions.

Projection data

HOTEL INVENTORY 149 Fill in the grey columns with your forecast.
DAYS OF MONTH 31 We are in the last days of February and these data correspond to March.
Room Nights ADR REVENUE
OTB SM LY ACTUAL LY (final result) OTB FORECAST BUDGET (objective) OTB SM LY ACTUAL LY (Final result) OTB FORECAST BUDGET OTB SM LY ACTUAL LY (final result) OTB FORECAST BUDGET (objective)
BAR 351 978 490 1,000 133.44 € 130.82 € 143.75 € 142.00 € 46,837.65 € 127,946.33 € 70,435.05 € 142,000.00 €
DISCOUNTS 218 275 138 150 99.01 € 101.09 € 118.50 € 118.00 € 21,583.70 € 27,800.77 € 16,352.74 € 17,700.00 €
TACTICAL 0 0 0 0 - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 €
PACKAGES 10 11 10 15 74.50 € 71.34 € 63.67 € 63.50 € 745.00 € 784.74 € 636.65 € 952.50 €
LOYALTY PROGRAMS 43 45 18 35 35.60 € 39.09 € 39.51 € 42.00 € 1,530.76 € 1,759.24 € 711.20 € 1,470.00 €
NEGOTIATED 233 808 389 969 106.18 € 104.02 € 94.55 € 95.31 € 24,740.17 € 84,048.88 € 36,781.52 € 92,355.39 €
NET 393 438 244 375 77.20 € 77.84 € 87.13 € 85.00 € 30,340.63 € 34,095.93 € 21,258.54 € 31,875.00 €
Subtotal Transient 1,248 2,555 1,289 2,544 100.78 € 108.19 € 113.40 € 112.56 € 125,777.91 € 276,435.89 € 146,175.70 € 286,352.89 €
MICE 617 1,084 805 1,170 101.94 € 103.40 € 115.40 € 116.41 € 62,894.36 € 112,080.89 € 92,896.66 € 136,198.53 €
TOUR GROUPS 185 205 0 0 74.61 € 73.95 € - 0 € - 0 € 13,801.99 € 15,159.12 € - 0 € - 0 €
LEISURE GROUPS 313 313 151 151 85.82 € 84.63 € 91.21 € 88.49 € 26,861.43 € 26,488.15 € 13,772.97 € 13,361.99 €
CREWS 0 0 0 0 - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 €
Subtotal Groups 1,115 1,602 956 1,321 92.88 € 95.96 € 111.58 € 113.22 € 103,557.78 € 153,728.16 € 106,669.63 € 149,560.52 €
OTHERS 0 9 3 10 - 0 € 56.58 € 140.59 € 81.30 € - 0 € 509.19 € 421.77 € 813.00 €
Subtotal Others 0 9 3 10 - 0 € 56.58 € 140.59 € 81.30 € - 0 € 509.19 € 421.77 € 813.00 €
COMPLEMENTARY 0 0 0 0 - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 €
Subtotal Non Revenue 0 0 0 0 - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 € - 0 €
TOTAL 2,363 4,166 2,248 3,875 97.05 € 103.38 € 112.66 € 112.70 € 229,335.69 € 430,673.24 € 253,267.10 € 436,726.41 €

3. DEMAND CALENDAR

This is an urban hotel that receives leisure and business clients.
The current month is January. As it is low season, this is a difficult month in the city. Yet, there are some dates where the demand is higher.
Competitors:
HOTEL A The best hotel in the city.
HOTEL B Different product style but same category than yours.
HOTEL D Small hotel but with good product.
What price positioning strategy could you use in this scenario to improve your performance? Give your price suggestions below in the template given, including a brief explanation of why you change.
COMPETITORS DATA
DESCRIPTION TRIPADVISOR RANKING LOCATION % OCC JANUARY Last Year
HOTEL A Best hotel in the city 1/250 center 50.00%
HOTEL B Different product style but same category than ours 50/250 outskirts 45.00%
HOTEL D Small hotel but with good product 35/250 close to center 60.00%
MY HOTEL 4* Business hotel 20/250 in front of city's convention venue 56.44%
205 rooms inventory
1/1/21 period starts
1/31/21 period ends
1/5/21 report retrieved
1/15/16 1/4/20 Pick Up 2021 last 7 days 1/5/21 COMPETITOR'S PRICES
Tot % OCC Def +tentatives ARR total room revenue tentatives Def % OCC Def Ind Grp Tot ARR Total room revenue definite Ind Grp Tot % OCC Def + tent ARR total room revenue tentatives definite % OCC Def Ind Grp EVENTS 2021 price HOTEL A HOTEL B HOTEL C HOTEL D HOTEL E HOTEL F
01/01/2021 150 73.17% 90.48 13,572 0 150 73.17% 150 0 0 0.00 0 0 0 0 140 68.29% 96.94 13,572 0 140 68.29% 140 0 NEW YEAR DAY 124 186 104 121 175 CLOSED 75
02/01/2021 179 87.32% 87.18 15,606 0 179 87.32% 179 0 0 0.00 0 0 0 0 168 81.95% 92.89 15,606 0 168 81.95% 150 18 124 119 172 121 160 189 75
03/01/2021 82 40.00% 95.94 7,867 0 82 40.00% 82 0 0 0.00 0 0 0 0 91 44.39% 86.45 7,867 0 91 44.39% 91 0 124 109 172 109 104 104 75
04/01/2021 66 32.20% 80.15 5,290 0 66 32.20% 66 0 5 112.94 565 5 5 0 66 32.20% 81.38 5,371 0 66 32.20% 66 0 124 119 104 89 104 89 75
05/01/2021 57 27.80% 80.87 4,610 0 57 27.80% 57 0 8 109.76 878 8 8 0 66 32.20% 83.15 5,488 0 66 32.20% 46 20 124 99 104 89 94 89 82
06/01/2021 53 25.85% 72.91 3,864 0 53 25.85% 53 0 7 97.02 679 7 7 0 53 25.85% 85.72 4,543 0 53 25.85% 53 0 FOOTBALL MATCH 124 99 114 89 114 109 82
07/01/2021 81 39.51% 69.70 5,645 0 81 39.51% 81 0 13 84.70 1,101 13 13 0 81 39.51% 83.29 6,746 0 81 39.51% 81 0 100 99 104 98 104 154 82
08/01/2021 56 27.32% 82.25 4,606 0 56 27.32% 56 0 8 82.65 661 8 8 0 63 30.73% 83.61 5,267 0 63 30.73% 63 0 100 99 105 89 104 94 75
09/01/2021 40 19.51% 90.16 3,606 0 40 19.51% 40 0 9 85.96 774 9 9 0 53 25.85% 82.64 4,380 0 53 25.85% 52 0 100 99 105 89 94 94 75
10/01/2021 38 18.54% 47.36 1,800 0 38 18.54% 38 0 9 102.46 922 9 9 0 39 19.02% 69.79 2,722 0 39 19.02% 39 0 100 99 105 89 96 89 75
11/01/2021 93 45.37% 61.05 5,678 0 93 45.37% 93 0 15 89.22 1,338 15 15 0 93 45.37% 75.44 7,016 0 93 45.37% 93 0 110 99 CLOSED 109 116 119 75
12/01/2021 114 55.61% 60.55 6,902 0 114 55.61% 114 0 20 83.12 1,662 20 13 7 114 55.61% 75.13 8,565 0 114 55.61% 107 7 115 145 CLOSED 89 125 120 85
13/01/2021 195 95.12% 68.05 13,269 0 195 95.12% 195 0 14 83.62 1,171 14 14 0 195 95.12% 74.05 14,440 0 195 95.12% 108 87 145 160 130 109 150 125 90
14/01/2021 59 28.78% 70.60 4,165 0 59 28.78% 59 0 7 102.27 716 7 7 0 59 28.78% 82.73 4,881 0 59 28.78% 59 0 110 99 95 89 136 85 75
15/01/2021 55 26.83% 74.81 4,115 0 55 26.83% 55 0 5 112.94 565 5 5 0 56 27.32% 83.56 4,679 8 48 23.41% 48 0 104 99 95 89 126 89 75
16/01/2021 35 17.07% 86.65 3,033 0 35 17.07% 35 0 7 108.30 758 7 7 0 40 19.51% 94.77 3,791 0 40 19.51% 40 0 104 99 95 89 175 89 75
17/01/2021 38 18.54% 55.17 2,096 0 38 18.54% 38 0 6 116.33 698 6 6 0 35 17.07% 79.84 2,794 0 35 17.07% 35 0 104 99 95 89 106 85 72
18/01/2021 176 85.85% 78.90 13,887 10 166 80.98% 166 0 3 114.88 345 3 3 0 176 85.85% 80.86 14,231 20 156 76.10% 156 0 129 150 115 105 140 104 80
19/01/2021 182 88.78% 76.51 13,925 20 162 79.02% 162 0 4 106.44 426 4 4 0 182 88.78% 78.85 14,351 20 162 79.02% 162 0 ESSMA SUMMIT 135 160 130 105 145 114 80
20/01/2021 134 65.37% 71.91 9,636 20 114 55.61% 114 0 0 0.00 0 0 0 0 128 62.44% 75.28 9,636 10 118 57.56% 118 0 ESSMA SUMMIT 125 130 115 89 120 104 72
21/01/2021 36 17.56% 86.41 3,111 0 36 17.56% 36 0 2 116.24 232 2 2 0 38 18.54% 87.98 3,343 0 38 18.54% 38 0 115 99 107 89 116 85 72
22/01/2021 57 27.80% 61.25 3,491 0 57 27.80% 57 0 3 112.22 337 3 3 0 45 21.95% 85.06 3,828 20 25 12.20% 25 0 109 99 107 89 116 85 72
23/01/2021 56 27.32% 72.60 4,066 40 16 7.80% 16 0 4 98.61 394 4 4 0 52 25.37% 85.77 4,460 20 32 15.61% 32 0 109 99 107 89 126 85 75
24/01/2021 50 24.39% 69.01 3,450 40 10 4.88% 10 0 4 90.88 364 4 4 0 51 24.88% 74.78 3,814 20 31 15.12% 31 0 109 99 107 89 94 80 72
25/01/2021 61 29.76% 72.31 4,411 40 21 10.24% 21 0 4 89.44 358 4 4 0 67 32.68% 71.17 4,768 0 67 32.68% 67 0 115 99 107 89 136 85 72
26/01/2021 98 47.80% 79.80 7,821 0 98 47.80% 98 0 4 96.53 386 4 4 0 107 52.20% 76.70 8,207 0 107 52.20% 107 0 125 99 113 89 94 89 72
27/01/2021 99 48.29% 83.90 8,306 0 99 48.29% 99 0 3 102.95 309 3 3 0 114 55.61% 75.57 8,615 0 114 55.61% 114 0 125 99 113 89 104 98 72
28/01/2021 99 48.29% 73.39 7,266 0 99 48.29% 99 0 3 92.49 277 3 3 0 98 47.80% 76.97 7,543 0 98 47.80% 98 0 TRENDS FAIR 120 99 113 89 104 80 72
29/01/2021 66 32.20% 48.79 3,220 10 56 27.32% 56 0 4 75.37 301 4 4 0 56 27.32% 62.89 3,522 0 56 27.32% 56 0 TRENDS FAIR 109 99 107 89 146 103 72
30/01/2021 54 26.34% 44.24 2,389 10 44 21.46% 44 0 5 98.66 493 5 5 0 44 21.46% 65.51 2,882 0 44 21.46% 44 0 TRENDS FAIR 109 99 107 98 146 98 72
31/01/2021 43 20.98% 48.89 2,102 0 43 20.98% 43 0 1 63.70 64 1 1 0 34 16.59% 63.70 2,166 0 34 16.59% 34 0 TRENDS FAIR 109 99 107 89 84 76 72
2,602 40.94% 74.10 192,803 190 2,412 37.95% 2,412 0 177 94.77 16,774 177 170 7 2,604 40.98% 80.30 209,094 118 2,486 39.12% 2,353 132
current price new price Why? BUDGET LY 2020 OTB LY 04/01/20
01/01/2021 124 RN 3690 3587 2,602
02/01/2021 124 ARR 74 72.92 74.10
03/01/2021 124 REVENUE 273,060 261,564 192,803
04/01/2021 124
05/01/2021 124
06/01/2021 124
07/01/2021 100
08/01/2021 100
09/01/2021 100
10/01/2021 100
11/01/2021 110
12/01/2021 115
13/01/2021 145
14/01/2021 110
15/01/2021 104
16/01/2021 104
17/01/2021 104
18/01/2021 129
19/01/2021 135
20/01/2021 125
21/01/2021 115
22/01/2021 109
23/01/2021 109
24/01/2021 109
25/01/2021 115
26/01/2021 125
27/01/2021 125
28/01/2021 120
29/01/2021 109
30/01/2021 109
31/01/2021 109

explain here general positioning strategy.

statement of authorship

Statement of authorship
I certify that this assignment is my own work and contains no material which has been submitted as part of an assignment in any institute college or university. Moreover, to the best of my knowledge and belief, it contains no material previously published or written by another person, except where due reference is made in the text of the assignment.
Signed…………………………………………..
Student number………………………………..