# Excel F1 Night City Race

Topics: Hotel, Variable cost, Costs Pages: 51 (4931 words) Published: January 27, 2013
Additional information, including supplemental material and rights and permission policies, is available at http://ite.pubs.informs.org.

Vol. 11, No. 1, September 2010, pp. 35–42
issn 1532-0545 10 1101 0035

informs

®

doi 10.1287/ited.1100.0053

INFORMS
Transactions on Education

Teaching Note

Spreadsheet Modeling to Determine the Optimum
Hotel Room Rate for a Short High-Demand Period
Thin-Yin Leong, Wee-Leong Lee

School of Information Systems, Singapore Management University, Singapore 178902, Republic of Singapore, {tyleong@smu.edu.sg, wllee@smu.edu.sg}

I

n this article, we describe a business modeling exercise that helps students understand the complex relationship between demand and price. The exercise seeks to determine the optimum pricing, in view of anticipated occupancy response that maximises proﬁt for a hotel. Through the exercise, students are introduced to advanced Excel operations such as Goal Seek and Solver. This exercise goes through a systematic series of basic modeling steps, starting from identifying input variables and performance measures, and building from a basic model to a ﬁnal model with sufﬁcient complexity to represent reality. A problem commonly encountered when modeling real-world problems is the lack of complete information; often, information has to be inferred from what little is available from the past. This is demonstrated in developing the hotel occupancy and rate relationship. To ensure the model is robust, we show how trade-off and sensitivity analyses can be conducted. Key words : spreadsheet modeling; demand management

History : Received: January 2010; accepted: June 2010.

1.

The F1 Night City Race

deciding on the ﬁnal room pricing. Some managers
were considering throwing in extras, such as wine,
cheese, fruits, and possibly tickets to popular tourist
attractions, to make the steep increase in room rates
more palatable.
The objective of this classroom exercise is to demonstrate how to systematically build a spreadsheet model to analyse the interactions between revenue,
cost, and proﬁt while taking into account the pricedemand relationship. The model seeks to determine the optimum hotel room rate.
A large body of work has supported the notion
that room demand (room occupancy rate) tends to be
price insensitive in the long term. Enz et al. (2009)
and Canina and Enz (2008) in their studies have concluded that pricing room rates below those of their competitors resulted in an increased room occupancy
rate but suffer in terms of revenue per room. On the
contrary, pricing room rates above the competitions
results in increasing the revenue per room performance despite a drop in room occupancy. This ﬁnding has been found to be consistent during both good and
bad times and across all market segments from luxury
to economy.
it is important to understand the variables involved.

The scenario for this business spreadsheet modeling exercise is based on the inaugural Formula One Grand Prix (“F1”) night city race, which was going
to be held in Singapore. This would not only help to
boost tourism in the short term but also to increase
the city’s global presence. The small country won the
5-year contract to host one of the F1 annual races
in the downtown business district. This would be
the ﬁrst time ever that a city race would be conducted at night. There was a palpable sense of novelty, excitement, and high tension. New lighting and improvement ideas would have to be tried out to

ensure that the unprecedented race event would take
place successfully.
Clearly, massive costs would be incurred in setting up the infrastructure for the race. The tourism authority had decided to impose a 30% levy on hotel
room charges for a limited time period to hotels
located near the race venue. They believed that, given
the popularity of the event, hotels could charge up
to three...