Calculated fields for hoteliers: ADR, nights, lead time and reconciliation, explained

Your system knows how much money came in yesterday and how many reservations were made. Those are true numbers, and almost useless as they stand. A raw number won’t tell you whether you sold cheap, whether demand is building hard for three weeks out, or whether what you charged matches what you recorded. That’s what calculated fields are for: small operations that take your raw data and turn it into understanding. This essay explains the four that matter most to a hotel, ADR, nights, lead time and reconciliation, in plain language, taking nothing for granted.

The problem with raw numbers

Imagine you open your report and read: room revenue, 4 700 dollars; reservations, 47. Was it a good day? You have no way to know. Maybe you sold many rooms very cheaply, or a few very expensively. Maybe those 47 reservations are for tonight or for two months out. A raw number is a photo with no caption: real, but mute.

A calculated field is a column you don’t capture by hand: the system derives it from others. It isn’t a new fact someone types in, it’s a relationship between data you already have. And that relationship is usually where the meaning lives. The difference between “4 700 dollars came in” and “your average rate was 63 dollars per night” is the difference between looking and understanding.

ADR: the average daily rate

ADR stands for Average Daily Rate. It is, by a wide margin, the most important calculated field in a hotel, and its formula is simple: you take room revenue and divide it by the number of room nights sold.

Watch one detail that trips many people up: ADR is calculated only from room revenue, not from everything the hotel charged. If a guest paid for the room plus breakfast plus the spa, only the room portion goes into ADR. That’s why it is a clean measure of the price you’re really selling the room at, undisturbed by whatever was spent around it.

Back to the illustrative example: if 4 700 dollars of room revenue came in and 75 nights were sold, your ADR was about 63 dollars. Now the number speaks. You can compare it against last week, against the same month last year, or against the hotel next door. An ADR that falls while your occupancy rises may mean you’re filling the hotel by giving the price away: you sell more rooms but each one leaves less.

ADR doesn’t tell you how much you sold. It tells you at what price you sold it. Those are different questions, and the second is the one we almost always forget to ask.On why the average price matters more than the total

Nights: the real unit of the business

Here lies a quiet confusion that costs money. Many people count reservations, but the real unit a hotel runs on isn’t the reservation: it’s the room night. A single reservation from a family staying seven nights is not the same as seven one-night reservations, even though “47 reservations” counts them alike.

The room night is what you actually sell: one room, occupied, for one night. It’s the unit your inventory is measured in (how many nights you have available), your occupancy (how many you sold of what you had) and, as we saw, your ADR (how much you charged for each). Counting reservations instead of nights is like counting shopping carts instead of products: it tells you how many people walked in, not how much they carried out.

• A reservation can be one night or fourteen; adding them all up as “1” hides the real size of each one.
• Occupancy and ADR only make sense in nights, never in reservations.
• Cancellations are better understood as lost nights than as lost reservations: losing a one-night booking is not the same as losing a ten-night one.

Lead time: how far ahead people book

Lead time is the number of days between the moment someone books and the date they arrive. If you book today to arrive in twenty days, your lead time is twenty. It’s a calculated field because the system derives it by subtracting two dates it already has: the booking date and the arrival date.

Why it predicts demand

Lead time is one of the few signals that looks forward rather than back. Most numbers tell you what happened; lead time hints at what’s about to. If this week people are booking much further ahead than usual for a future weekend, that’s an early clue the weekend is coming in strong. If suddenly everyone books at the last minute, your demand is becoming more unpredictable and your window to react is shrinking.

Understanding lead time doesn’t mean setting prices automatically. Spider Data measures and explains the pattern, it shows you that the lead-time curve moved earlier or later, and for which dates; the decision of what to do with that is still yours. But deciding with the curve in view is very different from deciding blind.

Reconciliation: making charged, recorded and received the same

To reconcile is to check that three things that should match, match. In a hotel those three things are usually what was recorded as due to be charged, what was actually charged, and what truly landed in the till or the account. When all three numbers agree, everything reconciles. When they don’t, something needs a look: a charge that wasn’t recorded, a payment that came in but no one logged, cash that never reached the shift count.

Reconciliation is a special calculated field because its most useful value is usually the difference: ideally zero. A reconciliation of zero means “all in order.” A reconciliation that comes out non-zero is a flag: it accuses no one, it just points to where to look. For a business juggling cash and digital, shifts and several payment methods, having the reconciliation calculated live, not discovered the next day when no one remembers, is the difference between fixing a small error and dragging a hole along.

1. Recorded: what the system says should have been charged.
2. Charged: what was actually marked as paid.
3. Received: the real money in the till or the account at the close of the shift.

Raw versus calculated: what each one tells you

The fastest way to see the leap is to set the raw datum next to the calculated field that interprets it. The raw one is true but mute; the calculated one is the one that speaks to you.

The value of having them ready out of the box

Any of these four fields can be calculated by hand. The trouble is that “by hand” means exporting to a spreadsheet, remembering the right formula, not slipping up by dividing revenue by reservations instead of by nights, and doing it all over again next Monday. Every manual calculation is a chance to err and a stretch of time lost.

In Spider Data, ADR, nights, lead time and reconciliation come ready as fields in the report builder: you drag them onto your dashboard just as you’d drag a raw datum, in plain language and without writing a single formula. And because the data is live, not last night’s close, the ADR you see is today’s and the reconciliation you check is the shift that just closed. If you want to take these fields into Power BI, Tableau or Looker, they leave through the API with a token: ready out of the box, but not locked in.

There’s an extra value hidden in standardizing these calculations: when the ADR in one report is computed the same way as in another, you stop arguing over whose number is right. If everyone builds their own formula in their own sheet, sooner or later two “true” reports contradict each other. Having the calculated field out of the box is also agreeing, once and for all, on what the words mean.

Close: the language your data uses to speak

Raw data has been there all along: how much came in, how many reservations, which payments. But silent, it does little good. Calculated fields are the translation: they turn revenue into price, reservations into nights, dates into lead time, loose payments into a reconciliation that lands at zero or lights a warning. They are, literally, the language your data uses to finally speak to you plainly.

And when your data speaks plainly, decisions stop being hunches. Not because a field decides for you, it doesn’t, nor should it, but because you understand what you’re seeing before you decide. That’s the whole difference: not more data, but data that finally means something.