Teaching Note—Spreadsheet Modeling to Determine the Optimum Hotel Room Rate for a Short High-Demand Period

Abstract

In 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 profit 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 final model with sufficient 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.