Case Study on Revenue Management

The answer should be in the excel sheet as per the instructions in the pdf file

1

PROJECT OUTLINE

Assessment title: Case Study on Revenue Management

Programme: MEIHM

Course name: Revenue and Pricing Management

Course number: M 9116

Faculty responsible: Ms. Montero

Hand out date: 4th January 2023

Due date: 22nd January 2023, 23:59hrs.

Weighting: 60%

Project Overview: The students should answer a case study on Revenue Management

provided by the faculty. The students will need to provide a report with

the answer to exercises about RM recommendations and reports.

Overall aim: To enable students to demonstrate their understanding of the importance

and function of Revenue Management.

Learning outcomes:

1. Identify the principles of revenue and yield management.

2. Discuss, demonstrate and apply demand management strategies, pricing, capacity
management, discount allocation, forecasting and other associated procedures essential

to successful Yield Management.

Organization and methodology

• Write the answers of the case study on revenue management in report template

provided by the faculty. The report has to be in Excel format.

• Deliver the project in the section provided in Moodle before the deadline. If you have
any problem with the submission in the platform, make sure that you send a copy by

mail to your teacher (rmontero@lesroches.es) before the deadline.

mailto:rmontero@lesroches.es

2

Include:

1. RGI: calculate all the KPIs for all the hotels with the given data. After that provide a
brief analysis explaining results of my hotel 1 and my hotel 2, positioning strategy of
these hotels and if it is correct, in the space provided in the documents.

2. PROYECTION EXERCISE: you need to answer the questions in the given space and in
the “Projection Data” sheet you need to fill in the grey columns doing a projection of
the given month using your knowledge about the use of pick-up.

3. DEMAND CALENDAR: according to the scenario given in the calendar you need to
explain what your positioning strategy would be to improve the performance of the
month of January. Please answer in the space given and use the template given to
provide your price suggestions.

Assessment tasks & weighting:

▪ TASKS: Written project 90%

▪ English Skills

10%

(Rubric over page)

Assessment
Tasks

Excellent Very good Good Pass Fail Zero Weight

100% 85% 70% 60% 40% 0% %

exercise 1

All results
correct and
coherent
supported
explanation.
Correct use
of excel.

numerical
results
correct but
weak
analysis, too
theoretical
approach.
Correct use
of excel.

results with
slight
mistakes, but
good
analysis.
Incorrect use
of excel.

Right
calculations
but with
wrong use of
data

Wrong
results and
wrong
answers

No answers
included

25%

exercise 2

All results
correct and
coherent
supported
explanation.
Correct use
of excel.

numerical
results
correct but
weak
analysis, too
theoretical
approach.
Correct use
of excel.

numerical
results
correct but
weak
analysis, too
theoretical
approach.
Incorrect use
of excel.

calculations
with good
use of
formulas but
with slight
calculation
problems
that
influence
analysis

Major errors
and wrong or
non-existent
analysis.

No answers
included.

37%

exercise 4

Coherent
strategy
supported by
data

Coherent
strategy with
slight error in
supporting
data

Coherent
strategy with
weak support
of data

Good
strategy with
no support

wong
strategy with
wrong
support

No answers
included

25%

English

Fulfils the
requirements
and follows
an
acceptable
style in the
correct
manner

Fulfils most
of the
requirements
and follows
an
acceptable
style in a
generally
correct
manner.

Attempts to
follow an
acceptable
style and
fulfils most of
the
requirements

Generally,
attempts to
conform to
requirements
and
adequately
executed

Does not
meet a
number of
key
requirements
and fails to
follow an
acceptable
style.

No
references

and
disastrous

presentation.

10%

3

Professional
presentation

Fulfils the
requirements
and follows
an
acceptable
style in the
correct
manner

Fulfils most
of the
requirements
and follows
an
acceptable
style in a
generally
correct
manner.

Attempts to
follow an
acceptable
style

Generally,
attempts to
conform to
requirements
and
adequately
executed

Does not
meet a
number of
key
requirements
and fails to
follow an
acceptable
style.

disastrous
presentation.

3%

Common skills: assessed (bold) or developed (italics):

MANAGING AND DEVELOPING SELF
1.Manages
own role and
responsibilities

2. Manages
own time in
achieving
objectives.

3.Undertakes
personal and
career
development

4. Transfers skills
gained to new
and changing
situations and
contexts.

5.Uses a
range of
thought
processes

WORKING WITH AND RELATING TO
OTHERS

6.Treats
others’ values,
beliefs and
opinions with
respect

COMMUNICATING

9.Receives
and responds
to a variety of
information

10.Presents
information in a
variety of
visual forms

11.Communicates
in writing

MANAGING TASKS AND SOLVING
PROBLEMS

13.Uses
information
sources

15.Identifies and
solves routine
and non-routine
problems

English Skills Full Achievement

100%-85%

Good

84%-75%

Reasonable

74%-65%

Adequate

64%-60%

Fail

59%-0%

Coherence/Structure/Flow
Writing is structured
Has a beginning & end
Ideas are well connected
Clear flow – easy to read

Effectively organised.
Well-structured with
good flow

Generally well
organised.
Sometimes ideas
are not linked
together.

Structure could be
better with clearer
development for
better reading

Shows some
organisation – just
enough to pass

Not clearly
organised
Difficult to follow

Relevant Use of Language
Writing uses specific language
used in hospitality asked for by
assignment

Minimum errors –
wide range of
appropriate
vocabulary

Good range of
appropriate
vocabulary

More than adequate
range used

Uses limited variety
of terms, and is often
repetitive

Poor use or no
use of specific
terms

Appropriate Tone
Required use of either formal or
informal style of writing

Appropriate tone
throughout and
reflects assignment
requirements

Tone on the whole
appropriate

Some inconsistent
use of language

Not always
appropriate therefore
not entirely
successful

Inappropriate
tone used in
most of the task

Clarity & Accuracy
Writing is clear with no
confusing areas

Extremely clear
Well developed &
expanded upon

Slight confusion due
to minor inaccuracy.
Ideas on the whole
developed well.

Occasional confusion
due to ideas not
being clear but
message is given.

At times, difficult to
follow and/or
repetitive

Difficult to read
due to
inaccuracies.

Impact/Effectiveness on Reader
Impact on reader

Excellent

Very Positive

Positive

Satisfactory – just a
pass

Poor effect on
reader

4

BECOMING NUMERATE AND USING
TECHNOLOGY

16.Applies
numerical
skills and
techniques

Special instructions:
Ensure all tasks are complete by the deadlines indicated. If the task is not in the platform by

the deadline date, the grade will be a 0.

Plagiarism
Plagiarism is the act of presenting another’s ideas or words as one’s own. Cheating includes,

but is not limited to, the intentional falsification or fabrication of any academic activity,

unauthorized copying of another person’s work, or aiding and abetting any such acts.
Particular care must be taken when presenting information that has been obtained from an

internet site. Should this information not be correctly referenced, then you are guilty of
plagiarism and will be penalized accordingly.

With respect to projects/assignments, faculty reserves the right to randomly call upon any

student and ask them to defend their work orally.
Any assignment/exam which is found to contain plagiarism will automatically be awarded a

grade of 0, and an e-mail will be sent to the student or the student’s parents/tutors/sponsors.
Depending on the circumstances, additional penalties could be imposed (see LRM Academic

Regulations, Section 11).

Statement of authorship
Following the title page of your assignment there should be a page on which you sign a
statement that the work included in the assignment is your own work except where
appropriately referenced. The following statement should be used:

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………………………………..

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………………………………..