Analyze prices and competitors now. Try Smartpricing Free!

How to mine your accommodation's data history and predict the future (+ Excel sheet)

An introductory guide to collecting historical data for lodging facilities without using a PMS. With an Excel sheet for calculating the reservations history.

How to use your accommodation's data history and predict the future - Smartpricing

Do you run a hospitality business? Are you wondering if there is an easy way to forecast and improve profits?

Indeed, there is!

The activity of collecting and processing historical performance data is the basis of Revenue Management and serves to do just that. In the next few lines we will see how to derive your record without relying on a property management system (PMS).

What is historical data and why is it important?

The "history" of a hospitality business is the set of past performance data. This data may be more or less detailed depending on how it was recorded, but potentially any number from the past can be a useful source of information.

Through analysis of such information, which is often not exploited as it should be, it is possible to better understand the nature of the target market and more accurately predict its future behavior.

The targetmarket is the set of potential customers and competitors of your accommodation, and it is important to know it as thoroughly as possible. A business strategy that leverages knowledge of the market and anticipates its trends is immensely more effective and profitable than one that simply follows and suffers it.

Those who can make the most accurate forecast have a greater strategic advantage over competitors on their side, but getting to that accuracy requires analyzing and collecting lots of data.

So let's see which data points are indispensable and how to collect them efficiently with the help of our Excel file.


Production is nothing more than the amount of revenue produced by your hospitality business. It is what is commonly referred to as "turnover," and you can use this term too, but pay attention to one aspect: when in revenue we talk about earnings, we never refer to the date or period of actual "billing" or "collection," but to the date of production.

Example: if your room or apartment is on the market at €100 per day and a customer staying from June 3 to June 10 pays you €700 at check-out on June 10, for revenue optimization purposes you are not interested in recording €700 under June 10, but €100 under 3,4,5,6,7,8 and 9. On June 10 indeed, if no other customer does arrive you will score €0!

Since the purpose of Revenue Management is to improve the sale of your service, all merely administrative aspects are not taken into consideration.

What you should therefore do following the criterion from this example is to mark the production for each day of the calendar year on the Excel sheet. If you have several room/apartment types, ideally you should report differentiated figures for each type.

You may also decide to simplify the work by recording production on a weekly or monthly basis instead of daily, or by eliminating the breakdown by room types. The resulting data will certainly be more approximate, but it is still better than not recording it at all.

In this case you will find yourself with reservations at the turn of the week or month and will need to determine where to mark production.

A common method is to conventionally allocate production based on the check-out or check-in date, but even then you will not be entirely safe from some alterations.

Example: if you have a reservation from June 20 to July 1 and you choose the check-out date for the production allocation on the month, you will practically transfer 10 days of monthly production from June to July with the risk of having very insignificant data to be used in the future.

In these cases it is better to use common sense and decide where to assign production on a case by case basis.


Another cornerstone of the record is occupancy, that is, how many rooms/nights you have sold. This concept is fairly simple to derive; you just record the number of rooms sold and the number of rooms available for each reporting period.

The number of available rooms (also called the "quota") is used to establish the maximum occupancy ceiling for the period and together with the rooms sold makes up the occupancy rate formula, one of the key performance indices in Revenue Management.

Rooms Sold/Available Rooms = % Occupancy

While in theory the available rooms are always the same, in practice it happens that some are temporarily not for sale (for example, due to technical failure). In these cases, adjusting the number of available rooms serves to correctly assess the occupancy rate achieved and objectively evaluate the performance of the facility.

If you choose to record data on a weekly or monthly basis, you will also need to decide how to handle "piggyback" bookings, possibly opting for a mode consistent with that chosen for production.

Example: if you choose the check-out date for production allocation on a monthly basis, then you should do the same for occupancy. That way, you will have consistent performance indices (we will look at them in a moment).

Key performance indices (KPIs)

At this point you have collected the most important data and can move on to the analysis of it.

The first step in the analysis is to turn the data into a "performance index" (also called a KPI, "Key Performance Indicator"). The basic performance indexes in revenue management are:

  • Occupancy Rate
  • ADR
  • RevPAR

We have already covered the Occupancy Rate in the previous section, now let's look at the other two.


The "Average Daily Rate" (ADR) tells us at what average amount we are selling our rooms during the period in question. This parameter is mainly used to measure your performance against your target market, competitors first and foremost.

ADR Formula: Production / Rooms Sold = ADR


Revenue Per Available Room (RevPAR) is the most important performance metric for two reasons:

  • it summarizes the effectiveness of your business strategy in a single value
  • it allows you to compare the results of different accommodations regardless of size and, within certain limits, category

You can obtain this index through two formulas:

ADR x %Occupancy = RevPAR


Facility Production / Available Rooms = RevPAR

RevPAR is for all intents and purposes the absolute performance index for your corporate business strategy. In fact, ADR and occupancy, when taken individually, offer no certainty that as they increase, turnover will also increase. Instead, RevPAR moves in direct proportion to overall turnover.

A certain goal of your strategy therefore must be to increase the value of your facility's RevPAR.

Calculate profits

As you well know, a business is not only made up of numbers in the positive range (unfortunately), but also many numbers in the negative range, i.e., expenses.

To make the data record an effective tool, it is important to relate revenues to expenses. Without going into too much detail on this topic, which you can learn more about by reading our article on how to calculate costs in your facility, let's just say that even for costs you have to do a careful job of collecting data.

Once you have collected this data, you can obtain indexes equivalent to those seen for revenues and correlate them to measure the profitability of your business.

The main ones are CostPAR and GopPAR.

CostPAR (the dark side of RevPAR)

CostPAR is the Average Cost Per Room Per Night (Cost Per Available Room).

You can get it by calculating the total costs for a period (day, week, month) and dividing it by the number of available rooms.

Operating Costs/Available Rooms = CostPAR

By comparing CostPAR with RevPAR you can get GopPAR, that is, your profits.


Also called Gross Operating Margin per available room, GopPAR is really the ultimate index because it gives you an immediate glimpse into the difference between costs and revenues. 

Like RevPAR it represents an absolute measure of performance, but unlike RevPAR it does not indicate the facility's ability to generate revenue, but rather profit.

If GopPAR rises, your business's profit increases, while if GopPAR falls, profit falls. 

To get the GopPAR you can divide the difference between revenues and operating costs by the number of available rooms:

(Revenues - Costs) / Available rooms = GopPAR

or subtract CostPAR from RevPAR

RevPAR - CostPAR = GopPAR

Getting this information without a management system is certainly costly, but it is also critical to running your facility effectively.

Only by looking at the data can you become aware of your performance, make improvements during less profitable periods, and intuit how bookings will evolve in the future.

To make this task easier, download our Excel file to calculate your accommodation's historical record!